# WL#4245 Antijoin for NOT IN and NOT EXISTS

CREATE TABLE t1(
 pk INTEGER PRIMARY KEY,
 uk INTEGER UNIQUE,
 ukn INTEGER UNIQUE NOT NULL,
 ik INTEGER,
 d INTEGER,
 INDEX ik(ik));

INSERT INTO t1 VALUES
(0, NULL, 0, NULL, NULL),
(1, 10, 20, 30, 40),
(2, 20, 40, 60, 80);

CREATE TABLE t2(
 pk INTEGER PRIMARY KEY);

INSERT INTO t2 VALUES
 (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),
(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),
(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),
(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),
(71),(72),(73),(74),(75),(76),(77),(78),(79),(80);

ANALYZE TABLE t1, t2;

let $c=2;

while ($c)
{
if ($c == 2)
{
set optimizer_switch="firstmatch=on,materialization=off";
}
if ($c == 1)
{
set optimizer_switch="firstmatch=off,materialization=on";
}

select @@optimizer_switch;

let $query=
SELECT 1 as a FROM dual
WHERE NOT EXISTS (SELECT * FROM t1 AS it);
eval EXPLAIN $query;
eval $query;

let $query=
SELECT 1 as a FROM t2 as ot
WHERE NOT EXISTS (SELECT * FROM t1 AS it);
eval EXPLAIN $query;
eval $query;

# Correlated:

let $query=
SELECT pk FROM t2 as ot
WHERE NOT EXISTS (SELECT * FROM t1 AS it WHERE it.uk=ot.pk)
AND ot.pk<25; # to reduce output
eval EXPLAIN $query;
# Looking at JSON format to make sure the materialized tmp table has
# the IS NULL condition.
# Cost value change a bit with different InnoDB page sizes, hide them
--replace_regex /(cost.*)/cost: #/
eval EXPLAIN FORMAT=JSON $query;
eval $query;

# In LEFT JOIN ... ON
let $query=
SELECT ot.pk, ot2.pk FROM t2 as ot
LEFT JOIN t2 as ot2
ON NOT EXISTS (SELECT * FROM t1 AS it WHERE it.uk=ot.pk+ot2.pk);
#AND ot.pk<25; # to reduce output
#eval EXPLAIN $query;
#eval $query;

dec $c;
}

DROP TABLE t1,t2;

# Verify that if SJ-mat is on right side of LEFT JOIN,
# subquery's WHERE is attached to SJ-inner tables for execution
# (t3.a>2, here).

CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES(1,1),(2,2),(3,3);
ANALYZE TABLE t1;
let $query=
SELECT * FROM t1 LEFT JOIN t1 t2
ON (t1.a IN
(SELECT /*+ NO_SEMIJOIN(FIRSTMATCH,LOOSESCAN,DUPSWEEDOUT) */ a FROM
t1 t3 WHERE a > 2));
eval EXPLAIN FORMAT=JSON $query;
eval $query;

# Verify that AJ nest isn't in SJ-or-AJ nest: the middle-level
# subquery is handled as a subquery.

ALTER TABLE t1 MODIFY a INT NOT NULL;
EXPLAIN SELECT * FROM t1 WHERE t1.a NOT IN
(SELECT a FROM t1 t2 WHERE a NOT IN (SELECT a FROM t1 t3));
EXPLAIN SELECT * FROM t1 WHERE t1.a IN
(SELECT a FROM t1 t2 WHERE a NOT IN (SELECT a FROM t1 t3));

# Verify that if the subquery's WHERE depends on an outer table (t1
# here), AJ inner tables don't go before t1 in the plan.

EXPLAIN SELECT * FROM t1 JOIN t1 t2 ON t1.a>t2.a
 AND NOT EXISTS(SELECT * FROM t1 t3, t1 t4 WHERE t3.a=t1.a);

EXPLAIN SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t3.a FROM t1 t3);
EXPLAIN SELECT * FROM t1 WHERE t1.a <> ALL (SELECT t3.a FROM t1 t3);

# Verify that wrapping in IS TRUE/etc doesn't prevent semijoin

ALTER TABLE t1 MODIFY a INT NULL;

# EXISTS

EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t1 t3);
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t1 t3);

EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t1 t3) IS TRUE;
EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t1 t3) IS FALSE;
# note that EXISTS() cannot return UNKNOWN
EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t1 t3) IS UNKNOWN;

EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t1 t3) IS NOT TRUE;
EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t1 t3) IS NOT FALSE;
EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t1 t3) IS NOT UNKNOWN;

# IN

EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT t3.a FROM t1 t3);
# no AJ, due to NULLs
EXPLAIN SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t3.a FROM t1 t3);

# just as a side-check: equivalent of above with EXISTS, allows AJ:
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS (SELECT t3.a FROM t1 t3 WHERE t3.a=t1.a);

EXPLAIN SELECT * FROM t1 WHERE (t1.a IN (SELECT t3.a FROM t1 t3)) IS TRUE;
EXPLAIN SELECT * FROM t1 WHERE (t1.a IN (SELECT t3.a FROM t1 t3)) IS FALSE;
EXPLAIN SELECT * FROM t1 WHERE (t1.a IN (SELECT t3.a FROM t1 t3)) IS UNKNOWN;
# ISNULL() function has a special code branch:
EXPLAIN SELECT * FROM t1 WHERE ISNULL(t1.a IN (SELECT t3.a FROM t1 t3));
SELECT * FROM t1 WHERE ISNULL(t1.a IN (SELECT t3.a FROM t1 t3));

EXPLAIN SELECT * FROM t1 WHERE (t1.a IN (SELECT t3.a FROM t1 t3)) IS NOT TRUE;
EXPLAIN SELECT * FROM t1 WHERE (t1.a IN (SELECT t3.a FROM t1 t3)) IS NOT FALSE;
EXPLAIN SELECT * FROM t1 WHERE (t1.a IN (SELECT t3.a FROM t1 t3)) IS NOT UNKNOWN;

# NOT IN

EXPLAIN SELECT * FROM t1 WHERE (NOT t1.a IN (SELECT t3.a FROM t1 t3)) IS TRUE;
EXPLAIN SELECT * FROM t1 WHERE (NOT t1.a IN (SELECT t3.a FROM t1 t3)) IS FALSE;
EXPLAIN SELECT * FROM t1 WHERE (NOT t1.a IN (SELECT t3.a FROM t1 t3)) IS UNKNOWN;
EXPLAIN SELECT * FROM t1 WHERE (NOT t1.a IN (SELECT t3.a FROM t1 t3)) IS NOT TRUE;
EXPLAIN SELECT * FROM t1 WHERE (NOT t1.a IN (SELECT t3.a FROM t1 t3)) IS NOT FALSE;
EXPLAIN SELECT * FROM t1 WHERE (NOT t1.a IN (SELECT t3.a FROM t1 t3)) IS NOT UNKNOWN;

# Demonstrate simplifications like
# (NOT IN) IS FALSE <=> IN IS TRUE.

EXPLAIN SELECT * FROM t1 WHERE (NOT t1.a IN (SELECT /*+ NO_SEMIJOIN() */ t3.a FROM t1 t3)) IS FALSE;

# Verify that NOT (IN IS TRUE) is not understood as (NOT IN) IS TRUE

EXPLAIN SELECT * FROM t1 WHERE NOT ((t1.a IN (SELECT /*+ NO_SEMIJOIN() */ t3.a FROM t1 t3)) IS TRUE);

DROP TABLE t1;

--echo #
--echo # Bug#29387313 WL#4245: SIG11 AT QEP_SHARED_OWNER::LAST_INNER() AT SQL/SQL_OPT_EXEC_SHARED.H
--echo #

CREATE TABLE t1 (
  pk int NOT NULL,
  col_varchar_key varchar(1),
  PRIMARY KEY (pk),
  KEY idx_cc_col_varchar_key (col_varchar_key)
);
INSERT INTO t1 VALUES (2,"a");
CREATE TABLE t2 LIKE t1;

let $query=
SELECT col_varchar_key FROM t1
WHERE NOT EXISTS
   (SELECT /*+ NO_SEMIJOIN(FIRSTMATCH) */
           subquery2_t1.col_varchar_key AS subquery2_field1
    FROM t2 AS subquery2_t1 LEFT JOIN 
           t1 AS subquery2_t2 INNER JOIN t1 AS subquery2_t3
           ON TRUE
         ON TRUE
    WHERE subquery2_t2.col_varchar_key <> subquery2_t1. col_varchar_key OR
          subquery2_t1.col_varchar_key >= '2'
   ) AND
     t1.pk IN (2);

eval EXPLAIN $query;
eval $query;
DROP TABLE t1, t2;

--echo #
--echo # Bug#29402512 WL#4245: SIG6 AT JOIN::MAKE_OUTERJOIN_INFO | SQL_OPTIMIZER.CC
--echo #

CREATE TABLE t1 (
  pk int NOT NULL AUTO_INCREMENT,
  col_int_key int,
  PRIMARY KEY (pk),
  KEY idx_cc_col_int_key (col_int_key));

INSERT INTO t1 VALUES(1,1);
INSERT INTO t1 (col_int_key) SELECT col_int_key*2 from t1;
INSERT INTO t1 (col_int_key) SELECT col_int_key*2 from t1;
INSERT INTO t1 (col_int_key) SELECT col_int_key*2 from t1;
INSERT INTO t1 (col_int_key) SELECT col_int_key*2 from t1;
INSERT INTO t1 (col_int_key) SELECT col_int_key*2 from t1;
INSERT INTO t1 (col_int_key) SELECT col_int_key*2 from t1;
ANALYZE TABLE t1;

EXPLAIN SELECT t1.col_int_key
FROM t1 AS t1 LEFT JOIN t1 AS t2 ON t1.pk NOT IN (SELECT 1 FROM t1 AS subq);
DROP TABLE t1;

--echo #
--echo # Bug#29499458 WL#4245: SIG 6 AT JOIN::SETUP_SEMIJOIN_MATERIALIZED_TABLE
--echo #

CREATE TABLE t1 (
  pk INT NOT NULL,
  col_int INT NOT NULL,
  PRIMARY KEY (pk)
);

let $query=
SELECT alias1.pk
FROM t1 AS alias1 LEFT JOIN
     (SELECT alias2.*
      FROM t1 LEFT JOIN t1 AS alias2 ON TRUE
      WHERE NOT EXISTS (SELECT pk FROM t1 AS sj1)
     ) AS alias3
     ON alias3.pk = alias1.col_int AND
        NOT EXISTS (SELECT * FROM t1 AS sj2 WHERE (SELECT 1) IS NULL);

eval EXPLAIN $query;
eval $query;

DROP TABLE t1;

--echo #
--echo # Bug#29503228 WL#4245: SIG 6 AT JOIN::SET_SEMIJOIN_EMBEDDING
--echo #

CREATE TABLE t1 (
  col_int INT NOT NULL,
  col_int2 INT NOT NULL
);

SELECT * FROM
t1 AS alias1 LEFT JOIN t1 AS alias2
ON NOT EXISTS
   ( SELECT *
     FROM
     ( SELECT * FROM t1
       WHERE col_int NOT IN
                     ( SELECT sq1_alias1 . col_int2
                       FROM t1 AS sq1_alias1 )
     ) AS alias3
   );

DROP TABLE t1;

--echo #
--echo # Test index_subquery and subquery-materialization with
--echo # surrounding NOT or IS TRUE/FALSE.
--echo #

CREATE TABLE t1 (
  col_int INT,
  col_int2 INT,
  key(col_int)
);
INSERT INTO t1 VALUES(1,1),(2,2),(null,null);
ANALYZE TABLE t1;
SET OPTIMIZER_SWITCH="semijoin=off";

let $c=2;

while($c)
{
if ($c==2)
{
  SET OPTIMIZER_SWITCH="materialization=off";
}
if ($c==1)
{
  SET OPTIMIZER_SWITCH="materialization=on,subquery_materialization_cost_based=off";
}
dec $c;

let $query=
SELECT * FROM t1 WHERE
t1.col_int+1 IN (SELECT col_int FROM t1 t2);
eval EXPLAIN $query;
eval $query;

let $query=
SELECT * FROM t1 WHERE
t1.col_int+1 IN (SELECT col_int FROM t1 t2) IS TRUE;
eval EXPLAIN $query;
eval $query;

let $query=
SELECT * FROM t1 WHERE
t1.col_int+1 IN (SELECT col_int FROM t1 t2) IS FALSE;
eval EXPLAIN $query;
eval $query;

let $query=
SELECT * FROM t1 WHERE
t1.col_int+1 NOT IN (SELECT col_int FROM t1 t2);
eval EXPLAIN $query;
eval $query;

let $query=
SELECT * FROM t1 WHERE
t1.col_int+1 IN (SELECT col_int FROM t1 t2) IS UNKNOWN;
eval EXPLAIN $query;
eval $query;

}

SET OPTIMIZER_SWITCH=DEFAULT;
DROP TABLE t1;

--echo #
--echo # Bug#29525549 WL#4245: SIG 6 AT JOIN::CREATE_ITERATORS() | SQL/SQL_EXECUTOR.CC
--echo #

CREATE TABLE t1 (
  pk INT NOT NULL,
  col_int INT NOT NULL,
  PRIMARY KEY (pk)
) ;
ANALYZE TABLE t1;

# Turn BNL off or iterator executor won't be used. Force
# materialization to repeat bug.
SET OPTIMIZER_SWITCH="block_nested_loop=off,firstmatch=off";

let $query=
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON 1 AND t1.col_int > ( SELECT @var FROM t1 as t3 ) AND
NOT EXISTS ( SELECT * FROM t1 as t4);
eval EXPLAIN $query;
# Show that the iterator executor is used
eval EXPLAIN FORMAT=TREE $query;
eval $query;

# Also crashed without any antijoin

SELECT * FROM t1 LEFT JOIN (t1 AS t2 LEFT JOIN t1 AS sq ON 1=1)
ON 1 AND t1.col_int > ( SELECT @var FROM t1 AS t4 )
WHERE sq.pk IS NULL;

SET OPTIMIZER_SWITCH=DEFAULT;

DROP TABLE t1;

--echo #
--echo # Bug#29533644 WL#4245: RESULT DIFFS WITH NOT IN (SUBQUERY)
--echo #

CREATE TABLE t1(id INT);

INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
ANALYZE TABLE t1;

let $query=
SELECT ID FROM t1 WHERE id IN ( SELECT 1 );
eval EXPLAIN $query;
eval $query;

let $query=
SELECT ID FROM t1 WHERE id NOT IN ( SELECT 1 );
eval EXPLAIN $query;
eval $query;

let $query=
SELECT id, id IN (SELECT NULL) IS TRUE AS test FROM t1;
eval EXPLAIN $query;
eval $query;

DROP TABLE t1;

--echo #
--echo # Bug#29540222 WL#4245 RESULT DIFFS WITH DUAL
--echo #

CREATE TABLE t1 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int int(11) NOT NULL,
  col_int_key int(11) NOT NULL,
  col_date date NOT NULL,
  col_date_key date NOT NULL,
  col_time time NOT NULL,
  col_time_key time NOT NULL,
  col_datetime datetime NOT NULL,
  col_datetime_key datetime NOT NULL,
  col_varchar varchar(1) NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY idx_C_col_int_key (col_int_key),
  KEY idx_C_col_date_key (col_date_key),
  KEY idx_C_col_time_key (col_time_key),
  KEY idx_C_col_datetime_key (col_datetime_key),
  KEY idx_C_col_varchar_key (col_varchar_key)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO t1 VALUES (1,1065483706,383929458,'1979-10-05','1976-08-21','22:00:27','23:05:58','1974-07-25 16:05:18','2015-12-02 10:01:50','k','9'),(2,-171723561,486531981,'1980-09-14','2027-01-15','00:57:31','00:12:08','2036-10-28 14:25:40','2010-09-04 08:25:26','M','Z');

# WHERE is false, NOT EXISTS is TRUE.

let $query=
SELECT alias1.col_int AS field1 , alias2.col_varchar_key AS field2 ,
       alias1.col_datetime_key AS field3
FROM t1 AS alias1 LEFT JOIN t1 AS alias2
  ON NOT EXISTS ( SELECT * FROM t1 AS alias3 WHERE ( SELECT 1 FROM DUAL ) IS NULL );

eval EXPLAIN $query;
eval $query;

DROP TABLE t1;

--echo #
--echo # Bug#29759277 ASSERTION FAILED: !"NO DERIVED TABLE FOUND!"
--echo #

CREATE TABLE t1 (c1 INT, c2 INT);
EXPLAIN FORMAT=JSON
SELECT c1 FROM t1
 WHERE NOT EXISTS (SELECT /*+ NO_MERGE() */ c2
                   FROM (SELECT c1 FROM t1) AS dt
                   WHERE FALSE);
DROP TABLE t1;