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

ij> AUTOCOMMIT OFF;
ij> -- MODULE DML079

-- SQL Test Suite, V6.0, Interactive SQL, dml079.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION HU
   set schema HU;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
HU                                                                                                                              
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment

-- date_time print


-- TEST:0451 UNIQUEness is case sensitive!

         UPDATE STAFF SET EMPNUM = 'e2'
           WHERE EMPNUM = 'E4';
1 row inserted/updated/deleted
ij> -- PASS:0451 If 1 row updated?  

         INSERT INTO STAFF(EMPNUM)
           VALUES ('E1');
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:0451 If ERROR, unique constraint, 0 rows inserted?
 
         INSERT INTO STAFF(EMPNUM)
           VALUES ('e1');
1 row inserted/updated/deleted
ij> -- PASS:0451 If 1 row inserted?

         UPDATE STAFF
           SET EMPNUM = 'E1' WHERE EMPNUM = 'e1';
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:0451 If ERROR, unique constraint, 0 rows updated?

         SELECT * FROM STAFF;
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E1 |Alice               |12   |Deale          
E2 |Betty               |10   |Vienna         
E3 |Carmen              |13   |Vienna         
e2 |Don                 |12   |Deale          
E5 |Ed                  |13   |Akron          
e1 |NULL                |NULL |NULL           
ij> -- PASS:0451 If 6 rows are selected?
-- PASS:0451 If EMPNUMs are 'e1','e2','E1','E2','E3','E5'?
      
         INSERT INTO WORKS (EMPNUM,PNUM)
           VALUES ('e1','p2');
1 row inserted/updated/deleted
ij> -- PASS:0451 If 1 row inserted?
   
         INSERT INTO WORKS (EMPNUM,PNUM)
           VALUES ('E1','p2');
1 row inserted/updated/deleted
ij> -- PASS:0451 If 1 row inserted?
  
         INSERT INTO WORKS (EMPNUM,PNUM) 
           VALUES ('E1','P2');
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 'WORKSC1' defined on 'WORKS'.
ij> -- PASS:0451 If ERROR, unique constraint, 0 rows inserted?
     
         INSERT INTO WORKS (EMPNUM,PNUM)
           VALUES ('e1', 'P2');
1 row inserted/updated/deleted
ij> -- PASS:0451 If 1 row inserted?
     
         UPDATE WORKS
           SET EMPNUM = 'E1'
           WHERE PNUM = 'P5' AND EMPNUM = 'E4';
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 'WORKSC1' defined on 'WORKS'.
ij> -- PASS:0451 If ERROR, unique constraint, 0 rows updated?

         UPDATE WORKS
           SET EMPNUM = 'e1'
           WHERE PNUM = 'P5' AND EMPNUM = 'E4';
1 row inserted/updated/deleted
ij> -- PASS:0451 If 1 row updated?

         UPDATE WORKS
           SET PNUM = 'P4'
           WHERE PNUM = 'P2' AND EMPNUM = 'E4';
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 'WORKSC1' defined on 'WORKS'.
ij> -- PASS:0451 If ERROR, unique constraint, 0 rows updated?

         UPDATE WORKS
           SET PNUM = 'p4'
           WHERE PNUM = 'P2' AND EMPNUM = 'E4';
1 row inserted/updated/deleted
ij> -- PASS:0451 If 1 row updated?

          SELECT * FROM WORKS
             ORDER BY EMPNUM, PNUM;
EM&|PN&|HOURS 
--------------
E1 |P1 |40    
E1 |P2 |20    
E1 |P3 |80    
E1 |P4 |20    
E1 |P5 |12    
E1 |P6 |12    
E1 |p2 |NULL  
E2 |P1 |40    
E2 |P2 |80    
E3 |P2 |20    
E4 |P4 |40    
E4 |p4 |20    
e1 |P2 |NULL  
e1 |P5 |80    
e1 |p2 |NULL  
ij> -- PASS:0451 If 15 rows are selected?
-- PASS:0451 If EMPNUM/PNUM values include ?
-- PASS:0451    e1/p2, E1/p2, e1/P2, e1/P5, E4/p4 ? 
-- PASS:0451 If no EMPNUM/PNUM values are duplicates ?

         ROLLBACK WORK;
ij> -- END TEST >>> 0451 <<< END TEST
-- *********************************************

-- TEST:0452 Order of precedence, left-to-right in UNION [ALL]!

      SELECT EMPNAME FROM STAFF
             UNION
      SELECT EMPNAME FROM STAFF
             UNION ALL 
      SELECT EMPNAME FROM STAFF;
EMPNAME             
--------------------
Alice               
Betty               
Carmen              
Don                 
Ed                  
Alice               
Betty               
Carmen              
Don                 
Ed                  
ij> -- PASS:0452 If 10 rows selected?

      SELECT EMPNAME FROM STAFF
             UNION ALL
      SELECT EMPNAME FROM STAFF
             UNION
      SELECT EMPNAME FROM STAFF;
EMPNAME             
--------------------
Alice               
Betty               
Carmen              
Don                 
Ed                  
ij> -- PASS:0452 If 5 rows selected? 

-- END TEST >>> 0452 <<< END TEST
-- *********************************************

-- TEST:0453 NULL with empty subquery of ALL, SOME, ANY!

   UPDATE PROJ
       SET CITY = NULL WHERE PNAME = 'IRM';
1 row inserted/updated/deleted
ij> --O   SELECT COUNT(*) 
   SELECT CITY 
       FROM PROJ
       WHERE CITY IS NULL;
CITY           
---------------
NULL           
ij> -- PASS:0453 If count = 1?

--O   SELECT COUNT(*)
   SELECT CITY 
       FROM PROJ
       WHERE CITY = ALL (SELECT CITY
                    FROM STAFF
                    WHERE EMPNUM = 'E8');
CITY           
---------------
Deale          
Vienna         
Tampa          
Deale          
NULL           
Deale          
ij> -- PASS:0453 If count = 6?

--O   SELECT COUNT(*)
   SELECT CITY 
       FROM PROJ
       WHERE CITY <> ALL (SELECT CITY
                     FROM STAFF
                     WHERE EMPNUM = 'E8');
CITY           
---------------
Deale          
Vienna         
Tampa          
Deale          
NULL           
Deale          
ij> -- PASS:0453 If count = 6?

--O   SELECT COUNT(*)
   SELECT CITY 
       FROM PROJ
       WHERE CITY = ANY (SELECT CITY
                    FROM STAFF
                    WHERE EMPNUM = 'E8');
CITY           
---------------
ij> -- PASS:0453 If count = 0?

--O   SELECT COUNT(*)
   SELECT CITY 
       FROM PROJ
       WHERE CITY <> ANY (SELECT CITY
                     FROM STAFF
                     WHERE EMPNUM = 'E8');
CITY           
---------------
ij> -- PASS:0453 If count = 0?

--O   SELECT COUNT(*)
   SELECT CITY 
       FROM PROJ
       WHERE CITY = SOME (SELECT CITY
                     FROM STAFF
                     WHERE EMPNUM = 'E8');
CITY           
---------------
ij> -- PASS:0453 If count = 0?

--O   SELECT COUNT(*)
   SELECT CITY 
       FROM PROJ
       WHERE CITY <> SOME (SELECT CITY
                      FROM STAFF
                      WHERE EMPNUM = 'E8');
CITY           
---------------
ij> -- PASS:0453 If count = 0?

   ROLLBACK WORK;
ij> -- END TEST >>> 0453 <<< END TEST

-- *************************************************////END-OF-MODULE
;
ij>