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/27 22:04:30 UTC
svn commit: r468514 [2/7] - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ suites/ tests/lang/ tests/nist/
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml013.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml013.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml013.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml013.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML013
+
-- SQL Test Suite, V6.0, Interactive SQL, dml013.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,13 +14,17 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0039 COUNT DISTINCT function!
+
-- setup
INSERT INTO WORKS
VALUES('E5','P5',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0039 If 1 row inserted?
+
SELECT COUNT(DISTINCT HOURS)
FROM WORKS;
1
@@ -26,16 +32,20 @@
4
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0039 If count = 4?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0039 <<< END TEST
-- ************************************************************
+
-- TEST:0167 SUM ALL function!
+
-- setup
INSERT INTO WORKS
VALUES('E5','P5',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0167 If 1 row is inserted?
+
SELECT SUM(ALL HOURS)
FROM WORKS;
1
@@ -43,16 +53,20 @@
464
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0167 If SUM(ALL HOURS) = 464?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0167 <<< END TEST
-- ************************************************************
+
-- TEST:0168 SUM function!
+
-- setup
INSERT INTO WORKS
VALUES('E5','P5',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0168 If 1 row is inserted?
+
SELECT SUM(HOURS)
FROM WORKS;
1
@@ -60,26 +74,32 @@
464
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0168 If SUM(HOURS) = 464?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0168 <<< END TEST
-- ***********************************************************
+
-- TEST:0169 COUNT(*) function !
+
-- setup
INSERT INTO WORKS
VALUES('E5','P5',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0169 If 1 row is inserted?
+
SELECT COUNT(*)
FROM WORKS;
1
-----------
13
ij> -- PASS:0169 If count = 13?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0169 <<< END TEST
-- *************************************************************
+
-- TEST:0040 SUM function with WHERE clause!
SELECT SUM(HOURS)
FROM WORKS
@@ -88,8 +108,10 @@
-----------
140
ij> -- PASS:0040 If SUM(HOURS) = 140?
+
-- END TEST >>> 0040 <<< END TEST
-- ***************************************************************
+
-- TEST:0170 SUM DISTINCT function with WHERE clause!
SELECT SUM(DISTINCT HOURS)
FROM WORKS
@@ -98,8 +120,10 @@
-----------
100
ij> -- PASS:0170 If SUM(DISTINCT HOURS) = 100?
+
-- END TEST >>> 0170 <<< END TEST
-- **************************************************************
+
-- TEST:0171 SUM(column) + value!
SELECT SUM(HOURS)+10
FROM WORKS
@@ -108,8 +132,10 @@
------------
150
ij> -- PASS:0171 If SUM(HOURS)+10 = 150?
+
-- END TEST >>> 0171 <<< END TEST
-- ***************************************************************
+
-- TEST:0041 MAX function in subquery!
SELECT EMPNUM
FROM STAFF
@@ -120,8 +146,10 @@
E3
E5
ij> -- PASS:0041 If 2 rows are selected and EMPNUMs = 'E3' and 'E5'?
+
-- END TEST >>> 0041 <<< END TEST
-- ***************************************************************
+
-- TEST:0042 MIN function in subquery!
SELECT EMPNUM
FROM STAFF
@@ -131,8 +159,10 @@
---
E2
ij> -- PASS:0042 If EMPNUM = 'E2'?
+
-- END TEST >>> 0042 <<< END TEST
-- ***************************************************************
+
-- TEST:0043 AVG function!
SELECT AVG(GRADE)
FROM STAFF;
@@ -140,18 +170,22 @@
------------
12.0000
ij> -- PASS:0043 If AVG(GRADE) = 12?
+
-- END TEST >>> 0043 <<< END TEST
-- ***************************************************************
+
-- TEST:0044 AVG function - empty result NULL value!
DELETE FROM TEMP_S;
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 AVG(GRADE)
FROM TEMP_S;
1
------------
NULL
ij> -- PASS:0044 If AVG(GRADE) is NULL?
+
-- END TEST >>> 0044 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml014.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml014.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml014.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml014.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML014
+
-- SQL Test Suite, V6.0, Interactive SQL, dml014.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,7 +14,9 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0045 BETWEEN predicate!
SELECT PNUM
FROM PROJ
@@ -21,6 +25,7 @@
---
P6
ij> -- PASS:0045 If PNUM = 'P6'?
+
SELECT PNUM
FROM PROJ
WHERE BUDGET >= 40000 AND BUDGET <= 60000;
@@ -28,8 +33,10 @@
---
P6
ij> -- PASS:0045 If PNUM = 'P6'?
+
-- END TEST >>> 0045 <<< END TEST
-- ***********************************************************
+
-- TEST:0046 NOT BETWEEN predicate !
SELECT CITY
FROM STAFF
@@ -38,6 +45,7 @@
---------------
Vienna
ij> -- PASS:0046 If CITY = 'Vienna'?
+
SELECT CITY
FROM STAFF
WHERE NOT(GRADE BETWEEN 12 AND 13);
@@ -45,8 +53,10 @@
---------------
Vienna
ij> -- PASS:0046 If CITY = 'Vienna'?
+
-- END TEST >>> 0046 <<< END TEST
-- *************************************************************
+
-- TEST:0047 IN predicate!
SELECT STAFF.EMPNAME
FROM STAFF
@@ -61,6 +71,7 @@
--------------------
Alice
ij> -- PASS:0047 If EMPNAME = 'Alice'?
+
SELECT STAFF.EMPNAME
FROM STAFF
WHERE STAFF.EMPNUM = ANY
@@ -74,8 +85,10 @@
--------------------
Alice
ij> -- PASS:0047 If EMPNAME = 'Alice'?
+
-- END TEST >>> 0047 <<< END TEST
-- ***********************************************************
+
-- TEST:0048 NOT IN predicate!
SELECT WORKS.HOURS
FROM WORKS
@@ -87,6 +100,7 @@
------
12
ij> -- PASS:0048 If HOURS = 12?
+
SELECT WORKS.HOURS
FROM WORKS
WHERE NOT (WORKS.PNUM IN
@@ -97,8 +111,10 @@
------
12
ij> -- PASS:0048 If HOURS = 12?
+
-- END TEST >>> 0048 <<< END TEST
-- ****************************************************************
+
-- TEST:0049 IN predicate value list!
SELECT HOURS
FROM WORKS
@@ -110,6 +126,7 @@
------
80
ij> -- PASS:0049 If HOURS = 80?
+
SELECT HOURS
FROM WORKS
WHERE NOT (PNUM IN
@@ -120,8 +137,10 @@
------
80
ij> -- PASS:0049 If HOURS = 80?
+
-- END TEST >>> 0049 <<< END TEST
-- **************************************************************
+
-- TEST:0050 LIKE predicate -- %!
SELECT EMPNAME
FROM STAFF
@@ -130,8 +149,10 @@
--------------------
Alice
ij> -- PASS:0050 If EMPNAME = 'Alice'?
+
-- END TEST >>> 0050 <<< END TEST
-- **************************************************************
+
-- TEST:0051 LIKE predicate -- underscore!
SELECT CITY
FROM STAFF
@@ -140,29 +161,37 @@
---------------
Vienna
ij> -- PASS:0051 If CITY = 'Vienna'?
+
-- END TEST >>> 0051 <<< END TEST
-- *************************************************************
+
-- TEST:0052 LIKE predicate -- ESCAPE character!
+
-- setup
INSERT INTO STAFF
VALUES('E36','Huyan',36,'Xi_an%');
1 row inserted/updated/deleted
ij> -- PASS:0052 If 1 row is inserted?
+
--O SELECT CITY
--O FROM STAFF
--O WHERE CITY LIKE 'XiS___S%%'
--O ESCAPE 'S';
-- PASS:0052 If CITY = 'Xi_an%' ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0052 <<< END TEST
-- **************************************************************
+
-- TEST:0053 NOT LIKE predicate!
+
-- setup
INSERT INTO STAFF
VALUES('E36','Huyan',36,'Xi_an%');
1 row inserted/updated/deleted
ij> -- PASS:0053 If 1 row is inserted?
+
SELECT COUNT(*)
FROM STAFF
WHERE EMPNUM NOT LIKE '_36';
@@ -170,6 +199,7 @@
-----------
5
ij> -- PASS:0053 If count = 5?
+
SELECT COUNT(*)
FROM STAFF
WHERE NOT(EMPNUM LIKE '_36');
@@ -177,16 +207,20 @@
-----------
5
ij> -- PASS:0053 If count = 5?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0053 <<< END TEST
-- ***************************************************************
+
-- TEST:0054 IS NULL predicate!
+
-- setup
INSERT INTO STAFF
VALUES('E36','Huyan',36,NULL);
1 row inserted/updated/deleted
ij> -- PASS:0054 If 1 row is inserted?
+
SELECT EMPNAME
FROM STAFF
WHERE CITY IS NULL;
@@ -194,22 +228,27 @@
--------------------
Huyan
ij> -- PASS:0054 If EMPNAME = 'Huyan'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0054 <<< END TEST
-- ************************************************************
+
-- TEST:0055 NOT NULL predicate!
+
-- setup
INSERT INTO STAFF
VALUES('E36','Huyan',36,NULL);
1 row inserted/updated/deleted
ij> -- PASS:0055 If 1 row is inserted?
+
SELECT COUNT(*)
FROM STAFF;
1
-----------
6
ij> -- PASS:0055 If count = 6?
+
SELECT COUNT(*)
FROM STAFF
WHERE CITY IS NOT NULL;
@@ -217,6 +256,7 @@
-----------
5
ij> -- PASS:0055 If count = 5?
+
SELECT COUNT(*)
FROM STAFF
WHERE NOT (CITY IS NULL);
@@ -224,10 +264,12 @@
-----------
5
ij> -- PASS:0055 If count = 5?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0055 <<< END TEST
-- ***************************************************************
+
-- TEST:0056 NOT EXISTS predicate!
SELECT STAFF.EMPNAME
FROM STAFF
@@ -243,8 +285,10 @@
--------------------
Alice
ij> -- PASS:0056 If EMPNAME = 'Alice'?
+
-- END TEST >>> 0056 <<< END TEST
-- ************************************************************
+
-- TEST:0057 ALL quantifier !
SELECT CITY
FROM PROJ
@@ -256,8 +300,10 @@
---------------
Deale
ij> -- PASS:0057 If CITY = 'Deale'?
+
-- END TEST >>> 0057 <<< END TEST
-- **************************************************************
+
-- TEST:0058 SOME quantifier!
SELECT EMPNAME
FROM STAFF
@@ -269,8 +315,10 @@
--------------------
Betty
ij> -- PASS:0058 If EMPNAME = 'Betty'?
+
-- END TEST >>> 0058 <<< END TEST
-- *************************************************************
+
-- TEST:0059 ANY quantifier !
SELECT EMPNAME
FROM STAFF
@@ -282,7 +330,9 @@
--------------------
Betty
ij> -- PASS:0059 If EMPNAME = 'Betty'?
+
-- END TEST >>> 0059 <<< END TEST
+
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml015.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml015.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml015.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml015.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML015
+
-- SQL Test Suite, V6.0, Interactive SQL, dml015.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,16 +14,23 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- NO_TEST:0060 COMMIT work closes CURSORs!
+
-- Testing cursors
+
-- ************************************************************
+
-- TEST:0061 COMMIT work keeps changes to database!
+
INSERT INTO TEMP_S
SELECT EMPNUM, GRADE, CITY
FROM STAFF;
5 rows inserted/updated/deleted
ij> -- PASS:0061 If 5 rows are inserted?
+
COMMIT WORK;
ij> -- verify previous COMMIT keeps changes
ROLLBACK WORK;
@@ -36,14 +45,18 @@
E4 |12 |Deale
E5 |13 |Akron
ij> -- PASS:0061 If count = 5?
+
-- END TEST >>> 0061 <<< END TEST
-- ************************************************************
+
-- TEST:0062 ROLLBACK work cancels changes to database!
-- NOTE:0062 uses data created by TEST 0061
+
DELETE FROM TEMP_S
WHERE EMPNUM = 'E5';
1 row inserted/updated/deleted
ij> -- PASS:0062 If 1 row is deleted?
+
--O SELECT COUNT(*)
SELECT *
FROM TEMP_S;
@@ -54,6 +67,7 @@
E3 |13 |Vienna
E4 |12 |Deale
ij> -- PASS:0062 If count = 4?
+
-- restore
ROLLBACK WORK;
ij> --O SELECT COUNT(*)
@@ -67,14 +81,17 @@
E4 |12 |Deale
E5 |13 |Akron
ij> -- PASS:0062 If count = 5?
+
-- restore
DELETE FROM TEMP_S;
5 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0062 <<< END TEST
-- ***********************************************************
+
-- NO_TEST:0063 ROLLBACK work closes CURSORs!
+
-- Testing cursors
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml016.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml016.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml016.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml016.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML016
+
-- SQL Test Suite, V6.0, Interactive SQL, dml016.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION SULLIVAN
create schema SULLIVAN;
0 rows inserted/updated/deleted
@@ -14,8 +16,11 @@
--------------------------------------------------------------------------------------------------------------------------------
SULLIVAN1
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0064 SELECT USER!
+
SELECT USER, PNAME
FROM HU.PROJ;
1 |PNAME
@@ -27,12 +32,17 @@
SULLIVAN1 |IRM
SULLIVAN1 |PAYR
ij> -- PASS:0064 If 6 rows are selected and each USER = 'SULLIVAN' ?
+
-- END TEST >>> 0064 <<< END TEST
-- ***********************************************************
+
-- NO_TEST:0172 SELECT USER into short variable!
-- Tests Host Variable
+
-- **********************************************************
+
-- TEST:0065 SELECT CHAR literal and term with numeric literal!
+
SELECT 'USER',PNAME
FROM HU.PROJ;
1 |PNAME
@@ -44,6 +54,7 @@
USER|IRM
USER|PAYR
ij> -- PASS:0065 If 6 rows are selected and first column is value 'USER'?
+
SELECT PNUM,'BUDGET IN GRAMS IS ',BUDGET * 5
FROM HU.PROJ
WHERE PNUM = 'P1';
@@ -51,8 +62,10 @@
--------------------------------------------
P1 |BUDGET IN GRAMS IS |50000
ij> -- PASS:0065 If values are 'P1', 'BUDGET IN GRAMS IS ', 50000?
+
-- END TEST >>> 0065 <<< END TEST
-- ************************************************************
+
-- TEST:0066 SELECT numeric literal!
SELECT EMPNUM,10
FROM HU.STAFF
@@ -61,6 +74,7 @@
---------------
E2 |10
ij> -- PASS:0066 If 1 row with values 'E2' and 10?
+
SELECT EMPNUM, 10
FROM HU.STAFF;
EM&|2
@@ -72,7 +86,8 @@
E5 |10
ij> -- PASS:0066 If 5 rows are selected with second value always = 10?
-- PASS:0066 and EMPNUMs are 'E1', 'E2', 'E3', 'E4', 'E5'?
+
-- END TEST >>> 0066 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml018.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml018.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml018.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml018.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML018
+
-- SQL Test Suite, V6.0, Interactive SQL, dml018.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,7 +14,9 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0069 HAVING COUNT with WHERE, GROUP BY!
SELECT PNUM
FROM WORKS
@@ -25,8 +29,10 @@
P4
P5
ij> -- PASS:0069 If 3 rows are selected with PNUMs = 'P2', 'P4', 'P5'?
+
-- END TEST >>> 0069 <<< END TEST
-- ***********************************************************
+
-- TEST:0070 HAVING COUNT with GROUP BY!
SELECT PNUM
FROM WORKS
@@ -36,8 +42,10 @@
---
P2
ij> -- PASS:0070 If PNUM = 'P2'?
+
-- END TEST >>> 0070 <<< END TEST
-- ***********************************************************
+
-- TEST:0071 HAVING MIN, MAX with GROUP BY 3 columns!
SELECT EMPNUM, PNUM, HOURS
FROM WORKS
@@ -55,8 +63,10 @@
ij> -- PASS:0071 If 7 rows are selected: EMPNUM/PNUMs are 'E1'/'P1',?
-- PASS:0071 'E1'/'P2','E1'/'P4', 'E2'/'P1',?
-- PASS:0071 'E3'/'P2', 'E4'/'P2', 'E4'/'P4'?
+
-- END TEST >>> 0071 <<< END TEST
-- *************************************************************
+
-- TEST:0072 Nested HAVING IN with no outer reference!
SELECT WORKS.PNUM
FROM WORKS
@@ -73,8 +83,10 @@
P3
P6
ij> -- PASS:0072 If 3 rows are selected: WORKS.PNUMs are 'P2', 'P3', 'P6'?
+
-- END TEST >>> 0072 <<< END TEST
-- ***********************************************************
+
-- TEST:0073 HAVING MIN with no GROUP BY!
SELECT SUM(HOURS)
FROM WORKS
@@ -83,7 +95,8 @@
-----------
464
ij> -- PASS:0073 If 1 row is selected with SUM(HOURS) = 464?
+
-- END TEST >>> 0073 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml019.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml019.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml019.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml019.out Fri Oct 27 13:04:21 2006
@@ -1,7 +1,9 @@
ij> -- MODULE DML019
+
-- SQL Test Suite, V6.0, Interactive SQL, dml019.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -11,7 +13,9 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0074 GROUP BY col with SELECT col., SUM!
SELECT PNUM, SUM(HOURS)
FROM WORKS
@@ -27,8 +31,10 @@
ij> -- PASS:0074 If 6 rows are selected?
-- PASS:0074 If PNUMs: 'P1', 'P2', 'P3', 'P4', 'P5', 'P6'?
-- PASS:0074 If SUM(HOURS) for 'P2' is 140 ?
+
-- END TEST >>> 0074 <<< END TEST
-- **********************************************************
+
-- TEST:0075 GROUP BY clause!
SELECT EMPNUM
FROM WORKS
@@ -42,8 +48,10 @@
E3
E4
ij> -- PASS:0075 If 4 rows are selected with EMPNUMs: 'E1','E2','E3','E4'?
+
-- END TEST >>> 0075 <<< END TEST
-- ************************************************************
+
-- TEST:0076 GROUP BY 2 columns!
SELECT EMPNUM,HOURS
FROM WORKS
@@ -64,8 +72,10 @@
E4 |80
ij> -- PASS:0076 If 10 rows are selected and EMPNUM = 'E1' in 4 rows ?
-- PASS:0076 for 1 row EMPNUM = 'E1' and HOURS = 12?
+
-- END TEST >>> 0076 <<< END TEST
-- ***********************************************************
+
-- TEST:0077 GROUP BY all columns with SELECT * !
SELECT *
FROM WORKS
@@ -85,8 +95,10 @@
E4 |P4 |40
E4 |P5 |80
ij> -- PASS:0077 If 12 rows are selected ?
+
-- END TEST >>> 0077 <<< END TEST
-- ***********************************************************
+
-- TEST:0078 GROUP BY three columns, SELECT two!
SELECT PNUM,EMPNUM
FROM WORKS
@@ -106,18 +118,23 @@
P4 |E4
P5 |E4
ij> -- PASS:0078 If 12 rows are selected ?
+
-- END TEST >>> 0078 <<< END TEST
-- *********************************************************
+
-- TEST:0079 GROUP BY NULL value!
+
-- setup
INSERT INTO STAFF(EMPNUM,EMPNAME,GRADE)
VALUES('E6','WANG',40);
1 row inserted/updated/deleted
ij> -- PASS:0079 If 1 row is inserted?
+
INSERT INTO STAFF(EMPNUM,EMPNAME,GRADE)
VALUES('E7','SONG',50);
1 row inserted/updated/deleted
ij> -- PASS:0079 If 1 row is inserted?
+
SELECT SUM(GRADE)
FROM STAFF
WHERE CITY IS NULL
@@ -126,10 +143,12 @@
---------
90
ij> -- PASS:0079 If SUM(GRADE) = 90?
+
-- restore
DELETE FROM STAFF WHERE CITY IS NULL;
2 rows inserted/updated/deleted
ij> -- PASS:0079 If 2 rows deleted?
+
--O SELECT COUNT(*) FROM STAFF;
SELECT * FROM STAFF;
EM&|EMPNAME |GRADE|CITY
@@ -140,7 +159,9 @@
E4 |Don |12 |Deale
E5 |Ed |13 |Akron
ij> -- PASS:0079 If count = 5?
+
-- END TEST >>> 0079 <<< END TEST
+
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml020.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml020.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml020.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml020.out Fri Oct 27 13:04:21 2006
@@ -1,7 +1,9 @@
ij> -- MODULE DML020
+
-- SQL Test Suite, V6.0, Interactive SQL, dml020.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -11,7 +13,9 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0080 Simple two-table join!
SELECT EMPNUM,EMPNAME,GRADE,STAFF.CITY, PNAME, PROJ.CITY
FROM STAFF, PROJ
@@ -31,8 +35,10 @@
E4 |Don |12 |Deale |SDP |Deale
ij> -- PASS:0080 If 10 rows are selected with EMPNAMEs:'Alice', 'Betty', ?
-- PASS:0080 'Carmen', and 'Don' but not 'Ed'?
+
-- END TEST >>> 0080 <<< END TEST
-- **************************************************************
+
-- TEST:0081 Simple two-table join with filter!
--
-- Added ORDER BY clause to get stable results across platforms - Jeff
@@ -49,8 +55,10 @@
E3 |Carmen |13 |Vienna |P2 |CALM |Code |30000 |Vienna
E3 |Carmen |13 |Vienna |P5 |IRM |Test |10000 |Vienna
ij> -- PASS:0081 If 4 rows selected with EMPNAMEs 'Betty' and 'Carmen' ?
+
-- END TEST >>> 0081 <<< END TEST
-- **************************************************************
+
-- TEST:0082 Join 3 tables!
SELECT DISTINCT STAFF.CITY, PROJ.CITY
FROM STAFF, WORKS, PROJ
@@ -65,8 +73,10 @@
Vienna |Deale
Vienna |Vienna
ij> -- PASS:0082 If 5 distinct rows are selected ?
+
-- END TEST >>> 0082 <<< END TEST
-- ************************************************************
+
-- TEST:0083 Join a table with itself!
SELECT FIRST1.EMPNUM, SECOND2.EMPNUM
FROM STAFF FIRST1, STAFF SECOND2
@@ -79,7 +89,8 @@
E2 |E3
ij> -- PASS:0083 If 2 rows are selected and ?
-- PASS:0083 If EMPNUM pairs are 'E1'/'E4' and 'E2'/'E3'?
+
-- END TEST >>> 0083 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml021.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml021.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml021.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml021.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML021
+
-- SQL Test Suite, V6.0, Interactive SQL, dml021.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,201 +14,251 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0084 Data type CHAR(20)!
+
-- setup
INSERT INTO AA
VALUES('abcdefghijklmnopqrst');
1 row inserted/updated/deleted
ij> -- PASS:0084 If 1 row is inserted?
+
SELECT CHARTEST
FROM AA;
CHARTEST
--------------------
abcdefghijklmnopqrst
ij> -- PASS:0084 If CHARTEST = 'abcdefghijklmnopqrst' ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0084 <<< END TEST
-- *************************************************************
+
-- TEST:0173 Data type CHAR!
+
-- setup
INSERT INTO BB
VALUES('a');
1 row inserted/updated/deleted
ij> -- PASS:0173 If 1 row is inserted?
+
SELECT CHARTEST
FROM BB;
CHA&
----
a
ij> -- PASS:0173 If CHARTEST = 'a'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0173 <<< END TEST
-- *****************************************************************
+
-- TEST:0085 Data type CHARACTER(20)!
+
-- setup
INSERT INTO CC
VALUES('abcdefghijklmnopqrst');
1 row inserted/updated/deleted
ij> -- PASS:0085 If 1 row is inserted?
+
SELECT CHARTEST
FROM CC;
CHARTEST
--------------------
abcdefghijklmnopqrst
ij> -- PASS:0085 If CHARTEST = 'abcdefghijklmnopqrst'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0085 <<< END TEST
-- *************************************************************
+
-- TEST:0174 Data type CHARACTER!
+
-- setup
INSERT INTO DD
VALUES('a');
1 row inserted/updated/deleted
ij> -- PASS:0174 If 1 row is inserted?
+
SELECT CHARTEST
FROM DD;
CHA&
----
a
ij> -- PASS:0174 If CHARTEST = 'a'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0174 <<< END TEST
-- ****************************************************************
+
-- TEST:0086 Data type INTEGER!
+
-- setup
INSERT INTO EE
VALUES(123456);
1 row inserted/updated/deleted
ij> -- PASS:0086 If 1 row is inserted?
+
SELECT INTTEST
FROM EE;
INTTEST
-----------
123456
ij> -- PASS:0086 If INTTEST = 123456?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0086 <<< END TEST
-- ***************************************************************
+
-- TEST:0087 Data type INT!
+
-- setup
INSERT INTO FF
VALUES(123456);
1 row inserted/updated/deleted
ij> -- PASS:0087 If 1 row is inserted?
+
SELECT INTTEST
FROM FF;
INTTEST
-----------
123456
ij> -- PASS:0087 If INTTEST = 123456?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0087 <<< END TEST
-- **************************************************************
+
-- TEST:0089 Data type SMALLINT!
+
-- setup
INSERT INTO HH
VALUES(123);
1 row inserted/updated/deleted
ij> -- PASS:0089 If 1 row is inserted?
+
SELECT *
FROM HH;
SMALL&
------
123
ij> -- PASS:0089 If SMALLTEST = 123?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0089 <<< END TEST
-- ****************************************************************
+
-- TEST:0175 Data type NUMERIC!
+
-- setup
INSERT INTO MM
VALUES(7);
1 row inserted/updated/deleted
ij> -- PASS:0175 If 1 row is inserted?
+
SELECT *
FROM MM;
NUMTE&
------
7
ij> -- PASS:0175 If NUMTEST = 7?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0175 <<< END TEST
-- ****************************************************************
+
-- TEST:0176 Data type NUMERIC(9), SELECT *!
+
-- making sure table is empty
DELETE FROM NN;
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> -- setup
INSERT INTO NN
VALUES(123456789);
1 row inserted/updated/deleted
ij> -- PASS:0176 If 1 row is inserted?
+
SELECT *
FROM NN;
NUMTEST
----------
123456789
ij> -- PASS:0176 If NUMTEST = 123456789 ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0176 <<< END TEST
-- *****************************************************************
+
-- TEST:0177 Data type NUMERIC(9), SELECT column!
+
-- setup
INSERT INTO OO
VALUES(123456789);
1 row inserted/updated/deleted
ij> -- PASS:0177 If 1 row is inserted ?
+
SELECT NUMTEST
FROM OO;
NUMTEST
----------
123456789
ij> -- PASS:0177 If NUMTEST = 123456789?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0177 <<< END TEST
-- **************************************************************
+
-- TEST:0178 Data type DECIMAL!
+
-- setup
INSERT INTO QQ
VALUES(56);
1 row inserted/updated/deleted
ij> -- PASS:0178 If 1 row is inserted?
+
SELECT *
FROM QQ;
NUMTE&
------
56
ij> -- PASS:0178 If NUMTEST = 56?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0178 <<< END TEST
-- **************************************************************
+
-- TEST:0179 Data type DECIMAL(8)!
+
-- setup
INSERT INTO RR
VALUES(12345678);
1 row inserted/updated/deleted
ij> -- PASS:0179 If 1 row is inserted?
+
SELECT *
FROM RR;
NUMTEST
---------
12345678
ij> -- PASS:0179 If NUMTEST = 12345678?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0179 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml022.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml022.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml022.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml022.out Fri Oct 27 13:04:21 2006
@@ -1,7 +1,9 @@
ij> -- MODULE DML022
+
-- SQL Test Suite, V6.0, Interactive SQL, dml022.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -11,7 +13,9 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0096 Subquery with MAX in < comparison predicate!
SELECT EMPNUM
FROM STAFF
@@ -24,8 +28,10 @@
E2
E4
ij> -- PASS:0096 If 3 rows selected with EMPNUMs:'E1', 'E2', 'E4'?
+
-- END TEST >>> 0096 <<< END TEST
-- **********************************************************
+
-- TEST:0097 Subquery with AVG - 1 in <= comparison predicate!
SELECT *
FROM STAFF
@@ -36,8 +42,10 @@
----------------------------------------------
E2 |Betty |10 |Vienna
ij> -- PASS:0097 If EMPNUM = 'E2' and EMPNAME = 'Betty'?
+
-- END TEST >>> 0097 <<< END TEST
-- *******************************************************************
+
-- TEST:0098 IN predicate with simple subquery!
SELECT EMPNAME
FROM STAFF
@@ -53,8 +61,10 @@
Carmen
Don
ij> -- PASS:0098 If 4 rows selected and first EMPNAME = 'Alice'?
+
-- END TEST >>> 0098 <<< END TEST
-- ***************************************************************
+
-- TEST:0099 Nested IN predicate - 2 levels!
SELECT EMPNAME
FROM STAFF
@@ -71,8 +81,10 @@
Betty
Don
ij> -- PASS:0099 If 3 rows selected with EMPNAMEs:'Alice', 'Betty', 'Don'?
+
-- END TEST >>> 0099 <<< END TEST
-- *****************************************************************
+
-- TEST:0100 Nested IN predicate - 6 levels!
SELECT EMPNUM, EMPNAME
FROM STAFF
@@ -104,8 +116,10 @@
E4 |Don
ij> -- PASS:0100 If 4 rows selected and first EMPNUM = 'E1'?
-- PASS:0100 and first EMPNAME = 'Alice'?
+
-- END TEST >>> 0100 <<< END TEST
-- ****************************************************************
+
-- TEST:0101 Quantified predicate <= ALL with AVG in GROUP BY!
SELECT EMPNUM,PNUM
FROM WORKS
@@ -118,8 +132,10 @@
E1 |P5
E1 |P6
ij> -- PASS:0101 If 2 rows selected and each EMPNUM = 'E1'?
+
-- END TEST >>> 0101 <<< END TEST
-- *******************************************************************
+
-- TEST:0102 Nested NOT EXISTS with correlated subquery and DISTINCT!
SELECT DISTINCT EMPNUM
FROM WORKS WORKSX
@@ -137,7 +153,8 @@
E1
E2
ij> -- PASS:0102 If 2 rows selected with EMPNUMs:'E1', 'E2'?
+
-- END TEST >>> 0102 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml023.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml023.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml023.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml023.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML023
+
-- SQL Test Suite, V6.0, Interactive SQL, dml023.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,7 +14,9 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0103 Subquery with comparison predicate!
SELECT PNUM
FROM PROJ
@@ -26,8 +30,10 @@
P4
P6
ij> -- PASS:0103 If 3 rows are selected with PNUMs:'P1','P4','P6?
+
-- END TEST >>> 0103 <<< END TEST
-- **************************************************************
+
-- TEST:0104 SQLCODE < 0, subquery with more than 1 value!
SELECT PNUM
FROM PROJ
@@ -38,8 +44,10 @@
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- PASS:0104 If ERROR, SELECT returns more than 1 row in subquery?
-- PASS:0104 If 0 rows are selected?
+
-- END TEST >>> 0104 <<< END TEST
-- ************************************************************
+
-- TEST:0105 Subquery in comparison predicate is empty!
--O SELECT COUNT(*)
SELECT *
@@ -51,6 +59,7 @@
EM&|EMPNAME |GRADE|CITY
----------------------------------------------
ij> -- PASS:0105 If count = 0?
+
--O SELECT COUNT(*)
SELECT *
FROM STAFF
@@ -61,8 +70,10 @@
EM&|EMPNAME |GRADE|CITY
----------------------------------------------
ij> -- PASS:0105 If count = 0?
+
-- END TEST >>> 0105 <<< END TEST
-- *************************************************************
+
-- TEST:0106 Comparison predicate <> !
SELECT PNUM
FROM PROJ
@@ -73,8 +84,10 @@
P3
P5
ij> -- PASS:0106 If 3 rows are selected with PNUMs:'P2','P3','P5'?
+
-- END TEST >>> 0106 <<< END TEST
-- *************************************************************
+
-- TEST:0107 Comp predicate with short string logically blank padded!
--O SELECT COUNT(*)
SELECT empnum
@@ -89,6 +102,7 @@
E1
E1
ij> -- PASS:0107 If count = 6 ?
+
--O SELECT COUNT(*)
SELECT empnum
FROM WORKS
@@ -102,15 +116,19 @@
E1
E1
ij> -- PASS:0107 If count = 6?
+
-- END TEST >>> 0107 <<< END TEST
-- ****************************************************************
+
-- TEST:0180 NULLs sort together in ORDER BY!
+
-- setup
UPDATE STAFF
SET GRADE = NULL
WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';
3 rows inserted/updated/deleted
ij> -- PASS:0180 If 3 rows are updated?
+
SELECT EMPNUM,GRADE
FROM STAFF
ORDER BY GRADE,EMPNUM;
@@ -124,17 +142,21 @@
ij> -- PASS:0180 If 5 rows are selected with NULLs together ?
-- PASS:0180 If first EMPNUM is either 'E1' or 'E2'?
-- PASS:0180 If last EMPNUM is either 'E4' or 'E5?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0180 <<< END TEST
-- ***************************************************************
+
-- TEST:0181 NULLs are equal for DISTINCT!
+
-- setup
UPDATE STAFF
SET GRADE = NULL
WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';
3 rows inserted/updated/deleted
ij> -- PASS:0181 If 3 rows are updated?
+
SELECT DISTINCT USER, GRADE
FROM STAFF
ORDER BY GRADE;
@@ -145,9 +167,11 @@
HU |NULL
ij> -- PASS:0181 If 3 rows are selected with GRADEs:10, 12, NULL ?
-- PASS:0181 GRADE 10 precedes GRADE 12?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0181 <<< END TEST
+
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml024.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml024.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml024.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml024.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML024
+
-- SQL Test Suite, V6.0, Interactive SQL, dml024.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,7 +14,9 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0108 Search condition true OR NOT(true)!
SELECT EMPNUM,CITY
FROM STAFF
@@ -25,8 +29,10 @@
E4 |Deale
E5 |Akron
ij> -- PASS:0108 If 5 rows are selected ?
+
-- END TEST >>> 0108 <<< END TEST
-- ****************************************************************
+
-- TEST:0109 Search condition true AND NOT(true)!
SELECT EMPNUM,CITY
FROM STAFF
@@ -34,14 +40,18 @@
EM&|CITY
-------------------
ij> -- PASS:0109 If 0 rows are selected ?
+
-- END TEST >>> 0109 <<< END TEST
-- **************************************************************
+
-- TEST:0110 Search condition unknown OR NOT(unknown)!
+
-- setup
INSERT INTO WORKS
VALUES('E8','P8',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0110 If 1 row is inserted?
+
SELECT EMPNUM,PNUM
FROM WORKS
WHERE HOURS < (SELECT HOURS FROM WORKS
@@ -51,16 +61,20 @@
EM&|PN&
-------
ij> -- PASS:0110 If 0 rows are selected ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0110 <<< END TEST
-- *************************************************************
+
-- TEST:0111 Search condition unknown AND NOT(unknown)!
+
-- setup
INSERT INTO WORKS
VALUES('E8','P8',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0111 If 1 row is inserted?
+
SELECT EMPNUM,PNUM
FROM WORKS
WHERE HOURS < (SELECT HOURS FROM WORKS
@@ -70,16 +84,20 @@
EM&|PN&
-------
ij> -- PASS:0111 If 0 rows are selected?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0111 <<< END TEST
-- ***************************************************************
+
-- TEST:0112 Search condition unknown AND true!
+
-- setup
INSERT INTO WORKS
VALUES('E8','P8',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0112 If 1 row is inserted?
+
SELECT EMPNUM,PNUM
FROM WORKS
WHERE HOURS < (SELECT HOURS FROM WORKS
@@ -88,16 +106,20 @@
EM&|PN&
-------
ij> -- PASS:0112 If 0 rows are selected?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0112 <<< END TEST
-- *************************************************************
+
-- TEST:0113 Search condition unknown OR true!
+
-- setup
INSERT INTO WORKS
VALUES('E8','P8',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0113 If 1 row is inserted?
+
SELECT EMPNUM,PNUM
FROM WORKS
WHERE HOURS < (SELECT HOURS FROM WORKS
@@ -120,9 +142,10 @@
E4 |P2
ij> -- PASS:0113 If 12 rows are selected?
-- PASS:0113 If first EMPNUM = 'E1'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0113 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml025.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml025.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml025.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml025.out Fri Oct 27 13:04:21 2006
@@ -1,7 +1,9 @@
ij> -- MODULE DML025
+
-- SQL Test Suite, V6.0, Interactive SQL, dml025.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -11,7 +13,9 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0114 Set functions without GROUP BY returns 1 row!
SELECT SUM(HOURS),AVG(HOURS),MIN(HOURS),MAX(HOURS)
FROM WORKS
@@ -21,8 +25,10 @@
184 |30.6666 |12 |80
ij> -- PASS:0114 If SUM(HOURS) = 184 and AVG(HOURS) is 30 to 31?
-- PASS:0114 If MIN(HOURS) = 12 and MAX(HOURS) = 80 ?
+
-- END TEST >>> 0114 <<< END TEST
-- ***********************************************************
+
-- TEST:0115 GROUP BY col, set function: 0 groups returns empty table!
SELECT PNUM,AVG(HOURS),MIN(HOURS),MAX(HOURS)
FROM WORKS
@@ -31,8 +37,10 @@
PN&|2 |3 |4
-------------------------------
ij> -- PASS:0115 If 0 rows are selected ?
+
-- END TEST >>> 0115 <<< END TEST
-- ***********************************************************
+
-- TEST:0116 GROUP BY set functions: zero groups returns empty table!
SELECT SUM(HOURS),AVG(HOURS),MIN(HOURS),MAX(HOURS)
FROM WORKS
@@ -41,8 +49,10 @@
1 |2 |3 |4
---------------------------------------
ij> -- PASS:0116 If 0 rows are selected?
+
-- END TEST >>> 0116 <<< END TEST
-- ***************************************************************
+
-- TEST:0117 GROUP BY column, set functions with several groups!
SELECT PNUM,AVG(HOURS),MIN(HOURS),MAX(HOURS)
FROM WORKS
@@ -58,7 +68,8 @@
P6 |12.0000 |12 |12
ij> -- PASS:0117 If 6 rows are selected and first PNUM = 'P1'?
-- PASS:0117 and first MAX(HOURS) = 40?
+
-- END TEST >>> 0117 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml026.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml026.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml026.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml026.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML026
+
-- SQL Test Suite, V6.0, Interactive SQL, dml026.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,37 +14,48 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0118 Monadic arithmetic operator +!
+
SELECT +MAX(DISTINCT HOURS)
FROM WORKS;
1
------
80
ij> -- PASS:0118 If +MAX(DISTINCT HOURS) = 80?
+
-- END TEST >>> 0118 <<< END TEST
-- *********************************************************
+
-- TEST:0119 Monadic arithmetic operator -!
+
SELECT -MAX(DISTINCT HOURS)
FROM WORKS;
1
------
-80
ij> -- PASS:0119 If -MAX(DISTINCT HOURS) = -80?
+
-- END TEST >>> 0119 <<< END TEST
-- *********************************************************
+
-- TEST:0120 Value expression with NULL primary IS NULL!
+
-- setup
INSERT INTO WORKS1
SELECT *
FROM WORKS;
12 rows inserted/updated/deleted
ij> -- PASS:0120 If 12 rows are inserted ?
+
-- setup
INSERT INTO WORKS1
VALUES('E9','P1',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0120 If 1 row is inserted?
+
SELECT EMPNUM
FROM WORKS1
WHERE HOURS IS NULL;
@@ -50,7 +63,9 @@
---
E9
ij> -- PASS:0120 If EMPNUM = 'E9'?
+
-- NOTE:0120 we insert into WORKS from WORKS1
+
-- setup
INSERT INTO WORKS
SELECT EMPNUM,'P9',20+HOURS
@@ -58,6 +73,7 @@
WHERE EMPNUM='E9';
1 row inserted/updated/deleted
ij> -- PASS:0120 If 1 row is inserted?
+
--O SELECT COUNT(*)
SELECT *
FROM WORKS
@@ -66,6 +82,7 @@
--------------
E9 |P9 |NULL
ij> -- PASS:0120 If count = 1 ?
+
--O SELECT COUNT(*)
SELECT *
FROM WORKS
@@ -74,11 +91,14 @@
--------------
E9 |P9 |NULL
ij> -- PASS:0120 If count = 1 ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0120 <<< END TEST
-- **********************************************************
+
-- TEST:0121 Dyadic operators +, -, *, /!
+
--O SELECT COUNT(*)
SELECT *
FROM VTABLE;
@@ -89,6 +109,7 @@
100 |200 |300 |400 |500.01
1000 |-2000 |3000 |NULL |4000.00
ij> -- PASS:0121 If count = 4 ?
+
SELECT +COL1+COL2 - COL3*COL4/COL1
FROM VTABLE
WHERE COL1=10;
@@ -96,9 +117,12 @@
-----------
-90
ij> -- PASS:0121 If answer is -90?
+
-- END TEST >>> 0121 <<< END TEST
-- *********************************************************
+
-- TEST:0122 Divisor shall not be zero!
+
SELECT COL2/COL1+COL3
FROM VTABLE
WHERE COL4=3;
@@ -106,9 +130,12 @@
-----------
ERROR 22012: Attempt to divide by zero.
ij> -- PASS:0122 If ERROR Number not Divisible by Zero?
+
-- END TEST >>> 0122 <<< END TEST
-- **********************************************************
+
-- TEST:0123 Evaluation order of expression!
+
SELECT (-COL2+COL1)*COL3 - COL3/COL1
FROM VTABLE
WHERE COL4 IS NULL;
@@ -116,7 +143,8 @@
-----------
8999997
ij> -- PASS:0123 If Answer is 8999997 (plus or minus 0.5)?
+
-- END TEST >>> 0123 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml027.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml027.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml027.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml027.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML027
+
-- SQL Test Suite, V6.0, Interactive SQL, dml027.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,39 +14,48 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0124 UPDATE UNIQUE column (key = key + 1) interim conflict!
+
-- setup
UPDATE UPUNIQ
SET NUMKEY = NUMKEY + 1;
6 rows inserted/updated/deleted
ij> -- PASS:0124 If 6 rows updated?
+
SELECT COUNT(*),SUM(NUMKEY)
FROM UPUNIQ;
1 |2
-------------------
6 |30
ij> -- PASS:0124 If count = 6 and SUM(NUMKEY) = 30?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0124 <<< END TEST
-- ********************************************************
+
-- TEST:0125 UPDATE UNIQUE column (key = key + 1) no interim conflit!
+
-- setup
UPDATE UPUNIQ
SET NUMKEY = NUMKEY + 1
WHERE NUMKEY >= 4;
3 rows inserted/updated/deleted
ij> -- PASS:0125 If 3 rows are updated?
+
SELECT COUNT(*),SUM(NUMKEY)
FROM UPUNIQ;
1 |2
-------------------
6 |27
ij> -- PASS:0125 If count = 6 and SUM(NUMKEY) = 27?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0125 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml029.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml029.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml029.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml029.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML029
+
-- SQL Test Suite, V6.0, Interactive SQL, dml029.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,13 +14,17 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0129 Double quote work in character string literal!
+
-- setup
INSERT INTO STAFF
VALUES('E8','Yang Ling',15,'Xi''an');
1 row inserted/updated/deleted
ij> -- PASS:0129 If 1 row is inserted?
+
SELECT GRADE,CITY
FROM STAFF
WHERE EMPNUM = 'E8';
@@ -26,16 +32,20 @@
---------------------
15 |Xi'an
ij> -- PASS:0129 If GRADE = 15 and CITY = 'Xi'an'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0129 <<< END TEST
-- ************************************************************
+
-- TEST:0130 Approximate numeric literal <mantissa>E<exponent>!
+
-- setup
INSERT INTO JJ
VALUES(123.456E3);
1 row inserted/updated/deleted
ij> -- PASS:0130 If 1 row is inserted?
+
--O SELECT COUNT(*)
SELECT *
FROM JJ
@@ -44,16 +54,20 @@
----------------------
123456.0
ij> -- PASS:0130 If count = 1 ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0130 <<< END TEST
-- ***************************************************************
+
-- TEST:0131 Approximate numeric literal with negative exponent!
+
-- setup
INSERT INTO JJ
VALUES(123456E-3);
1 row inserted/updated/deleted
ij> -- PASS:0131 If 1 row is inserted?
+
--O SELECT COUNT(*)
SELECT *
FROM JJ
@@ -62,16 +76,20 @@
----------------------
123.456
ij> -- PASS:0131 If count = 1 ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0131 <<< END TEST
-- ********************************************************
+
-- TEST:0182 Approx numeric literal with negative mantissa & exponent!
+
-- setup
INSERT INTO JJ
VALUES(-123456E-3);
1 row inserted/updated/deleted
ij> -- PASS:0182 If 1 row is inserted?
+
--O SELECT COUNT(*)
SELECT *
FROM JJ
@@ -80,9 +98,10 @@
----------------------
-123.456
ij> -- PASS:0182 If count = 1 ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0182 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml033.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml033.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml033.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml033.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML033
+
-- SQL Test Suite, V6.0, Interactive SQL, dml033.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,13 +14,17 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0135 Upper and loer case letters are distinct!
+
-- setup
INSERT INTO WORKS
VALUES('UPP','low',100);
1 row inserted/updated/deleted
ij> -- PASS:0135 If 1 row is inserted?
+
SELECT EMPNUM,PNUM
FROM WORKS
WHERE EMPNUM='UPP' AND PNUM='low';
@@ -26,15 +32,17 @@
-------
UPP|low
ij> -- PASS:0135 If EMPNUM = 'UPP' and PNUM = 'low'?
+
SELECT EMPNUM,PNUM
FROM WORKS
WHERE EMPNUM='upp' OR PNUM='LOW';
EM&|PN&
-------
ij> -- PASS:0135 If 0 rows are selected - out of data?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0135 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml034.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml034.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml034.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml034.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML034
+
-- SQL Test Suite, V6.0, Interactive SQL, dml034.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,13 +14,17 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0088 Data type REAL!
+
-- setup
INSERT INTO GG
VALUES(123.4567E-2);
1 row inserted/updated/deleted
ij> -- PASS:0088 If 1 row is inserted?
+
SELECT REALTEST
FROM GG;
REALTEST
@@ -26,6 +32,7 @@
1.234567
ij> -- PASS:0088 If REALTEST = 1.234567 ?
-- PASS:0088 OR is between 1.234562 and 1.234572 ?
+
SELECT *
FROM GG
WHERE REALTEST > 1.234561 and REALTEST < 1.234573;
@@ -33,16 +40,20 @@
-------------
1.234567
ij> -- PASS:0088 If 1 row selected?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0088 <<< END TEST
-- ****************************************************************
+
-- TEST:0090 Data type DOUBLE PRECISION!
+
-- setup
INSERT INTO II
VALUES(0.123456123456E6);
1 row inserted/updated/deleted
ij> -- PASS:0090 If 1 row is inserted?
+
SELECT DOUBLETEST
FROM II;
DOUBLETEST
@@ -50,6 +61,7 @@
123456.123456
ij> -- PASS:0090 If DOUBLETEST = 123456.123456 ?
-- PASS:0090 OR is between 123456.123451 and 123456.123461 ?
+
SELECT *
FROM II
WHERE DOUBLETEST > 123456.123450 and DOUBLETEST < 123456.123462;
@@ -57,16 +69,20 @@
----------------------
123456.123456
ij> -- PASS:0090 If 1 row selected?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0090 <<< END TEST
-- ***********************************************************
+
-- TEST:0091 Data type FLOAT!
+
-- setup
INSERT INTO JJ
VALUES(12.345678);
1 row inserted/updated/deleted
ij> -- PASS:0091 If 1 row is inserted?
+
SELECT FLOATTEST
FROM JJ;
FLOATTEST
@@ -74,6 +90,7 @@
12.345678
ij> -- PASS:0091 If FLOATTEST = 12.345678 ?
-- PASS:0091 OR is between 12.345673 and 12.345683 ?
+
SELECT *
FROM JJ
WHERE FLOATTEST > 12.345672 and FLOATTEST < 12.345684;
@@ -81,16 +98,20 @@
----------------------
12.345678
ij> -- PASS:0091 If 1 row selected?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0091 <<< END TEST
-- **********************************************************
+
-- TEST:0092 Data type FLOAT(32)!
+
-- setup
INSERT INTO KK
VALUES(123.456123456E+3);
1 row inserted/updated/deleted
ij> -- PASS:0092 If 1 row is inserted?
+
SELECT FLOATTEST
FROM KK;
FLOATTEST
@@ -98,6 +119,7 @@
123456.123456
ij> -- PASS:0092 If FLOATTEST = 123456.123456 ?
-- PASS:0092 OR is between 123456.1233 and 123456.1236 ?
+
SELECT *
FROM KK
WHERE FLOATTEST > 123456.123450 and FLOATTEST < 123456.123462;
@@ -105,16 +127,20 @@
----------------------
123456.123456
ij> -- PASS:0092 If 1 row selected?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0092 <<< END TEST
-- *************************************************************
+
-- TEST:0093 Data type NUMERIC(13,6)!
+
-- setup
INSERT INTO LL
VALUES(123456.123456);
1 row inserted/updated/deleted
ij> -- PASS:0093 If 1 row is inserted?
+
SELECT *
FROM LL;
NUMTEST
@@ -122,6 +148,7 @@
123456.123456
ij> -- PASS:0093 If NUMTEST = 123456.123456 ?
-- PASS:0093 OR is between 123456.123451 and 123456.123461 ?
+
SELECT *
FROM LL
WHERE NUMTEST > 123456.123450 and NUMTEST < 123456.123462;
@@ -129,16 +156,20 @@
---------------
123456.123456
ij> -- PASS:0093 If 1 row selected?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0093 <<< END TEST
-- *************************************************************
+
-- TEST:0094 Data type DECIMAL(13,6)!
+
-- setup
INSERT INTO PP
VALUES(123456.123456);
1 row inserted/updated/deleted
ij> -- PASS:0094 If 1 row is inserted?
+
SELECT *
FROM PP;
NUMTEST
@@ -146,16 +177,20 @@
123456.123456
ij> -- PASS:0094 If NUMTEST = 123456.123456 ?
-- PASS:0094 OR is between 123456.123451 and 123456.123461 ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0094 <<< END TEST
-- **************************************************************
+
-- TEST:0095 Data type DEC(13,6)!
+
-- setup
INSERT INTO SS
VALUES(123456.123456);
1 row inserted/updated/deleted
ij> -- PASS:0095 If 1 row is inserted?
+
SELECT *
FROM SS;
NUMTEST
@@ -163,9 +198,10 @@
123456.123456
ij> -- PASS:0095 If NUMTEST = 123456.123456 ?
-- PASS:0095 OR is between 123456.123451 and 123456.123461 ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0095 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml035.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml035.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml035.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml035.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML035
+
-- SQL Test Suite, V6.0, Interactive SQL, dml035.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,8 +14,11 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0157 ORDER BY approximate numeric!
+
-- setup
INSERT INTO JJ VALUES(66.2);
1 row inserted/updated/deleted
@@ -33,6 +38,7 @@
INSERT INTO JJ VALUES(-66.25);
1 row inserted/updated/deleted
ij> -- PASS:0157 If 1 row is inserted?
+
SELECT FLOATTEST
FROM JJ
ORDER BY FLOATTEST DESC;
@@ -46,9 +52,10 @@
-87.0
ij> -- PASS:0157 If 6 rows are selected ?
-- PASS:0157 If last FLOATTEST = -87 OR is between -87.5 and -86.5 ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0157 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml037.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml037.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml037.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml037.out Fri Oct 27 13:04:21 2006
@@ -1,7 +1,9 @@
ij> -- MODULE DML037
+
-- SQL Test Suite, V6.0, Interactive SQL, dml037.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -11,15 +13,22 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- NO_TEST:0202 Host variable names same as column name!
+
-- Testing host identifier
+
-- ***********************************************************
+
-- TEST:0234 SQL-style comments with SQL statements!
-- OPTIONAL TEST
+
DELETE -- we empty the table
FROM TEXT240;
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> INSERT INTO TEXT240 -- This is the test for the rules
VALUES -- for the placement
('SQL-STYLE COMMENTS') -- of
@@ -27,15 +36,17 @@
;
1 row inserted/updated/deleted
ij> -- PASS:0234 If 1 row is inserted?
+
SELECT *
FROM TEXT240;
TEXXT
--------------------------------------------------------------------------------------------------------------------------------
SQL-STYLE COMMENTS &
ij> -- PASS:0234 If TEXXT = 'SQL-STYLE COMMENTS'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0234 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml038.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml038.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml038.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml038.out Fri Oct 27 13:04:21 2006
@@ -1,7 +1,9 @@
ij> -- MODULE DML038
+
-- SQL Test Suite, V6.0, Interactive SQL, dml038.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -11,8 +13,11 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0205 Cartesian product is produced without WHERE clause!
+
SELECT GRADE, HOURS, BUDGET
FROM STAFF, WORKS, PROJ order by GRADE, HOURS, BUDGET;
GRADE|HOURS |BUDGET
@@ -378,7 +383,8 @@
13 |80 |50000
13 |80 |50000
ij> -- PASS:0205 If 360 rows are selected ?
+
-- END TEST >>> 0205 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml039.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml039.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml039.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml039.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML039
+
-- SQL Test Suite, V6.0, Interactive SQL, dml039.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,17 +14,22 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0208 Upper and lower case in LIKE predicate!
+
-- setup
INSERT INTO STAFF
VALUES('E7', 'yanping',26,'China');
1 row inserted/updated/deleted
ij> -- PASS:0208 If 1 row is inserted?
+
INSERT INTO STAFF
VALUES('E8','YANPING',30,'NIST');
1 row inserted/updated/deleted
ij> -- PASS:0208 If 1 row is inserted?
+
SELECT CITY
FROM STAFF
WHERE EMPNAME LIKE 'yan____%';
@@ -30,6 +37,7 @@
---------------
China
ij> -- PASS:0208 If CITY = 'China'?
+
SELECT CITY
FROM STAFF
WHERE EMPNAME LIKE 'YAN____%';
@@ -37,9 +45,10 @@
---------------
NIST
ij> -- PASS:0208 If CITY = 'NIST'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0208 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml042.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml042.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml042.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml042.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML042
+
-- SQL Test Suite, V6.0, Interactive SQL, dml042.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,23 +14,29 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0213 FIPS sizing -- 100 columns in a row!
-- FIPS sizing TEST
+
-- setup
INSERT INTO T100(C1,C21,C41,C61,C81,C100)
VALUES(' 1','21','41','61','81','00');
1 row inserted/updated/deleted
ij> -- PASS:0213 If 1 row is inserted?
+
SELECT C1,C21,C41,C61,C81,C100
FROM T100;
C1 |C21 |C41 |C61 |C81 |C100
-----------------------------
1 |21 |41 |61 |81 |00
ij> -- PASS:0213 If C1 = ' 1' and C100 = '00' ?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0213 <<< END TEST
+
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml043.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml043.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml043.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml043.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML043
+
-- SQL Test Suite, V6.0, Interactive SQL, dml043.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,9 +14,12 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0214 FIPS sizing -- 2000-byte row!
-- FIPS sizing TEST
+
-- setup
INSERT INTO T2000(STR110,STR200,STR216)
VALUES
@@ -23,16 +28,19 @@
'STR66666666666666666666666666666666666666666666666');
1 row inserted/updated/deleted
ij> -- PASS:0214 If 1 row is inserted?
+
UPDATE T2000
SET STR140 =
'STR44444444444444444444444444444444444444444444444';
1 row inserted/updated/deleted
ij> -- PASS:0214 If 1 row is updated?
+
UPDATE T2000
SET STR180 =
'STR88888888888888888888888888888888888888888888888';
1 row inserted/updated/deleted
ij> -- PASS:0214 If 1 row is updated?
+
SELECT STR110,STR180,STR216
FROM T2000;
STR110 |STR180 |STR216
@@ -42,9 +50,10 @@
-- PASS:0214 'STR88888888888888888888888888888888888888888888888'?
-- PASS:0214 If STR216 = ?
-- PASS:0214 'STR66666666666666666666666666666666666666666666666'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0214 <<< END TEST
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml044.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml044.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml044.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml044.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML044
+
-- SQL Test Suite, V6.0, Interactive SQL, dml044.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
@@ -12,47 +14,59 @@
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
+
-- date_time print
+
-- TEST:0215 FIPS sizing -- 6 columns in a UNIQUE constraint!
-- FIPS sizing TEST
+
-- setup
INSERT INTO T8
VALUES('th','seco','third3','fourth_4','fifth_colu',
'sixth_column','seventh_column','last_column_of_t');
1 row inserted/updated/deleted
ij> -- PASS:0215 If 1 row is inserted?
+
INSERT INTO T8
VALUES('th','seco','third3','fourth_4','fifth_colu',
'sixth_column','column_seventh','column_eighth_la');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'T8C1' defined on 'T8'.
ij> -- PASS:0215 If ERROR, unique constraint, 0 rows inserted?
+
SELECT COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8
FROM T8;
C&|COL2|COL3 |COL4 |COL5 |COL6 |COL7 |COL8
-------------------------------------------------------------------------------
th|seco|third3|fourth_4|fifth_colu|sixth_column|seventh_column|last_column_of_t
ij> -- PASS:0215 If COL1 = 'th'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0215 <<< END TEST
-- **************************************************************
+
-- TEST:0216 FIPS sizing -- 120 bytes in a UNIQUE constraint!
-- FIPS sizing TEST
+
-- setup
DELETE FROM T4;
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> -- Making sure the table is empty
+
-- setup
INSERT INTO T4 VALUES (
'This test is trying to test the limit on the total length of an index',
-123456, 'which is','not less than 120');
1 row inserted/updated/deleted
ij> -- PASS:0216 If 1 row is inserted?
+
INSERT INTO T4 VALUES (
'This test is trying to test the limit on the total length of an index',
-123456,'which is','not less than 120');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'T4C1' defined on 'T4'.
ij> -- PASS:0216 If ERROR, unique constraint, 0 rows inserted?
+
SELECT STR110
FROM T4;
STR110
@@ -60,9 +74,11 @@
This test is trying to test the limit on the total length of an index
ij> -- PASS:0216 If STR110 starts with 'This test is trying to test the '?
-- PASS:0216 and ends with 'limit on the total length of an index'?
+
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0216 <<< END TEST
+
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file