You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2006/10/30 19:12:16 UTC
svn commit: r469215 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ tests/lang/
Author: djd
Date: Mon Oct 30 10:12:15 2006
New Revision: 469215
URL: http://svn.apache.org/viewvc?view=rev&rev=469215
Log:
Move a set of language SQL scripts from the old harness (derbylang suite) to the JUnit test LangScripts.
Tests moved are: aggbuiltin, comparisons, delete, insert, schemas, select, stringtypes, update, valuesclause, views.
Removed:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/aggbuiltin_app.properties
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/comparisons_app.properties
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggbuiltin.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/comparisons.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/insert.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/stringtypes.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/valuesclause.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggbuiltin.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggbuiltin.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggbuiltin.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggbuiltin.out Mon Oct 30 10:12:15 2006
@@ -16,6 +16,7 @@
--
-- Note that bug 5704 occurs throughout this test
-- Decimal results may be outside the range of valid types in Cloudscape
+
-- ** insert avg.sql
-- create an all types tables
create table t (i int, s smallint, l bigint,
@@ -41,95 +42,96 @@
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', x'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (1, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 200, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 2000000,
'duplicate', 'this is duplicated', 'also duplicated',
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 222.22);
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> insert into t values (0, 100, 1000000,
'goodbye', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'noone is here', 'jimmie noone was here',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
100.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 100.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'ffff', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'1111111111111111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> --------------------------------------
-- NEGATIVE TESTS
--------------------------------------
+
-- cannot aggregate datatypes that don't support NumberDataValue
select avg(c) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type CHAR.
@@ -183,11 +185,12 @@
ERROR 42Y22: Aggregate AVG cannot operate on type DATE.
ij> select avg(time('12:30:30')) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type TIME.
-ij> select avg(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t;
+ij> select avg(timestamp('1999-06-06 12:30:30')) from t;
ERROR 42Y22: Aggregate AVG cannot operate on type TIMESTAMP.
ij> ---------------------------
-- NULL AGGREGATION
---------------------------
+
-- scalar
select avg(i) from empty;
1
@@ -564,6 +567,7 @@
dc decimal(5,2));
0 rows inserted/updated/deleted
ij> -- bit maps to Byte[], so can't test for now
+
-- populate tables
insert into t (i) values (null);
1 row inserted/updated/deleted
@@ -572,96 +576,97 @@
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (1, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 200, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 2000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'goodbye', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'noone is here', 'jimmie noone was here',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
100.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 100.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
X'12af', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'ffff', X'0000111100001111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'1111111111111111', X'1234', 111.11);
1 row inserted/updated/deleted
ij> ---------------------------
-- NULL AGGREGATION
---------------------------
+
-- scalar
select count(i) from empty;
1
@@ -976,7 +981,7 @@
1
-----------
17
-ij> select count(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t;
+ij> select count(timestamp('1999-06-06 12:30:30')) from t;
1
-----------
17
@@ -1022,7 +1027,7 @@
14
1
2
-ij> select count(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t group by ts;
+ij> select count(timestamp('1999-06-06 12:30:30')) from t group by ts;
1
-----------
14
@@ -1094,6 +1099,7 @@
0 rows inserted/updated/deleted
ij> -- ** insert countStar.sql
-- Test the COUNT() aggregate
+
-- create an all types tables
create table t (i int, s smallint, l bigint,
c char(10), v varchar(50), lvc long varchar,
@@ -1116,96 +1122,97 @@
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (1, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 200, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 2000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'goodbye', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'noone is here', 'jimmie noone was here',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
100.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 100.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
X'12af', X'0000111100001111', X'ABCD');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'ffff', X'0000111100001111', X'1234');
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated', 'also duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'1111111111111111', X'ABCD');
1 row inserted/updated/deleted
ij> ---------------------------
-- NULL AGGREGATION
---------------------------
+
-- scalar
select count(*) from empty;
1
@@ -1306,6 +1313,7 @@
--to all the type system to get it. alternatively,
--need to cast/normalize/setWidth() the result to ensure
--right type.
+
-- create an all types tables
create table t (i int, s smallint, l bigint,
c char(10), v varchar(50),
@@ -1321,6 +1329,7 @@
b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2));
0 rows inserted/updated/deleted
ij> -- bit maps to Byte[], so can't test for now
+
-- populate tables
insert into t (i) values (null);
1 row inserted/updated/deleted
@@ -1329,97 +1338,99 @@
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (1, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 200, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 2000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'goodbye', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'noone is here',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
100.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 100.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'ffff', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'1111111111111111', 111.11);
1 row inserted/updated/deleted
ij> -- bit maps to Byte[], so can't test for now
+
--------------------------------------
-- NEGATIVE TESTS
--------------------------------------
+
-- cannot aggregate datatypes that don't support NumberDataValue
select sum(c) from t;
ERROR 42Y22: Aggregate SUM cannot operate on type CHAR.
@@ -1465,11 +1476,12 @@
ERROR 42Y22: Aggregate SUM cannot operate on type DATE.
ij> select sum(time('12:30:30')) from t;
ERROR 42Y22: Aggregate SUM cannot operate on type TIME.
-ij> select sum(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t;
+ij> select sum(timestamp('1999-06-06 12:30:30')) from t;
ERROR 42Y22: Aggregate SUM cannot operate on type TIMESTAMP.
ij> ---------------------------
-- NULL AGGREGATION
---------------------------
+
-- scalar
select sum(i) from empty;
1
@@ -1738,6 +1750,7 @@
b char(2) for bit data, bv varchar(8) for bit data, dc decimal(5,2));
0 rows inserted/updated/deleted
ij> -- bit maps to Byte[], so can't test for now
+
-- populate tables
insert into t (i) values (null);
1 row inserted/updated/deleted
@@ -1746,96 +1759,97 @@
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (1, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 200, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 2000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'goodbye', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'noone is here',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
100.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 100.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'ffff', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'1111111111111111', 111.11);
1 row inserted/updated/deleted
ij> --------------------------------------
-- NEGATIVE TESTS
--------------------------------------
+
-- long varchar datatypes too
create table t1 (c1 long varchar);
0 rows inserted/updated/deleted
@@ -1846,6 +1860,7 @@
ij> ---------------------------
-- NULL AGGREGATION
---------------------------
+
-- scalar
select max(i) from empty;
1
@@ -1999,7 +2014,7 @@
ij> select max(ts) from t;
1
--------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
+1992-01-01 12:55:55.0
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(b) from t;
1
@@ -2082,8 +2097,8 @@
ij> select max(ts) from t group by ts;
1
--------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-xxxxxxFILTERED-TIMESTAMPxxxxx
+1992-01-01 12:30:30.0
+1992-01-01 12:55:55.0
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select max(b) from t group by b;
@@ -2136,10 +2151,10 @@
1
--------
12:30:30
-ij> select max(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t;
+ij> select max(timestamp('1999-06-06 12:30:30')) from t;
1
--------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
+1999-06-06 12:30:30.0
ij> select max(1) from t group by i;
1
-----------
@@ -2182,12 +2197,12 @@
12:30:30
12:30:30
12:30:30
-ij> select max(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t group by ts;
+ij> select max(timestamp('1999-06-06 12:30:30')) from t group by ts;
1
--------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-xxxxxxFILTERED-TIMESTAMPxxxxx
-xxxxxxFILTERED-TIMESTAMPxxxxx
+1999-06-06 12:30:30.0
+1999-06-06 12:30:30.0
+1999-06-06 12:30:30.0
ij> -- multicolumn grouping
select max(i), max(dt), max(b) from t group by i, dt, b;
1 |2 |3
@@ -2275,96 +2290,97 @@
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (1, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 200, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 2000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 222.22);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'goodbye', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'noone is here',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
100.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 100.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-09-09'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-09-09'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:55:55'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:55:55'), timestamp('1992-01-01 12:30:30'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:55:55'),
X'12af', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'ffff', X'0000111100001111', 111.11);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 1000000,
'duplicate', 'this is duplicated',
200.0e0, 200.0e0,
- date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx),
+ date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'),
X'12af', X'1111111111111111', 111.11);
1 row inserted/updated/deleted
ij> --------------------------------------
-- NEGATIVE TESTS
--------------------------------------
+
-- long varchar datatypes too
create table t1 (c1 long varchar);
0 rows inserted/updated/deleted
@@ -2375,6 +2391,7 @@
ij> ---------------------------
-- NULL AGGREGATION
---------------------------
+
-- scalar
select min(i) from empty;
1
@@ -2528,7 +2545,7 @@
ij> select min(ts) from t;
1
--------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
+1992-01-01 12:30:30.0
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select min(b) from t;
1
@@ -2611,8 +2628,8 @@
ij> select min(ts) from t group by ts;
1
--------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-xxxxxxFILTERED-TIMESTAMPxxxxx
+1992-01-01 12:30:30.0
+1992-01-01 12:55:55.0
NULL
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> select min(b) from t group by b;
@@ -2665,10 +2682,10 @@
1
--------
12:30:30
-ij> select min(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t;
+ij> select min(timestamp('1999-06-06 12:30:30')) from t;
1
--------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
+1999-06-06 12:30:30.0
ij> select min(1) from t group by i;
1
-----------
@@ -2711,12 +2728,12 @@
12:30:30
12:30:30
12:30:30
-ij> select min(timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)) from t group by ts;
+ij> select min(timestamp('1999-06-06 12:30:30')) from t group by ts;
1
--------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-xxxxxxFILTERED-TIMESTAMPxxxxx
-xxxxxxFILTERED-TIMESTAMPxxxxx
+1999-06-06 12:30:30.0
+1999-06-06 12:30:30.0
+1999-06-06 12:30:30.0
ij> -- multicolumn grouping
select min(i), min(dt), min(b) from t group by i, dt, b;
1 |2 |3
@@ -2781,4 +2798,4 @@
0 rows inserted/updated/deleted
ij> drop table empty;
0 rows inserted/updated/deleted
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/comparisons.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/comparisons.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/comparisons.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/comparisons.out Mon Oct 30 10:12:15 2006
@@ -17,7 +17,9 @@
--
-- this test shows the current supported comparison operators
--
+
-- first, do comparisons on the int type
+
-- create a table with couple of int columns
create table inttab (c1 int, c2 int);
0 rows inserted/updated/deleted
@@ -274,6 +276,7 @@
ij> -- create a table with a couple of smallint columns. All smallint vs. smallint
-- comparisons must be done between columns, because there are no smallint
-- constants in the language
+
create table smallinttab (c1 smallint, c2 smallint);
0 rows inserted/updated/deleted
ij> -- insert some values
@@ -750,6 +753,7 @@
1
32767
ij> -- create a table with a couple of bigint columns.
+
create table biginttab (c1 bigint, c2 bigint, c3 int, c4 smallint);
0 rows inserted/updated/deleted
ij> -- insert some values
@@ -1004,9 +1008,11 @@
9223372036854775807
-9223372036854775808
ij> -- create a table with char columns of different lengths
+
create table chartab (c1 char(1), c2 char(5));
0 rows inserted/updated/deleted
ij> -- insert some values
+
insert into chartab values (' ', ' ');
1 row inserted/updated/deleted
ij> insert into chartab values ('a', 'a ');
@@ -1019,9 +1025,11 @@
select c1 from chartab where c1 = ' ';
C1
----
+
ij> select c2 from chartab where c2 = ' ';
C2
-----
+
ij> select c1 from chartab where c1 = 'a';
C1
----
@@ -1042,18 +1050,23 @@
select c1 from chartab where c1 = '';
C1
----
+
ij> select c1 from chartab where c1 = ' ';
C1
----
+
ij> select c2 from chartab where c2 = '';
C2
-----
+
ij> select c2 from chartab where c2 = ' ';
C2
-----
+
ij> select c2 from chartab where c2 = ' ';
C2
-----
+
ij> select c1 from chartab where c1 = 'a ';
C1
----
@@ -1087,11 +1100,13 @@
ij> select c1 from chartab where c1 is not null;
C1
----
+
a
b
ij> select c1 from chartab where not c1 is null;
C1
----
+
a
b
ij> -- Now test <>
@@ -1108,18 +1123,22 @@
ij> select c1 from chartab where c1 <> 'a';
C1
----
+
b
ij> select c2 from chartab where c2 <> 'a ';
C2
-----
+
bcdef
ij> select c1 from chartab where c1 <> 'b';
C1
----
+
a
ij> select c2 from chartab where c2 <> 'bcdef';
C2
-----
+
a
ij> select c1 from chartab where c1 != ' ';
C1
@@ -1134,18 +1153,22 @@
ij> select c1 from chartab where c1 != 'a';
C1
----
+
b
ij> select c2 from chartab where c2 != 'a ';
C2
-----
+
bcdef
ij> select c1 from chartab where c1 != 'b';
C1
----
+
a
ij> select c2 from chartab where c2 != 'bcdef';
C2
-----
+
a
ij> -- now check for end-of-string blank semantics
select c1 from chartab where c1 <> '';
@@ -1176,22 +1199,27 @@
ij> select c1 from chartab where c1 <> 'a ';
C1
----
+
b
ij> select c2 from chartab where c2 <> 'a ';
C2
-----
+
bcdef
ij> select c1 from chartab where c1 <> 'b ';
C1
----
+
a
ij> select c2 from chartab where c2 <> 'bcdef ';
C2
-----
+
a
ij> select c2 from chartab where c2 <> 'bcde ';
C2
-----
+
a
bcdef
ij> -- now check null <> null semantics
@@ -1209,16 +1237,20 @@
ij> select c1 from chartab where c1 < 'a';
C1
----
+
ij> select c2 from chartab where c2 < 'a ';
C2
-----
+
ij> select c1 from chartab where c1 < 'b';
C1
----
+
a
ij> select c2 from chartab where c2 < 'bcdef';
C2
-----
+
a
ij> -- now check for end-of-string blank semantics
select c1 from chartab where c1 < '';
@@ -1239,20 +1271,25 @@
ij> select c1 from chartab where c1 < 'a ';
C1
----
+
ij> select c2 from chartab where c2 < 'a ';
C2
-----
+
ij> select c1 from chartab where c1 < 'b ';
C1
----
+
a
ij> select c2 from chartab where c2 < 'bcdef ';
C2
-----
+
a
ij> select c2 from chartab where c2 < 'bcde ';
C2
-----
+
a
ij> -- now check null < null semantics
select c1, c2 from chartab where c1 < c2;
@@ -1336,64 +1373,80 @@
select c1 from chartab where c1 <= ' ';
C1
----
+
ij> select c2 from chartab where c2 <= ' ';
C2
-----
+
ij> select c1 from chartab where c1 <= 'a';
C1
----
+
a
ij> select c2 from chartab where c2 <= 'a ';
C2
-----
+
a
ij> select c1 from chartab where c1 <= 'b';
C1
----
+
a
b
ij> select c2 from chartab where c2 <= 'bcdef';
C2
-----
+
a
bcdef
ij> -- now check for end-of-string blank semantics
select c1 from chartab where c1 <= '';
C1
----
+
ij> select c1 from chartab where c1 <= ' ';
C1
----
+
ij> select c2 from chartab where c2 <= '';
C2
-----
+
ij> select c2 from chartab where c2 <= ' ';
C2
-----
+
ij> select c2 from chartab where c2 <= ' ';
C2
-----
+
ij> select c1 from chartab where c1 <= 'a ';
C1
----
+
a
ij> select c2 from chartab where c2 <= 'a ';
C2
-----
+
a
ij> select c1 from chartab where c1 <= 'b ';
C1
----
+
a
b
ij> select c2 from chartab where c2 <= 'bcdef ';
C2
-----
+
a
bcdef
ij> select c2 from chartab where c2 <= 'bcde ';
C2
-----
+
a
ij> -- now check null <= null semantics
select c1, c2 from chartab where c1 <= c2;
@@ -1406,11 +1459,13 @@
select c1 from chartab where c1 >= ' ';
C1
----
+
a
b
ij> select c2 from chartab where c2 >= ' ';
C2
-----
+
a
bcdef
ij> select c1 from chartab where c1 >= 'a';
@@ -1435,26 +1490,31 @@
select c1 from chartab where c1 >= '';
C1
----
+
a
b
ij> select c1 from chartab where c1 >= ' ';
C1
----
+
a
b
ij> select c2 from chartab where c2 >= '';
C2
-----
+
a
bcdef
ij> select c2 from chartab where c2 >= ' ';
C2
-----
+
a
bcdef
ij> select c2 from chartab where c2 >= ' ';
C2
-----
+
a
bcdef
ij> select c1 from chartab where c1 >= 'a ';
@@ -1488,10 +1548,12 @@
ij> -- create a table with a few varchar columns. All varchar vs. varchar
-- comparisons must be done between columns, because there are no varchar
-- constants in the language
+
create table varchartab (c1 varchar(1), c2 varchar(1), c3 varchar(5),
c4 varchar(5));
0 rows inserted/updated/deleted
ij> -- insert some values
+
insert into varchartab values ('', '', '', '');
1 row inserted/updated/deleted
ij> insert into varchartab values ('a', 'a', 'a', 'a');
@@ -1510,20 +1572,26 @@
select c1 from varchartab where c1 = c2;
C1
----
+
a
b
ij> select c3 from varchartab where c3 = c4;
C3
-----
+
a
bcdef
ij> -- test varchar = char semantics. Test with trailing blanks.
select c1 from varchartab where c1 = ' ';
C1
----
+
+
ij> select c1 from varchartab where c1 = '';
C1
----
+
+
ij> select c1 from varchartab where c1 = 'a ';
C1
----
@@ -1540,9 +1608,13 @@
ij> select c3 from varchartab where c3 = ' ';
C3
-----
+
+
ij> select c3 from varchartab where c3 = '';
C3
-----
+
+
ij> select c3 from varchartab where c3 = 'a ';
C3
-----
@@ -1559,9 +1631,13 @@
select c1 from varchartab where ' ' = c1;
C1
----
+
+
ij> select c1 from varchartab where '' = c1;
C1
----
+
+
ij> select c1 from varchartab where 'a ' = c1;
C1
----
@@ -1578,9 +1654,13 @@
ij> select c3 from varchartab where ' ' = c3;
C3
-----
+
+
ij> select c3 from varchartab where '' = c3;
C3
-----
+
+
ij> select c3 from varchartab where 'a ' = c3;
C3
-----
@@ -1622,18 +1702,24 @@
ij> select c1 from varchartab where c1 <> 'a ';
C1
----
+
b
+
b
ij> select c1 from varchartab where c1 <> 'b ';
C1
----
+
a
+
a
ij> select c1 from varchartab where c1 <> 'bb';
C1
----
+
a
b
+
a
b
ij> select c3 from varchartab where c3 <> ' ';
@@ -1653,19 +1739,25 @@
ij> select c3 from varchartab where c3 <> 'a ';
C3
-----
+
bcdef
+
b
ij> select c3 from varchartab where c3 <> 'bcdef ';
C3
-----
+
a
+
a
b
ij> select c3 from varchartab where c3 <> 'bbbb';
C3
-----
+
a
bcdef
+
a
b
ij> select c1 from varchartab where c1 != ' ';
@@ -1685,18 +1777,24 @@
ij> select c1 from varchartab where c1 != 'a ';
C1
----
+
b
+
b
ij> select c1 from varchartab where c1 != 'b ';
C1
----
+
a
+
a
ij> select c1 from varchartab where c1 != 'bb';
C1
----
+
a
b
+
a
b
ij> select c3 from varchartab where c3 != ' ';
@@ -1716,19 +1814,25 @@
ij> select c3 from varchartab where c3 != 'a ';
C3
-----
+
bcdef
+
b
ij> select c3 from varchartab where c3 != 'bcdef ';
C3
-----
+
a
+
a
b
ij> select c3 from varchartab where c3 != 'bbbb';
C3
-----
+
a
bcdef
+
a
b
ij> -- test char <> varchar semantics. Test with trailing blanks.
@@ -1749,18 +1853,24 @@
ij> select c1 from varchartab where 'a ' <> c1;
C1
----
+
b
+
b
ij> select c1 from varchartab where 'b ' <> c1;
C1
----
+
a
+
a
ij> select c1 from varchartab where 'bb' <> c1;
C1
----
+
a
b
+
a
b
ij> select c3 from varchartab where ' ' <> c3;
@@ -1780,19 +1890,25 @@
ij> select c3 from varchartab where 'a ' <> c3;
C3
-----
+
bcdef
+
b
ij> select c3 from varchartab where 'bcdef ' <> c3;
C3
-----
+
a
+
a
b
ij> select c3 from varchartab where 'bbbb' <> c3;
C3
-----
+
a
bcdef
+
a
b
ij> -- Now test <
@@ -1815,16 +1931,22 @@
ij> select c1 from varchartab where c1 < 'a ';
C1
----
+
+
ij> select c1 from varchartab where c1 < 'b ';
C1
----
+
a
+
a
ij> select c1 from varchartab where c1 < 'bb';
C1
----
+
a
b
+
a
b
ij> select c3 from varchartab where c3 < ' ';
@@ -1836,16 +1958,22 @@
ij> select c3 from varchartab where c3 < 'a ';
C3
-----
+
+
ij> select c3 from varchartab where c3 < 'bcdef ';
C3
-----
+
a
+
a
b
ij> select c3 from varchartab where c3 < 'bbbb';
C3
-----
+
a
+
a
b
ij> -- test char < varchar semantics. Test with trailing blanks.
@@ -1970,16 +2098,22 @@
ij> select c1 from varchartab where 'a ' > c1;
C1
----
+
+
ij> select c1 from varchartab where 'b ' > c1;
C1
----
+
a
+
a
ij> select c1 from varchartab where 'bb' > c1;
C1
----
+
a
b
+
a
b
ij> select c3 from varchartab where ' ' > c3;
@@ -1991,28 +2125,36 @@
ij> select c3 from varchartab where 'a ' > c3;
C3
-----
+
+
ij> select c3 from varchartab where 'bcdef ' > c3;
C3
-----
+
a
+
a
b
ij> select c3 from varchartab where 'bbbb' > c3;
C3
-----
+
a
+
a
b
ij> -- Now test <=
select c1 from varchartab where c1 <= c2;
C1
----
+
a
b
a
ij> select c3 from varchartab where c3 <= c4;
C3
-----
+
a
bcdef
a
@@ -2021,65 +2163,89 @@
select c1 from varchartab where c1 <= ' ';
C1
----
+
+
ij> select c1 from varchartab where c1 <= '';
C1
----
+
+
ij> select c1 from varchartab where c1 <= 'a ';
C1
----
+
a
+
a
ij> select c1 from varchartab where c1 <= 'b ';
C1
----
+
a
b
+
a
b
ij> select c1 from varchartab where c1 <= 'bb';
C1
----
+
a
b
+
a
b
ij> select c3 from varchartab where c3 <= ' ';
C3
-----
+
+
ij> select c3 from varchartab where c3 <= '';
C3
-----
+
+
ij> select c3 from varchartab where c3 <= 'a ';
C3
-----
+
a
+
a
ij> select c3 from varchartab where c3 <= 'bcdef ';
C3
-----
+
a
bcdef
+
a
b
ij> select c3 from varchartab where c3 <= 'bbbb';
C3
-----
+
a
+
a
b
ij> -- test char <= varchar semantics. Test with trailing blanks.
select c1 from varchartab where ' ' <= c1;
C1
----
+
a
b
+
a
b
ij> select c1 from varchartab where '' <= c1;
C1
----
+
a
b
+
a
b
ij> select c1 from varchartab where 'a ' <= c1;
@@ -2100,15 +2266,19 @@
ij> select c3 from varchartab where ' ' <= c3;
C3
-----
+
a
bcdef
+
a
b
ij> select c3 from varchartab where '' <= c3;
C3
-----
+
a
bcdef
+
a
b
ij> select c3 from varchartab where 'a ' <= c3;
@@ -2130,27 +2300,33 @@
select c1 from varchartab where c1 >= c2;
C1
----
+
a
b
b
ij> select c3 from varchartab where c3 >= c4;
C3
-----
+
a
bcdef
ij> -- test varchar >= char semantics. Test with trailing blanks.
select c1 from varchartab where c1 >= ' ';
C1
----
+
a
b
+
a
b
ij> select c1 from varchartab where c1 >= '';
C1
----
+
a
b
+
a
b
ij> select c1 from varchartab where c1 >= 'a ';
@@ -2171,15 +2347,19 @@
ij> select c3 from varchartab where c3 >= ' ';
C3
-----
+
a
bcdef
+
a
b
ij> select c3 from varchartab where c3 >= '';
C3
-----
+
a
bcdef
+
a
b
ij> select c3 from varchartab where c3 >= 'a ';
@@ -2201,50 +2381,70 @@
select c1 from varchartab where ' ' >= c1;
C1
----
+
+
ij> select c1 from varchartab where '' >= c1;
C1
----
+
+
ij> select c1 from varchartab where 'a ' >= c1;
C1
----
+
a
+
a
ij> select c1 from varchartab where 'b ' >= c1;
C1
----
+
a
b
+
a
b
ij> select c1 from varchartab where 'bb' >= c1;
C1
----
+
a
b
+
a
b
ij> select c3 from varchartab where ' ' >= c3;
C3
-----
+
+
ij> select c3 from varchartab where '' >= c3;
C3
-----
+
+
ij> select c3 from varchartab where 'a ' >= c3;
C3
-----
+
a
+
a
ij> select c3 from varchartab where 'bcdef ' >= c3;
C3
-----
+
a
bcdef
+
a
b
ij> select c3 from varchartab where 'bbbb' >= c3;
C3
-----
+
a
+
a
b
ij> -- test is null semantics
@@ -2255,15 +2455,19 @@
ij> select c1 from varchartab where c1 is not null;
C1
----
+
a
b
+
a
b
ij> select c1 from varchartab where not c1 is null;
C1
----
+
a
b
+
a
b
ij> -- clean up
@@ -2277,4 +2481,4 @@
0 rows inserted/updated/deleted
ij> drop table varchartab;
0 rows inserted/updated/deleted
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out Mon Oct 30 10:12:15 2006
@@ -14,15 +14,18 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
+
--
-- this test is for basic delete functionality
--
+
-- create the table
create table t1 (c1 int);
0 rows inserted/updated/deleted
ij> create table t2 (c1 int);
0 rows inserted/updated/deleted
ij> -- negative tests
+
-- table name required for positioned delete and for searched delete
delete;
ERROR 42X01: Syntax error: Encountered "<EOF>" at line 4, column 6.
@@ -83,6 +86,7 @@
0
2
ij> -- target table in source, should be done in deferred mode
+
-- repopulate the tables
insert into t1 values(1);
1 row inserted/updated/deleted
@@ -140,6 +144,7 @@
where c1 = 2)
);
0 rows inserted/updated/deleted
+WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> select * from t1;
C1
-----------
@@ -234,6 +239,7 @@
-- Test delete piece of the fix for bug171.
--
--------------------------------------------
+
create table bug171_employee( empl_id int, bonus int );
0 rows inserted/updated/deleted
ij> create table bug171_bonuses( empl_id int, bonus int );
@@ -267,6 +273,7 @@
ij> --
-- The problem query. could not use correlation names in delete.
--
+
delete from bug171_employee e
where e.empl_id > 2 and e.bonus <
(
@@ -280,6 +287,7 @@
1 |0
2 |0
ij> -- positioned delete with correlation names
+
autocommit off;
ij> get cursor bug171_c2 as
'select * from bug171_employee where empl_id = 2 for update';
@@ -298,8 +306,9 @@
ij> --
-- Cleanup
--
+
drop table bug171_employee;
0 rows inserted/updated/deleted
ij> drop table bug171_bonuses;
0 rows inserted/updated/deleted
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/insert.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/insert.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/insert.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/insert.out Mon Oct 30 10:12:15 2006
@@ -14,10 +14,13 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
+
--
-- this test is for basic insert functionality
--
+
-- NOTE: drop, create, select from the same table doesn't work yet either.
+
-- create the tables
create table t1 (i int, j int);
0 rows inserted/updated/deleted
@@ -366,7 +369,7 @@
ts timestamp);
0 rows inserted/updated/deleted
ij> insert into i3 values (X'11', X'22', X'25', '3', '4', '5', '1990-10-10',
- '11:11:11', 'xxxxxxFILTERED-TIMESTAMPxxxxx);
+ '11:11:11', '1990-11-11 11:11:11');
1 row inserted/updated/deleted
ij> insert into i3 values (null, null, null, null, null, null, null, null, null);
1 row inserted/updated/deleted
@@ -375,7 +378,7 @@
ij> select * from i4;
B |BV |LBV |C |CV |LVC |DT |T |TS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-11 |22 |25 |3 |4 |5 |1990-10-10|11:11:11|xxxxxxFILTERED-TIMESTAMPxxxxx
+11 |22 |25 |3 |4 |5 |1990-10-10|11:11:11|1990-11-11 11:11:11.0
NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL
ij> delete from i4;
2 rows inserted/updated/deleted
@@ -388,7 +391,7 @@
ts timestamp);
0 rows inserted/updated/deleted
ij> insert into tab2 values ('3', '4', '5', '1990-10-10',
- '11:11:11', 'xxxxxxFILTERED-TIMESTAMPxxxxx);
+ '11:11:11', '1990-11-11 11:11:11');
1 row inserted/updated/deleted
ij> insert into tab2 values (null, null, null, null, null, null);
1 row inserted/updated/deleted
@@ -397,7 +400,7 @@
ij> select * from i4;
B |BV |LBV |C |CV |LVC |DT |T |TS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL|NULL|NULL |3 |4 |5 |1990-10-10|11:11:11|xxxxxxFILTERED-TIMESTAMPxxxxx
+NULL|NULL|NULL |3 |4 |5 |1990-10-10|11:11:11|1990-11-11 11:11:11.0
NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL
ij> -- drop the tables
drop table t1;
@@ -421,6 +424,7 @@
ij> drop table tab2;
0 rows inserted/updated/deleted
ij> -- test bug 4293, extremely huge insert statement, not using FileImport.
+
create table POLICY_STATEMENTS (c1 int, c2 int, c3 int, c4 long varchar, c5 long varchar, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int);
0 rows inserted/updated/deleted
ij> INSERT INTO POLICY_STATEMENTS VALUES
@@ -787,4 +791,4 @@
356
ij> drop table POLICY_STATEMENTS;
0 rows inserted/updated/deleted
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out Mon Oct 30 10:12:15 2006
@@ -21,6 +21,7 @@
--
-- Catalog names are not supported, and result in syntax errors when used.
--
+
create table myschem.t(c int);
0 rows inserted/updated/deleted
ij> insert into t values (1);
@@ -55,6 +56,7 @@
-- - Jeff
--
-- select honk.blat.s.c from honk.blat.s;
+
drop table xyzzy.t;
ERROR 42Y07: Schema 'XYZZY' does not exist
ij> -- catalog name not supported:
@@ -405,10 +407,10 @@
ij> -- create a table in test
set schema test;
0 rows inserted/updated/deleted
-ij> create table sampletab (c1 int check (c1 > 1), c2 char(20));
+ij> create table sampletab (c1 int constraint st_c1 check (c1 > 1), c2 char(20));
0 rows inserted/updated/deleted
ij> insert into sampletab values (1,'in schema: TEST');
-ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'TEST.SAMPLETAB'.
+ERROR 23513: The check constraint 'ST_C1' was violated while performing an INSERT or UPDATE on table 'TEST.SAMPLETAB'.
ij> insert into sampletab values (2,'in schema: TEST');
1 row inserted/updated/deleted
ij> -- verify it
@@ -436,7 +438,7 @@
set schema APP;
0 rows inserted/updated/deleted
ij> -- create table with same name in APP
-create table sampletab (c1 int check(c1 > 1), c2 char(20));
+create table sampletab (c1 int constraint st_c1 check(c1 > 1), c2 char(20));
0 rows inserted/updated/deleted
ij> insert into sampletab values (2,'in schema: APP');
1 row inserted/updated/deleted
@@ -624,6 +626,7 @@
1 |1 |1 |1
1 |1 |1 |1
ij> -- test subqueries
+
select * from test.s where exists (select test.s.* from test.t);
I |S |C |VC
--------------------------------------------------------------------------------
@@ -733,6 +736,7 @@
I |S |C |VC
--------------------------------------------------------------------------------
ij> -- correlated subqueries
+
-- exists disallowed in select clause
select (exists (select * from test.ttt
where iii = (select 11 from test.tt where ii = i and ii <> 1)) )
@@ -741,6 +745,7 @@
order by a;
ERROR 42X01: Syntax error: Encountered "exists" at line 4, column 9.
ij> -- negative tests
+
-- multiple matches at parent level
select * from test.s, test.t where exists (select i from test.tt);
ERROR 42X03: Column name 'I' is in more than one table in the FROM list.
@@ -760,6 +765,7 @@
select * from test.s, (select * from test.tt where test.s.i = ii) a;
ERROR 42X04: Column 'TEST.S.I' 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 'TEST.S.I' is not a column in the target table.
ij> -- positive tests
+
-- skip levels to find match
select * from test.s where exists (select * from test.ttt where iii =
(select 11 from test.tt where ii = i and ii <> 1));
@@ -871,6 +877,7 @@
-- TABLE,VIEW,PROCEDURE TRIGGER, STATEMENT
-- Cloudscape requires that the INDEX schema matches the (existing) table schema so
-- there is no implict schema creation for CREATE INDEX.
+
prepare ISC_TABLE as 'create table ISC.I(i int)';
ij> execute ISC_TABLE;
0 rows inserted/updated/deleted
@@ -959,4 +966,4 @@
0 rows inserted/updated/deleted
ij> drop schema test restrict;
0 rows inserted/updated/deleted
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out Mon Oct 30 10:12:15 2006
@@ -17,6 +17,8 @@
--
-- 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);
@@ -178,6 +180,7 @@
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;
@@ -199,4 +202,4 @@
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>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/stringtypes.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/stringtypes.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/stringtypes.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/stringtypes.out Mon Oct 30 10:12:15 2006
@@ -18,10 +18,13 @@
-- this test shows the current supported char, varchar, and long varchar
-- functionality
--
+
+
-- create a table with null and non-null char columns of different lengths
create table ct (c1 char, c2 char(5) not null, c3 char(30) default null);
0 rows inserted/updated/deleted
ij> -- insert some rows
+
-- first, try values that fill each column with non-blanks
insert into ct values ('1', '11111', '111111111111111111111111111111');
1 row inserted/updated/deleted
@@ -66,6 +69,7 @@
create table vt (c1 varchar(1), c2 varchar(5) not null, c3 varchar(30) default null);
0 rows inserted/updated/deleted
ij> -- insert some rows
+
-- first, try values that fill each column with non-blanks
insert into vt values ('1', '11111', '111111111111111111111111111111');
1 row inserted/updated/deleted
@@ -403,6 +407,7 @@
--
-- This test could be done from within a java program, but it would take
-- a fair amount of work for the benefit.
+
select * from vt;
C1 |C2 |C3
-----------------------------------------
@@ -427,6 +432,7 @@
ij> drop table trash;
0 rows inserted/updated/deleted
ij> --- Test for really big literals. Throw an error for now. beetle 2758
+
CREATE TABLE "LVC"
(
"LV" long varchar
@@ -436,4 +442,4 @@
ERROR 54002: A string constant starting with ''4749463837617201db01f70000000900001d00001500141a0a002200002&' is too long.
ij> drop table lvc;
0 rows inserted/updated/deleted
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out Mon Oct 30 10:12:15 2006
@@ -14,9 +14,13 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
+
--
-- this test is for basic update functionality
--
+
+
+
-- create the table
create table t1 (int_col int, smallint_col smallint, char_30_col char(30),
varchar_50_col varchar(50));
@@ -209,6 +213,7 @@
where int_col = 1)
where int_col = 37;
0 rows inserted/updated/deleted
+WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> select * from t1;
INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL
----------------------------------------------------------------------------------------------------
@@ -424,6 +429,7 @@
0 rows inserted/updated/deleted
ij> -- bug 4318, possible deadlock if table first has IX, then X table lock; make
-- sure you don't have IX table lock and X table lock at the same time
+
create table tab1 (c1 int not null primary key, c2 int);
0 rows inserted/updated/deleted
ij> insert into tab1 values (1, 8);
@@ -457,6 +463,7 @@
-- Test upgrade piece of the fix for bug171.
--
--------------------------------------------
+
create table bug171_employee( empl_id int, bonus int );
0 rows inserted/updated/deleted
ij> create table bug171_bonuses( empl_id int, bonus int );
@@ -490,6 +497,7 @@
ij> --
-- The problem query. could not use correlation names in update.
--
+
update bug171_employee e
set e.bonus =
(
@@ -504,6 +512,7 @@
2 |600
3 |900
ij> -- positioned update with correlation names
+
autocommit off;
ij> get cursor bug171_c1 as
'select * from bug171_employee where empl_id = 1 for update';
@@ -530,6 +539,7 @@
ij> --
-- Cleanup
--
+
drop table bug171_employee;
0 rows inserted/updated/deleted
ij> drop table bug171_bonuses;
@@ -605,4 +615,4 @@
0 rows inserted/updated/deleted
ij> DROP TABLE DERBY10432;
0 rows inserted/updated/deleted
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/valuesclause.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/valuesclause.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/valuesclause.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/valuesclause.out Mon Oct 30 10:12:15 2006
@@ -14,9 +14,11 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
+
--
-- this test is for the values clause functionality
--
+
-- create the tables
create table t1 (i int, j int);
0 rows inserted/updated/deleted
@@ -48,6 +50,7 @@
values();
ERROR 42X01: Syntax error: Encountered ")" at line 2, column 8.
ij> -- positive tests
+
-- single value
values 1;
1
@@ -134,7 +137,9 @@
NULL |2
NULL |4
ij> -- table constructor tests
+
-- negative tests
+
-- non-matching # of elements
values 1, (2, 3), 4;
ERROR 42X59: The number of columns in each VALUES constructor must be the same.
@@ -147,6 +152,7 @@
prepare v1 as 'values (1, ?, 2), (3, ?, 4), (5, ?, 7)';
ERROR 42Y10: A table constructor that is not in an INSERT statement has all ? parameters in one of its columns. For each column, at least one of the rows must have a non-parameter.
ij> -- positive tests
+
values 1, 2, 3;
1
-----------
@@ -551,4 +557,4 @@
1
-----------
1
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out Mon Oct 30 10:12:15 2006
@@ -15,6 +15,7 @@
-- limitations under the License.
--
-- tests for views
+
-- set autocommit off
autocommit off;
ij> -- create some tables
@@ -70,6 +71,7 @@
ij> insert into t2 values (8, 7, 6.0, 5.0);
1 row inserted/updated/deleted
ij> -- negative tests
+
-- view with a parameter
create view vneg as select * from t1 where i = ?;
ERROR 42X98: Parameters are not allowed in a VIEW definition.
@@ -384,4 +386,4 @@
0 rows inserted/updated/deleted
ij> DROP SCHEMA TEST_SCHEMA RESTRICT;
0 rows inserted/updated/deleted
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java Mon Oct 30 10:12:15 2006
@@ -61,9 +61,19 @@
* Language SQL scripts (.sql files) that only run in embedded.
*/
private static final String[] EMBEDDED_TESTS = {
+ "aggbuiltin",
"arithmetic",
+ "comparisons",
+ "delete",
"depend",
+ "insert",
+ "schemas",
+ "select",
+ "stringtypes",
"union",
+ "update",
+ "valuesclause",
+ "views",
};
/**
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql?view=diff&rev=469215&r1=469214&r2=469215
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/schemas.sql Mon Oct 30 10:12:15 2006
@@ -248,7 +248,7 @@
-- create a table in test
set schema test;
-create table sampletab (c1 int check (c1 > 1), c2 char(20));
+create table sampletab (c1 int constraint st_c1 check (c1 > 1), c2 char(20));
insert into sampletab values (1,'in schema: TEST');
insert into sampletab values (2,'in schema: TEST');
@@ -270,7 +270,7 @@
set schema APP;
-- create table with same name in APP
-create table sampletab (c1 int check(c1 > 1), c2 char(20));
+create table sampletab (c1 int constraint st_c1 check(c1 > 1), c2 char(20));
insert into sampletab values (2,'in schema: APP');
-- verify it