mariadb/mysql-test/main/opt_hints_subquery_innodb.result
Sergei Petrunia b89a1e7f35 MDEV-36169: Two subqueries with LOOSESCAN hints create invalid query plan
LooseScan strategy doesn't support join orders where tables from two
subqueries interleave. There is a check in LooseScan_picker::check_qep()
to prevent use of LooseScan for such join orders.

However for two subqueries with one table each a join order of
  subquery1_table
  subquery2_table
  outer_table
was not rejected (although the POSITION object for the last table in the
join order clearly had pos->dups_producing_tables!=0).

Modified the no-interleaving check to catch more cases.
2025-05-05 12:02:47 +07:00

22 lines
1.2 KiB
Text

#
# MDEV-36169: LooseScan optimization picks invalid plan
#
CREATE TABLE t1 (a INTEGER NOT NULL, b INT, UNIQUE KEY (a)) engine=InnoDB;
CREATE TABLE t2 (a INTEGER NOT NULL, KEY (a));
CREATE TABLE t3 (a INTEGER NOT NULL, b INT, KEY (a));
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
INSERT INTO t2 VALUES (2), (2), (3), (3), (4), (5);
INSERT INTO t3 VALUES (10,3), (15,3), (20,4), (30,5);
# Must not be: t3, t3(LooseScan), t1
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE
t1.a IN (SELECT /*+ SEMIJOIN(LOOSESCAN) */ a FROM t2) AND
t1.b IN (SELECT /*+ SEMIJOIN(LOOSESCAN) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref a a 4 test.t2.a 1 16.67 Using where
1 PRIMARY t3 ref a a 4 test.t1.b 1 100.00 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`select#2` LOOSESCAN) SEMIJOIN(@`select#3` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t3`.`a` = `test`.`t1`.`b`
DROP TABLE t1,t2,t3;