### t/ml_err39.test use test; --disable_warnings DROP TABLE IF EXISTS STAFF,WORKS; --enable_warnings # NOT NULL UNIQUE seems to be essential CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL UNIQUE); CREATE TABLE WORKS (EMPNUM CHAR(3) ); INSERT INTO STAFF VALUES ('E1'); # The next row seems to be not necessary INSERT INTO WORKS VALUES ('E1'); COMMIT ; ##### very simplified version giving the crash ##### let $run= 1; while ($run) { # DELETE or UPDATE(needs more columns) seems to be essential # the join within the subquery seems to be essential # NOT IN or only IN give the same crash DELETE FROM STAFF WHERE STAFF.EMPNUM NOT IN (SELECT WORKS.EMPNUM FROM WORKS WHERE STAFF.EMPNUM = WORKS.EMPNUM); dec $run ; } ##### statements for comparison ##### ## 1. SELECT is NOT critical SELECT * FROM STAFF WHERE STAFF.EMPNUM NOT IN (SELECT WORKS.EMPNUM FROM WORKS WHERE STAFF.EMPNUM = WORKS.EMPNUM); ## 2. the join within the subquery seems to be needed DELETE FROM STAFF WHERE STAFF.EMPNUM NOT IN (SELECT WORKS.EMPNUM FROM WORKS WHERE EMPNUM= 'E1'); ## 3. STAFF.EMPNUM NOT NULL UNIQUE seems to be needed --disable_warnings DROP TABLE IF EXISTS STAFF,WORKS; --enable_warnings CREATE TABLE STAFF (EMPNUM CHAR(3)); CREATE TABLE WORKS (EMPNUM CHAR(3) ); INSERT INTO STAFF VALUES ('E1'); INSERT INTO WORKS VALUES ('E1'); COMMIT ; DELETE FROM STAFF WHERE STAFF.EMPNUM NOT IN (SELECT WORKS.EMPNUM FROM WORKS WHERE STAFF.EMPNUM = WORKS.EMPNUM); #### The original test case (slightly modified NIST test case) # Please activate the test by removing the leading '#' # let $run= 1; while ($run) { --disable_warnings DROP TABLE IF EXISTS STAFF,WORKS; --enable_warnings CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL UNIQUE, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL, PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5), UNIQUE(EMPNUM,PNUM)); INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale'); INSERT INTO WORKS VALUES ('E1','P1',40); COMMIT ; -- TEST:0035 UPDATE with correlated subquery in ! UPDATE STAFF SET GRADE=10*STAFF.GRADE WHERE STAFF.EMPNUM NOT IN (SELECT WORKS.EMPNUM FROM WORKS WHERE STAFF.EMPNUM = WORKS.EMPNUM); dec $run ; }