mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-03-31 12:25:38 +02:00
Code Issues Projects Releases Packages Wiki Activity Actions 1
mariadb/mysql-test/main/opt_hints_subquery.test

863 lines
36 KiB
Text
Raw Permalink Blame History

--enable_prepare_warnings
--disable_view_protocol # Since optimizer hints are not supported inside views
CREATE TABLE t1 (a INTEGER NOT NULL, b INT, PRIMARY KEY (a));
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), (3), (4), (5);
INSERT INTO t3 VALUES (10,3), (20,4), (30,5);
ANALYZE TABLE t1, t2, t3;
--echo # Parser tests
--echo # Correct hints (no warnings):
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN() */ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SEMIJOIN(@qb1) */ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) NO_SEMIJOIN(@qb1 firstmatch) */ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SEMIJOIN( @qb1 firstmatch, dupsweedout ) */ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN( FIRSTMATCH, LOOSESCAN,materialization, DUPSWEEDOUT ) */ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb2) NO_SEMIJOIN(@qb2 FIRSTMATCH,LOOSESCAN, materialization, DUPSWEEDOUT) */ a FROM t1;
set optimizer_switch='derived_merge=off';
--echo # Correct 'cause hints refer to different query blocks:
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@qb1) SEMIJOIN(loosescan)*/ a
FROM (SELECT /*+ QB_NAME(qb1)*/ * FROM t2) AS tt;
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN()*/ a
FROM (SELECT /*+ SEMIJOIN(loosescan)*/ * FROM t2) AS tt;
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) */ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY( INTOEXISTS ) */ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ QB_NAME (qb1) SUBQUERY(@qb1 materialization) */ a FROM t1;
--echo # Incorrect hints (warnings)
SELECT /*+ SEMIJOIN(loosescan @qb1) */ a FROM t1;
SELECT /*+ SEMIJOIN(@qb1 @qb2) */ a FROM t1;
SELECT /*+ SEMIJOIN(@qb1 LOOSESCAN,materialization, unknown_strategy) */ a FROM t1;
SELECT /*+ NO_SEMIJOIN(@qb1, @qb2) */ a FROM t1;
SELECT /*+ NO_SEMIJOIN(FIRSTMATCH, ,LOOSESCAN, materialization) */ a FROM t1;
SELECT /*+ NO_SEMIJOIN(FIRSTMATCH, @qb2,LOOSESCAN) */ a FROM t1;
SELECT /*+ SUBQUERY(wrong_strat) */ a FROM t1;
SELECT /*+ SUBQUERY(materialization, intoexists) */ a FROM t1;
SELECT /*+ SUBQUERY(@qb1 materialization) */ a FROM t1;
SELECT /*+ SUBQUERY() */ a FROM t1;
--echo # Mix of correct and incorrect hints:
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(firstmatch ) SEMIJOIN(loosescan @qb1) */ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@qb1, @qb2) SEMIJOIN()*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN() NO_SEMIJOIN(FIRSTMATCH, @qb2,LOOSESCAN) */ a FROM t1;
--echo # Conflicting hints:
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN() SEMIJOIN(dupsweedout) NO_SEMIJOIN(firstmatch)*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(loosescan,materialization) SEMIJOIN(dupsweedout)*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(firstmatch,loosescan,materialization) SEMIJOIN() NO_SEMIJOIN()*/ a
FROM t1;
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SEMIJOIN(@qb1) SEMIJOIN(loosescan) NO_SEMIJOIN(@qb1 dupsweedout)*/ a
FROM t1;
EXPLAIN EXTENDED SELECT /*+ SEMIJOIN(firstmatch) NO_SEMIJOIN()*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) SUBQUERY(intoexists)*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN() SUBQUERY(materialization) SUBQUERY(intoexists)*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) SUBQUERY(intoexists)*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) SUBQUERY(intoexists) SUBQUERY(materialization)*/ a
FROM t1;
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) SEMIJOIN(firstmatch) SUBQUERY(intoexists)*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SEMIJOIN(@qb1) SUBQUERY(@qb1 materialization) SUBQUERY(intoexists)*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SUBQUERY(@qb1 materialization) SEMIJOIN(@qb1 firstmatch) SUBQUERY(intoexists)*/ a FROM t1;
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@qb1) SEMIJOIN(loosescan) NO_SEMIJOIN(@qb1 dupsweedout)*/ a
FROM (SELECT /*+ QB_NAME(qb1)*/ * FROM t2) AS tt;
DROP TABLE t1, t2 ,t3;
set optimizer_switch=default;
--echo # End of parser tests
CREATE TABLE t1 (a INTEGER NOT NULL, b INT, PRIMARY KEY (a));
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);
ANALYZE TABLE t1, t2, t3;
--echo # This query will normally use Table Pull-out
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1);
--echo # Check that we can disable SEMIJOIN transformation
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1);
--echo # Same with hint in outer query
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
--echo # Query with two sub-queries
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT a FROM t1 tx)
AND t3.b IN (SELECT a FROM t1 ty);
--echo # No SEMIJOIN transformation for first subquery
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx)
AND t3.b IN (SELECT a FROM t1 ty);
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(`subq1`) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
--echo # No SEMIJOIN transformation for latter subquery
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT a FROM t1 tx)
AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty);
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@`subq2`) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
--echo # No SEMIJOIN transformation for any subquery
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty);
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
--echo # Query with nested sub-queries
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo # No SEMIJOIN transformation for outer subquery
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo # No SEMIJOIN transformation for inner-most subquery
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo # No SEMIJOIN transformation at all
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo # This query does not support SEMIJOIN. SEMIJOIN hint is ignored
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
--echo # This query will get LooseScan by default
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Let's turn off LooseScan, FirstMatch is then SELECTed
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Let's also turn off FirstMatch, DupsWeedout is then used.
--echo # (StartTemporary, EndTemporary) in the output indicate DupsWeedout usage
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Materialization is used here
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Turn off all strategies, DuplicateWeedout should be used as a fallback
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION,
DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Turn off non-used strategies, nothing should change. Still Loosescan
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Test same query with SEMIJOIN hint
--echo # Forcing LooseScan, should not change anything
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Force FirstMatch
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Force Materialization
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Force DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # If LooseScan is among candidates, it will be used
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION,
DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Drop LooseScan from list of strategies, FirstMatch will be used
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo For this query LooseScan and Materialization is not applicable
# Warnings "Field or reference <fx> of SELECT #n was resolved in SELECT #m"
# are generated during both prepare and execution stages. So disable PS protocol
# to avoid duplication
--disable_ps_protocol
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--echo # Turn off all applicable strategies. DuplicateWeedout should be used
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--echo # Similar with SEMIJOIN hint
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--enable_ps_protocol
--echo # Test multiple subqueries.
--echo # Default for this query is Loosecan for first and FirstMatch for latter
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Forcing the default strategy should not change anything
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Forcing a strategy for one, may change the other due to cost changes
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Forcing same strategy for both
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Loosescan for both is not possible, ends up with DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Swap strategies compared to default
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Different subsets of strategies for different subqueries
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN)
SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Vice versa
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT)
SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Another combination
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, FIRSTMATCH)
SEMIJOIN(@subq2 LOOSESCAN, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Turn off default
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN)
NO_SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Also turn off 2nd choice. Gives DuplicateWeedout over both
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH)
NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Also turn off DuplicateWeedout. Materialization is only one left.
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT)
NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Force materialization with SEMIJOIN hints instead
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)
SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)
SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)
SEMIJOIN(@subq2 LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # A query with nested subqueries which are joined together
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
--echo # Let's turn off FirtMatch, DuplicateWeedout is then chosen
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
--echo # If we turn off all strategies, DuplicateWeedout should still be used
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION,
DUPSWEEDOUT) */ *
FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
--echo # Test the same query with SEMIJOIN hint
--echo # Force FirstMatch, should not change anything
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
--echo # Force LooseScan, will fall back to DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
--echo # Force DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
--echo # If FirstMatch is among candidates, it will be used
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, FIRSTMATCH, LOOSESCAN,
DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
--echo # Test hints with prepared statements
PREPARE stmt1 FROM "EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN)
NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)";
EXECUTE stmt1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET optimizer_switch = default;
--echo # Tests with non-default optimizer_switch settings
SET optimizer_switch = 'semijoin=off';
--echo # No table pull-out for this query
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1);
--echo # This should not change anything
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
--echo # Force semijoin, table pull-out is performed
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
--echo # Setting strategy should still force semijoin
--echo # Strategy is ignored since table pull-out is done
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
--echo # Query with two sub-queries
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT a FROM t1 tx)
AND t3.b IN (SELECT a FROM t1 ty);
--echo # SEMIJOIN transformation for first subquery
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
--echo # SEMIJOIN transformation for latter subquery
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
--echo # SEMIJOIN transformation for both subqueries
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1) SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
--echo # Query with nested sub-queries
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo # SEMIJOIN transformation for outer subquery
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo # SEMIJOIN transformation for inner-most subquery
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo # SEMIJOIN transformation for both
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1) SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo Test strategies when some are disabled by optimizer_switch
SET optimizer_switch='semijoin=on';
SET optimizer_switch='loosescan=off';
--echo # This query will get LooseScan by default. FirstMatch now
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Let's turn off LooseScan also by hint, FirstMatch is then selected
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Let's also turn off FirstMatch, DupsWeedout is then used.
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Turn off DupsWeedout, Materialization is used here
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Let's force LooseScan back on
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Forcing another strategy
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # If LooseScan is among candidates, it is used even if originally disabled
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION,
DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Disable another strategy
SET optimizer_switch='firstmatch=off';
--echo # Turn on FirstMatch, but not LooseScan on with hint
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Drop all remaining strategies with hint, should use DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # For this query LooseScan and Materialization is not applicable
--echo # Should use DuplicateWeedout since FirstMatch is disabled
# Warnings "Field or reference <fx> of SELECT #n was resolved in SELECT #m"
# are generated during both prepare and execution stages. So disable PS protocol
# to avoid duplication
--disable_ps_protocol
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--echo Turn off all applicable strategies. DuplicateWeedout should still be used
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--echo Reverse which strategies are allowed with hint
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--enable_ps_protocol
--echo # Default for this query is Loosecan for first and FirstMatch for latter
--echo # Since both strategies are disabled, will now use DuplicateWeedout
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Allowing LooseScan and FirstMatch and optimizer_switch is ignored
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH)
SEMIJOIN(@subq2 LOOSESCAN, FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Forcing a disabled strategy for one
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Forcing same strategy for both
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Swap strategies compared to default
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Different subsets of strategies for different subqueries
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN)
SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Turn off DuplicateWeedout for both. Materialization is left
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 DUPSWEEDOUT)
NO_SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Forcing materialization should have same effect
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)
SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Turn off DuplicateWeedout for first. MatLookup is used for both
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Turn off DuplicateWeedout for second. Same effect.
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo Enable all strategies except DuplicateWeedout
SET optimizer_switch='firstmatch=on,loosescan=on,materialization=on,duplicateweedout=off';
--echo # If we turn off all other strategies, DuplicateWeedout will be used
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # LooseScan and Materialization is not applicable, FirstMatch is used
# Warnings "Field or reference <fx> of SELECT #n was resolved in SELECT #m"
# are generated during both prepare and execution stages. So disable PS protocol
# to avoid duplication
--disable_ps_protocol
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--echo # Turn off all applicable strategies. DuplicateWeedout should be used
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--echo # Similar with SEMIJOIN hint
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--enable_ps_protocol
--echo # Disable all strategies
SET optimizer_switch='firstmatch=off,loosescan=off,materialization=off,duplicateweedout=off';
--echo # DuplicateWeedout is then used
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Turning off extra strategies should not change anything
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # Turning on some strategies should give one of those
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo # For this query that cannot use LooseScan or Materialization,
--echo # turning those on will still give DuplicateWeedout
# Warnings "Field or reference <fx> of SELECT #n was resolved in SELECT #m"
# are generated during both prepare and execution stages. So disable PS protocol
# to avoid duplication
--disable_ps_protocol
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--echo # Turning on FirstMatch should give FirstMatch
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
--enable_ps_protocol
SET optimizer_switch = default;
--echo Test that setting optimizer_switch after prepare will change strategy
PREPARE stmt1 FROM "EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN)
NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)";
EXECUTE stmt1;
SET optimizer_switch = 'duplicateweedout=off';
--echo Will now use materialization
EXECUTE stmt1;
SET optimizer_switch = 'duplicateweedout=on';
--echo Turn DuplicateWeedout back on
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET optimizer_switch = default;
--echo # Specifying two SEMIJOIN/NO_SEMIJOIN for same query block gives warning
--echo # First has effect, second is ignored
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SEMIJOIN() */ a FROM t1);
--echo # Try opposite order
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SEMIJOIN() NO_SEMIJOIN() */ a FROM t1);
--echo # Specify at different levels, hint inside block has effect
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1);
--echo # Specify at different levels, opposite order
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1);
--echo # Duplicate hints also gives warning, but hint has effect
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1);
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1);
--echo # Multiple subqueries with conflicting hints
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) NO_SEMIJOIN() */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) SEMIJOIN(LOOSESCAN) */ a FROM t2);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) NO_SEMIJOIN(LOOSESCAN) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) SEMIJOIN(LOOSESCAN) */ a FROM t2);
--echo # Conflicting hints in same hint comment
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq1 LOOSESCAN) */ *
FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq1 FIRSTMATCH) */ *
FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # Non-supported strategies should give warnings
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 INTOEXISTS) NO_SEMIJOIN(@subq2 INTOEXISTS) */ *
FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
--echo # SUBQUERY tests
--echo # SUBQUERY should disable SEMIJOIN and use specified subquery strategy
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);
EXPLAIN
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
--echo # Query with two subqueries
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
--echo # Query with nested sub-queries
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION) SUBQUERY(@subq2 INTOEXISTS) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
--echo # This query does not support SEMIJOIN. Materialization is default
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
--echo # Use In-to-exists instead
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
--echo # For this query In-to-exists is default
EXPLAIN EXTENDED
SELECT a, a IN (SELECT a FROM t1) FROM t2;
--echo # Force Subquery Materialization
EXPLAIN EXTENDED
SELECT a, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ a,
a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2;
--echo # This query does not support Subquery Materialization due to type mismatch
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ sum(b) FROM t1 group by a);
--echo # Trying to force Subquery Materialization will not change anything
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ sum(b) FROM t1 group by a);
--echo # Test hints with prepared statements
PREPARE stmt1 FROM "EXPLAIN
SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION)
SUBQUERY(@subq2 INTOEXISTS) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)";
EXECUTE stmt1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
--echo # Test optimizer_switch settings with SUBQUERY hint
SET optimizer_switch='materialization=off';
--echo This query will now use In-to-exist
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
--echo # Force it to use Materialization
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
SET optimizer_switch='materialization=on';
--echo # This query will now use In-to_exists
EXPLAIN EXTENDED
SELECT a, a IN (SELECT a FROM t1) FROM t2;
--echo Force Materialization
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ a,
a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2;
--echo # Specifying both strategies should give a warning
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION, INTOEXISTS)
SUBQUERY(@subq2 MATERIALIZATION, INTOEXISTS) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
--echo # Non-supported strategies should give warnings
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 FIRSTMATCH) SUBQUERY(@subq2 LOOSESCAN) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
SET optimizer_switch= default;
--echo # Specifying two SUBQUERY for same query block gives warning
--echo # First has effect, second is ignored
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) SUBQUERY(INTOEXISTS) */ a
FROM t1);
--echo # Try opposite order
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SUBQUERY(MATERIALIZATION) */ a
FROM t1);
--echo # Specify at different levels, hint inside block has effect
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(INTOEXISTS) */ a FROM t1);
--echo # Specify at different levels, opposite order
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(MATERIALIZATION) */ a FROM t1);
--echo # Specifying combinations of SUBQUERY and SEMIJOIN/NO_SEMIJOIN
--echo # for same query block gives warning
--echo # First has effect, second is ignored
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SEMIJOIN() */ a FROM t1);
--echo # Try opposite order
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SUBQUERY(MATERIALIZATION) */ a FROM t1);
--echo # Specify at different levels, hint inside block has effect
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1);
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1);
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(@subq INTOEXISTS) */ a FROM t1);
DROP TABLE t1,t2,t3;
Reference in a new issue View git blame Copy permalink