# Tests for # Bug#45191 Nested subqueries, wrong result - duplicate rows # derived from NIST tests dml050 # # Some notes (mleich): # 1. The original test uses unique indexes on all three tables # (see the "CREATE UNIQUE INDEX ..." statements set to comment) # # 2. Variants of this test which might be of interest # a) The test just as it is (MyISAM, no UNIQUE INDEXes) # b) One till all UNIQUE INDEXes enabled, MyISAM # c) One till all UNIQUE INDEXes enabled, one till all tables InnoDB, # other tables MyISAM # # The goal of this test is to check if execution strategies decided the # optimizer lead to a correct result set. The maximum coverage could be # reached if we "encourage" the optimizer to generate as much different # strategies as possible. This could be done many different combinations # existing/not existing physical indexes and clustered primary keys # (only InnoDB has this feature). # # It is rather unlikely that we have a bug within storage engine code which # leads to wrong result sets. At least we have a lot of other tests with # much simpler SELECTs which would be most probably able to reveal such # problems. Therefore storage engines should be outside of the focus of # this test. # # --source include/have_innodb.inc # let $engine= InnoDB; let $engine= MyISAM; # I was told that these settings might cause that problems disappear # but they did not help. SET GLOBAL OPTIMIZER_SWITCH = 'materialization=off,semijoin=off'; SET GLOBAL optimizer_use_mrr = 'disable'; --disable_warnings DROP SCHEMA IF EXISTS mysql_test; --disable_abort_on_error CREATE SCHEMA mysql_test; USE mysql_test; # The (slightly different) table definitions were in schema/schema1.mysql . --replace_result $engine eval CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)) ENGINE = $engine; # CREATE UNIQUE INDEX staff_uidx ON STAFF (EMPNUM); --replace_result $engine eval CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL, PNAME CHAR(20), PTYPE CHAR(6), BUDGET DECIMAL(9), CITY CHAR(15)) ENGINE = $engine; # CREATE UNIQUE INDEX proj_uidx ON PROJ (PNUM); --replace_result $engine eval CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL, PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5)) ENGINE = $engine; # CREATE UNIQUE INDEX works_uidx ON WORKS (EMPNUM,PNUM); # The statements for filling the tables are in sql/basetab.mysql . INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale'); INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna'); INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna'); INSERT INTO STAFF VALUES ('E4','Don',12,'Deale'); INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron'); INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale'); INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna'); INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa'); INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale'); INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna'); INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale'); INSERT INTO WORKS VALUES ('E1','P1',40); INSERT INTO WORKS VALUES ('E1','P2',20); INSERT INTO WORKS VALUES ('E1','P3',80); INSERT INTO WORKS VALUES ('E1','P4',20); INSERT INTO WORKS VALUES ('E1','P5',12); INSERT INTO WORKS VALUES ('E1','P6',12); INSERT INTO WORKS VALUES ('E2','P1',40); INSERT INTO WORKS VALUES ('E2','P2',80); INSERT INTO WORKS VALUES ('E3','P2',20); INSERT INTO WORKS VALUES ('E4','P2',20); INSERT INTO WORKS VALUES ('E4','P4',40); INSERT INTO WORKS VALUES ('E4','P5',80); COMMIT WORK; SELECT COUNT(*) FROM PROJ; # -- PASS:Setup if count = 6? SELECT COUNT(*) FROM STAFF; # -- PASS:Setup if count = 5? SELECT COUNT(*) FROM WORKS; # -- PASS:Setup if count = 12? # Bug#45191 Nested subqueries, wrong result - duplicate rows --echo This is the fat SELECT with nested SUBQUERIES from NIST --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE PNUM IN (SELECT PNUM FROM WORKS WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE CITY IN (SELECT CITY FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE HOURS = 20 AND PNUM = 'P2' ))))))))); --echo -- PASS:0226 If 4 rows selected excluding EMPNUM='E5', EMPNAME='Ed'? --echo --echo Variation 1 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE PNUM IN (SELECT PNUM FROM WORKS WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE CITY IN (SELECT CITY FROM STAFF)))))))); --echo --echo Variation 2 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE PNUM IN (SELECT PNUM FROM WORKS WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ))))))); --echo --echo Variation 3 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE PNUM IN (SELECT PNUM FROM WORKS WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ)))))); # The following variations do not give wrong results. # Therefore I disable them. if (0) { --echo The variants >= 4 get correct results --echo Variation 4 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE PNUM IN (SELECT PNUM FROM WORKS WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS))))); --echo --echo Variation 5 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE PNUM IN (SELECT PNUM FROM WORKS)))); --echo --echo Variation 6 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ))); --echo --echo Variation 7 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ)); --echo --echo Variation 8 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS); --echo --echo Variation 9 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE PNUM IN (SELECT PNUM FROM PROJ WHERE PTYPE IN (SELECT PTYPE FROM PROJ WHERE PNUM IN (SELECT PNUM FROM WORKS WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE HOURS = 20 AND PNUM = 'P2' ))))); --echo --echo Variation 10 --sorted_result SELECT EMPNUM, EMPNAME FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS WHERE HOURS = 20 AND PNUM = 'P2' ); } --echo --echo Cleanup DROP SCHEMA mysql_test;