Merge 10.2 into bb-10.2-ext

4 files changed, 168 insertions, 1 deletions

diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index 1f21dbcd36d..9436665bfee 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -853,3 +853,32 @@ execute stmt;
 deallocate prepare stmt;
 
 drop table t1;
+
+--echo #
+--echo # MDEV-14852: CTE using temporary table in query
+--echo #             with two references to the CTE
+--echo #
+
+create temporary table t1 (i int);
+insert into t1 values (5),(4),(1),(2),(3);
+
+with
+c1 as (select i from t1),
+c2 as (select i from c1 where c1.i=2)
+select i from c1 where i > 3 union  select i from c2;
+
+drop table t1;
+
+create table t1 (term char(10));
+create temporary table t2 (term char(10));
+
+insert into t1 values ('TERM01'),('TERM02'),('TERM03');
+insert into t2 values ('TERM02'),('TERM03'),('TERM04');
+
+with c1 as (select * from t1), c2 as (select * from t2)
+(select * from c1 left outer join c2 on c1.term = c2.term)
+union all
+(select * from c1 right outer join c2 on c1.term = c2.term
+  where c1.term is null);
+
+drop table t1,t2;
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 12013d43c1f..d2e33b165f7 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2000,6 +2000,93 @@ set @var=
 drop table t1;
 
 --echo #
+--echo # mdev-14777: crash caused by the same as in mdev-14755
+--echo #
+
+--source include/have_sequence.inc
+
+CREATE TABLE t1 (i1 int NOT NULL, i2 int);
+CREATE TABLE t2 (d1 int NOT NULL PRIMARY KEY);
+CREATE TABLE t3 (i int );
+
+insert into t1 select seq,seq from seq_1_to_100000;
+insert into t2 select seq from  seq_1000_to_100000;
+insert into t3 select seq from seq_1_to_1000;
+
+SELECT *
+FROM
+(
+  SELECT *
+  FROM
+  (
+    WITH RECURSIVE rt AS
+    (
+      SELECT i2 P, i1 C FROM t1 WHERE i1 IN (SELECT d1 FROM t2)
+      UNION
+      SELECT t1.i2 P, rt.C C FROM t1, rt
+    )
+    SELECT C,P
+      FROM ( SELECT P,C FROM rt WHERE NOT EXISTS (SELECT 1 FROM t1) ) Y
+  ) X
+  WHERE 1 = 1
+) K, t3;
+
+drop table t1,t2,t3;
+
+--echo #
+--echo # mdev-14879: subquery with recursive reference in WHERE of CTE
+--echo #
+
+create table flights
+(departure varchar(32),
+ arrival varchar(32),
+ carrier varchar(20),
+ flight_number char(7));
+
+insert into flights values
+('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'),
+('Seattle', 'Chicago', 'American', 'AA 2573'),
+('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'),
+('Chicago', 'New York', 'American', 'AA 375'),
+('Chicago', 'Montreal', 'Air Canada', 'AC 3053'),
+('Los Angeles', 'New York', 'Delta', 'DL 1197'),
+('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'),
+('New York', 'Paris', 'Air France', 'AF 23'),
+('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'),
+('Tokyo', 'Seattle', 'ANA', 'NH 178'),
+('Los Angeles', 'Tokyo', 'ANA', 'NH 175'),
+('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'),
+('Montreal', 'Paris', 'Air Canada', 'AC 870'),
+('Cairo', 'Paris', 'Air France', 'AF 503'),
+('New York', 'Seattle', 'American', 'AA 45'),
+('Paris', 'Chicago', 'Air France', 'AF 6734');
+
+with recursive destinations (city) as
+( select a.arrival from flights a where a.departure='Cairo'
+  union
+  select b.arrival from destinations r, flights b where r.city=b.departure)
+select * from destinations;
+
+set standard_compliant_cte=0;
+
+let $q=
+with recursive destinations (city, legs) as
+(
+  select a.arrival, 1 from flights a where a.departure='Cairo'
+  union
+  select b.arrival, r.legs + 1 from destinations r, flights b
+  where r.city=b.departure and b.arrival not in (select city from destinations)
+)
+select * from destinations;
+
+eval $q;
+eval explain extended $q;
+
+set standard_compliant_cte=default;
+
+drop table flights;
+
+--echo #
 --echo # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field
 --echo #
 
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index a9bb998bc33..f5caa018e94 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -1567,6 +1567,31 @@ eval explain format=json $q;
 drop table t1;
 
 --echo #
+--echo # MDEV-13454: consequence of mdev-14368 fixed for 5.5
+--echo #
+
+SET  sql_mode = 'ONLY_FULL_GROUP_BY';
+
+create table t1 (id int, id2 int);
+insert into t1 values (1,1),(2,3),(3,4),(7,2);
+
+create table t2(id2 int);
+insert  t2 values (1),(2),(3);
+
+let $q=
+SELECT * FROM t1
+  LEFT OUTER JOIN
+  (SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2)
+WHERE (vc.ct>0);
+
+eval $q;
+eval EXPLAIN FORMAT=JSON $q;
+
+DROP TABLE t1,t2;
+
+SET  sql_mode = DEFAULT;
+
+--echo #
 --echo # MDEV-10855: Pushdown into derived with window functions 
 --echo #
 
@@ -1851,4 +1876,3 @@ SELECT * FROM v3 JOIN t1 ON (bmax = b);
 
 DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
-
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index ce0b949fcac..c5cec99cebf 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -6078,6 +6078,33 @@ SELECT (SELECT MAX(sq.f2) FROM t1) FROM (SELECT * FROM t2) AS sq WHERE f2 = 2;
 --echo #
 drop table t1, t2;
 
+--echo #
+--echo # MDEV-13933: Wrong results in COUNT() query with EXISTS and exists_to_in
+--echo # (5.5 test)
+--echo #
+SET @optimiser_switch_save= @@optimizer_switch;
+ 
+CREATE TABLE t1 (a INT NOT NULL);
+INSERT INTO t1 VALUES (1),(1),(1),(5),(5);
+ 
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (5),(1);
+ 
+CREATE TABLE t3 (c INT, KEY(c));
+INSERT INTO t3 VALUES (5),(5);
+ 
+SET optimizer_switch='semijoin=on';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+ 
+SET optimizer_switch='semijoin=off';
+select t1.a from t1 where t1.a in (select `test`.`t2`.`b` from `test`.`t2`)
+and t1.a in (select `test`.`t3`.`c` from `test`.`t3`);
+ 
+SET @@optimizer_switch= @optimiser_switch_save;
+DROP TABLE t1, t2, t3;
+
+--echo End of 5.5 tests
 --echo # End of 10.0 tests
 
 --echo #