Fixed the bug mdev-13710.

This patch corrects the code of the patch for mdev-13369 that
introduced the splitting technique when using materialized
derived tables / views with GROUP BY. The second actual parameters
of the call of the method JOIN::reoptimize() in the function
JOIN::push_splitting_cond_into_derived() was calculated incorrectly.
This could cause different failures for queries using derived tables
or views with GROUP BY when their FROM lists contained empty or
single-row tables.

2 files changed, 38 insertions, 0 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 925feb5411d..fea62302343 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -10149,3 +10149,19 @@ EXPLAIN
 }
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-13710: Optimization for equi-joins of grouping derived tables
+#             (Splitting derived tables / views with GROUP BY) :
+#             FROM list of the derived table contains constant tables
+#
+CREATE TABLE t1 (a int, INDEX(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (9),(5),(1);
+CREATE TABLE t2 (b int) ENGINE=MyISAM;
+CREATE TABLE t3 (c varchar(8), d int) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('foo',2),('bar',6);
+CREATE VIEW v1 AS SELECT a FROM t1, t2 GROUP BY a;
+SELECT * FROM t3
+WHERE d IN ( SELECT * FROM v1 ) AND c LIKE 'z%' OR c IS NULL;
+c	d
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 1919b4a1cc1..66d8ba3efa9 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -1794,3 +1794,25 @@ eval explain format=json $q1;
 
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+
+--echo #
+--echo # MDEV-13710: Optimization for equi-joins of grouping derived tables
+--echo #             (Splitting derived tables / views with GROUP BY) :
+--echo #             FROM list of the derived table contains constant tables
+--echo #
+
+CREATE TABLE t1 (a int, INDEX(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (9),(5),(1);
+
+CREATE TABLE t2 (b int) ENGINE=MyISAM;
+
+CREATE TABLE t3 (c varchar(8), d int) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('foo',2),('bar',6);
+
+CREATE VIEW v1 AS SELECT a FROM t1, t2 GROUP BY a;
+
+SELECT * FROM t3
+  WHERE d IN ( SELECT * FROM v1 ) AND c LIKE 'z%' OR c IS NULL;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;