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
|
--
-- 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);
insert into t (i,s) values (1956,475);
-- select a subset of the columns
select i from t;
-- select all columns in order
select i,s from t;
-- select columns out of order
select s,i from t;
-- select with repeating columns
select i,i,s,s,i,i from t;
-- select with constants
select 10 from t;
-- select with table name
select t.i from t;
-- select with correlation name
select b.i from t b;
-- select *
select * from t;
-- select * and constants and columns
select *, 10, i from t;
-- select correlation name dot star
select b.* from t b;
-- select table name dot star
select t.* from t;
-- believe it or not, the next query is valid
(select * from t);
-- negative testing
-- non-boolean where clause
-- (only put here due to small size of this test)
select * from t where i;
-- invalid correlation name for "*"
select asdf.* from t;
-- Column aliases are not supported in WHERE clause:
select s as col_s from t where col_s = 475;
-- Column references in WHERE clause always refer to the base table column:
select s as i from t where s = 475;
select s as i from t where i = 1956;
-- cleanup
drop table t;
-- Beetle 5019. We had a failure in V5.1.20.
CREATE SCHEMA CONTENT;
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);
ALTER TABLE CONTENT.CONTENT ADD CONSTRAINT CONTENT_ID PRIMARY KEY (ID);
CREATE TABLE CONTENT.STYLE (ID INTEGER NOT NULL,DESCRIPTION VARCHAR(128) NOT NULL);
ALTER TABLE CONTENT.STYLE ADD CONSTRAINT STYLE_ID PRIMARY KEY (ID);
CREATE TABLE CONTENT.CONTENT_STYLE (CONTENT_ID INTEGER NOT NULL, STYLE_ID INTEGER NOT NULL);
ALTER TABLE CONTENT.CONTENT_STYLE ADD CONSTRAINT CONTENTSTYLEID PRIMARY KEY (CONTENT_ID, STYLE_ID);
CREATE TABLE CONTENT.KEYGEN (KEYVAL INTEGER NOT NULL, KEYNAME VARCHAR(256) NOT NULL);
ALTER TABLE CONTENT.KEYGEN ADD CONSTRAINT PK_KEYGEN PRIMARY KEY (KEYNAME);
CREATE TABLE CONTENT.RATING (ID INTEGER NOT NULL,RATING DOUBLE PRECISION NOT NULL,ENTRIES DOUBLE PRECISION NOT NULL);
ALTER TABLE CONTENT.RATING ADD CONSTRAINT PK_RATING PRIMARY KEY (ID);
INSERT INTO CONTENT.STYLE VALUES (1, 'BIRD');
INSERT INTO CONTENT.STYLE VALUES (2, 'CAR');
INSERT INTO CONTENT.STYLE VALUES (3, 'BUILDING');
INSERT INTO CONTENT.STYLE VALUES (4, 'PERSON');
INSERT INTO CONTENT.CONTENT values(1, 'djd', CURRENT DATE, 'http://url.1', 'title1', 'desc1', 100, 100);
INSERT INTO CONTENT.CONTENT values(2, 'djd', CURRENT DATE, 'http://url.2', 'title2', 'desc2', 100, 100);
INSERT INTO CONTENT.CONTENT values(3, 'djd', CURRENT DATE, 'http://url.3', 'title3', 'desc3', 100, 100);
INSERT INTO CONTENT.CONTENT values(4, 'djd', CURRENT DATE, 'http://url.4', 'title4', 'desc4', 100, 100);
INSERT INTO CONTENT.CONTENT values(5, 'djd', CURRENT DATE, 'http://url.5', 'title5', 'desc5', 100, 100);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(1,1);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(1,2);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(2,1);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(2,4);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,3);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,4);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(3,1);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(4,4);
INSERT INTO CONTENT.CONTENT_STYLE VALUES(5,1);
INSERT INTO CONTENT.RATING VALUES(1, 4.5, 1);
INSERT INTO CONTENT.RATING VALUES(2, 4.0, 1);
INSERT INTO CONTENT.RATING VALUES(3, 3.9, 1);
INSERT INTO CONTENT.RATING VALUES(4, 4.1, 1);
INSERT INTO CONTENT.RATING VALUES(5, 4.0, 1);
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;
drop table content.rating;
drop table content.content_style;
drop table content.content;
drop table content.style;
drop table content.keygen;
drop schema content restrict;
-- 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);
create schema s1;
create table s1.t1 (id int not null primary key, d2 int);
select * from s1.t1, "S1.T1" where s1.t1.id = "S1.T1".id;
select s1.t1.* from "S1.T1";
select "S1.T1".* from s1.t1;
select * from "S1.T1" , APP."S1.T1";
select "S1.T1".d1 from "S1.T1", APP."S1.T1";
select SYS."S1.T1".d1 from "S1.T1";
select SYS."S1.T1".d1 from "S1.T1" t1;
|
