java/testing/org/apache/derbyTesting/functionTests/master/nonreserved.out

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>