MDEV-19134: EXISTS() slower if ORDER BY is defined

Step #2: "[ORDER BY ...] LIMIT n" should not prevent EXISTS-to-IN
conversion, as long as
- the LIMIT clause doesn't have OFFSET
- the LIMIT is not "LIMIT 0".

1 files changed, 23 insertions, 6 deletions

diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test
index 5c5dd797353..07fdbc310ff 100644
--- a/mysql-test/main/subselect4.test
+++ b/mysql-test/main/subselect4.test
@@ -2083,19 +2083,36 @@ create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
 create table t1(a int, b int);
-insert into t1 select 
+insert into t1 select
   A.a + B.a*10, A.a + B.a*10 from t0 A, t0 B;
 
 create table t2 as select * from t1;
 
---echo # This will not be able to convert to semi-join but will not require filesort:
---replace_column 9 #
-explain 
-select * from t1 where exists (select * from t2 where t2.a=t1.a order by t2.b);
+--echo # This will be converted to semi-join:
+explain
+select * from t1
+where exists (select * from t2 where t2.a=t1.a order by t2.b);
+
+--echo # query with a non-zero constant LIMIT is converted to semi-join, too:
+explain
+select * from t1
+where exists (select * from t2 where t2.a=t1.a order by t2.b limit 2);
+
+--echo # Zero LIMIT should prevent the conversion (but it is not visible atm
+--echo #   due to MDEV-19429)
+explain
+select * from t1
+where exists (select * from t2 where t2.a=t1.a order by t2.b limit 0);
+
+--echo # LIMIT+OFFSET prevents the conversion:
+explain
+select * from t1
+where exists (select * from t2 where t2.a=t1.a order by t2.b limit 2,3);
 
 --echo # This will be merged and converted into a semi-join:
-explain 
+explain
 select * from t1 where t1.a in (select t2.a from t2 order by t2.b);
 
+
 drop table t0, t1, t2;