--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

# Attention: t1.HOURS DECIMAL(5)
#            t2.HOURS BIGINT
CREATE TABLE t1
(EMPNUM   CHAR(3) NOT NULL,
HOURS    DECIMAL(5));
CREATE TABLE t2
(EMPNUM   CHAR(3) NOT NULL,
HOURS    BIGINT);

INSERT INTO t1 VALUES  ('E1',40);
INSERT INTO t2 VALUES  ('E1',40);

#### Both tables do not contain any NULL within HOURS
# All statements are harmless and give the expected result
SELECT EMPNUM FROM   t1 
WHERE HOURS IN (SELECT HOURS FROM t1);
SELECT EMPNUM FROM   t1 
WHERE HOURS IN (SELECT HOURS FROM t2);
SELECT EMPNUM FROM   t2 
WHERE HOURS IN (SELECT HOURS FROM t1);
SELECT EMPNUM FROM   t2 
WHERE HOURS IN (SELECT HOURS FROM t2);

INSERT INTO t2 VALUES('E8',NULL);
#### only t2 contains one record with HOURS(BIGINT) IS NULL
# All statements are harmless and give the expected result
SELECT EMPNUM FROM   t1 
WHERE HOURS IN (SELECT HOURS FROM t1);
SELECT EMPNUM FROM   t1 
WHERE HOURS IN (SELECT HOURS FROM t2);
SELECT EMPNUM FROM   t2 
WHERE HOURS IN (SELECT HOURS FROM t1);
SELECT EMPNUM FROM   t2 
WHERE HOURS IN (SELECT HOURS FROM t2);

INSERT INTO t1 VALUES('E8',NULL);
DELETE FROM t2 WHERE EMPNUM = 'E8';
#### only t1 contains one record with HOURS(DECIMAL(5)) IS NULL
# harmless
SELECT EMPNUM FROM   t2 
WHERE HOURS IN (SELECT HOURS FROM t1);
# harmless
SELECT EMPNUM FROM   t2 
WHERE HOURS IN (SELECT HOURS FROM t2);
###############################################################
# It looks like the next statement is the most interesting one.
# Because it contains all needed requirements for the crash.
# - The outer query contains a record with a DECIMAL column
#   containing NULL
# - The value of that DECIMAL column should be withIN the
#   result of the inner query.
# Not needed properties !!
#   - The result of the inner query contains a record with HOURS IS NULL.
#   - t2.HOURS has the data type DECIMAL
#
# crash
SELECT EMPNUM FROM   t1 
WHERE HOURS IN (SELECT HOURS FROM t2);
###############################################################
# crash
SELECT EMPNUM FROM   t1 
WHERE HOURS IN (SELECT HOURS FROM t1);


# The original slightly modified test case from NIST
#     dml024   tablename WORKS instead of t1

--disable_warnings
  DROP TABLE t1;
--enable_warnings

  CREATE TABLE t1
   (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    HOURS    DECIMAL(5),
    UNIQUE(EMPNUM,PNUM));

      INSERT INTO t1 VALUES  ('E1','P1',40);
      INSERT INTO t1 VALUES  ('E1','P2',20);
      INSERT INTO t1 VALUES  ('E1','P3',80);
      INSERT INTO t1 VALUES  ('E1','P4',20);
      INSERT INTO t1 VALUES  ('E1','P5',12);
      INSERT INTO t1 VALUES  ('E1','P6',12);
      INSERT INTO t1 VALUES  ('E2','P1',40);
      INSERT INTO t1 VALUES  ('E2','P2',80);
      INSERT INTO t1 VALUES  ('E3','P2',20);
      INSERT INTO t1 VALUES  ('E4','P2',20);
      INSERT INTO t1 VALUES  ('E4','P4',40);
      INSERT INTO t1 VALUES  ('E4','P5',80);

     INSERT INTO t1
            VALUES('E8','P8',NULL);
-- PASS:0110 If 1 row is inserted?

     # crash
     SELECT EMPNUM,PNUM
          FROM   t1
          WHERE HOURS  < (SELECT HOURS FROM t1
                          WHERE EMPNUM = 'E8')
          OR NOT(HOURS < (SELECT HOURS FROM t1
                          WHERE EMPNUM = 'E8'));
-- PASS:0110 If 0 rows are selected ?