dml112.out

derby database source code.good for you.

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
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
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,
     RDATE DATE,
     RTIME TIME,
     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
--O     IS NOT NULL AND SOLD IS NULL AND
--O     MERCH.ITEMKEY = COR1.ITEMKEY
--O            UNION
--O     SELECT ITEMKEY, ORDERED,
--O     CAST (NULL AS INTERVAL MONTH),
--O     CAST (NULL AS INTERVAL DAY TO HOUR) FROM
--O     MERCH WHERE RDATE IS NOT NULL AND SOLD IS NULL
--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));
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          
-----------
0          
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          
-----------
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;
1          
-----------
6          
ij> -- PASS:0623 If count = 6?
   SELECT COUNT(*) 
     FROM JNULL6
      WHERE C2 IS NULL;
1          
-----------
2          
ij> -- PASS:0623 If count = 2?
   SELECT COUNT(*) 
     FROM JNULL6
      WHERE C2 = C1
        AND D1 IS NULL;
1          
-----------
2          
ij> -- PASS:0623 If count = 2?
   COMMIT WORK;
ij> --O   DROP TABLE JNULL1 CASCADE;
   DROP VIEW JNULL3 ;
0 rows inserted/updated/deleted
ij> DROP VIEW JNULL5 ;
0 rows inserted/updated/deleted
ij> DROP VIEW JNULL6 ;
0 rows inserted/updated/deleted
ij> DROP VIEW JNULL4 ;
0 rows inserted/updated/deleted
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