1 files changed, 201 insertions, 0 deletions
diff --git a/mysql-test/main/sort_nest_sj.test b/mysql-test/main/sort_nest_sj.test
new file mode 100644
index 00000000000..2a06da7097c
--- /dev/null
+++ b/mysql-test/main/sort_nest_sj.test
@@ -0,0 +1,201 @@
+--source include/have_sequence.inc
+
+--echo #
+--echo # SORT-NEST WITH SEMI JOINS
+--echo #
+
+--echo
+--echo # MERGED SEMI-JOINS
+--echo
+
+--echo # SEMI JOIN MATERIALIZATION SCAN with SORT-NEST
+
+CREATE TABLE t0(a int);
+CREATE TABLE t1 (a int, b int, c int);
+CREATE TABLE t2 (a int, b int, c int);
+CREATE TABLE t3 (a int, b int, c int, key(a));
+CREATE TABLE t4 (a int, b int, c int, key(a));
+
+INSERT INTO t0 SELECT seq-1 FROM seq_1_to_10;
+INSERT INTO t1 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100;
+INSERT INTO t2 SELECT a,a,a FROM t0;
+INSERT INTO t3 SELECT a,a,a FROM t0;
+INSERT INTO t4 SELECT a,a,a FROM t0;
+
+--disable_result_log
+ANALYZE TABLE t0 PERSISTENT FOR ALL;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+ANALYZE TABLE t3 PERSISTENT FOR ALL;
+ANALYZE TABLE t4 PERSISTENT FOR ALL;
+--enable_result_log
+
+--echo # SJM scan inside the sort-nest
+--echo # sort-nest includes (t2, <subquery2>)
+
+let $query= SELECT t1.a, t2.a, t1.b,t2.b
+            FROM t1, t2
+              WHERE t1.a=t2.a AND
+                    t1.b IN (SELECT t3.b FROM t3,t4
+                             WHERE t3.a < 3 AND t3.a=t4.a)
+            ORDER BY t1.b DESC ,t2.b DESC
+            LIMIT 5;
+
+set use_sort_nest=1;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
+eval $query;
+
+set use_sort_nest=0;
+eval $query;
+
+--echo #
+--echo # SJM scan table is the first table inside the sort-nest
+--echo #
+
+alter table t2 add key(b);
+let $query= SELECT t1.a, t2.a, t1.b,t2.b
+            FROM t1, t2
+            WHERE t1.a=t2.a AND t2.b < 5 AND
+                  t1.b IN (SELECT t3.b FROM t3,t4
+                           WHERE t3.a < 3 AND t3.a=t4.a)
+            ORDER BY t2.b DESC, t1.b DESC
+            LIMIT 5;
+
+set use_sort_nest=1;
+eval EXPLAIN $query;
+eval $query;
+
+set use_sort_nest= 0;
+eval $query;
+
+DROP TABLE t0, t1, t2, t3, t4;
+
+--echo #
+--echo # SJM Lookup with sort-nest, where SJM lookup table is outside the
+--echo # sort-nest
+--echo #
+
+create table t1 (a int, b int, c int, key(a));
+create table t2 (a int, b int, c int, key(c));
+create table t3 (a int, b int, c int, key(a));
+create table t4 (a int, b int, c int);
+
+INSERT INTO t1 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_10;
+INSERT INTO t2 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100;
+INSERT INTO t3 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_1000;
+INSERT INTO t4 SELECT seq-1, seq-1, seq-1 FROM seq_1_to_100;
+
+--disable_result_log
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+ANALYZE TABLE t2 PERSISTENT FOR ALL;
+ANALYZE TABLE t3 PERSISTENT FOR ALL;
+ANALYZE TABLE t4 PERSISTENT FOR ALL;
+--enable_result_log
+
+let $query= SELECT t1.a, t2.a, t2.b
+            FROM t1, t2
+            WHERE t2.a in (SELECT t3.b from t3)
+                  AND t1.a= t2.b
+                  AND t1.a < 5
+            ORDER BY t1.b DESC, t2.a DESC
+            LIMIT 5;
+
+set use_sort_nest= 1;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
+eval $query;
+
+set use_sort_nest= 0;
+eval $query;
+
+DROP TABLE t1, t2, t3, t4;
+
+--echo #
+--echo # Firstmatch strategy
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+create table t0(a int);
+insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, c int, key(a));
+insert t1 SELECT a,a,a from t0;
+create table t2 as SELECT * from t1;
+create table t3 as SELECT * from t1;
+let $query= SELECT * FROM t1, t2
+            WHERE t1.a=t2.a AND
+            t1.b IN (SELECT b FROM t3 WHERE t3.c<=t2.c)
+            ORDER BY t2.c DESC, t1.c DESC
+            LIMIT 5;
+
+set use_sort_nest=1;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
+eval $query;
+
+set use_sort_nest=0;
+eval $query;
+
+set optimizer_switch='firstmatch=off';
+
+--echo #
+--echo # Duplicate Weedout strategy
+--echo #
+
+set use_sort_nest=1;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
+eval $query;
+
+set use_sort_nest=0;
+eval $query;
+
+set optimizer_switch=@save_optimizer_switch;
+DROP TABLE t0,t1,t2,t3;
+
+--echo
+--echo # NON-MERGED SEMI JOINS
+--echo
+
+create table t0 (a int);
+INSERT INTO t0 SELECT seq-1 FROM seq_1_to_10;
+create table t1 (a int, b int);
+insert into t1 SELECT a,a from t0 where a <5;
+create table t2 as SELECT * from t1 where a < 5;
+create table t3(a int, b int);
+INSERT INTO t3 SELECT seq-1, seq-1 FROM seq_1_to_100;
+
+--echo <subquery2> outside the sort-nest
+
+let $query= SELECT * from t2,t1
+            WHERE t2.b=t1.b
+            AND
+            t1.a IN (SELECT max(t3.a) FROM t3 GROUP BY t3.b)
+            ORDER BY t2.a DESC,t1.a DESC
+            LIMIT 5;
+
+set use_sort_nest=1;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
+eval $query;
+
+set use_sort_nest=0;
+eval $query;
+
+--echo <subquery2> inside the sort-nest
+
+let $query= SELECT * FROM t3,t2
+            WHERE t3.b=t2.b AND
+            t3.a IN (SELECT max(t1.a) FROM t1 GROUP BY t1.b)
+            ORDER BY t3.a DESC,t2.a DESC
+            LIMIT 5;
+
+set use_sort_nest=1;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
+eval $query;
+
+set use_sort_nest=0;
+eval $query;
+
+DROP TABLE t1,t2,t3,t0;