1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
|
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>
|
