Google Git
Sign in
apache / derby / 08cc0f89fe077234078430bf9b93ebc9dfd70ac5 / . / java / testing / org / apache / derbyTesting / functionTests / master / select.out
blob: 26e8d91059c9f7ed455586c6b07bc83883a5353d [file] [log] [blame]
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
--
-- this test shows the current supported select functionality
--
create table t(i int, s smallint);
0 rows inserted/updated/deleted
ij> insert into t (i,s) values (1956,475);
1 row inserted/updated/deleted
ij> -- select a subset of the columns
select i from t;
I
-----------
1956
ij> -- select all columns in order
select i,s from t;
I |S
------------------
1956 |475
ij> -- select columns out of order
select s,i from t;
S |I
------------------
475 |1956
ij> -- select with repeating columns
select i,i,s,s,i,i from t;
I |I |S |S |I |I
-------------------------------------------------------------
1956 |1956 |475 |475 |1956 |1956
ij> -- select with constants
select 10 from t;
1
-----------
10
ij> -- select with table name
select t.i from t;
I
-----------
1956
ij> -- select with correlation name
select b.i from t b;
I
-----------
1956
ij> -- select *
select * from t;
I |S
------------------
1956 |475
ij> -- select * and constants and columns
select *, 10, i from t;
ERROR 42X01: Syntax error: Encountered "," at line 2, column 9.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> -- select correlation name dot star
select b.* from t b;
I |S
------------------
1956 |475
ij> -- select table name dot star
select t.* from t;
I |S
------------------
1956 |475
ij> -- believe it or not, the next query is valid
(select * from t);
I |S
------------------
1956 |475
ij> -- negative testing
-- non-boolean where clause
-- (only put here due to small size of this test)
select * from t where i;
ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> -- invalid correlation name for "*"
select asdf.* from t;
ERROR 42X10: 'ASDF' is not an exposed table name in the scope in which it appears.
ij> -- Column aliases are not supported in WHERE clause:
select s as col_s from t where col_s = 475;
ERROR 42X04: Column 'COL_S' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COL_S' is not a column in the target table.
ij> -- Column references in WHERE clause always refer to the base table column:
select s as i from t where s = 475;
I
------
475
ij> select s as i from t where i = 1956;
I
------
475
ij> -- cleanup
drop table t;
0 rows inserted/updated/deleted
ij> -- Beetle 5019. We had a failure in V5.1.20.
CREATE SCHEMA CONTENT;
0 rows inserted/updated/deleted
ij> CREATE TABLE CONTENT.CONTENT (ID INTEGER NOT NULL, CREATOR VARCHAR(128) NOT NULL, CREATION_DATE DATE NOT NULL, URL VARCHAR(256) NOT NULL, TITLE VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(512) NOT NULL, HEIGHT INTEGER NOT NULL, WIDTH INTEGER NOT NULL);
0 rows inserted/updated/deleted
ij> ALTER TABLE CONTENT.CONTENT ADD CONSTRAINT CONTENT_ID PRIMARY KEY (ID);
0 rows inserted/updated/deleted
ij> CREATE TABLE CONTENT.STYLE (ID INTEGER NOT NULL,DESCRIPTION VARCHAR(128) NOT NULL);
0 rows inserted/updated/deleted
ij> ALTER TABLE CONTENT.STYLE ADD CONSTRAINT STYLE_ID PRIMARY KEY (ID);
0 rows inserted/updated/deleted
ij> CREATE TABLE CONTENT.CONTENT_STYLE (CONTENT_ID INTEGER NOT NULL, STYLE_ID INTEGER NOT NULL);
0 rows inserted/updated/deleted
ij> ALTER TABLE CONTENT.CONTENT_STYLE ADD CONSTRAINT CONTENTSTYLEID PRIMARY KEY (CONTENT_ID, STYLE_ID);
0 rows inserted/updated/deleted
ij> CREATE TABLE CONTENT.KEYGEN (KEYVAL INTEGER NOT NULL, KEYNAME VARCHAR(256) NOT NULL);
0 rows inserted/updated/deleted
ij> ALTER TABLE CONTENT.KEYGEN ADD CONSTRAINT PK_KEYGEN PRIMARY KEY (KEYNAME);
0 rows inserted/updated/deleted
ij> CREATE TABLE CONTENT.RATING (ID INTEGER NOT NULL,RATING DOUBLE PRECISION NOT NULL,ENTRIES DOUBLE PRECISION NOT NULL);
0 rows inserted/updated/deleted
ij> ALTER TABLE CONTENT.RATING ADD CONSTRAINT PK_RATING PRIMARY KEY (ID);
0 rows inserted/updated/deleted
ij> INSERT INTO CONTENT.STYLE VALUES (1, 'BIRD');
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.STYLE VALUES (2, 'CAR');
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.STYLE VALUES (3, 'BUILDING');
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.STYLE VALUES (4, 'PERSON');
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT values(1, 'djd', CURRENT DATE, 'http://url.1', 'title1', 'desc1', 100, 100);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT values(2, 'djd', CURRENT DATE, 'http://url.2', 'title2', 'desc2', 100, 100);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT values(3, 'djd', CURRENT DATE, 'http://url.3', 'title3', 'desc3', 100, 100);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT values(4, 'djd', CURRENT DATE, 'http://url.4', 'title4', 'desc4', 100, 100);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT values(5, 'djd', CURRENT DATE, 'http://url.5', 'title5', 'desc5', 100, 100);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(1,1);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(1,2);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(2,1);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(2,4);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,3);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,4);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,1);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(4,4);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(5,1);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.RATING VALUES(1, 4.5, 1);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.RATING VALUES(2, 4.0, 1);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.RATING VALUES(3, 3.9, 1);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.RATING VALUES(4, 4.1, 1);
1 row inserted/updated/deleted
ij> INSERT INTO CONTENT.RATING VALUES(5, 4.0, 1);
1 row inserted/updated/deleted
ij> select S.DESCRIPTION, FAV.MAXRATE, C.TITLE, C.URL FROM CONTENT.RATING R, CONTENT.CONTENT C, CONTENT.STYLE S, CONTENT.CONTENT_STYLE CS, (select S.ID, max(rating) from CONTENT.RATING R, CONTENT.CONTENT C, CONTENT.STYLE S, CONTENT.CONTENT_STYLE CS group by S.ID) AS FAV(FID,MAXRATE) where R.ID = C.ID AND C.ID = CS.CONTENT_ID AND CS.STYLE_ID = FAV.FID AND FAV.FID = S.ID AND FAV.MAXRATE = R.RATING;
DESCRIPTION |MAXRATE |TITLE |URL
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BIRD |4.5 |title1 |http://url.1
CAR |4.5 |title1 |http://url.1
ij> drop table content.rating;
0 rows inserted/updated/deleted
ij> drop table content.content_style;
0 rows inserted/updated/deleted
ij> drop table content.content;
0 rows inserted/updated/deleted
ij> drop table content.style;
0 rows inserted/updated/deleted
ij> drop table content.keygen;
0 rows inserted/updated/deleted
ij> drop schema content restrict;
0 rows inserted/updated/deleted
ij> -- This is to test quotes handling in tables.
-- This tests patch for Derby 13
create table "S1.T1" (id int not null primary key, d1 int);
0 rows inserted/updated/deleted
ij> create schema s1;
0 rows inserted/updated/deleted
ij> create table s1.t1 (id int not null primary key, d2 int);
0 rows inserted/updated/deleted
ij> select * from s1.t1, "S1.T1" where s1.t1.id = "S1.T1".id;
ID |D2 |ID |D1
-----------------------------------------------
ij> select s1.t1.* from "S1.T1";
ERROR 42X10: 'S1.T1' is not an exposed table name in the scope in which it appears.
ij> select "S1.T1".* from s1.t1;
ERROR 42X10: 'S1.T1' is not an exposed table name in the scope in which it appears.
ij> select * from "S1.T1" , APP."S1.T1";
ERROR 42X09: The table or alias name 'APP.S1.T1' is used more than once in the FROM list.
ij> select "S1.T1".d1 from "S1.T1", APP."S1.T1";
ERROR 42X09: The table or alias name 'APP.S1.T1' is used more than once in the FROM list.
ij> select SYS."S1.T1".d1 from "S1.T1";
ERROR 42X04: Column 'SYS.S1.T1.D1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SYS.S1.T1.D1' is not a column in the target table.
ij> select SYS."S1.T1".d1 from "S1.T1" t1;
ERROR 42X04: Column 'SYS.S1.T1.D1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SYS.S1.T1.D1' is not a column in the target table.
ij>