test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/t/subquery_bugs.test

#
# Regression tests for bugs in subquery evaluation that need
# a specific optimizer strategy selection
#

--echo # Bug#26436185 Assertion 'buf_is_inside_another(data_in_mysql_buf, ...)

CREATE TABLE t1 (
  pk int NOT NULL,
  col_int_key int DEFAULT NULL,
  col_int int DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
);

INSERT INTO t1 VALUES (10,7,5,'l'), (12,7,4,'o');

CREATE TABLE t2 (
  col_date_key date DEFAULT NULL,
  col_datetime_key datetime DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  col_varchar varchar(1) DEFAULT NULL,
  col_time time DEFAULT NULL,
  pk int NOT NULL,
  col_date date DEFAULT NULL,
  col_time_key time DEFAULT NULL,
  col_datetime datetime DEFAULT NULL,
  col_int int DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_date_key (col_date_key),
  KEY col_datetime_key (col_datetime_key),
  KEY col_int_key (col_int_key),
  KEY col_varchar_key (col_varchar_key),
  KEY col_time_key (col_time_key)
);

INSERT INTO t2(col_int_key,col_varchar_key,col_varchar,pk,col_int)  VALUES
 (8,'a','w',1,5),
 (9,'y','f',7,0),
 (9,'z','i',11,9),
 (9,'r','s',12,3),
 (7,'n','i',13,6),
 (9,'j','v',17,9),
 (240,'u','k',20,6);

CREATE TABLE t3 (
  col_int int DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  pk int NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
);

INSERT INTO t3 VALUES (8,4,1);

ANALYZE TABLE t1, t2, t3;

let $query=
SELECT table2.col_int_key AS field1
FROM (SELECT sq1_t1.*
      FROM t1 AS sq1_t1 RIGHT OUTER JOIN t2 AS sq1_t2
           ON sq1_t2.col_varchar_key = sq1_t1.col_varchar
     ) AS table1
     LEFT JOIN t1 AS table2
       RIGHT JOIN t2 AS table3
       ON table3.pk = table2.col_int_key
     ON table3.col_int_key = table2.col_int
WHERE table3.col_int_key >= ALL
   (SELECT sq2_t1.col_int AS sq2_field1
    FROM t2 AS sq2_t1 STRAIGHT_JOIN t3 AS sq2_t2
         ON sq2_t2.col_int = sq2_t1.pk AND
            sq2_t1.col_varchar IN
       (SELECT sq21_t1.col_varchar AS sq21_field1
        FROM t2 AS sq21_t1 STRAIGHT_JOIN t1 AS sq21_t2
             ON sq21_t2.col_int_key = sq21_t1.pk
        WHERE sq21_t1.pk = 7
       )
    WHERE sq2_t2.col_int_key >= table2.col_int AND
          sq2_t1.col_int_key <= table2.col_int_key
   );

eval EXPLAIN $query;
eval $query;

DROP TABLE t1, t2, t3;

--echo #
--echo # Bug#24713879 ASSERTION `MAYBE_NULL' FAILED. HANDLE_FATAL_SIGNAL IN TEM_FUNC_CONCAT::VAL_STR
--echo #

CREATE TABLE t1(k VARCHAR(10) PRIMARY KEY);
CREATE TABLE t2(k VARCHAR(10) PRIMARY KEY);

SET SQL_MODE='';

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';

eval EXPLAIN $query;
eval $query;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
       SUM(k)
FROM t1;

eval EXPLAIN $query;
eval $query;

let $query=
SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';

eval EXPLAIN $query;
eval $query;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')
        AND SUM(t1.k)) = 'XXX'
FROM t1;

eval EXPLAIN $query;
eval $query;

SET SQL_MODE=ONLY_FULL_GROUP_BY;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';

eval EXPLAIN $query;
eval $query;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
       SUM(k)
FROM t1;

--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
eval EXPLAIN $query;

let $query=
SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';

--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
eval EXPLAIN $query;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')
        AND SUM(t1.k)) = 'XXX'
FROM t1;

--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
eval EXPLAIN $query;

SET SQL_MODE=STRICT_TRANS_TABLES;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';

eval EXPLAIN $query;
eval $query;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
       SUM(k)
FROM t1;

eval EXPLAIN $query;
eval $query;

let $query=
SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';

eval EXPLAIN $query;
eval $query;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')
        AND SUM(t1.k)) = 'XXX'
FROM t1;

eval EXPLAIN $query;
eval $query;

SET SQL_MODE=DEFAULT;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX'
FROM t1
WHERE k ='X';

eval EXPLAIN $query;
eval $query;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX',
       SUM(k)
FROM t1;

--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
eval EXPLAIN $query;

let $query=
SELECT SUM(k), k
FROM t1
HAVING (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')) = 'XXX';

--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
eval EXPLAIN $query;

let $query=
SELECT (SELECT 'X' FROM t2
        WHERE t2.k = CONCAT(t1.k, 'X')
        AND SUM(t1.k)) = 'XXX'
FROM t1;

--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
eval EXPLAIN $query;

DROP TABLE t1,t2;

--echo #
--echo # Bug#27665085 ASSERTION FAILED: JOIN == 0.
--echo #

SET sql_mode='';
CREATE TABLE a(d INT,e BIGINT, KEY(e));
INSERT a VALUES (0,0);
CREATE TABLE b(f TIME);
INSERT b VALUES (null),(null),(null);
CREATE TABLE c(g DATETIME(6) NOT NULL);
INSERT c(g) VALUES (now()+interval 1 day);
INSERT c(g) VALUES (now()-interval 1 day);

--error ER_SUBQUERY_NO_1_ROW
SELECT 1 FROM a WHERE (SELECT f FROM b WHERE (SELECT 1 FROM c)) <=> e GROUP BY d;

SET sql_mode=default;
DROP TABLES a, b, c;

--echo #
--echo # Bug#27182010 SUBQUERY INCORRECTLY SHOWS DUPLICATE VALUES ON SUBQUERIES
--echo #

CREATE TABLE p (Id INT,PRIMARY KEY (Id));
INSERT INTO p VALUES (1);

--echo # Test UNIQUE KEY with NULL values
CREATE TABLE s (Id INT, u INT, UNIQUE KEY o(Id, u) );
INSERT INTO s VALUES (1, NULL),(1, NULL);

let query1= SELECT p.Id FROM (p) WHERE p.Id IN (
            SELECT s.Id FROM s WHERE Id=1 AND u IS NULL)ORDER BY Id DESC;
let query2= SELECT p.Id FROM (p) WHERE p.Id IN (
            SELECT s.Id FROM s WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC;

eval EXPLAIN $query1;
eval EXPLAIN $query2;

eval $query1;
eval $query2;

--echo # UNIQUE KEY without NULL values
CREATE TABLE s1 (Id INT, u INT, UNIQUE KEY o(Id, u) );
INSERT INTO s1 VALUES (1, 2),(1, 3);

let query3= SELECT p.Id FROM (p) WHERE p.Id IN (
            SELECT s1.Id FROM s1 WHERE Id=1 AND u IS NOT NULL) ORDER BY Id DESC;
let query4= SELECT p.Id FROM (p) WHERE p.Id IN (
            SELECT s1.Id FROM s1 WHERE Id=1 AND u != 1) ORDER BY Id DESC;

eval EXPLAIN $query3;
eval EXPLAIN $query4;

eval $query3;
eval $query4;

--echo # NON UNIQUE KEY Scenario
CREATE TABLE s2 (Id INT, u INT, KEY o(Id, u) );
INSERT INTO s2 VALUES (1, NULL),(1, NULL);

let query5= SELECT p.Id FROM (p) WHERE p.Id IN (
            SELECT s.Id FROM s2 s WHERE Id=1 AND u IS NULL) ORDER BY Id DESC;

--echo #UNIQUE KEY with NON NULL FIELDS
CREATE TABLE s3 (Id INT NOT NULL, u INT NOT NULL, UNIQUE KEY o(Id, u));
INSERT INTO s3 VALUES (1, 2),(1, 3);

let query6= SELECT p.Id FROM (p) WHERE p.Id IN (
            SELECT s.Id FROM s3 s WHERE Id=1 AND u IS NOT NULL)
            ORDER BY Id DESC;

eval EXPLAIN $query5;
eval EXPLAIN $query6;

eval $query5;
eval $query6;

DROP TABLE p, s, s1, s2, s3;
--echo #
--echo # Bug#28239008: WL#9571: SIG11 IN ITEM_FIELD::RESULT_TYPE() AT SQL/ITEM.H
--echo #

CREATE TABLE t1 (f1 varchar(1) DEFAULT NULL);

INSERT INTO t1 VALUES ('5');

CREATE TABLE t2 (f1 varchar(1) DEFAULT NULL);

INSERT INTO t2 VALUES ('Y');

PREPARE prep_stmt FROM "SELECT t2.f1 FROM (t2 LEFT JOIN t1
 ON (1 = ANY (SELECT f1 FROM t1 WHERE 1 IS NULL)))" ;

EXECUTE prep_stmt ;

DROP TABLE t1,t2;

CREATE TABLE t1 (f1 varchar(1) DEFAULT NULL);
INSERT INTO t1 VALUES ('Z') ;
CREATE TABLE t2 (f1 varchar(1) DEFAULT NULL);
INSERT INTO t2 VALUES ('Z') ;

PREPARE prep_stmt FROM "
SELECT t2.f1 FROM t2 LEFT OUTER JOIN
(SELECT  * FROM t2 WHERE ('y',1)
 IN (SELECT alias1.f1 , 0 FROM t1 AS alias1 LEFT JOIN t2 ON 0)) AS alias ON 0";

EXECUTE prep_stmt ;

PREPARE prep_stmt FROM "
SELECT t2.f1 FROM (t2 LEFT OUTER JOIN (SELECT  * FROM t2 WHERE ('y',1)
 IN (SELECT alias1.f1 , 0 FROM
     (t1 INNER JOIN  (t1 AS alias1 LEFT JOIN t2 ON 0) ON 0))) AS alias ON 0)";

EXECUTE prep_stmt ;

DROP TABLE t1,t2;

--echo #
--echo # Bug#28805105: Sig11 in calc_length_and_keyparts
--echo #

CREATE TABLE t1 (cv VARCHAR(1) DEFAULT NULL);

INSERT INTO t1 VALUES ('h'), ('Q'), ('I'), ('q'), ('W');

ANALYZE TABLE t1;

let $query=
SELECT cv
FROM t1
WHERE EXISTS (SELECT alias1.cv AS field1
              FROM t1 AS alias1 RIGHT JOIN t1 AS alias2
                   ON alias1.cv = alias2.cv
             );

eval EXPLAIN $query;
eval $query;

DROP TABLE t1;

--echo # Bug#28970261: Sig6 in decorrelate_equality()

CREATE TABLE t1 (col_varchar_key varchar(1) DEFAULT NULL);

let $query=
SELECT *
FROM t1
WHERE col_varchar_key IN
       (SELECT col_varchar_key
        FROM t1
        WHERE col_varchar_key =
              (SELECT col_varchar_key
               FROM t1
               WHERE col_varchar_key > @var1
              )
       );
eval EXPLAIN $query;
eval $query;

let $query=
SELECT *
FROM t1
WHERE col_varchar_key IN
       (SELECT col_varchar_key
        FROM t1
        WHERE col_varchar_key =
              (SELECT col_varchar_key
               FROM t1
               WHERE col_varchar_key = RAND()
              )
       );
eval EXPLAIN $query;
eval $query;

DROP TABLE t1;

--echo #
--echo # Bug #29193761: WL#12470: SIG 11 IN MARKUNHANDLEDDUPLICATES() AT SQL/SQL_EXECUTOR.CC
--echo #

#
# Tests the case where the iterator executor has to move a weedout
# to the top because it conflicts with an outer join; the common case
# is already tested in subquery_sj.inc, but this tests a confluent weedout,
# which is a special case.
#

CREATE TABLE t1 (
  pk integer NOT NULL PRIMARY KEY,
  f1 varchar(1),
  KEY k1 (f1)
);

CREATE TABLE t2 ( pk integer NOT NULL PRIMARY KEY );
CREATE VIEW v2 AS select * FROM t2;

INSERT INTO t1 VALUES (1, 'G');
INSERT INTO t1 VALUES (2, 'j');
INSERT INTO t1 VALUES (3, 'K');
INSERT INTO t1 VALUES (4, 'v');
INSERT INTO t1 VALUES (5, 'E');

ANALYZE TABLE t1, t2;
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN v2 USING (pk) WHERE f1 >= 'o' );

DROP TABLE t1, t2;
DROP VIEW v2;

--echo #
--echo # Bug #29236241: WL#12470: SIG 6 IN TEMPTABLE::HANDLER::POSITION AT SRC/HANDLER.CC
--echo #

#
# Sets up weedout involving a const table, which is a special case
# (and a nonsensical one; how would it ever return more than one row?).
# Note that we can't even EXPLAIN this query, as it errors out
# with “subquery more than one row” during optimization (but nevertheless,
# it should not crash MySQL).
#
# If it suddenly spits out a plan (and that plan doesn't involve weedout
# of a ConstIterator), probably it changed enough that it's not worth
# keeping the test around.
#

CREATE TABLE t1 (
  f1 varchar(1),
  KEY k1 (f1)
);

INSERT INTO t1 VALUES ('6'),('6');

--error ER_SUBQUERY_NO_1_ROW
EXPLAIN FORMAT=tree SELECT 1 WHERE (
  SELECT 1
  FROM t1 LEFT JOIN t1 AS t2 ON 'f' IN ( SELECT f1 FROM t1 )
  WHERE EXISTS ( SELECT * FROM t1 LEFT JOIN t1 AS t3 ON t3.f1='a' )
);

DROP TABLE t1;

--echo #
--echo # Bug #29231551: WL#12470: SIG 6 QEP_TAB->LAST_INNER() == (-2) CONNECTJOINS()|SQL/SQL_EXECUTOR.CC
--echo #

#
# Another case where the optimizer sets up a partially overlapping semijoin
# and outer join. (If the plan changes to one without weedout as a result of
# optimizer changes, most likely this test should just be deleted.)
#

CREATE TABLE t1 (
  pk integer PRIMARY KEY,
  f1 integer,
  f2 varchar(1)
);

INSERT INTO t1 VALUES (1,100,'x'),(2,200,'y');

CREATE TABLE t2 (
  f2 varchar(1)
);

ANALYZE TABLE t1, t2;

EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t1 AS t3 ON t1.pk = t3.pk AND t1.f2 IN ( SELECT f2 FROM t2 )
WHERE EXISTS ( SELECT * FROM t1 AS t4, t2 AS t5 ) AND t1.f1 = 80 AND t1.pk > t1.f1;

DROP TABLE t1, t2;

--echo #
--echo # Bug#29356132:OPTIMIZED-AWAY SUBQUERY IN PREPARED STATEMENT CAUSES ASSERT FAILURE IN EXECUTION
--echo #

CREATE TABLE t1 (f1 varchar(1));

INSERT INTO t1 VALUES ('5');

CREATE TABLE t2 (f1 varchar(1));

INSERT INTO t2 VALUES ('Y');

PREPARE prep_stmt FROM "SELECT t2.f1 FROM (t2 LEFT JOIN t1
 ON 1 IN (SELECT f1 FROM t1 WHERE FALSE))" ;

EXECUTE prep_stmt ;

DROP TABLE t1,t2;

--echo # Bug#28955216: Assertion 'keyparts > 0' failed

set optimizer_switch='derived_merge=off';

CREATE TABLE t1 (
 pk INTEGER,
 col_int_key INTEGER,
 col_datetime_gckey DATETIME,
 col_time_gckey TIME,
 col_varchar_key VARCHAR(15)
);

CREATE TABLE t2 (
 pk INTEGER,
 col_int_key INTEGER,
 col_varchar_key VARCHAR(15)
);

let $query=
SELECT alias1.col_time_gckey AS field1,
       alias1.col_datetime_gckey AS field2
FROM t1 AS alias1,
     (SELECT DISTINCT sq1_alias2.*
      FROM t1 AS sq1_alias1, t1 AS sq1_alias2
     ) AS alias2,
     (SELECT sq2_alias1.*
      FROM t1 AS sq2_alias1 RIGHT OUTER JOIN
             t1 AS sq2_alias2 INNER JOIN t2 AS sq2_alias3
             ON sq2_alias3.col_int_key = sq2_alias2.col_int_key
           ON sq2_alias3.col_varchar_key = sq2_alias2.col_varchar_key
     ) AS alias3
WHERE alias2.col_int_key = SOME
       (WITH qn AS
        (SELECT sq3_alias1.pk AS sq3_field1
         FROM t1 AS sq3_alias1
         WHERE sq3_alias1.col_int_key = alias3.pk
        )
        SELECT /*+ MERGE(qn) */ * FROM qn
       );

eval EXPLAIN $query;
eval $query;

DROP TABLE t1, t2;

set optimizer_switch=default;

--echo #
--echo # Bug #29493026: INCORRECT RESULT FROM QUERY CONTAINING AN IN-SUBQUERY
--echo #

CREATE TABLE t1 (
  pk INTEGER
);
INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (
  pk INTEGER PRIMARY KEY
);
INSERT INTO t2 VALUES(1);

CREATE TABLE t3 (
  col_int_key INTEGER,
  pk INTEGER
);
INSERT INTO t3 VALUES (31,4),(2,5),(17,3),(5,2),(17,1);

CREATE TABLE t4 (
  col_int_key INTEGER,
  col_int_unique INTEGER,
  UNIQUE KEY ix2 (col_int_key,col_int_unique)
);
INSERT INTO t4 VALUES (6,2),(34,3);

ANALYZE TABLE t1, t2, t3, t4;

set optimizer_switch='firstmatch=off';

# Creates a weedout that overlaps with the right side of an outer join.
# The weedout should then get pushed to the top.
let $query = SELECT * FROM t1 WHERE pk IN (
  SELECT t2.pk FROM
    t4
    RIGHT JOIN t3 ON t4.col_int_key = t3.pk
    RIGHT JOIN t2 ON t3.col_int_key <> t2.pk
);

eval EXPLAIN FORMAT=tree $query;
eval $query;

DROP TABLE t1, t2, t3, t4;

set optimizer_switch=default;

--echo #
--echo # Bug #29693294: ASSERTION FAILURE M_INDEX_CURSOR.IS_POSITIONED() | SRC/HANDLER.CC
--echo #

# Sorts inside weedouts need to be by row ID (see JOIN::add_sorting_to_table()).
# The EXPLAIN demonstrates it, and running the query verifies that it doesn't crash.
CREATE TABLE t1 ( pk integer, f1 varchar(1) );
INSERT INTO t1 VALUES (1,'D'), (20,'G');
ANALYZE TABLE t1;
EXPLAIN FORMAT=TREE SELECT * FROM ( SELECT DISTINCT * FROM t1 ) AS d0 LEFT JOIN t1 ON d0.pk IN ( SELECT 1 FROM t1 ) ORDER BY d0.f1;
SELECT * FROM ( SELECT DISTINCT * FROM t1 ) AS d0 LEFT JOIN t1 ON d0.pk IN ( SELECT 1 FROM t1 ) ORDER BY d0.f1;
DROP TABLE t1;

--echo #
--echo # Bug#29664504 REGRESSION: CRASHING FROM ITEM_FUNC_AS_WKT::VAL_STR_ASCII()
--echo #

CREATE TABLE t1(a TINYBLOB);
INSERT INTO t1 VALUES('aaa'),('bbb'),(''),('ccc');

--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT DISTINCT * FROM t1
ORDER BY UUID_TO_BIN(
  ST_ISEMPTY(
    ST_POINTFROMTEXT(
      ST_ASWKT(
       NOT EXISTS(
         SELECT 1 FROM t1
         WHERE IS_UUID(CAST(SHA(a>>0xA7FE1B22)AS JSON)) WINDOW w1 AS()
       ),'AXIS-ORDER=SRID-DEFINED'
      ),1,'AXIS-ORDER=LONG-LAT'
    )
 )
);

DROP TABLE t1;

--echo #
--echo # Bug#29669840 REGRESSION: CRASH IN DECIMAL_ADD()
--echo #

CREATE TABLE t1(a DATETIME(2));
INSERT INTO t1 VALUES(NOW(2)),(NOW(2));
--error ER_USER_LOCK_WRONG_NAME
SELECT STD(IS_FREE_LOCK(0x2ADA5C38)),1 FROM t1 WHERE a+(EXISTS(SELECT 1));
DROP TABLE t1;

--echo #
--echo # Bug#29668446 REGRESSION: CRASH IN DO_DIV_MOD()
--echo #

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1),(2),(3);
--error ER_SUBQUERY_NO_1_ROW
SELECT 1 FROM t1 WHERE
(EXISTS(SELECT 1 FROM t1 WHERE (SELECT 1 FROM t1 WINDOW w1 AS())))/1;
DROP TABLE t1;

--echo # Bug#29525304 Sig 6 at Item_in_subselect::val_int()

CREATE TABLE t1 (vc varchar(1) NOT NULL);

CREATE VIEW v1 AS SELECT * FROM t1 WHERE 5 IN (SELECT 1) IS UNKNOWN;

DROP VIEW v1;
DROP TABLE t1;

--echo #
--echo # Bug #29889223: SIG 6 AT TEMPTABLE::HANDLER::UPDATE_ROW | SRC/HANDLER.CC
--echo #

set optimizer_switch='block_nested_loop=off,batched_key_access=off';

CREATE TABLE t1 (
  field1 integer
);
INSERT INTO t1 VALUES (13);

CREATE TABLE t2 (
  field2 integer
);
INSERT INTO t2 VALUES (18);

CREATE TABLE t3 (
  field3 integer
);
INSERT INTO t3 VALUES (1);

# Set up a query where we have weedout of a streamed materialization,
# which verifies that we have working row IDs on the temporary table
# used for streaming.
#
# We don't care about the result of the output; it should just not crash.
UPDATE t3 SET field3 = 9 WHERE field3 IN (
  SELECT 1
  FROM ( SELECT * FROM t2 ) AS alias1
  WHERE EXISTS ( SELECT * FROM t1 WHERE field1 <> alias1.field2 )
);

DROP TABLE t1, t2, t3;
set optimizer_switch=default;

--echo #
--echo # Bug #29836364: WL#13000: SIG 11 AT COST_SKIP_SCAN() | SQL/OPT_RANGE.CC
--echo #

CREATE TABLE t1 (
  f1 integer NOT NULL PRIMARY KEY,
  f2 varchar(1),
  KEY f2_idx (f2)
);

INSERT INTO t1 VALUES (20,'2');

ANALYZE TABLE t1;

# Essentially verifies that running the range optimizer on a three-table
# JOIN during execution (“re-planned for each iteration”) doesn't crash.
# If it does, it's typically because the range optimizer doesn't know which
# join it is working over.
let $query = SELECT (
        SELECT t2.f2 FROM t1 JOIN (
                t1 AS t2 LEFT JOIN t1 AS t3 USING (f2)
        ) ON t3.f2 = t2.f2
        WHERE t2.f1 > table1.f2
) FROM t1 AS table1;

eval explain format=tree $query;
eval $query;

DROP TABLE t1;