ij> ---- this test shows the current supported select functionality--create table t(i int, s smallint);0 rows inserted/updated/deletedij> insert into t (i,s) values (1956,475);1 row inserted/updated/deletedij> -- select a subset of the columnsselect i from t;I          -----------1956       ij> -- select all columns in orderselect i,s from t;I          |S     ------------------1956       |475   ij> -- select columns out of orderselect s,i from t;S     |I          ------------------475   |1956       ij> -- select with repeating columnsselect i,i,s,s,i,i from t;I          |I          |S     |S     |I          |I          -------------------------------------------------------------1956       |1956       |475   |475   |1956       |1956       ij> -- select with constantsselect 10 from t;1          -----------10         ij> -- select with table nameselect t.i from t;I          -----------1956       ij> -- select with correlation nameselect 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.ij> -- select correlation name dot starselect b.* from t b;I          |S     ------------------1956       |475   ij> -- select table name dot starselect 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> -- cleanupdrop table t;0 rows inserted/updated/deletedij> -- Beetle 5019.  We had a failure in V5.1.20.CREATE SCHEMA CONTENT;0 rows inserted/updated/deletedij> 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/deletedij> ALTER TABLE CONTENT.CONTENT ADD CONSTRAINT CONTENT_ID PRIMARY KEY (ID);0 rows inserted/updated/deletedij> CREATE TABLE CONTENT.STYLE (ID INTEGER NOT NULL,DESCRIPTION VARCHAR(128) NOT NULL);0 rows inserted/updated/deletedij> ALTER TABLE CONTENT.STYLE ADD CONSTRAINT STYLE_ID PRIMARY KEY (ID);0 rows inserted/updated/deletedij> CREATE TABLE CONTENT.CONTENT_STYLE  (CONTENT_ID INTEGER NOT NULL, STYLE_ID INTEGER NOT NULL);0 rows inserted/updated/deletedij> ALTER TABLE CONTENT.CONTENT_STYLE ADD CONSTRAINT CONTENTSTYLEID PRIMARY KEY (CONTENT_ID, STYLE_ID);0 rows inserted/updated/deletedij> CREATE TABLE CONTENT.KEYGEN (KEYVAL INTEGER NOT NULL, KEYNAME VARCHAR(256) NOT NULL);0 rows inserted/updated/deletedij> ALTER TABLE CONTENT.KEYGEN  ADD CONSTRAINT PK_KEYGEN PRIMARY KEY (KEYNAME);0 rows inserted/updated/deletedij> CREATE TABLE CONTENT.RATING  (ID INTEGER NOT NULL,RATING DOUBLE PRECISION NOT NULL,ENTRIES DOUBLE PRECISION NOT NULL);0 rows inserted/updated/deletedij> ALTER TABLE CONTENT.RATING ADD CONSTRAINT PK_RATING PRIMARY KEY (ID);0 rows inserted/updated/deletedij> INSERT INTO CONTENT.STYLE VALUES (1, 'BIRD');1 row inserted/updated/deletedij> INSERT INTO CONTENT.STYLE VALUES (2, 'CAR');1 row inserted/updated/deletedij> INSERT INTO CONTENT.STYLE VALUES (3, 'BUILDING');1 row inserted/updated/deletedij> INSERT INTO CONTENT.STYLE VALUES (4, 'PERSON');1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT values(1, 'djd', CURRENT DATE, 'http://url.1', 'title1', 'desc1', 100, 100);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT values(2, 'djd', CURRENT DATE, 'http://url.2', 'title2', 'desc2', 100, 100);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT values(3, 'djd', CURRENT DATE, 'http://url.3', 'title3', 'desc3', 100, 100);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT values(4, 'djd', CURRENT DATE, 'http://url.4', 'title4', 'desc4', 100, 100);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT values(5, 'djd', CURRENT DATE, 'http://url.5', 'title5', 'desc5', 100, 100);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(1,1);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(1,2);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(2,1);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(2,4);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,3);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,4);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,1);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(4,4);1 row inserted/updated/deletedij> INSERT INTO CONTENT.CONTENT_STYLE VALUES(5,1);1 row inserted/updated/deletedij> INSERT INTO CONTENT.RATING VALUES(1, 4.5, 1);1 row inserted/updated/deletedij> INSERT INTO CONTENT.RATING VALUES(2, 4.0, 1);1 row inserted/updated/deletedij> INSERT INTO CONTENT.RATING VALUES(3, 3.9, 1);1 row inserted/updated/deletedij> INSERT INTO CONTENT.RATING VALUES(4, 4.1, 1);1 row inserted/updated/deletedij> INSERT INTO CONTENT.RATING VALUES(5, 4.0, 1);1 row inserted/updated/deletedij> 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/deletedij> drop table content.content_style;0 rows inserted/updated/deletedij> drop table content.content;0 rows inserted/updated/deletedij> drop table content.style;0 rows inserted/updated/deletedij> drop table content.keygen;0 rows inserted/updated/deletedij> drop schema content restrict;0 rows inserted/updated/deletedij> -- This is to test quotes handling in tables.-- This tests patch for Derby 13create table "S1.T1" (id int not null primary key, d1 int);0 rows inserted/updated/deletedij> create schema s1;0 rows inserted/updated/deletedij> create table s1.t1 (id int not null primary key, d2 int);0 rows inserted/updated/deletedij> 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>