Fixed LP bug #901478.

If the duplicate elimination strategy is used for a semi-join and potentially
one of the block-based join algorithms can be employed to join the inner
tables of the semi-join then sorting of the head (first non-constant) table
for a query with ORDER BY / GROUP BY cannot be used.

1 files changed, 63 insertions, 0 deletions

diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index ef64e8caea1..5a48f7653e4 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -3361,5 +3361,68 @@ SET optimizer_switch=@tmp_optimizer_switch;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # Bug #901478: semijoin + ORDER BY + join_cache_level=4|6 
+--echo #
+
+CREATE TABLE t1 (a char(1));
+INSERT INTO t1 VALUES ('x');
+CREATE TABLE t2 (a int, b int, c int, KEY(a), KEY(b), KEY(c));
+INSERT INTO t2 VALUES
+  (9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4);
+CREATE TABLE t3 (a CHAR(1));
+INSERT INTO t3 VALUES ('x');
+CREATE TABLE t4 (a int, b int, c int, KEY(b), KEY(c));
+INSERT INTO t4 VALUES
+  (9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4);
+INSERT INTO t4 VALUES
+  (19,11,10), (17,12,18), (12,13,15), (14,12,19),
+  (18,13,18), (13,14,11), (15,15,14);
+
+SET @tmp_optimizer_switch=@@optimizer_switch;
+SET @@optimizer_switch='semijoin=on';
+SET @@optimizer_switch='firstmatch=off';
+SET @@optimizer_switch='mrr=off';
+SET @@optimizer_switch='semijoin_with_cache=off';
+
+set join_cache_level=1;
+EXPLAIN
+SELECT * FROM t1,t2
+  WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+        t2.a BETWEEN 4 and 5
+  ORDER BY t2.b;
+SELECT * FROM t1,t2
+  WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+        t2.a BETWEEN 4 and 5
+  ORDER BY t2.b;
+
+set join_cache_level=4;
+EXPLAIN
+SELECT * FROM t1,t2
+  WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+        t2.a BETWEEN 4 and 5
+  ORDER BY t2.b;
+SELECT * FROM t1,t2
+  WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+        t2.a BETWEEN 4 and 5
+  ORDER BY t2.b;
+
+SET @@optimizer_switch='semijoin_with_cache=on';
+set join_cache_level=6;
+EXPLAIN
+SELECT * FROM t1,t2
+  WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+        t2.a BETWEEN 4 and 5
+  ORDER BY t2.b;
+SELECT * FROM t1,t2
+  WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
+        t2.a BETWEEN 4 and 5
+  ORDER BY t2.b;
+
+SET join_cache_level = DEFAULT;
+SET optimizer_switch=@tmp_optimizer_switch;
+
+DROP TABLE t1,t2,t3,t4;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;