# 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 <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 <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 <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;