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

ij> --
--   Licensed to the Apache Software Foundation (ASF) under one or more
--   contributor license agreements.  See the NOTICE file distributed with
--   this work for additional information regarding copyright ownership.
--   The ASF licenses this file to You under the Apache License, Version 2.0
--   (the "License"); you may not use this file except in compliance with
--   the License.  You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
--   Unless required by applicable law or agreed to in writing, software
--   distributed under the License is distributed on an "AS IS" BASIS,
--   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--   See the License for the specific language governing permissions and
--   limitations under the License.
--
AUTOCOMMIT OFF;
ij> -- MODULE CDR027

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

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

-- date_time print


-- TEST:0446 Table CHECK constraint allows unknown (NULL)!

   DELETE FROM STAFF5;
1 row inserted/updated/deleted
ij> INSERT INTO STAFF5 VALUES('E7','Mimi',NULL,'Miami');
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row inserted?

   INSERT INTO STAFF5 VALUES('E8','Joe',NULL,'Boston');
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row inserted?

   INSERT INTO STAFF5(EMPNUM) VALUES('E9');
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row inserted?

--O   UPDATE STAFF
   UPDATE HU.STAFF
       SET GRADE = NULL
       WHERE EMPNUM = 'E1';
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row updated?

   INSERT INTO STAFF5 
        SELECT *
--O         FROM STAFF;
         FROM HU.STAFF;
5 rows inserted/updated/deleted
ij> -- PASS:0446 If 5 rows inserted?

   UPDATE STAFF5 
       SET GRADE = NULL
       WHERE EMPNUM = 'E2';
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row updated?

   UPDATE STAFF5
       SET GRADE = NULL 
       WHERE EMPNUM = 'E4';
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row updated?

   SELECT COUNT(*)
       FROM STAFF5;
1          
-----------
8          
ij> -- PASS:0446 If count = 8?

   SELECT COUNT(*)
       FROM STAFF5
       WHERE GRADE IS NULL;
1          
-----------
6          
ij> -- PASS:0446 If count = 6?

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

-- TEST:0447 NULLs with check constraint and check option!

   DELETE FROM STAFF6;
1 row inserted/updated/deleted
ij> --O   INSERT INTO STAFF6_WITH_GRADES
--O       VALUES('X1','Vicki',NULL,'Houston');
-- PASS:0447 If ERROR, view check constraint, 0 rows inserted?

   INSERT INTO STAFF6
       VALUES('X2','Tina',NULL,'Orlando');
1 row inserted/updated/deleted
ij> -- PASS:0447 If 1 row inserted?

--O   SELECT COUNT(*)
--O       FROM STAFF6_WITH_GRADES;
-- PASS:0447 If count = 0?

   SELECT COUNT(*)
      FROM STAFF6;
1          
-----------
1          
ij> -- PASS:0447 If count = 1?

   SELECT EMPNAME
       FROM STAFF6
       WHERE GRADE IS NULL;
EMPNAME             
--------------------
Tina                
ij> -- PASS:0447 If EMPNAME is Tina?

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

-- TEST:0448 PRIMARY KEY implies UNIQUE!

   DELETE FROM STAFF9;
1 row inserted/updated/deleted
ij> INSERT INTO STAFF9(EMPNUM,EMPNAME)
       VALUES('D1','Muddley');
1 row inserted/updated/deleted
ij> -- PASS:0448 If 1 row inserted?


   INSERT INTO STAFF9(EMPNUM,EMPNAME)
       VALUES('D1','Muddley');
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 'STAFF9_PK' defined on 'STAFF9'.
ij> -- PASS:0448 If ERROR, unique constraint, 0 rows inserted?

   INSERT INTO STAFF9(EMPNUM,EMPNAME)
       VALUES('d1','Muddley');
1 row inserted/updated/deleted
ij> -- PASS:0448 If 1 row inserted?

   SELECT COUNT(*)
       FROM STAFF9;
1          
-----------
2          
ij> -- PASS:0448 If count = 2?

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

-- TEST:0449 Constraint definition is case sensitive!

   DELETE FROM STAFF9;
1 row inserted/updated/deleted
ij> INSERT INTO STAFF9(EMPNUM,EMPNAME)
       VALUES('Z1','Tina');
ERROR 23513: The check constraint 'STAFF9_EMPNAME' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF9"'.
ij> -- PASS:0449 If ERROR, check constraint, 0 rows inserted?

   INSERT INTO STAFF9(EMPNUM,EMPNAME)
       VALUES('Z2','tina');
1 row inserted/updated/deleted
ij> -- PASS:0449 If 1 row inserted?

   INSERT INTO STAFF9(EMPNUM,EMPNAME)
       VALUES('Z3','ANTHONY');
1 row inserted/updated/deleted
ij> -- PASS:0449 If 1 row inserted?

   SELECT COUNT(*)
        FROM STAFF9;
1          
-----------
2          
ij> -- PASS:0449 If count = 2?

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

-- TEST:0450 Referential integrity is case sensitive!

   INSERT INTO DEPT
       VALUES(11,'VOLLEYBALL','VICKI');
1 row inserted/updated/deleted
ij> -- PASS:0450 If 1 row inserted?

   INSERT INTO DEPT 
       VALUES(10,'volleyball','vicki');
1 row inserted/updated/deleted
ij> -- PASS:0450 If 1 row inserted?
-- NOTE:0450 insert lower case value of above.

   SELECT COUNT(*) FROM DEPT WHERE DNO = 10;
1          
-----------
1          
ij> -- PASS:0450 If count = 1?

--O   INSERT INTO EMP
--O       VALUES(13,'MARY','Dancer',15,'VOLLEYBALL',010101);
-- PASS:0450 If 1 row inserted?

   DELETE FROM DEPT
       WHERE DNO = 10;
1 row inserted/updated/deleted
ij> -- PASS:0450 If 1 row deleted?

--O   UPDATE DEPT
--O       SET DNAME = 'EDUCATION'
--O       WHERE DNAME = 'Education';
-- PASS:0450 If RI ERROR, children exist, 0 rows updated?

--O   SELECT * FROM DEPT ORDER BY DNO;
-- PASS:0450 If 5 rows selected?
-- PASS:0450 If DNO values are 11, 12, 13, 14, 15?
-- PASS:0450 If DNAME = 'Education' (not 'EDUCATION') for DNO = 14?

--O   INSERT INTO EMP
--O       VALUES(28,'BARBARA','Jogger',14,'EDUCATION',010101);
-- PASS:0450 If RI ERROR, parent missing, 0 rows inserted?

--O   UPDATE EMP
--O       SET DNAME = 'PHYSICS'
--O       WHERE ENO = 25;
-- PASS:0450 If RI ERROR, parent missing, 0 rows updated?

--O   SELECT ENO, ENAME, DNO, DNAME 
--O       FROM EMP ORDER BY ENO;
-- PASS:0450 If 8 rows selected?
-- PASS:0450 If ENO values are 13, 21 through 27?
-- PASS:0450 If DNAME = 'Physics' (not 'PHYSICS') for ENO = 25?

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

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