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 [5/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/dml112.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml112.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml112.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml112.out Fri Oct 27 13:04:21 2006
@@ -1,11 +1,14 @@
ij> -- LTRIM(string, trimSet)/RTRIM(string, trimSet) is not supported anymore. Mastering the
-- output with errors for now. We may implement our own LTRIM_TRIMSET()/RTRIM_TRIMSET()
-- functions for testing only in the future and replace usages of LTRIM/RTRIM here.
+
AUTOCOMMIT OFF;
ij> -- MODULE DML112
+
-- SQL Test Suite, V6.0, Interactive SQL, dml112.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -17,7 +20,9 @@
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
ij> -- date_time print
+
-- TEST:0621 DATETIME NULLs!
+
CREATE TABLE MERCH (
ITEMKEY INT,
ORDERED DATE,
@@ -26,13 +31,16 @@
SOLD TIMESTAMP);
0 rows inserted/updated/deleted
ij> -- PASS:0621 If table is created?
+
COMMIT WORK;
ij> --O CREATE TABLE TURNAROUND (
--O ITEMKEY INT,
--O MWAIT INTERVAL MONTH,
--O DWAIT INTERVAL DAY TO HOUR);
-- PASS:0621 If table is created?
+
--O COMMIT WORK;
+
--O CREATE VIEW INVENTORY AS
--O SELECT MERCH.ITEMKEY AS ITEMKEY, ORDERED,
--O MWAIT, DWAIT FROM MERCH, TURNAROUND COR1 WHERE RDATE
@@ -46,131 +54,168 @@
--O AND MERCH.ITEMKEY NOT IN (SELECT ITEMKEY
--O FROM TURNAROUND);
-- PASS:0621 If view is created?
+
--O COMMIT WORK;
+
INSERT INTO MERCH VALUES (0, DATE( '1993-11-23'), NULL, NULL, NULL);
1 row inserted/updated/deleted
ij> -- PASS:0621 If 1 row is inserted?
+
INSERT INTO MERCH VALUES (1, DATE( '1993-12-10'), DATE( '1994-01-03'),
CAST (NULL AS TIME), NULL);
1 row inserted/updated/deleted
ij> -- PASS:0621 If 1 row is inserted?
+
INSERT INTO MERCH VALUES (2, DATE( '1993-12-11'), NULL,
---O NULL, CAST ('TIMESTAMP ''xxxxxxFILTERED-TIMESTAMPxxxxx'' AS TIMESTAMP));
- NULL, TIMESTAMP( 'xxxxxxFILTERED-TIMESTAMPxxxxx));
+--O NULL, CAST ('TIMESTAMP ''1993-12-11 13:00:00''' AS TIMESTAMP));
+ NULL, TIMESTAMP( '1993-12-11 13:00:00' ));
1 row inserted/updated/deleted
ij> -- PASS:0621 If 1 row is inserted?
+
INSERT INTO MERCH VALUES (4, DATE( '1993-01-26'), DATE( '1993-01-27'),
NULL, NULL);
1 row inserted/updated/deleted
ij> -- PASS:0621 If 1 row is inserted?
+
--O INSERT INTO TURNAROUND VALUES (2, INTERVAL '1' MONTH,
--O INTERVAL '20:0' DAY TO HOUR);
-- PASS:0621 If 1 row is inserted?
+
--O INSERT INTO TURNAROUND VALUES (5, INTERVAL '5' MONTH,
--O CAST (NULL AS INTERVAL DAY TO HOUR));
-- PASS:0621 If 1 row is inserted?
+
--O INSERT INTO TURNAROUND VALUES (6, INTERVAL '2' MONTH, NULL);
-- PASS:0621 If 1 row is inserted?
+
--O SELECT COUNT(*) FROM
--O MERCH A, MERCH B WHERE A.SOLD = B.SOLD;
-- PASS:0621 If count = 1?
+
--O SELECT COUNT(*) FROM
--O MERCH A, MERCH B WHERE A.RTIME = B.RTIME;
-- PASS:0621 If count = 0?
+
--O SELECT COUNT(*) FROM
--O MERCH WHERE RDATE IS NULL;
-- PASS:0621 If count = 2?
+
--O SELECT COUNT(*) FROM
--O TURNAROUND WHERE DWAIT IS NOT NULL;
-- PASS:0621 If count = 1?
+
--O SELECT DAY( RDATE)
--O FROM MERCH, TURNAROUND WHERE MERCH.ITEMKEY =
--O TURNAROUND.ITEMKEY;
-- PASS:0621 If 1 row selected and value is NULL?
+
SELECT ITEMKEY FROM MERCH WHERE SOLD IS NOT NULL;
ITEMKEY
-----------
2
ij> -- PASS:0621 If 1 row selected and ITEMKEY is 2?
+
--O SELECT HOUR( AVG (DWAIT))
--O FROM MERCH, TURNAROUND WHERE
--O MERCH.ITEMKEY = TURNAROUND.ITEMKEY OR
--O TURNAROUND.ITEMKEY NOT IN
--O (SELECT ITEMKEY FROM MERCH);
-- PASS:0621 If 1 row selected and value is 0?
+
--O SELECT COUNT(*)
--O FROM INVENTORY WHERE MWAIT IS NULL
--O AND DWAIT IS NULL;
-- PASS:0621 If count = 2?
+
COMMIT WORK;
ij> --O DROP TABLE MERCH CASCADE;
DROP TABLE MERCH ;
0 rows inserted/updated/deleted
ij> -- PASS:0621 If table is dropped?
+
COMMIT WORK;
ij> --O DROP TABLE TURNAROUND CASCADE;
-- PASS:0621 If table is dropped?
+
--O COMMIT WORK;
+
-- END TEST >>> 0621 <<< END TEST
+
-- *********************************************
+
-- TEST:0623 OUTER JOINs with NULLs and empty tables!
+
+
CREATE TABLE JNULL1 (C1 INT, C2 INT);
0 rows inserted/updated/deleted
ij> -- PASS:0623 If table is created?
+
COMMIT WORK;
ij> CREATE TABLE JNULL2 (D1 INT, D2 INT);
0 rows inserted/updated/deleted
ij> -- PASS:0623 If table is created?
+
COMMIT WORK;
ij> CREATE VIEW JNULL3 AS
SELECT C1, D1, D2 FROM JNULL1 LEFT OUTER JOIN JNULL2
ON C2 = D2;
0 rows inserted/updated/deleted
ij> -- PASS:0623 If view is created?
+
COMMIT WORK;
ij> CREATE VIEW JNULL4 AS
SELECT D1, D2 AS C2 FROM JNULL2;
0 rows inserted/updated/deleted
ij> -- PASS:0623 If view is created?
+
COMMIT WORK;
ij> CREATE VIEW JNULL5 AS
SELECT C1, D1, JNULL1.C2 FROM JNULL1 RIGHT OUTER JOIN JNULL4
ON (JNULL1.C2 = JNULL4.C2);
0 rows inserted/updated/deleted
ij> -- PASS:0623 If view is created?
+
COMMIT WORK;
ij> CREATE VIEW JNULL6 (C1, C2, D1, D2) AS
SELECT * FROM JNULL1 LEFT OUTER JOIN JNULL4
ON (JNULL1.C2 = JNULL4.C2);
0 rows inserted/updated/deleted
ij> -- PASS:0623 If view is created?
+
COMMIT WORK;
ij> INSERT INTO JNULL1 VALUES (NULL, NULL);
1 row inserted/updated/deleted
ij> -- PASS:0623 If 1 row is inserted?
+
INSERT INTO JNULL1 VALUES (1, NULL);
1 row inserted/updated/deleted
ij> -- PASS:0623 If 1 row is inserted?
+
INSERT INTO JNULL1 VALUES (NULL, 1);
1 row inserted/updated/deleted
ij> -- PASS:0623 If 1 row is inserted?
+
INSERT INTO JNULL1 VALUES (1, 1);
1 row inserted/updated/deleted
ij> -- PASS:0623 If 1 row is inserted?
+
INSERT INTO JNULL1 VALUES (2, 2);
1 row inserted/updated/deleted
ij> -- PASS:0623 If 1 row is inserted?
+
SELECT COUNT(*) FROM JNULL3;
1
-----------
5
ij> -- PASS:0623 If count = 5?
+
SELECT COUNT(*) FROM JNULL3
WHERE D2 IS NOT NULL OR D1 IS NOT NULL;
1
-----------
0
ij> -- PASS:0623 If count = 0?
+
SELECT COUNT(*) FROM JNULL5;
1
-----------
@@ -178,43 +223,51 @@
ij> ---- ON (C2);
---- SELECT D1, D2 AS C2 FROM JNULL2;
-- PASS:0623 If count = 0?
+
SELECT COUNT(*) FROM JNULL6
WHERE C2 IS NOT NULL;
1
-----------
3
ij> -- PASS:0623 If count = 3?
+
INSERT INTO JNULL2
SELECT * FROM JNULL1;
5 rows inserted/updated/deleted
ij> -- PASS:0623 If 5 rows are inserted?
+
UPDATE JNULL2
SET D2 = 1 WHERE D2 = 2;
1 row inserted/updated/deleted
ij> -- PASS:0623 If 1 row is updated?
+
SELECT COUNT(*) FROM JNULL3;
1
-----------
9
ij> -- PASS:0623 If count = 9?
+
SELECT COUNT(*)
FROM JNULL3 WHERE C1 IS NULL;
1
-----------
4
ij> -- PASS:0623 If count = 4?
+
SELECT COUNT(*)
FROM JNULL3 WHERE D1 IS NULL;
1
-----------
5
ij> -- PASS:0623 If count = 5?
+
SELECT COUNT(*)
FROM JNULL3 WHERE D2 IS NULL;
1
-----------
3
ij> -- PASS:0623 If count = 3?
+
SELECT AVG(D1) * 10
FROM JNULL3;
1
@@ -222,6 +275,7 @@
10
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0623 If value is 15 (approximately)?
+
SELECT COUNT(*)
FROM JNULL6
WHERE C2 = 1;
@@ -229,6 +283,7 @@
-----------
6
ij> -- PASS:0623 If count = 6?
+
SELECT COUNT(*)
FROM JNULL6
WHERE C2 IS NULL;
@@ -236,6 +291,7 @@
-----------
2
ij> -- PASS:0623 If count = 2?
+
SELECT COUNT(*)
FROM JNULL6
WHERE C2 = C1
@@ -244,6 +300,7 @@
-----------
2
ij> -- PASS:0623 If count = 2?
+
COMMIT WORK;
ij> --O DROP TABLE JNULL1 CASCADE;
DROP VIEW JNULL3 ;
@@ -257,160 +314,217 @@
ij> DROP TABLE JNULL1 ;
0 rows inserted/updated/deleted
ij> -- PASS:0623 If table is dropped?
+
COMMIT WORK;
ij> --O DROP TABLE JNULL2 CASCADE;
DROP TABLE JNULL2 ;
0 rows inserted/updated/deleted
ij> -- PASS:0623 If table is dropped?
+
COMMIT WORK;
ij> -- END TEST >>> 0623 <<< END TEST
+
-- *********************************************
+
-- TEST:0625 ADD COLUMN and DROP COLUMN!
+
CREATE TABLE CHANGG
(NAAM CHAR (14) NOT NULL PRIMARY KEY, AGE INT);
0 rows inserted/updated/deleted
ij> -- PASS:0625 If table is created?
+
COMMIT WORK;
ij> CREATE VIEW CHANGGVIEW AS
SELECT * FROM CHANGG;
0 rows inserted/updated/deleted
ij> -- PASS:0625 If view is created?
+
COMMIT WORK;
ij> --O ALTER TABLE CHANGG
--O DROP NAAM RESTRICT;
-- PASS:0625 If ERROR, view references NAAM?
+
--O COMMIT WORK;
+
INSERT INTO CHANGG VALUES ('RALPH', 22);
1 row inserted/updated/deleted
ij> -- PASS:0625 If 1 row is inserted?
+
INSERT INTO CHANGG VALUES ('RUDOLPH', 54);
1 row inserted/updated/deleted
ij> -- PASS:0625 If 1 row is inserted?
+
INSERT INTO CHANGG VALUES ('QUEEG', 33);
1 row inserted/updated/deleted
ij> -- PASS:0625 If 1 row is inserted?
+
INSERT INTO CHANGG VALUES ('BESSIE', 106);
1 row inserted/updated/deleted
ij> -- PASS:0625 If 1 row is inserted?
+
SELECT COUNT(*)
FROM CHANGG WHERE DIVORCES IS NULL;
ERROR 42X04: Column 'DIVORCES' 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 'DIVORCES' is not a column in the target table.
ij> -- PASS:0625 If ERROR, column does not exist?
+
COMMIT WORK;
ij> ALTER TABLE CHANGG ADD NUMBRR CHAR(11);
0 rows inserted/updated/deleted
ij> -- PASS:0625 If column is added?
+
COMMIT WORK;
ij> SELECT MAX(AGE) FROM CHANGGVIEW;
1
-----------
106
ij> -- PASS:0625 If value is 106?
+
SELECT MAX(NUMBRR) FROM CHANGGVIEW;
ERROR 42X04: Column 'NUMBRR' 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 'NUMBRR' is not a column in the target table.
ij> -- PASS:0625 If ERROR, column does not exist ?
+
COMMIT WORK;
ij> --O DROP VIEW CHANGGVIEW CASCADE;
DROP VIEW CHANGGVIEW ;
0 rows inserted/updated/deleted
ij> -- PASS:0625 If view is dropped?
+
COMMIT WORK;
ij> --O ALTER TABLE CHANGG
--O ADD COLUMN DIVORCES INT DEFAULT 0;
-- PASS:0625 If column is added?
+
--O COMMIT WORK;
+
--O SELECT COUNT(*)
--O FROM CHANGG WHERE NUMBRR IS NOT NULL
--O OR DIVORCES <> 0;
-- PASS:0625 If count = 0?
+
--O UPDATE CHANGG
--O SET NUMBRR = '837-47-1847', DIVORCES = 3
--O WHERE NAAM = 'RUDOLPH';
-- PASS:0625 If 1 row is updated?
+
--O UPDATE CHANGG
--O SET NUMBRR = '738-47-1847', DIVORCES = NULL
--O WHERE NAAM = 'QUEEG';
-- PASS:0625 If 1 row is updated?
+
DELETE FROM CHANGG
WHERE NUMBRR IS NULL;
4 rows inserted/updated/deleted
ij> -- PASS:0625 If 2 rows are deleted?
+
--O INSERT INTO CHANGG (NAAM, AGE, NUMBRR)
--O VALUES ('GOOBER', 16, '000-10-0001');
-- PASS:0625 If 1 row is inserted?
+
--O INSERT INTO CHANGG
--O VALUES ('OLIVIA', 20, '111-11-1111', 0);
-- PASS:0625 If 1 row is inserted?
+
--O SELECT AGE, NUMBRR, DIVORCES
--O FROM CHANGG
--O WHERE NAAM = 'RUDOLPH';
-- PASS:0625 If 1 row selected with values 54, 837-47-1847, 3 ?
+
--O SELECT AGE, NUMBRR, DIVORCES
--O FROM CHANGG
--O WHERE NAAM = 'QUEEG';
-- PASS:0625 If 1 row selected with values 33, 738-47-1847, NULL ?
+
--O SELECT AGE, NUMBRR, DIVORCES
--O FROM CHANGG
--O WHERE NAAM = 'GOOBER';
-- PASS:0625 If 1 row selected with values 16, 000-10-0001, 0 ?
+
--O SELECT AGE, NUMBRR, DIVORCES
--O FROM CHANGG
--O WHERE NAAM = 'OLIVIA';
-- PASS:0625 If 1 row selected with values 20, 111-11-1111, 0 ?
+
SELECT COUNT(*) FROM CHANGG;
1
-----------
0
ij> -- PASS:0625 If count = 4?
+
COMMIT WORK;
ij> --O ALTER TABLE CHANGG DROP AGE CASCADE;
-- PASS:0625 If column is dropped?
+
--O COMMIT WORK;
+
--O ALTER TABLE CHANGG DROP COLUMN DIVORCES RESTRICT;
-- PASS:0625 If column is dropped?
+
--O COMMIT WORK;
+
--O SELECT COUNT(*)
--O FROM CHANGG WHERE AGE > 30;
-- PASS:0625 If ERROR, column does not exist?
+
--O SELECT COUNT(*)
--O FROM CHANGG WHERE DIVORCES IS NULL;
-- PASS:0625 If ERROR, column does not exist?
+
--O SELECT NAAM
--O FROM CHANGG
--O WHERE NUMBRR LIKE '%000%';
-- PASS:0625 If 1 row selected with value GOOBER ?
+
--O COMMIT WORK;
+
--O CREATE TABLE REFERENCE_CHANGG (
--O NAAM CHAR (14) NOT NULL PRIMARY KEY
--O REFERENCES CHANGG);
-- PASS:0625 If table is created?
+
--O COMMIT WORK;
+
--O INSERT INTO REFERENCE_CHANGG VALUES ('NO SUCH NAAM');
-- PASS:0625 If RI ERROR, parent missing, 0 rows inserted?
+
--O COMMIT WORK;
+
--O ALTER TABLE CHANGG DROP NAAM RESTRICT;
-- PASS:0625 If ERROR, referential constraint exists?
+
--O COMMIT WORK;
+
--O ALTER TABLE CHANGG DROP NAAM CASCADE;
-- PASS:0625 If column is dropped?
+
--O COMMIT WORK;
+
--O INSERT INTO REFERENCE_CHANGG VALUES ('NO SUCH NAAM');
-- PASS:0625 If 1 row is inserted?
+
--O COMMIT WORK;
+
--O ALTER TABLE CHANGG DROP NUMBRR RESTRICT;
-- PASS:0625 If ERROR, last column may not be dropped?
+
--O COMMIT WORK;
+
--O DROP TABLE CHANGG CASCADE;
DROP TABLE CHANGG ;
0 rows inserted/updated/deleted
ij> -- PASS:0625 If table is dropped?
+
COMMIT WORK;
ij> --O DROP TABLE REFERENCE_CHANGG CASCADE;
-- PASS:0625 If table is dropped?
+
--O COMMIT WORK;
+
-- END TEST >>> 0625 <<< END TEST
+
-- *********************************************
+
-- TEST:0631 Datetimes in a <default clause>!
+
--O CREATE TABLE OBITUARIES (
--O NAAM CHAR (14) NOT NULL PRIMARY KEY,
--O BORN DATE DEFAULT DATE( '1880-01-01'),
@@ -419,7 +533,9 @@
--O TESTING1 DATE,
--O TESTING2 TIMESTAMP);
-- PASS:0631 If table is created?
+
--O COMMIT WORK;
+
--O CREATE TABLE BIRTHS (
--O NAAM CHAR (14) NOT NULL PRIMARY KEY,
--O CHECKIN TIME (0)
@@ -430,23 +546,30 @@
--O DEFAULT CURRENT_TIME,
--O TESTING TIME);
-- PASS:0631 If table is created?
+
--O COMMIT WORK;
+
--O INSERT INTO OBITUARIES (NAAM, TESTING1, TESTING2)
--O VALUES ('KEITH', CURRENT_DATE, CURRENT_TIMESTAMP);
-- PASS:0631 If 1 row is inserted?
+
--O INSERT INTO BIRTHS (NAAM, TESTING)
--O VALUES ('BJORN', CURRENT_TIME);
-- PASS:0631 If 1 row is inserted?
+
--O SELECT HOUR( CHECKIN) +
--O MINUTE( CHECKIN) +
--O SECOND( CHECKIN)
--O FROM BIRTHS;
-- PASS:0631 If 1 row selected with value 0?
+
--O SELECT HOUR( LABOR) FROM BIRTHS;
-- PASS:0631 If 1 row selected with value 4?
+
--O SELECT COUNT (*) FROM BIRTHS
--O WHERE TESTING <> CHECKOUT OR CHECKOUT IS NULL;
-- PASS:0631 If count = 0?
+
--O SELECT COUNT (*) FROM OBITUARIES
--O WHERE BORN <> DATE( '1880-01-01')
--O OR BORN IS NULL
@@ -455,60 +578,78 @@
--O OR ENTERED <> TESTING2
--O OR ENTERED IS NULL;
-- PASS:0631 If count = 0?
+
--O COMMIT WORK;
+
--O DROP TABLE BIRTHS CASCADE;
-- PASS:0631 If table is dropped?
+
--O COMMIT WORK;
+
--O DROP TABLE OBITUARIES CASCADE;
-- PASS:0631 If table is dropped?
+
--O COMMIT WORK;
+
-- END TEST >>> 0631 <<< END TEST
+
-- *********************************************
+
-- TEST:0633 TRIM function!
+
CREATE TABLE WEIRDPAD (
NAAM CHAR (14),
SPONSOR CHAR (14),
PADCHAR CHAR (1));
0 rows inserted/updated/deleted
ij> -- PASS:0633 If table is created?
+
COMMIT WORK;
ij> INSERT INTO WEIRDPAD (NAAM, SPONSOR) VALUES
('KATEBBBBBBBBBB', '000000000KEITH');
1 row inserted/updated/deleted
ij> -- PASS:0633 If 1 row is inserted?
+
INSERT INTO WEIRDPAD (NAAM, SPONSOR) VALUES
(' KEITH ', 'XXXXKATEXXXXXX');
1 row inserted/updated/deleted
ij> -- PASS:0633 If 1 row is inserted?
+
SELECT LTRIM (RTRIM (SPONSOR,'X'),'X')
FROM WEIRDPAD
WHERE LTRIM (RTRIM (NAAM)) = 'KEITH';
ERROR 42X01: Syntax error: Encountered "," at line 3, column 32.
ij> -- PASS:0633 If 1 row selected with value KATE ?
+
SELECT LTRIM (SPONSOR, 'X')
FROM WEIRDPAD
WHERE RTRIM (NAAM) = ' KEITH';
ERROR 42X01: Syntax error: Encountered "," at line 3, column 25.
ij> -- PASS:0633 If 1 row selected with value KATEXXXXXX ?
+
SELECT LTRIM (SPONSOR, 'X')
FROM WEIRDPAD
WHERE RTRIM (SPONSOR, 'X') = 'XXXXKATE';
ERROR 42X01: Syntax error: Encountered "," at line 3, column 25.
ij> -- PASS:0633 If 1 row selected with value KATEXXXXXX ?
+
SELECT LTRIM (B.NAAM) FROM WEIRDPAD A,
WEIRDPAD B WHERE RTRIM (LTRIM (A.NAAM, 'B'),'B')
= RTRIM (LTRIM (B.SPONSOR, 'X'),'X');
ERROR 42X01: Syntax error: Encountered "," at line 4, column 43.
ij> -- PASS:0633 If 1 row selected with value KEITH ?
+
SELECT COUNT(*) FROM WEIRDPAD A,
WEIRDPAD B WHERE LTRIM (A.SPONSOR, '0')
= RTRIM (LTRIM (B.NAAM, ' '), ' ');
ERROR 42X01: Syntax error: Encountered "," at line 4, column 39.
ij> -- PASS:0633 If count = 1?
+
SELECT RTRIM (NAAM, 'BB')
FROM WEIRDPAD WHERE NAAM LIKE 'KATE%';
ERROR 42X01: Syntax error: Encountered "," at line 3, column 22.
ij> -- PASS:0633 If ERROR, length of trim character must be 1 ?
+
INSERT INTO WEIRDPAD (NAAM, SPONSOR)
SELECT DISTINCT LTRIM (HU.STAFF.CITY, 'D'),
RTRIM (PTYPE, 'n')
@@ -516,57 +657,70 @@
WHERE EMPNAME = 'Alice';
ERROR 42X01: Syntax error: Encountered "," at line 4, column 42.
ij> -- PASS:0633 If 3 rows are inserted?
+
SELECT COUNT(*) FROM WEIRDPAD;
1
-----------
2
ij> -- PASS:0633 If count = 5?
+
UPDATE WEIRDPAD
SET SPONSOR = LTRIM (RTRIM (SPONSOR, 'X'), 'X'),
NAAM = RTRIM (NAAM, 'B');
ERROR 42X01: Syntax error: Encountered "," at line 4, column 41.
ij> -- PASS:0633 If 5 rows are updated?
+
SELECT COUNT(*) FROM WEIRDPAD
WHERE NAAM = 'KATE' OR SPONSOR = 'KATE';
1
-----------
0
ij> -- PASS:0633 If count = 2?
+
DELETE FROM WEIRDPAD WHERE
LTRIM('Kest', 'K') = LTRIM(SPONSOR, 'T');
ERROR 42X01: Syntax error: Encountered "," at line 4, column 18.
ij> -- PASS:0633 If 1 row is deleted?
+
SELECT COUNT(*) FROM WEIRDPAD;
1
-----------
2
ij> -- PASS:0633 If count = 4?
+
UPDATE WEIRDPAD
SET PADCHAR = '0'
WHERE SPONSOR = '000000000KEITH'
OR NAAM = 'eale';
1 row inserted/updated/deleted
ij> -- PASS:0633 If 3 rows are updated?
+
UPDATE WEIRDPAD
SET SPONSOR = NULL
WHERE SPONSOR = 'Desig';
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> -- PASS:0633 If 1 row is updated?
+
SELECT COUNT(*) FROM WEIRDPAD
WHERE RTRIM (SPONSOR, PADCHAR) IS NULL;
ERROR 42X01: Syntax error: Encountered "," at line 4, column 26.
ij> -- PASS:0633 If count = 2?
+
SELECT COUNT(*) FROM WEIRDPAD
WHERE LTRIM (SPONSOR, PADCHAR) = 'KEITH';
ERROR 42X01: Syntax error: Encountered "," at line 4, column 26.
ij> -- PASS:0633 If count = 1?
+
COMMIT WORK;
ij> --0 DROP TABLE WEIRDPAD CASCADE;
DROP TABLE WEIRDPAD;
0 rows inserted/updated/deleted
ij> -- PASS:0633 If table is dropped?
+
COMMIT WORK;
ij> -- END TEST >>> 0633 <<< 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/dml114.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml114.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml114.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml114.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML114
+
-- SQL Test Suite, V6.0, Interactive SQL, dml114.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -14,12 +16,15 @@
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
ij> -- date_time print
+
-- TEST:0635 Feature 13, grouped operations (static)!
+
CREATE VIEW WORKWEEK AS
SELECT EMPNUM, HOURS FROM HU.WORKS
GROUP BY HOURS, EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0635 If table is created?
+
COMMIT WORK;
ij> SELECT EMPNUM, SUM (HOURS)
FROM WORKWEEK
@@ -30,12 +35,14 @@
---------------
E1 |120
ij> -- PASS:0635 If 1 row selected and EMPNUM = 'E1' and SUM(HOURS) = 120?
+
SELECT COUNT(*)
FROM WORKWEEK WHERE HOURS > 40;
1
-----------
3
ij> -- PASS:0635 If count = 3?
+
SELECT EMPNAME
FROM HU.STAFF, WORKWEEK
WHERE HU.STAFF.EMPNUM = WORKWEEK.EMPNUM
@@ -44,6 +51,7 @@
--------------------
Alice
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
+
SELECT COUNT(*), MAX(EMPNUM), MIN(EMPNUM), AVG(HOURS)
FROM WORKWEEK;
1 |2 |3 |4
@@ -51,6 +59,7 @@
10 |E4 |E1 |43.2000
ij> -- PASS:0635 If 1 row selected and count = 10 and MAX(EMPNUM) = 'E4'?
-- PASS:0635 AND MIN(EMPNUM) = 'E1' and AVG(HOURS) = 43 (approximately)?
+
SELECT EMPNAME
FROM HU.STAFF WHERE EMPNUM =
(SELECT EMPNUM FROM WORKWEEK
@@ -59,6 +68,7 @@
--------------------
Alice
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
+
SELECT EMPNAME
FROM HU.STAFF WHERE EMPNUM =
(SELECT EMPNUM FROM HU.WORKS
@@ -68,47 +78,58 @@
--------------------
Alice
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
+
-- NOTE:0635 Cursor subtest deleted.
+
COMMIT WORK;
ij> --0 DROP VIEW WORKWEEK CASCADE;
DROP VIEW WORKWEEK ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0635 <<< END TEST
+
-- *********************************************
+
-- TEST:0637 Feature 14, Qualified * in select list (static)!
+
CREATE VIEW QUALSTAR AS
SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS
WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
+
COMMIT WORK;
ij> CREATE VIEW CORRQUALSTAR AS
SELECT BLAH.*, HOURS FROM HU.STAFF BLAH, HU.WORKS
WHERE BLAH.EMPNUM = HU.WORKS.EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
+
COMMIT WORK;
ij> CREATE VIEW SUBQ2 AS
SELECT DISTINCT * FROM QUALSTAR;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
+
COMMIT WORK;
ij> CREATE VIEW CORRSUBQ2 AS
SELECT DISTINCT * FROM CORRQUALSTAR;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
+
COMMIT WORK;
ij> SELECT COUNT(*) FROM QUALSTAR;
1
-----------
12
ij> -- PASS:0637 If count = 12?
+
SELECT COUNT(*) FROM SUBQ2;
1
-----------
10
ij> -- PASS:0637 If count = 10?
+
SELECT EMPNUM, GRADE, CITY, HOURS
FROM QUALSTAR WHERE EMPNAME = 'Carmen';
EM&|GRADE|CITY |HOURS
@@ -116,7 +137,9 @@
E3 |13 |Vienna |20
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3' and GRADE = 13?
-- PASS:0637 AND CITY = 'Vienna' and HOURS = 20?
+
-- NOTE:0637 Cursor subtest deleted.
+
SELECT HU.STAFF.*, HOURS
FROM HU.STAFF, HU.WORKS
WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM
@@ -126,16 +149,19 @@
E3 |Carmen |13 |Vienna |20
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3' and EMPNAME = 'Carmen'?
-- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20?
+
SELECT COUNT(*) FROM CORRQUALSTAR;
1
-----------
12
ij> -- PASS:0637 If count = 12?
+
SELECT COUNT(*) FROM CORRSUBQ2;
1
-----------
10
ij> -- PASS:0637 If count = 10?
+
SELECT EMPNUM, GRADE, CITY, HOURS
FROM CORRQUALSTAR WHERE EMPNAME = 'Carmen';
EM&|GRADE|CITY |HOURS
@@ -143,6 +169,7 @@
E3 |13 |Vienna |20
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3'?
-- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20?
+
COMMIT WORK;
ij> --0 DROP VIEW QUALSTAR CASCADE;
DROP VIEW SUBQ2 ;
@@ -157,12 +184,16 @@
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0637 <<< END TEST
+
-- *********************************************
+
-- TEST:0639 Feature 15, Lowercase Identifiers (static)!
+
create view Staff (Empnum, empname, Grade, City) as
select empnum, EMPNAME, Grade, cItY from Hu.Staff;
0 rows inserted/updated/deleted
ij> -- PASS:0639 If view is created?
+
commit work;
ij> SELECT EMPNUM as WhatsHisNumber, GRADE, CITY
FROM Flater.staff FLaterStaff_Flater
@@ -175,78 +206,101 @@
E3 |13 |Vienna
ij> -- PASS:0639 If 1 row selected and EMPNUM = 'E3'?
-- PASS:0639 AND GRADE = 13 and CITY = 'Vienna'?
+
-- NOTE:0639 Cursor subtest deleted.
+
COMMIT WORK;
ij> --0 DROP VIEW STAFF CASCADE;
DROP VIEW STAFF ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0639 <<< END TEST
+
-- *********************************************
+
-- TEST:0641 Feature 16, PRIMARY KEY enhancement (static)!
+
CREATE TABLE FEAT16 (
- EMPNUM INT NOT NULL PRIMARY KEY,
- PNUM INT NOT NULL UNIQUE);
+ EMPNUM INT NOT NULL CONSTRAINT FEAT16_PK PRIMARY KEY,
+ PNUM INT NOT NULL CONSTRAINT FEAT16_PNUM UNIQUE);
0 rows inserted/updated/deleted
ij> -- PASS:0641 If view is created?
+
COMMIT WORK;
ij> CREATE TABLE BARNO (
P1 INT NOT NULL, P2 CHAR NOT NULL, X1 INT NOT NULL, X2 CHAR NOT NULL,
- UNIQUE (X2, X1),
- PRIMARY KEY (P1, P2));
+ CONSTRAINT BARNO_UNIQUE UNIQUE (X2, X1),
+ CONSTRAINT BARNO_PK PRIMARY KEY (P1, P2));
0 rows inserted/updated/deleted
ij> -- PASS:0641 If view is created?
+
COMMIT WORK;
ij> INSERT INTO FEAT16 VALUES (1, 10);
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO FEAT16 VALUES (2, 20);
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO FEAT16 VALUES (1, 30);
-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 'xxxxGENERATED-IDxxxx' defined on 'FEAT16'.
+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 'FEAT16_PK' defined on 'FEAT16'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
+
INSERT INTO FEAT16 VALUES (3, 20);
-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 'xxxxGENERATED-IDxxxx' defined on 'FEAT16'.
+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 'FEAT16_PNUM' defined on 'FEAT16'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
+
INSERT INTO FEAT16 VALUES (3, NULL);
ERROR 23502: Column 'PNUM' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO FEAT16 VALUES (4, NULL);
ERROR 23502: Column 'PNUM' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO FEAT16 VALUES (5, NULL);
ERROR 23502: Column 'PNUM' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO BARNO VALUES (1, 'A', 10, 'a');
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO BARNO VALUES (2, 'A', 20, 'a');
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO BARNO VALUES (1, 'A', 30, 'a');
-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 'xxxxGENERATED-IDxxxx' defined on 'BARNO'.
+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 'BARNO_PK' defined on 'BARNO'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
+
INSERT INTO BARNO VALUES (3, 'A', 20, 'a');
-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 'xxxxGENERATED-IDxxxx' defined on 'BARNO'.
+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 'BARNO_UNIQUE' defined on 'BARNO'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
+
INSERT INTO BARNO VALUES (3, NULL, 30, 'a');
ERROR 23502: Column 'P2' cannot accept a NULL value.
ij> -- PASS:0641 If ERROR, PRIMARY KEY constraint, 0 rows inserted?
-- PASS:0641 OR ERROR, NOT NULL constraint, 0 rows inserted?
+
INSERT INTO BARNO VALUES (3, NULL, 30, 'b');
ERROR 23502: Column 'P2' cannot accept a NULL value.
ij> -- PASS:0641 If ERROR, PRIMARY KEY constraint, 0 rows inserted?
-- PASS:0641 OR ERROR, NOT NULL constraint, 0 rows inserted?
+
INSERT INTO BARNO VALUES (3, 'A', 30, NULL);
ERROR 23502: Column 'X2' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO BARNO VALUES (3, 'B', 30, NULL);
ERROR 23502: Column 'X2' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
+
INSERT INTO BARNO VALUES (4, 'B', NULL, NULL);
ERROR 23502: Column 'X1' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
+
COMMIT WORK;
ij> --0 DROP TABLE FEAT16 CASCADE;
DROP TABLE FEAT16 ;
@@ -259,4 +313,4 @@
ij> -- END TEST >>> 0641 <<< 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/dml119.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml119.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml119.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml119.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML119
+
-- SQL Test Suite, V6.0, Interactive SQL, dml119.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -14,12 +16,15 @@
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
ij> -- date_time print
+
-- TEST:0647 Feature 20, CAST functions (static)!
+
CREATE TABLE USER_INPUT (
USER_ID INT, USER_TYPED CHAR (10),
CASH_BALANCE NUMERIC (5, 2));
0 rows inserted/updated/deleted
ij> -- PASS:0647 If table is created?
+
COMMIT WORK;
ij> CREATE VIEW STANDARD_INPUT AS
SELECT CAST (USER_ID AS CHAR (10)) AS USER_NAME,
@@ -28,42 +33,50 @@
FROM USER_INPUT;
0 rows inserted/updated/deleted
ij> -- PASS:0647 If view is created?
+
COMMIT WORK;
ij> INSERT INTO USER_INPUT VALUES
(0, '999.99', 999.99);
1 row inserted/updated/deleted
ij> -- PASS:0647 If 1 row is inserted?
+
INSERT INTO USER_INPUT VALUES
(1, '-999.99', -999.99);
1 row inserted/updated/deleted
ij> -- PASS:0647 If 1 row is inserted?
+
INSERT INTO USER_INPUT VALUES
(2, ' 54.', 54);
1 row inserted/updated/deleted
ij> -- PASS:0647 If 1 row is inserted?
+
INSERT INTO USER_INPUT VALUES
(CAST ('3' AS INT), CAST (-7.02 AS CHAR (10)),
CAST (' -.702E+1' AS NUMERIC (5, 2)));
1 row inserted/updated/deleted
ij> -- PASS:0647 If 1 row is inserted?
+
SELECT CAST (AVG (CAST (USER_TYPED AS INT)) AS INT)
FROM USER_INPUT;
1
-----------
ERROR 22018: Invalid character string format for type INTEGER.
ij> -- PASS:0647 If 1 row selected and value is 11 or 12?
+
SELECT AVG (USER_INPUT)
FROM STANDARD_INPUT;
1
-----------
11.7450
ij> -- PASS:0647 If 1 row selected and value is 11.745 +- 0.01?
+
UPDATE USER_INPUT
SET USER_TYPED = CAST (0 AS CHAR (10)),
CASH_BALANCE = CASH_BALANCE - CAST ('500' AS NUMERIC (5, 2))
WHERE USER_ID = CAST ('-0' AS INT);
1 row inserted/updated/deleted
ij> -- PASS:0647 If 1 row is updated?
+
SELECT SUM (USER_INPUT) * 100, SUM (RECEIVABLE)
FROM STANDARD_INPUT;
1 |2
@@ -71,6 +84,7 @@
-95301.00 |-453.02
ij> -- PASS:0647 If 1 row selected and first value is -95301 +- 4?
-- PASS:0647 AND second value is -453.02 +- 0.04?
+
DELETE FROM USER_INPUT;
4 rows inserted/updated/deleted
ij> INSERT INTO USER_INPUT VALUES
@@ -78,11 +92,13 @@
CAST (' -.702E+1' AS NUMERIC (5, 2)));
1 row inserted/updated/deleted
ij> -- PASS:0647 If 1 row is inserted?
+
INSERT INTO USER_INPUT VALUES
(CAST ('3' AS SMALLINT), CAST (-7.02 AS CHAR (5)),
CAST (' -.702E+1' AS DECIMAL (3, 2)));
1 row inserted/updated/deleted
ij> -- PASS:0647 If 1 row is inserted?
+
SELECT USER_ID
FROM USER_INPUT
GROUP BY USER_ID, USER_TYPED, CASH_BALANCE
@@ -91,6 +107,7 @@
-----------
3
ij> -- PASS:0647 If 1 row selected and USER_ID = 3?
+
COMMIT WORK;
ij> --O DROP TABLE USER_INPUT CASCADE;
drop view standard_input;
@@ -101,4 +118,4 @@
ij> -- END TEST >>> 0647 <<< 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/dml130.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml130.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml130.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml130.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML130
+
-- SQL Test Suite, V6.0, Interactive SQL, dml130.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -14,13 +16,16 @@
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
ij> -- date_time print
+
-- TEST:0678 Data type semantics with NULL / NOT NULL!
+
CREATE TABLE CH1 (
CH1A CHARACTER (10) NOT NULL,
CH1B CHARACTER NOT NULL,
CH1C CHAR (10) NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0678 If table is created?
+
COMMIT WORK;
ij> CREATE TABLE NUM1 (
NUM1C1 NUMERIC (3, 2) NOT NULL,
@@ -28,10 +33,12 @@
NUM1C3 NUMERIC NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0678 If table is created?
+
COMMIT WORK;
ij> INSERT INTO CH1 VALUES ('FOO', '', '0123456789');
1 row inserted/updated/deleted
ij> -- PASS:0678 If 1 row is inserted?
+
SELECT CH1A, CH1B, CH1C
FROM CH1;
CH1A |&|CH1C
@@ -39,17 +46,21 @@
FOO | |0123456789
ij> -- PASS:0678 If 1 row selected and CH1A = 'FOO '?
-- PASS:0678 AND CH1B = ' ' and CH1C = '0123456789'?
+
-- NOTE:0678 One subtest deleted.
+
INSERT INTO CH1 VALUES ('FOO',
'F', 'LITTLETOOLONG');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'LITTLETOOLONG' to length 10.
ij> -- PASS:0678 If ERROR, string data, right truncation, 0 rows selected?
+
DELETE FROM CH1;
1 row inserted/updated/deleted
ij> INSERT INTO CH1 VALUES ('FOO',
'F', 'BLANKS ');
1 row inserted/updated/deleted
ij> -- PASS:0678 If 1 row is inserted?
+
SELECT CH1A, CH1B, CH1C
FROM CH1;
CH1A |&|CH1C
@@ -57,34 +68,41 @@
FOO |F|BLANKS
ij> -- PASS:0678 If 1 row selected and CH1A = 'FOO '?
-- PASS:0678 AND CH1B = 'F' and CH1C = 'BLANKS '?
+
INSERT INTO NUM1 VALUES (9.99, -99, 9);
1 row inserted/updated/deleted
ij> -- PASS:0678 If 1 row is inserted?
+
SELECT NUM1C1 * 100, NUM1C2, NUM1C3
FROM NUM1;
1 |NU&|NUM1C3
---------------------------
999.00 |-99|9
ij> -- PASS:0678 If 1 row selected and values are 999, -99, 9?
+
DELETE FROM NUM1;
1 row inserted/updated/deleted
ij> INSERT INTO NUM1 VALUES (-10, 0, 0);
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(3,2).
ij> -- PASS:0678 If ERROR, string data, numeric value out of range?
-- PASS:0678 AND 0 rows inserted?
+
INSERT INTO NUM1 VALUES (0, 100, 0);
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(2,0).
ij> -- PASS:0678 If ERROR, string data, numeric value out of range?
-- PASS:0678 AND 0 rows inserted?
+
INSERT INTO NUM1 VALUES (0, 0, 0.1);
1 row inserted/updated/deleted
ij> -- PASS:0678 If 1 row is inserted?
+
SELECT NUM1C1 * 100, NUM1C2, NUM1C3
FROM NUM1;
1 |NU&|NUM1C3
---------------------------
0.00 |0 |0
ij> -- PASS:0678 If 1 row selected and values are 0, 0, 0?
+
ROLLBACK WORK;
ij> --O DROP TABLE NUM1 CASCADE;
DROP TABLE NUM1 ;
@@ -95,7 +113,9 @@
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0678 <<< END TEST
+
-- *********************************************
+
--O-- TEST:0679 INFO_SCHEM: Table data types!
--O
--O CREATE TABLE LOTSA_TYPES (
@@ -578,4 +598,4 @@
--O
--O-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml132.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml132.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml132.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml132.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML132
+
-- SQL Test Suite, V6.0, Interactive SQL, dml132.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -13,14 +15,19 @@
FLATER
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O ROLLBACK WORK;
+
-- date_time print
+
-- TEST:0515 FIPS sizing: NUMERIC (15) decimal precision!
+
INSERT INTO HU.P15 VALUES (999999999999999);
1 row inserted/updated/deleted
ij> -- PASS:0515 If 1 row is inserted?
+
INSERT INTO HU.P15 VALUES (-999999999999999);
1 row inserted/updated/deleted
ij> -- PASS:0515 If 1 row is inserted?
+
SELECT NUMTEST - 999999999999990,
NUMTEST / 9999999
FROM HU.P15 WHERE NUMTEST > 0;
@@ -28,6 +35,7 @@
--------------------------------------------------------
9 |100000010.0000009000000900
ij> -- PASS:0515 If 1 row selected and values are 9 and 100000010 +- 1?
+
SELECT NUMTEST + 999999999999990,
NUMTEST / 9999999
FROM HU.P15 WHERE NUMTEST < 0;
@@ -35,18 +43,22 @@
--------------------------------------------------------
-9 |-100000010.0000009000000900
ij> -- PASS:0515 If 1 row selected and values are -9 and -100000010 +- 1?
+
DELETE FROM HU.P15;
2 rows inserted/updated/deleted
ij> -- PASS:0515 If 2 rows are deleted?
+
INSERT INTO HU.P15 VALUES (562949953421313);
1 row inserted/updated/deleted
ij> -- PASS:0515 If 1 row is inserted?
+
--O SELECT COUNT(*)
SELECT *
FROM HU.P15 WHERE NUMTEST = 562949953421312;
NUMTEST
----------------
ij> -- PASS:0515 If count = 0?
+
--O SELECT COUNT(*)
SELECT *
FROM HU.P15 WHERE NUMTEST = 562949953421313;
@@ -54,18 +66,24 @@
----------------
562949953421313
ij> -- PASS:0515 If count = 1?
+
--O SELECT COUNT(*)
SELECT *
FROM HU.P15 WHERE NUMTEST = 562949953421314;
NUMTEST
----------------
ij> -- PASS:0515 If count = 0?
+
ROLLBACK WORK;
ij> -- END TEST >>> 0515 <<< END TEST
+
-- *********************************************
+
-- TEST:0524 FIPS sizing: 100 Items in a SELECT list!
+
DELETE FROM HU.T100;
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 HU.T100 VALUES ('00', '01', '02',
'03', '04', '05', '06', '07', '08', '09', '0a', '0b', '0c',
'0d', '0e', '0f', '10', '11', '12', '13', '14', '15', '16',
@@ -79,6 +97,7 @@
'5d', '5e', '5f', '60', '61', '62', '63');
1 row inserted/updated/deleted
ij> -- PASS:0524 If 1 row is inserted?
+
SELECT
C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14,
C15, C16, C17, C18, C19, C20, C21, C22, C23, C24, C25, C26,
@@ -98,20 +117,28 @@
-- PASS:0524 AND C50 is '31'?
-- PASS:0524 AND C67 is '42'?
-- PASS:0524 AND C100 is '63'?
+
ROLLBACK WORK;
ij> -- END TEST >>> 0524 <<< END TEST
+
-- *********************************************
+
-- TEST:0525 FIPS sizing: 15 Table references in SQL statement!
+
DELETE FROM BASE_WCOV;
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 BASE_WCOV VALUES (1);
1 row inserted/updated/deleted
ij> -- PASS:0525 If 1 row is inserted?
+
DELETE FROM HU.STAFF WHERE EMPNUM > 'E2';
3 rows inserted/updated/deleted
ij> -- PASS:0525 If 3 rows are deleted?
+
-- next query restated with a cursor which we get only 1 row from; this is a sufficient demonstation
-- of 15 table references.
+
get cursor x as
--O SELECT COUNT(*) FROM
'SELECT * FROM
@@ -125,8 +152,9 @@
E1 |P1 |40 |P1 |MXSS |Design|10000 |Deale |E2 |Betty |10 |Vienna |0 |2 |4 |6 |0 |1 |0 |1 |1 |E1 |Alice |12 |Deale |1
ij> close x;
ij> -- PASS:0525 If count = 46080?
+
ROLLBACK WORK;
ij> -- END TEST >>> 0525 <<< 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/dml134.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml134.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml134.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml134.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML134
+
-- SQL Test Suite, V6.0, Interactive SQL, dml134.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -13,28 +15,37 @@
FLATER
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O ROLLBACK WORK;
+
-- date_time print
+
-- NO_TEST:0688 INFO_SCHEM: Dynamic changes are visible!
+
-- Testing dynamic SQL
+
-- *********************************************
+
-- TEST:0689 Many Trans SQL features #1: inventory system!
+
CREATE TABLE COST_CODES (
COSTCODE INT NOT NULL UNIQUE,
COSTTEXT VARCHAR (50) NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is created?
+
COMMIT;
ij> CREATE TABLE CONDITION_CODES (
CONDCODE INT NOT NULL UNIQUE,
CONDTEXT VARCHAR (50) NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is created?
+
COMMIT;
ij> CREATE TABLE ITEM_CODES (
ITEMCODE INT NOT NULL PRIMARY KEY,
ITEMTEXT VARCHAR (50) NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is created?
+
COMMIT;
ij> CREATE TABLE INVENTORY (
--O COSTCODE INT REFERENCES COST_CODES (COSTCODE),
@@ -45,14 +56,18 @@
ITEMCODE INT );
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is created?
+
--O COMMIT;
+
--O CREATE VIEW COMPLETES AS
--O SELECT ITEMTEXT, CONDTEXT, COSTTEXT
--O FROM INVENTORY NATURAL JOIN COST_CODES
--O NATURAL JOIN CONDITION_CODES
--O NATURAL JOIN ITEM_CODES;
-- PASS:0689 If view is created?
+
--O COMMIT;
+
--O CREATE VIEW INCOMPLETES AS
--O SELECT ITEMTEXT, CONDTEXT, COSTTEXT
--O FROM INVENTORY, COST_CODES, CONDITION_CODES, ITEM_CODES
@@ -64,242 +79,301 @@
--O AND INVENTORY.CONDCODE IS NULL
--O AND CONDITION_CODES.CONDCODE IS NULL));
-- PASS:0689 If view is created?
+
--O COMMIT;
+
--O CREATE VIEW VERBOSE_INV AS
--O SELECT * FROM COMPLETES UNION SELECT * FROM INCOMPLETES;
-- PASS:0689 If view is created?
+
--O COMMIT;
+
INSERT INTO COST_CODES VALUES (
NULL,
RTRIM ('No cost code assigned '));
ERROR 23502: Column 'COSTCODE' cannot accept a NULL value.
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO COST_CODES VALUES (
0,
RTRIM ('Expensive '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO COST_CODES VALUES (
1,
RTRIM ('Absurdly expensive '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO COST_CODES VALUES (
2,
RTRIM ('Outrageously expensive '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO COST_CODES VALUES (
3,
RTRIM ('Robbery; a complete and total rip-off '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO CONDITION_CODES VALUES (
NULL,
RTRIM ('Unknown '));
ERROR 23502: Column 'CONDCODE' cannot accept a NULL value.
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO CONDITION_CODES VALUES (
1,
RTRIM ('Slightly used '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO CONDITION_CODES VALUES (
2,
RTRIM ('Returned as defective '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO CONDITION_CODES VALUES (
3,
RTRIM ('Visibly damaged (no returns) '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO ITEM_CODES VALUES (
1,
RTRIM ('Lousy excuse for a tape deck '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO ITEM_CODES VALUES (
3,
RTRIM ('World''s worst VCR '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO ITEM_CODES VALUES (
4,
RTRIM ('Irreparable intermittent CD player '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO ITEM_CODES VALUES (
7,
RTRIM ('Self-destruct VGA monitor w/ critical need detect '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO INVENTORY VALUES (3, NULL, 4);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO INVENTORY VALUES (1, 2, 3);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO INVENTORY VALUES (2, 3, 7);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO INVENTORY VALUES (0, 3, 1);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
INSERT INTO INVENTORY VALUES (3, 1, 7);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
+
--O SELECT COUNT(*) FROM VERBOSE_INV;
-- PASS:0689 If count = 5?
+
--O SELECT COUNT(*) FROM INCOMPLETES;
-- PASS:0689 If count = 1?
+
--O SELECT COUNT(*) FROM COMPLETES;
-- PASS:0689 If count = 4?
+
--O SELECT COUNT(*) FROM VERBOSE_INV
--O WHERE ITEMTEXT = 'Irreparable intermittent CD player'
--O AND CONDTEXT = 'Unknown'
--O AND COSTTEXT = 'Robbery; a complete and total rip-off';
-- PASS:0689 If count = 1?
+
--O SELECT COUNT(*) FROM VERBOSE_INV
--O WHERE ITEMTEXT = 'Lousy excuse for a tape deck'
--O AND CONDTEXT = 'Visibly damaged (no returns)'
--O AND COSTTEXT = 'Expensive';
-- PASS:0689 If count = 1?
+
--O SELECT COUNT(*) FROM VERBOSE_INV
--O WHERE ITEMTEXT =
--O 'Self-destruct VGA monitor w/ critical need detect'
--O AND CONDTEXT = 'Slightly used'
--O AND COSTTEXT = 'Robbery; a complete and total rip-off';
-- PASS:0689 If count = 1?
+
--O SELECT COUNT(*) FROM VERBOSE_INV
--O WHERE ITEMTEXT =
--O 'Self-destruct VGA monitor w/ critical need detect'
--O AND CONDTEXT = 'Visibly damaged (no returns)'
--O AND COSTTEXT = 'Outrageously expensive';
-- PASS:0689 If count = 1?
+
--O SELECT COUNT(*) FROM VERBOSE_INV
--O WHERE ITEMTEXT = 'World''s worst VCR'
--O AND CONDTEXT = 'Returned as defective'
--O AND COSTTEXT = 'Absurdly expensive';
-- PASS:0689 If count = 1?
+
COMMIT;
ij> --O DROP TABLE INVENTORY CASCADE;
DROP TABLE INVENTORY ;
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table and 3 views are dropped?
+
COMMIT;
ij> --O DROP TABLE COST_CODES CASCADE;
DROP TABLE COST_CODES ;
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is dropped?
+
COMMIT;
ij> --O DROP TABLE CONDITION_CODES CASCADE;
DROP TABLE CONDITION_CODES ;
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is dropped?
+
COMMIT;
ij> --O DROP TABLE ITEM_CODES CASCADE;
DROP TABLE ITEM_CODES ;
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is dropped?
+
COMMIT;
ij> -- END TEST >>> 0689 <<< END TEST
+
-- *********************************************
+
-- TEST:0690 Many Trans SQL features #2: talk show schedule!
+
--O CREATE TABLE PORGRAM (
--O SEGNO INT PRIMARY KEY,
--O STARTS TIME NOT NULL,
--O LASTS INTERVAL MINUTE TO SECOND NOT NULL,
--O SEGMENT VARCHAR (50));
-- PASS:0690 If table is created?
+
--O COMMIT;
+
--O CREATE VIEW GAPS AS
--O SELECT * FROM PORGRAM AS OUTERR WHERE NOT EXISTS
--O (SELECT * FROM PORGRAM AS INNERR WHERE OUTERR.STARTS
--O + OUTERR.LASTS = INNERR.STARTS);
-- PASS:0690 If view is created?
+
--O COMMIT;
+
--O INSERT INTO PORGRAM VALUES (
--O 1, TIME( '12:00:00'),
--O CAST ('10:00' AS INTERVAL MINUTE TO SECOND),
--O 'Monologue');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 2, TIME( '12:10:00'),
--O CAST ('04:30' AS INTERVAL MINUTE TO SECOND),
--O 'Stupid commercials');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 3, TIME( '12:14:30'),
--O CAST ('12:30' AS INTERVAL MINUTE TO SECOND),
--O 'Braunschweiger, plug Explosion Man II');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 4, TIME( '12:27:00'),
--O CAST ('03:00' AS INTERVAL MINUTE TO SECOND),
--O 'Stupid commercials');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 5, TIME( '12:30:00'),
--O CAST ('00:10' AS INTERVAL MINUTE TO SECOND),
--O 'Tease');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 6, TIME( '12:30:10'),
--O CAST ('03:50' AS INTERVAL MINUTE TO SECOND),
--O 'Stupid commercials, local news');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 7, TIME( '12:34:00'),
--O CAST ('11:00' AS INTERVAL MINUTE TO SECOND),
--O 'Spinal Tap, plug Asexual Harassment');
--O-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 8, TIME( '12:45:00'),
--O CAST ('05:00' AS INTERVAL MINUTE TO SECOND),
--O 'Stupid commercials');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 9, TIME( '12:50:00'),
--O CAST ('05:00' AS INTERVAL MINUTE TO SECOND),
--O 'Spinal Tap, play Ode du Toilette');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 10, TIME( '12:55:00'),
--O CAST ('03:00' AS INTERVAL MINUTE TO SECOND),
--O 'Stupid commercials');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 11, TIME( '12:58:00'),
--O CAST ('00:10' AS INTERVAL MINUTE TO SECOND),
--O 'Credits');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 12, TIME( '12:58:10'),
--O CAST ('01:50' AS INTERVAL MINUTE TO SECOND),
--O 'Stupid commercials');
-- PASS:0690 If 1 row is inserted?
+
--O INSERT INTO PORGRAM VALUES (
--O 13, TIME( '13:00:00'),
--O CAST ('00:00' AS INTERVAL MINUTE TO SECOND),
--O 'END');
-- PASS:0690 If 1 row is inserted?
+
--O SELECT COUNT(*) FROM GAPS;
-- PASS:0690 If count = 0?
+
--O UPDATE PORGRAM
--O SET STARTS = TIME( '12:14:30')
--O WHERE SEGNO = 7;
-- PASS:0690 If 1 row is updated?
+
--O UPDATE PORGRAM SET STARTS = STARTS -
--O CAST ('01:30' AS INTERVAL MINUTE TO SECOND)
--O WHERE SEGNO >= 4 AND SEGNO <= 6;
-- PASS:0690 If 3 rows are updated?
+
--O UPDATE PORGRAM SET STARTS = TIME( '12:28:40') +
--O CAST ('03:50' AS INTERVAL MINUTE TO SECOND)
--O WHERE SEGNO = 3;
-- PASS:0690 If 1 row is updated?
+
--O SELECT COUNT(*) FROM GAPS;
-- PASS:0690 If count = 0?
+
--O SELECT SEGNO FROM PORGRAM ORDER BY STARTS;
-- PASS:0690 If 13 rows selected with SEGNO in the following order?
-- PASS:0690 1
@@ -315,29 +389,41 @@
-- PASS:0690 11
-- PASS:0690 12
-- PASS:0690 13
+
--O UPDATE PORGRAM SET LASTS = LASTS -
--O CAST (30 AS INTERVAL SECOND) WHERE SEGNO
--O = 10;
-- PASS:0690 If 1 row is updated?
+
--O SELECT SEGNO FROM GAPS;
-- PASS:0690 If 1 row selected and SEGNO = 10?
+
--O UPDATE PORGRAM SET LASTS = LASTS +
--O CAST ('30' AS INTERVAL SECOND) WHERE
--O SEGNO = 9;
-- PASS:0690 If 1 row is updated?
+
--O UPDATE PORGRAM SET STARTS = STARTS +
--O CAST (30. AS INTERVAL SECOND) WHERE
--O SEGNO = 10;
-- PASS:0690 If 1 row is updated?
+
--O SELECT COUNT(*) FROM GAPS;
-- PASS:0690 If count = 0?
+
--O COMMIT;
+
--O DROP TABLE PORGRAM CASCADE;
-- PASS:0690 If table and view are dropped?
+
--O COMMIT;
+
-- END TEST >>> 0690 <<< END TEST
+
-- *********************************************
+
-- TEST:0691 INFO_SCHEM: SQLSTATEs for length overruns!
+
CREATE TABLE LONG1 (
C1 INT,
CHECK (
@@ -384,6 +470,7 @@
0 rows inserted/updated/deleted
ij> -- PASS:0691 If WARNING: search condition too long for information schema?
-- PASS:0691 OR successful completion?
+
ROLLBACK WORK;
ij> CREATE VIEW LONG2 AS
SELECT * FROM USIG WHERE
@@ -430,8 +517,10 @@
0 rows inserted/updated/deleted
ij> -- PASS:0691 If WARNING: query expression too long for information schema?
-- PASS:0691 OR successful completion?
+
ROLLBACK WORK;
ij> -- END TEST >>> 0691 <<< 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/dml141.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml141.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml141.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml141.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML141
+
-- SQL Test Suite, V6.0, Interactive SQL, dml141.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -14,6 +16,7 @@
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
ij> -- date_time print
+
-- NOTE Direct support for SQLCODE or SQLSTATE is not required
-- NOTE in Interactive Direct SQL, as defined in FIPS 127-2.
-- NOTE ********************* instead ***************************
@@ -25,17 +28,21 @@
-- NOTE "warning" or "no data", then the system shall display
-- NOTE a message indicating that the statement completed,
-- NOTE giving a textual description of the "warning" or "no data."
+
-- TEST:0514 SQLSTATE 23502: integrity constraint violation!
+
-- NOT NULL constraint violated
INSERT INTO HU.STAFF VALUES (NULL, NULL, NULL, NULL);
ERROR 23502: Column 'EMPNUM' cannot accept a NULL value.
ij> -- PASS:0514 If ERROR, integrity constraint violation, 0 rows inserted?
+
-- UNIQUE constraint violated
INSERT INTO HU.STAFF VALUES ('E1', 'Agnetha', 12, 'Paris');
-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 'xxxxGENERATED-IDxxxx' defined on 'STAFF'.
+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 'STAFF_UNIQUE' defined on 'STAFF'.
ij> -- PASS:0514 If ERROR, integrity constraint violation, 0 rows inserted?
+
ROLLBACK WORK;
ij> -- END TEST >>> 0514 <<< 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/dml144.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml144.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml144.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml144.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML144
+
-- SQL Test Suite, V6.0, Interactive SQL, dml144.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -13,11 +15,15 @@
FLATER
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O ROLLBACK WORK;
+
-- date_time print
+
-- TEST:0834 <length expression> (static)!
+
CREATE TABLE GRUB (C1 VARCHAR (10));
0 rows inserted/updated/deleted
ij> -- PASS:0834 If table is created?
+
COMMIT WORK;
ij> SELECT LENGTH (EMPNAME)
FROM HU.STAFF WHERE GRADE = 10;
@@ -25,49 +31,61 @@
-----------
20
ij> -- PASS:0834 If 1 row selected and value is 20?
+
SELECT LENGTH ('HI' || 'THERE')
FROM HU.ECCO;
1
-----------
7
ij> -- PASS:0834 If 1 row selected and value is 7?
+
INSERT INTO GRUB VALUES ('Hi ');
1 row inserted/updated/deleted
ij> -- PASS:0834 If 1 row is inserted?
+
SELECT LENGTH (C1)
FROM GRUB;
1
-----------
4
ij> -- PASS:0834 If 1 row selected and value is 4?
+
-- following is not supported in derby
-- SELECT OCTET_LENGTH (C1)
-- FROM GRUB;
-- PASS:0834 If 1 row selected and value is > 2?
+
UPDATE GRUB SET C1 = NULL;
1 row inserted/updated/deleted
ij> -- PASS:0834 If 1 row is updated?
+
SELECT LENGTH (C1)
FROM GRUB;
1
-----------
NULL
ij> -- PASS:0834 If 1 row selected and value is NULL?
+
-- following is not supported in derby
-- SELECT OCTET_LENGTH (C1)
-- FROM GRUB;
-- PASS:0834 If 1 row selected and value is NULL?
+
ROLLBACK WORK;
ij> --O DROP TABLE GRUB CASCADE;
DROP TABLE GRUB ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0834 <<< END TEST
+
-- *********************************************
+
-- TEST:0835 <character substring function> (static)!
+
CREATE TABLE MOREGRUB (C1 VARCHAR (10), ID INT);
0 rows inserted/updated/deleted
ij> -- PASS:0835 If table is created?
+
COMMIT WORK;
ij> CREATE VIEW X4 (S1, S2, ID) AS
SELECT SUBSTR (C1, 6),
@@ -75,6 +93,7 @@
FROM MOREGRUB;
0 rows inserted/updated/deleted
ij> -- PASS:0835 If view is created?
+
COMMIT WORK;
ij> SELECT SUBSTR (CITY, 4, 10)
FROM HU.STAFF WHERE EMPNAME = 'Ed';
@@ -82,64 +101,82 @@
----------
on
ij> -- PASS:0835 If 1 row selected and value is 'on '?
+
-- NOTE:0835 Right truncation subtest deleted.
+
SELECT SUBSTR (CITY, 4, -1)
FROM HU.STAFF WHERE EMPNAME = 'Ed';
1
---------------
ERROR 22011: The second or third argument of the SUBSTR function is out of range.
ij> -- PASS:0835 If ERROR, substring error, 0 rows selected?
+
SELECT SUBSTR (CITY, 0, 10)
FROM HU.STAFF WHERE EMPNAME = 'Ed';
1
----------
ERROR 22011: The second or third argument of the SUBSTR function is out of range.
ij> -- PASS:0835 If 1 row selected and value is 'Akron '?
+
-- NOTE:0835 Host language variable subtest deleted.
+
SELECT SUBSTR (CITY, 1, 1)
FROM HU.STAFF WHERE EMPNAME = 'Ed';
1
----
A
ij> -- PASS:0835 If 1 row selected and value is 'A'?
+
SELECT SUBSTR (CITY, 1, 0)
FROM HU.STAFF WHERE EMPNAME = 'Ed';
1
---------------
+
ij> -- PASS:0835 If 1 row selected and value is ''?
+
SELECT SUBSTR (CITY, 12, 1)
FROM HU.STAFF WHERE EMPNAME = 'Ed';
1
----
+
ij> -- PASS:0835 If 1 row selected and value is ''?
+
INSERT INTO MOREGRUB VALUES ('Pretzels', 1);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?
+
INSERT INTO MOREGRUB VALUES (NULL, 2);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?
+
INSERT INTO MOREGRUB VALUES ('Chips', 3);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?
+
SELECT S1 FROM X4 WHERE ID = 1;
S1
----------
els
ij> -- PASS:0835 If 1 row selected and S1 = 'els'?
+
SELECT S1 FROM X4 WHERE ID = 3;
S1
----------
+
ij> -- PASS:0835 If 1 row selected and S1 = ''?
+
SELECT S2 FROM X4 WHERE ID = 1;
S2
----
retz
ij> -- PASS:0835 If 1 row selected and S2 = 'retz'?
+
SELECT S2 FROM X4 WHERE ID = 3;
S2
----
hips
ij> -- PASS:0835 If 1 row selected and S2 = 'hips'?
+
SELECT SUBSTR (C1, ID)
FROM MOREGRUB
WHERE C1 LIKE 'Ch%';
@@ -147,6 +184,7 @@
----------
ips
ij> -- PASS:0835 If 1 row selected and value is 'ips'?
+
SELECT SUBSTR (C1, 1, ID)
FROM MOREGRUB
WHERE C1 LIKE 'Ch%';
@@ -154,29 +192,35 @@
----------
Chi
ij> -- PASS:0835 If 1 row selected and value is 'Chi'?
+
-- NOTE:0835 Host language variable subtest deleted.
+
SELECT S1 FROM X4 WHERE ID = 2;
S1
----------
NULL
ij> -- PASS:0835 If 1 row selected and S1 is NULL?
+
DELETE FROM MOREGRUB;
3 rows inserted/updated/deleted
ij> INSERT INTO MOREGRUB VALUES ('Tacos', NULL);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?
+
SELECT SUBSTR (C1, 1, ID)
FROM MOREGRUB;
1
----------
NULL
ij> -- PASS:0835 If 1 row selected and value is NULL?
+
SELECT SUBSTR (C1, ID, 1)
FROM MOREGRUB;
1
----
NULL
ij> -- PASS:0835 If 1 row selected and value is NULL?
+
UPDATE MOREGRUB SET C1 = NULL;
1 row inserted/updated/deleted
ij> SELECT SUBSTR (C1, ID, ID)
@@ -185,6 +229,7 @@
----------
NULL
ij> -- PASS:0835 If 1 row selected and value is NULL?
+
ROLLBACK WORK;
ij> --O DROP TABLE MOREGRUB CASCADE;
drop view x4;
@@ -193,8 +238,11 @@
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0835 <<< END TEST
+
-- *********************************************
+
-- TEST:0839 Composed <length expression> and SUBSTR!
+
SELECT LENGTH (SUBSTR
(CITY, 4, 4))
FROM HU.STAFF WHERE EMPNAME = 'Ed';
@@ -202,6 +250,7 @@
-----------
4
ij> -- PASS:0839 If 1 row selected and value is 4?
+
SELECT LENGTH (SUBSTR
(EMPNUM, 1))
FROM HU.STAFF WHERE EMPNAME = 'Ed';
@@ -209,8 +258,9 @@
-----------
3
ij> -- PASS:0839 If 1 row selected and value is 3?
+
COMMIT WORK;
ij> -- END TEST >>> 0839 <<< 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/dml147.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml147.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml147.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml147.out Fri Oct 27 13:04:21 2006
@@ -1,9 +1,12 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML147
+
-- SQL Test Suite, V6.0, Interactive SQL, dml147.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
+
--O SELECT USER FROM HU.ECCO;
VALUES USER;
1
@@ -11,39 +14,49 @@
FLATER
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O ROLLBACK WORK;
+
-- date_time print
+
-- TEST:0840 Roll back schema manipulation !
+
CREATE TABLE NOT_THERE (C1 CHAR (10));
0 rows inserted/updated/deleted
ij> -- PASS:0840 If table is created?
+
ROLLBACK WORK;
ij> INSERT INTO NOT_THERE VALUES ('1234567890');
ERROR 42X05: Table/View 'NOT_THERE' does not exist.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
+
ROLLBACK WORK;
ij> CREATE VIEW NOT_HERE AS
SELECT * FROM USIG;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If view is created?
+
ROLLBACK WORK;
ij> SELECT COUNT (*) FROM NOT_HERE;
ERROR 42X05: Table/View 'NOT_HERE' does not exist.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
+
ROLLBACK WORK;
ij> ALTER TABLE USIG
ADD COLUMN NUL INT;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If column is added?
+
ROLLBACK WORK;
ij> SELECT COUNT (*)
FROM USIG WHERE NUL IS NULL;
ERROR 42X04: Column 'NUL' 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 'NUL' is not a column in the target table.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
+
ROLLBACK WORK;
ij> --O DROP TABLE USIG CASCADE;
DROP TABLE USIG ;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If table is dropped?
+
ROLLBACK WORK;
ij> SELECT COUNT(*)
FROM U_SIG;
@@ -51,6 +64,7 @@
-----------
2
ij> -- PASS:0840 If count = 2?
+
ROLLBACK WORK;
ij> SELECT COUNT(*)
FROM USIG;
@@ -58,13 +72,18 @@
-----------
2
ij> -- PASS:0840 If count = 2?
+
ROLLBACK WORK;
ij> -- END TEST >>> 0840 <<< END TEST
+
-- *********************************************
+
-- TEST:0841 Multiple-join and default order of joins !
+
-- setup
DELETE FROM HU.STAFF4;
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 HU.STAFF4
SELECT * FROM HU.STAFF3
WHERE EMPNUM > 'E3';
@@ -81,21 +100,27 @@
E4
ij> -- PASS:0841 If 2 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E5, E4 ?
+
--O SELECT EMPNUM FROM
--O (HU.STAFF3 NATURAL LEFT JOIN HU.STAFF) NATURAL INNER JOIN HU.STAFF4
--O ORDER BY EMPNUM ASC;
-- PASS:0841 If 2 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E4, E5 ?
+
--O SELECT EMPNUM FROM
--O HU.STAFF3 NATURAL LEFT JOIN (HU.STAFF NATURAL INNER JOIN HU.STAFF4)
--O ORDER BY EMPNUM;
--O ;
-- PASS:0841 If 5 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E1, E2, E3, E4, E5 ?
+
ROLLBACK WORK;
ij> -- END TEST >>> 0841 <<< END TEST
+
-- *********************************************
+
-- TEST:0842 Multi-column joins !
+
-- setup
CREATE TABLE STAFF66 (
SALARY INTEGER,
@@ -110,12 +135,15 @@
FROM HU.STAFF3 WHERE EMPNUM > 'E2';
3 rows inserted/updated/deleted
ij> -- PASS:0842 If 3 rows inserted ?
+
UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM = 'E5';
1 row inserted/updated/deleted
ij> -- PASS:0842 If 1 row updated ?
+
UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE = 12;
2 rows inserted/updated/deleted
ij> -- PASS:0842 If 2 rows updated ?
+
-- FULL OUTER JOIN of tables with unique data in the joined column
--O SELECT EMPNUM, CITY, SALARY
--O FROM HU.STAFF3 LEFT JOIN STAFF66 USING (EMPNUM)
@@ -130,6 +158,7 @@
-- PASS:0842 E4 Deale 12000 ?
-- PASS:0842 E5 NULL 13000 ?
-- PASS:0842 E6 Akron NULL ?
+
-- 7.5 SR 6 d
-- table STAFF66 has 3 rows, only 1 matching on all columns
-- this is a 3-column join:
@@ -144,6 +173,8 @@
ij> -- PASS:0842 If 1 row selected?
-- PASS:0842 If column values are in the exact order: ?
-- PASS:0842 EMPNAME=Carmen,GRADE=13,EMPNUM=E3,SALARY=13000,CITY=Vienna?
+
+
-- table STAFF66 has 3 rows, only 1 matching on all columns
-- this is a 3-column join, preserving HU.STAFF3:
--O SELECT EMPNUM, EMPNAME, SALARY FROM
@@ -155,6 +186,8 @@
-- PASS:0842 E3 Carmen 13000 ?
-- PASS:0842 E4 Ali NULL ?
-- PASS:0842 E6 Ed NULL ?
+
+
-- table HU.STAFF has 5 rows, only 3 matching on all columns
-- this is a 3-column join, preserving HU.STAFF:
--O SELECT EMPNUM, EMPNAME, SALARY FROM
@@ -166,6 +199,8 @@
-- PASS:0842 E4 Don 12000 ?
-- PASS:0842 E3 Carmen 13000 ?
-- PASS:0842 E2 Betty NULL ?
+
+
-- table HU.STAFF has 5 rows, only 3 matching on all columns
-- ordinal position is determined by order in T1, not USING list
-- REF: 7.5 SR 6 d
@@ -179,6 +214,8 @@
-- PASS:0842 Carmen 13 E3 13000 Vienna ?
-- PASS:0842 Don 12 E4 12000 Deale ?
-- PASS:0842 Ed 13 E5 13000 Akron ?
+
+
-- table STAFF66 has 3 rows, with 2 matching on named columns
-- this is a 2-column join, preserving HU.STAFF3:
--O SELECT * FROM
@@ -190,6 +227,8 @@
-- PASS:0842 E3 13 Carmen Vienna 13000 Carmen ?
-- PASS:0842 E4 12 Ali Deale 12000 Don ?
-- PASS:0842 E6 13 Ed Akron NULL NULL ?
+
+
-- similar to above, except for explicit names of columns
--O SELECT staff3.EMPNUM, staff3.GRADE, HU.STAFF3.EMPNAME, CITY,
SELECT HU.staff3.EMPNUM, HU.staff3.GRADE, HU.STAFF3.EMPNAME, CITY,
@@ -203,6 +242,7 @@
E3 |13 |Carmen |Vienna |13000 |Carmen
ij> -- PASS:0842 If 1 row selected with ordered column values?
-- PASS:0842 E3 13 Carmen Vienna 13000 Carmen ?
+
-- REF: 7.5 GR 1 d ii
-- this is a cartesian product
--O SELECT COUNT (*) FROM STAFF66 NATURAL RIGHT JOIN HU.PROJ;
@@ -211,12 +251,15 @@
-----------
18
ij> -- PASS:0842 If count = 18?
+
+
ROLLBACK WORK;
ij> --O DROP TABLE STAFF66 CASCADE;
DROP TABLE STAFF66 ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0842 <<< 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/dml148.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml148.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml148.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml148.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML148
+
-- SQL Test Suite, V6.0, Interactive SQL, dml148.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -14,9 +16,12 @@
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
ij> -- date_time print
+
-- TEST:0843 Ordering of column names in joins !
+
-- REFERENCE: 7.5 sr 6 f
-- NOTE:0843 ordering of column names in NATURAL JOIN
+
SELECT *
--O FROM HU.WORKS NATURAL LEFT JOIN HU.PROJ
FROM HU.WORKS JOIN HU.PROJ on (HU.WORKS.pnum=HU.PROJ.pnum)
@@ -40,7 +45,10 @@
-- PASS:0843 If ordered row and column values for first two rows are: ?
-- PASS:0843 P2 E4 20 CALM Code 30000 Vienna ?
-- PASS:0843 P4 E4 40 SDP Design 20000 Deale ?
+
+
-- NOTE:0843 ordering of column names in JOIN ... ON
+
SELECT *
FROM HU.WORKS JOIN HU.PROJ ON (HU.WORKS.PNUM=HU.PROJ.PNUM)
ORDER BY EMPNUM DESC, HU.PROJ.PNUM;
@@ -63,8 +71,11 @@
-- PASS:0843 P2 E4 20 CALM Code 30000 Vienna ?
-- PASS:0843 P4 E4 40 SDP Design 20000 Deale ?
-- NOTE:0843 Same answer as above
+
+
-- NOTE:0843 ordering of column names in NATURAL JOIN
-- REFERENCE: 7.5 sr 5
+
SELECT *
--O FROM HU.WORKS RIGHT JOIN HU.PROJ
--O ON HU.WORKS.PNUM = HU.PROJ.PNUM
@@ -89,10 +100,15 @@
-- PASS:0843 If ordered row and column values for first two rows are: ?
-- PASS:0843 E4 P2 20 P2 CALM Code 30000 Vienna ?
-- PASS:0843 E4 P4 40 P4 SDP Design 20000 Deale ?
+
ROLLBACK WORK;
ij> -- END TEST >>> 0843 <<< END TEST
+
-- *********************************************
+
-- TEST:0844 Outer join predicates !
+
+
CREATE TABLE SEVEN_TYPES (
T_INT INTEGER,
T_CHAR CHAR(10),
@@ -106,6 +122,7 @@
ij> -- setup
DELETE FROM SEVEN_TYPES;
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 SEVEN_TYPES VALUES (1, 'E1',-11, 2, 3, 4, 5);
1 row inserted/updated/deleted
ij> INSERT INTO SEVEN_TYPES VALUES (2, 'E2', -5, 13, 33,-444, -55);
@@ -137,6 +154,7 @@
-- PASS:0844 Carmen Vienna NULL ?
-- PASS:0844 Don Deale NULL ?
-- PASS:0844 Ed Akron NULL ?
+
-- NOTE:0844 comparable CHAR types
-- NOTE:0844 IN predicate, with literals and variable value
SELECT T_INT, T_CHAR, EMPNAME, EMPNUM, GRADE
@@ -157,6 +175,7 @@
-- PASS:0844 NULL NULL Carmen E3 13 ?
-- PASS:0844 NULL NULL Don E4 12 ?
-- PASS:0844 NULL NULL Ed E5 13 ?
+
SELECT HU.STAFF.CITY,EMPNAME,PNAME,BUDGET
--O FROM HU.STAFF LEFT JOIN HU.PROJ
FROM HU.STAFF left outer JOIN HU.PROJ
@@ -181,6 +200,7 @@
-- PASS:0844 Deale Don NULL NULL ?
-- PASS:0844 Vienna Betty NULL NULL ?
-- PASS:0844 Vienna Carmen NULL NULL ?
+
-- NOTE:0844 difference between WHERE and ON
SELECT HU.STAFF.CITY,EMPNAME,PNAME,BUDGET
--O FROM HU.STAFF LEFT JOIN HU.PROJ
@@ -204,6 +224,7 @@
-- PASS:0844 Deale Alice PAYR 50000 ?
-- PASS:0844 Vienna Betty NULL NULL ?
-- PASS:0844 Vienna Carmen NULL NULL ?
+
-- NOTE:0844 correlation name with self-JOIN
SELECT XX.T_INT, YY.T_INT
FROM SEVEN_TYPES XX RIGHT OUTER JOIN SEVEN_TYPES YY
@@ -223,6 +244,7 @@
-- PASS:0844 NULL 3 ?
-- PASS:0844 NULL 12 ?
-- PASS:0844 NULL 12 ?
+
-- NOTE:0844 nested booleans
-- NOTE:0844 data types are merely comparable
SELECT GRADE, T_FLOAT, T_DOUBLE
@@ -244,12 +266,14 @@
-- PASS:0844 12 NULL NULL ?
-- PASS:0844 13 44 (approximately) 555 (approximately) ?
-- PASS:0844 13 44 (approximately) 555 (approximately) ?
+
ROLLBACK WORK;
ij> --ODROP TABLE SEVEN_TYPES CASCADE;
DROP TABLE SEVEN_TYPES ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0844 <<< 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/dml149.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml149.out?view=diff&rev=468514&r1=468513&r2=468514
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml149.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dml149.out Fri Oct 27 13:04:21 2006
@@ -1,8 +1,10 @@
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML149
+
-- SQL Test Suite, V6.0, Interactive SQL, dml149.sql
-- 59-byte ID
-- TEd Version #
+
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
@@ -13,29 +15,44 @@
FLATER
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O ROLLBACK WORK;
+
-- date_time print
+
-- TEST:0561 Double SET TRANSACTION!
+
ROLLBACK WORK;
ij> -- PASS:0561 If successful completion?
+
--O SET TRANSACTION READ ONLY;
-- PASS:0561 If successful completion?
+
--O SET TRANSACTION READ WRITE;
-- PASS:0561 If successful completion?
+
--O INSERT INTO USIG VALUES (10, 20);
-- PASS:0561 If 1 row is inserted?
+
--O ROLLBACK WORK;
-- PASS:0561 If successful completion?
+
--O SET TRANSACTION READ WRITE;
-- PASS:0561 If successful completion?
+
--O SET TRANSACTION READ ONLY;
-- PASS:0561 If successful completion?
+
--O INSERT INTO USIG VALUES (10, 20);
-- PASS:0561 If ERROR, invalid transaction state, 0 rows inserted?
+
--O ROLLBACK WORK;
-- PASS:0561 If successful completion?
+
-- END TEST >>> 0561 <<< END TEST
+
-- *********************************************
+
-- TEST:0846 Feature 20, CAST functions (static) nits!
+
CREATE TABLE NO_DUCK (
GOOSE NUMERIC (4, 2),
ALBATROSS FLOAT,
@@ -43,6 +60,7 @@
OSPREY CHAR (10));
0 rows inserted/updated/deleted
ij> -- PASS:0846 If table is created?
+
COMMIT WORK;
ij> -- NOTE:0846 CAST (100 AS NUMERIC (2)) loses the leading significant digit
SELECT CAST (100 AS NUMERIC (2))
@@ -51,22 +69,26 @@
----
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(2,0).
ij> -- PASS:0846 If ERROR, numeric value out of range, 0 rows selected?
+
SELECT CAST (100.5 AS DECIMAL (3))
FROM HU.ECCO;
1
----
100
ij> -- PASS:0846 If 1 row selected and value is 100 or 101?
+
INSERT INTO NO_DUCK VALUES (
CAST (' 23.23 ' AS NUMERIC (4, 2)), 1.57E-1, -9, 'QUACK');
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is inserted?
+
SELECT COUNT(*)
FROM NO_DUCK WHERE GOOSE = 23.23;
1
-----------
1
ij> -- PASS:0846 If count = 1?
+
-- No cast from CHAR to FLOAT allowed
-- DELETE FROM NO_DUCK
-- WHERE ALBATROSS - CAST (' 15.5E0 ' AS FLOAT) < 3E-1;
@@ -80,36 +102,43 @@
-----------
0
ij> -- PASS:0846 If count = 0?
+
INSERT INTO NO_DUCK
--O SELECT 22.22, CAST (C1 AS FLOAT), 0, C1 FROM HU.ECCO;
SELECT 22.22, CAST (C1 AS FLOAT), 0, C1 FROM ECCO;
ERROR 42X05: Table/View 'ECCO' does not exist.
ij> -- PASS:0846 If ERROR, invalid character value for cast, 0 rows inserted?
+
INSERT INTO NO_DUCK
SELECT 22.22, 2.222E1, CAST (C1 AS INT), 'QUACK!' FROM HU.ECCO;
ERROR 22018: Invalid character string format for type INTEGER.
ij> -- PASS:0846 If ERROR, invalid character value for cast, 0 rows inserted?
+
SELECT CAST (CAST (3 AS DEC (5, 3)) AS CHAR (5))
FROM HU.ECCO;
1
-----
3.000
ij> -- PASS:0846 If 1 row selected and value is '3.000'?
+
INSERT INTO NO_DUCK VALUES (
12.00, -10.5E0, 12, 'QUACK!');
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is inserted?
+
UPDATE NO_DUCK
SET OSPREY = CAST (GOOSE AS CHAR (10))
WHERE SEAGULL = CAST (GOOSE AS DEC);
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?
+
SELECT OSPREY
FROM NO_DUCK;
OSPREY
----------
12.00
ij> -- PASS:0846 If 1 row selected and OSPREY = '12.00 '?
+
SELECT OSPREY
FROM NO_DUCK
WHERE OSPREY < CAST (SEAGULL + 1 AS CHAR (10))
@@ -118,16 +147,19 @@
----------
12.00
ij> -- PASS:0846 If 1 row selected and OSPREY = '12.00 '?
+
UPDATE NO_DUCK
SET OSPREY = CAST (-SEAGULL AS CHAR (10));
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?
+
SELECT OSPREY
FROM NO_DUCK;
OSPREY
----------
-12
ij> -- PASS:0846 If 1 row selected and OSPREY = '-12 '?
+
-- NOTE:0846 Expected value -12.00 is too long for CHAR (5) cast
SELECT CAST (-GOOSE AS CHAR (5))
FROM NO_DUCK;
@@ -135,67 +167,81 @@
-----
-12.0
ij> -- PASS:0846 If ERROR, string data, right truncation, 0 rows selected?
+
UPDATE NO_DUCK
SET ALBATROSS = 0.0;
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?
+
SELECT CAST (-ALBATROSS AS CHAR (5))
FROM NO_DUCK;
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row selected and value is '0E0 '?
+
SELECT CAST (0230E-1 AS CHAR (10))
FROM HU.ECCO;
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row selected and value is '2.3E1 '?
+
SELECT CAST (0230E+1 AS CHAR (10))
FROM HU.ECCO;
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row selected and value is '2.3E3 '?
+
DELETE FROM NO_DUCK;
1 row inserted/updated/deleted
ij> INSERT INTO NO_DUCK VALUES (
0.00, -10.5E0, -0, 'QUACK!');
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is inserted?
+
UPDATE NO_DUCK
SET OSPREY = CAST (ALBATROSS AS CHAR (10))
WHERE GOOSE = CAST (SEAGULL AS NUMERIC (2));
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row is updated?
+
SELECT OSPREY
FROM NO_DUCK;
OSPREY
----------
QUACK!
ij> -- PASS:0846 If 1 row selected and OSPREY = '-1.05E1 '?
+
UPDATE NO_DUCK SET ALBATROSS = -0.5;
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?
+
UPDATE NO_DUCK
SET OSPREY = CAST (ALBATROSS AS CHAR (10));
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row is updated?
+
SELECT OSPREY
FROM NO_DUCK;
OSPREY
----------
QUACK!
ij> -- PASS:0846 If 1 row selected and OSPREY = '-5E-1 '?
+
UPDATE NO_DUCK
SET OSPREY = CAST (-ALBATROSS AS CHAR (10));
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If 1 row is updated?
+
SELECT OSPREY
FROM NO_DUCK;
OSPREY
----------
QUACK!
ij> -- PASS:0846 If 1 row selected and OSPREY = '5E-1 '?
+
-- NOTE:0846 Expected value -5E-1 is too long for CHAR (4) cast
SELECT CAST (ALBATROSS AS CHAR (4))
FROM NO_DUCK;
ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'.
ij> -- PASS:0846 If ERROR, string data, right truncation, 0 rows selected?
+
SELECT CAST (NULL AS CHAR (10)), GOOSE FROM NO_DUCK
WHERE SEAGULL = 0
UNION
@@ -205,28 +251,33 @@
------------------
NULL |0.00
ij> -- PASS:0846 If 1 row selected and first value is NULL?
+
UPDATE NO_DUCK SET GOOSE =
CAST (NULL AS NUMERIC (2, 2));
1 row inserted/updated/deleted
ij> -- PASS:0846 If 1 row is updated?
+
SELECT COUNT(*)
FROM NO_DUCK WHERE GOOSE IS NULL;
1
-----------
1
ij> -- PASS:0846 If count = 1?
+
SELECT CAST (GOOSE AS INT)
FROM NO_DUCK;
1
-----------
NULL
ij> -- PASS:0846 If 1 row selected and value is NULL?
+
ROLLBACK WORK;
ij> --O DROP TABLE NO_DUCK CASCADE;
DROP TABLE NO_DUCK ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0846 <<< END TEST
+
-- *************************************************////END-OF-MODULE
;
-ij>
+ij>
\ No newline at end of file