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
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
|
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 tests that SQL92 formally reserved words are now unreserved
--
-- INTERVAL
create table interval(interval int);
0 rows inserted/updated/deleted
ij> prepare interval as 'select * from interval';
ij> execute interval;
INTERVAL
-----------
ij> create index interval on interval(interval);
0 rows inserted/updated/deleted
ij> drop table interval;
0 rows inserted/updated/deleted
ij> remove interval;
ij> -- MODULE
create table module(module int);
0 rows inserted/updated/deleted
ij> prepare module as 'select * from module';
ij> execute module;
MODULE
-----------
ij> create index module on module(module);
0 rows inserted/updated/deleted
ij> drop table module;
0 rows inserted/updated/deleted
ij> remove module;
ij> -- NAMES
create table names(names int);
0 rows inserted/updated/deleted
ij> prepare names as 'select * from names';
ij> execute names;
NAMES
-----------
ij> create index names on names(names);
0 rows inserted/updated/deleted
ij> drop table names;
0 rows inserted/updated/deleted
ij> remove names;
ij> -- PRECISION
create table precision(precision int);
0 rows inserted/updated/deleted
ij> prepare precision as 'select * from precision';
ij> execute precision;
PRECISION
-----------
ij> create index precision on precision(precision);
0 rows inserted/updated/deleted
ij> drop table precision;
0 rows inserted/updated/deleted
ij> remove precision;
ij> -- POSITION
create table position(position int);
0 rows inserted/updated/deleted
ij> prepare position as 'select * from position';
ij> execute position;
POSITION
-----------
ij> create index position on position(position);
0 rows inserted/updated/deleted
ij> drop table position;
0 rows inserted/updated/deleted
ij> remove position;
ij> -- SECTION
create table section(section int);
0 rows inserted/updated/deleted
ij> prepare section as 'select * from section';
ij> execute section;
SECTION
-----------
ij> create index section on section(section);
0 rows inserted/updated/deleted
ij> drop table section;
0 rows inserted/updated/deleted
ij> remove section;
ij> -- VALUE
create table value(value int);
0 rows inserted/updated/deleted
ij> prepare value as 'select * from value';
ij> execute value;
VALUE
-----------
ij> create index value on value(value);
0 rows inserted/updated/deleted
ij> drop table value;
0 rows inserted/updated/deleted
ij> remove value;
ij> -- DATE
create table date (date date);
0 rows inserted/updated/deleted
ij> insert into date(date) values (date('2001-01-01'));
1 row inserted/updated/deleted
ij> select date from date;
DATE
----------
2001-01-01
ij> select date( '2001-02-02'), date "2001-02-02" from date;
1 |2001-02-02
---------------------
2001-02-02|2001-01-01
ij> select date date from date;
DATE
----------
2001-01-01
ij> select date as date from date;
DATE
----------
2001-01-01
ij> select date.date as date from date date;
DATE
----------
2001-01-01
ij> select date.date as date from date as date;
DATE
----------
2001-01-01
ij> delete from date where date = date('2001-01-01');
1 row inserted/updated/deleted
ij> create index date on date(date);
0 rows inserted/updated/deleted
ij> drop table date;
0 rows inserted/updated/deleted
ij> remove date;
IJ ERROR: Unable to establish prepared statement DATE@CONNECTION0
ij> -- TIME
create table time (time time);
0 rows inserted/updated/deleted
ij> insert into time(time) values (time('01:01:01'));
1 row inserted/updated/deleted
ij> select time from time;
TIME
--------
01:01:01
ij> select time( '02:02:02'), time "02:02:02" from time;
1 |02:02:02
-----------------
02:02:02|01:01:01
ij> select time time from time;
TIME
--------
01:01:01
ij> select time as time from time;
TIME
--------
01:01:01
ij> select time.time as time from time time;
TIME
--------
01:01:01
ij> select time.time as time from time as time;
TIME
--------
01:01:01
ij> delete from time where time = time('01:01:01');
1 row inserted/updated/deleted
ij> create index time on time(time);
0 rows inserted/updated/deleted
ij> drop table time;
0 rows inserted/updated/deleted
ij> remove time;
IJ ERROR: Unable to establish prepared statement TIME@CONNECTION0
ij> -- TIMESTAMP
create table timestamp (timestamp timestamp);
0 rows inserted/updated/deleted
ij> insert into timestamp(timestamp) values (timestamp('2002-05-22 16:17:34.144'));
1 row inserted/updated/deleted
ij> select timestamp from timestamp;
TIMESTAMP
-----------------------------
2002-05-22 16:17:34.144
ij> select timestamp( '2003-05-22 16:17:34.144'), timestamp "2003-05-22 16:17:34.144" from timestamp;
1 |2003-05-22 16:17:34.144
-----------------------------------------------------------
2003-05-22 16:17:34.144 |2002-05-22 16:17:34.144
ij> select timestamp timestamp from timestamp;
TIMESTAMP
-----------------------------
2002-05-22 16:17:34.144
ij> select timestamp as timestamp from timestamp;
TIMESTAMP
-----------------------------
2002-05-22 16:17:34.144
ij> select timestamp.timestamp as timestamp from timestamp timestamp;
TIMESTAMP
-----------------------------
2002-05-22 16:17:34.144
ij> select timestamp.timestamp as timestamp from timestamp as timestamp;
TIMESTAMP
-----------------------------
2002-05-22 16:17:34.144
ij> delete from timestamp where timestamp = timestamp('2002-05-22 16:17:34.144');
1 row inserted/updated/deleted
ij> create index timestamp on timestamp(timestamp);
0 rows inserted/updated/deleted
ij> drop table timestamp;
0 rows inserted/updated/deleted
ij> remove timestamp;
IJ ERROR: Unable to establish prepared statement TIMESTAMP@CONNECTION0
ij> --
create table DOMAIN (domain int);
0 rows inserted/updated/deleted
ij> insert into domain values (1);
1 row inserted/updated/deleted
ij> select domain from domain where domain > 0;
DOMAIN
-----------
1
ij> select domain from domain domain where domain > 0;
DOMAIN
-----------
1
ij> select domain.domain from domain domain where domain.domain > 0;
DOMAIN
-----------
1
ij> prepare domain as 'select * from domain';
ij> execute domain;
DOMAIN
-----------
1
ij> create index domain on domain(domain);
0 rows inserted/updated/deleted
ij> drop table DOMAIN;
0 rows inserted/updated/deleted
ij> remove domain;
ij> create table CATALOG (catalog int);
0 rows inserted/updated/deleted
ij> insert into catalog values (1);
1 row inserted/updated/deleted
ij> select catalog from catalog where catalog > 0;
CATALOG
-----------
1
ij> select catalog from catalog catalog where catalog > 0;
CATALOG
-----------
1
ij> prepare catalog as 'select * from catalog';
ij> execute catalog;
CATALOG
-----------
1
ij> create index catalog on catalog(catalog);
0 rows inserted/updated/deleted
ij> drop table CATALOG;
0 rows inserted/updated/deleted
ij> remove catalog;
ij> create table TIME (time int);
0 rows inserted/updated/deleted
ij> insert into time values (1);
1 row inserted/updated/deleted
ij> select time from time where time > 0;
TIME
-----------
1
ij> select time from time time where time > 0;
TIME
-----------
1
ij> prepare time as 'select * from time';
ij> execute time;
TIME
-----------
1
ij> create index time on time(time);
0 rows inserted/updated/deleted
ij> drop table TIME;
0 rows inserted/updated/deleted
ij> remove time;
ij> create table ACTION (action int);
0 rows inserted/updated/deleted
ij> insert into action values (1);
1 row inserted/updated/deleted
ij> select action from action where action > 0;
ACTION
-----------
1
ij> select action from action action where action > 0;
ACTION
-----------
1
ij> prepare action as 'select * from action';
ij> create index action on action(action);
0 rows inserted/updated/deleted
ij> drop table ACTION;
0 rows inserted/updated/deleted
ij> create table DAY (day int);
0 rows inserted/updated/deleted
ij> insert into day values (1);
1 row inserted/updated/deleted
ij> select day from day where day > 0;
DAY
-----------
1
ij> select day from day day where day > 0;
DAY
-----------
1
ij> prepare day as 'select * from day';
ij> create index day on day(day);
0 rows inserted/updated/deleted
ij> drop table DAY;
0 rows inserted/updated/deleted
ij> create table MONTH (month int);
0 rows inserted/updated/deleted
ij> insert into month values (1);
1 row inserted/updated/deleted
ij> select month from month where month > 0;
MONTH
-----------
1
ij> select month from month month where month > 0;
MONTH
-----------
1
ij> select month.month from month month where month.month > 0;
MONTH
-----------
1
ij> prepare month as 'select * from month';
ij> execute month;
MONTH
-----------
1
ij> create index month on month(month);
0 rows inserted/updated/deleted
ij> drop table MONTH;
0 rows inserted/updated/deleted
ij> remove month;
ij> create table USAGE (usage int);
0 rows inserted/updated/deleted
ij> insert into usage values (1);
1 row inserted/updated/deleted
ij> select usage from usage where usage > 0;
USAGE
-----------
1
ij> select usage from usage usage where usage > 0;
USAGE
-----------
1
ij> select usage.usage from usage usage where usage.usage > 0;
USAGE
-----------
1
ij> prepare usage as 'select * from usage';
ij> create index usage on usage(usage);
0 rows inserted/updated/deleted
ij> drop table USAGE;
0 rows inserted/updated/deleted
ij> remove usage;
ij> create table LANGUAGE (language int);
0 rows inserted/updated/deleted
ij> insert into language values (1);
1 row inserted/updated/deleted
ij> select language from language where language > 0;
LANGUAGE
-----------
1
ij> select language from language language where language > 0;
LANGUAGE
-----------
1
ij> select language.language from language language where language.language > 0;
LANGUAGE
-----------
1
ij> prepare language as 'select * from language';
ij> create index language on language(language);
0 rows inserted/updated/deleted
ij> drop table LANGUAGE;
0 rows inserted/updated/deleted
ij> remove language;
ij> -- making LOCKS keyword nonreserved as fix for Derby-38
create table LOCKS (c11 int);
0 rows inserted/updated/deleted
ij> drop table LOCKS;
0 rows inserted/updated/deleted
ij> create table t1 (LOCKS int);
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> create table LOCKS (locks int);
0 rows inserted/updated/deleted
ij> insert into locks values (1);
1 row inserted/updated/deleted
ij> select locks from locks where locks > 0;
LOCKS
-----------
1
ij> select locks from locks locks where locks > 0;
LOCKS
-----------
1
ij> select locks.locks from locks locks where locks.locks > 0;
LOCKS
-----------
1
ij> prepare locks as 'select * from locks';
ij> create index locks on locks(locks);
0 rows inserted/updated/deleted
ij> drop table LOCKS;
0 rows inserted/updated/deleted
ij> remove locks;
ij> -- making COUNT keyword nonreserved as fix for Derby-
create table count(i int);
0 rows inserted/updated/deleted
ij> drop table count;
0 rows inserted/updated/deleted
ij> create table t1 (count int);
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> create table count(count int);
0 rows inserted/updated/deleted
ij> insert into count values (1);
1 row inserted/updated/deleted
ij> select * from count;
COUNT
-----------
1
ij> select count from count;
COUNT
-----------
1
ij> select count from count where count=1;
COUNT
-----------
1
ij> select count.count from count;
COUNT
-----------
1
ij> prepare count as 'select * from count';
ij> create index count on count(count);
0 rows inserted/updated/deleted
ij> drop table count;
0 rows inserted/updated/deleted
ij> remove count;
ij> create table t1(i int);
0 rows inserted/updated/deleted
ij> insert into t1 values -1,2,-3,4,-5,6,-7,8,-9,0;
10 rows inserted/updated/deleted
ij> create function count(i int) returns int no sql
external name 'java.lang.Math.abs' language java parameter style java;
0 rows inserted/updated/deleted
ij> select count(*) from t1;
1
-----------
10
ij> select count(i) from t1;
1
-----------
10
ij> select * from t1 where count(i)=i;
ERROR 42903: Invalid use of an aggregate function.
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- making SECURITY keyword nonreserved as fix for Derby-4268
create table SECURITY (security int);
0 rows inserted/updated/deleted
ij> insert into security values (1);
1 row inserted/updated/deleted
ij> select security from security where security > 0;
SECURITY
-----------
1
ij> select security from security security where security > 0;
SECURITY
-----------
1
ij> select security.security from security where security.security > 0;
SECURITY
-----------
1
ij> prepare security as 'select * from security';
ij> execute security;
SECURITY
-----------
1
ij> create index security on security(security);
0 rows inserted/updated/deleted
ij> drop table SECURITY;
0 rows inserted/updated/deleted
ij> remove security;
ij>
|
