mirror of
https://github.com/MariaDB/server.git
synced 2025-07-21 10:48:15 +02:00
b89a1e7f35
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.
21 lines
682 B
Text
21 lines
682 B
Text
--echo #
|
|
--echo # MDEV-36169: LooseScan optimization picks invalid plan
|
|
--echo #
|
|
|
|
--source include/have_innodb.inc
|
|
|
|
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);
|
|
|
|
--echo # 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);
|
|
|
|
DROP TABLE t1,t2,t3;
|