mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-28 16:39:42 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/opt_hints_subquery.test
Dave Gosselin 049ee29e7e MDEV-37260 Implicitly named query blocks, CREATE VIEW AS supports hints 
Query blocks have implicit names, such as `select#1`, formulated
by appending their select number to the string `select#`.  This patch
allows hints to scope their applicability by implicit query block
name.  For example,
  SELECT /*+ JOIN_ORDER(@`select#2` t1, t2) */ ...
@`select#2` is an implicit query block name.  Users can control hint
applicability per query block without first naming the blocks with
QB_NAME().

Hints may now be specified within VIEWs during their creation and
they are applied locally within that VIEW's scope.  For example,
  CREATE VIEW v1 AS
    SELECT /*+ IGNORE_INDEX(t1 idx1) */ FROM t1 ... GROUP BY ... HAVING ...
In many cases and for some parts of the VIEW, the query plan
doesn't really depend on how the VIEW is used, so it makes sense
to control a part of the query plan from the VIEW definition.

Implicit names are not yet supported in VIEWs.  Attempting to create
a VIEW with an implicit name reference will cause the server to create
the VIEW, but it will emit a warning and exclude that hint from the query.
2025-10-27 10:29:22 -04:00

862 lines
36 KiB
Text
Raw Permalink Blame History

--enable_prepare_warnings
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