opt_hints_subquery.test

--echo # WL#8244 Hints for subquery execution

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 This query will normally use Table Pull-out
EXPLAIN
SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1);
--echo Check that we can disable SEMIJOIN transformation
EXPLAIN
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1);
--echo Same with hint in outer query
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);

--echo Query with two sub-queries
EXPLAIN
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
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx)
  AND t3.b IN (SELECT a FROM t1 ty);
EXPLAIN
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
SELECT * FROM t3
WHERE t3.a IN (SELECT a FROM t1 tx)
  AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty);
EXPLAIN
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
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
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
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
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
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
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
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
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, MatLookup is then used
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Let's also turn off Materialization, DuplicateWeedout should then be used
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo If we turn off all strategies, DuplicateWeedout should still be used
EXPLAIN
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
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
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Force FirstMatch
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Force Materialization
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Force DuplicateWeedout
EXPLAIN
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
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
EXPLAIN
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
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Drop FirstMatch, MatLookup is next
EXPLAIN
SELECT /*+ 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
EXPLAIN
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
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
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);

--echo Test multiple subqueries.
--echo Default for this query is Loosecan for first and FirstMatch for latter
EXPLAIN
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
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
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
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
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
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
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
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
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
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
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
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
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 This query gives DuplicateWeedout over both since combining
--echo DuplicateWeedout with another strategy does not seem possible.
EXPLAIN
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);
--echo More alternatives for 2nd subquery gives Materialization for first
EXPLAIN
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 by default is materialized together
EXPLAIN
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 Materialization, DuplicateWeedout is then SELECTed
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION) */ * 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 also turn off DuplicateWeedout,  FirstMatch is then used
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 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 Also turn off FirstMatch. LooseScan not usable. Back to DuplicateWeedout
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT, 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 same query with SEMIJOIN hint
--echo Force Materialization, should not change anything
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * 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 use DuplicateWeedout
EXPLAIN
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 FirstMatch
EXPLAIN
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 DuplicateWeedout
EXPLAIN
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 Materialization is among candidates, it will be used
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 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));
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, 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));
EXPLAIN
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 Drop Materialization. DuplicateWeedout will be used
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 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 Drop DuplicateWeedout, FirstMatch is next
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, 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 Strategy hints on inner-most query is ignored since sj-nests are merged
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq2 MATERIALIZATION) */ * 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 Ditto
EXPLAIN
SELECT /*+ SEMIJOIN(@subq2 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 Turn off semijoin for outer subquery. FirstMatch is used for inner
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1) */ * 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 Do not use FirstMatch for inner
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2 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 Do not use FirstMatch nor Materialization for inner
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1)
           NO_SEMIJOIN(@subq2 FIRSTMATCH, MATERIALIZATION) */ * 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 LooseScan is last resort
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1)
           NO_SEMIJOIN(@subq2 FIRSTMATCH, 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 Allow all stragies except default
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1)
           SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT, 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 a particular strategy
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1) SEMIJOIN(@subq2 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 Turn off semijoin for inner-most subquery.  FirstMatch is used for outer
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq2) */ * 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 Do not use FirstMatch for outer
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) NO_SEMIJOIN(@subq2) */ * 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 Do not use FirstMatch nor Materialization for outer
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION)
       	   NO_SEMIJOIN(@subq2) */ * 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 LooseScan can not be used since index scan would not be "covering"
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT)
       	   NO_SEMIJOIN(@subq2) */ * 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 Allow all stragies except default
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT, LOOSESCAN)
       	   NO_SEMIJOIN(@subq2) */ * 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 a particular strategy
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) NO_SEMIJOIN(@subq2) */ * 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 Turn off semijoin for both subqueries
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * 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
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;
--echo Another Prepared Statement test
PREPARE stmt1 FROM "EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1) SEMIJOIN(@subq2 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))";
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
SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1);
--echo This should not change anything
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
--echo Force semijoin
EXPLAIN
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
SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
--echo Query with two sub-queries
EXPLAIN
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
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
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
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
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
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
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
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
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Let's turn off LooseScan also by hint, FirstMatch should still be SELECTed
EXPLAIN
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, MatLookup should then be used
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Let's also turn off Materialization, DuplicateWeedout should then be used
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

--echo Let's force LooseScan back on
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Forcing another strategy
EXPLAIN
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
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
EXPLAIN
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
EXPLAIN
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
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
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
EXPLAIN
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
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
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);

--echo Default for this query is Loosecan for first and FirstMatch for latter
--echo Since both strategies are disabled, will now use DuplicateWeedout
EXPLAIN
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
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
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
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
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
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
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
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
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
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
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
EXPLAIN
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
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
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);

--echo Disable all strategies
SET optimizer_switch='firstmatch=off,loosescan=off,materialization=off,duplicateweedout=off';
--echo DuplicateWeedout is then used
EXPLAIN
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
--echo Turning off extra strategies should not change anything
EXPLAIN
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
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 DupliateWeedout
EXPLAIN
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
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);

SET optimizer_switch = default;

--echo Test that setting optimizer_switch after prepare will change strategy
PREPARE stmt1 FROM "EXPLAIN
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
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SEMIJOIN() */ a FROM t1);
--echo Try opposite order
EXPLAIN
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
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
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
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1);
EXPLAIN
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
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
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
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
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
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
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
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
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
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
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
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
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
SELECT a, a IN (SELECT a FROM t1) FROM t2;
--echo Force Subquery Materialization
EXPLAIN
SELECT a, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
EXPLAIN
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
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
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
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
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,subquery_materialization_cost_based=off';
--echo This query will now use materialization
EXPLAIN
SELECT a, a IN (SELECT a FROM t1) FROM t2;
--echo Force In-to-exists
EXPLAIN
SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ a,
       a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2;

--echo Specifying both strategies should give a warning
EXPLAIN
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
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
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) SUBQUERY(INTOEXISTS) */ a
FROM t1);
--echo Try opposite order
EXPLAIN
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
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
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
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SEMIJOIN() */ a FROM t1);
--echo Try opposite order
EXPLAIN
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
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1);
EXPLAIN
SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1);
EXPLAIN
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;