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 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>