MDEV-16917 Index affects query results

The optimizer erroneously allowed to use join cache when joining a
splittable materialized table together with splitting optimization.
As a consequence in some rare cases the server returned wrong result
sets for queries with materialized derived.

This patch allows to use either join cache without usage of splitting
technique for materialization of a splittable derived table or splitting
without usage of join cache when joining such table. The costs the these
alternatives are compared and the best variant is chosen.

2 files changed, 52 insertions, 0 deletions

diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
new file mode 100644
index 00000000000..6fa20b721e0
--- /dev/null
+++ b/mysql-test/main/derived_split_innodb.result
@@ -0,0 +1,26 @@
+#
+# MDEV-16917: do not use splitting for derived with join cache
+#
+CREATE TABLE t1 (
+n1 int(10) NOT NULL,
+n2 int(10) NOT NULL,
+c1 char(1) NOT NULL,
+KEY c1 (c1),
+KEY n1_c1_n2 (n1,c1,n2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
+WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	c1,n1_c1_n2	n1_c1_n2	9	NULL	2	Using where; Using index
+1	PRIMARY	<derived2>	ref	key0	key0	8	test.t1.n1,test.t1.n2	2	
+2	LATERAL DERIVED	t1	ref	c1,n1_c1_n2	n1_c1_n2	4	test.t1.n1	1	Using where; Using index
+SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
+WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
+n1
+0
+1
+DROP TABLE t1;
diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test
new file mode 100644
index 00000000000..2abd6fa9f97
--- /dev/null
+++ b/mysql-test/main/derived_split_innodb.test
@@ -0,0 +1,26 @@
+--source include/have_innodb.inc
+
+--echo #
+--echo # MDEV-16917: do not use splitting for derived with join cache
+--echo #
+
+CREATE TABLE t1 (
+  n1 int(10) NOT NULL,
+  n2 int(10) NOT NULL,
+  c1 char(1) NOT NULL,
+  KEY c1 (c1),
+  KEY n1_c1_n2 (n1,c1,n2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a');
+
+ANALYZE TABLE t1;
+
+Let $q=
+SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
+  WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
+
+eval EXPLAIN $q;
+eval $q;
+
+DROP TABLE t1;
+