[Commits] eb09580: MDEV-19588 Wrong results from query, using left join.

IgorBabaev igor at mariadb.com
Wed May 29 00:53:08 EEST 2019


revision-id: eb09580b67ee19f7ac30c1a41c8307b9c7d482d1 (mariadb-5.5.64-11-geb09580)
parent(s): 0955462d0aafab01def9c1a5ec131eb641cb9e68
author: Igor Babaev
committer: Igor Babaev
timestamp: 2019-05-28 14:53:08 -0700
message:

MDEV-19588 Wrong results from query, using left join.

This bug could happen when queries with nested outer joins were
executed employing join buffers. At such an execution if the method
JOIN_CACHE::join_records() is called when a join buffer has become
full no 'first_unmatched' field should be cleaned up in the JOIN_TAB
structure to which the join cache with this buffer is attached.

---
 mysql-test/r/join_nested.result      | 33 +++++++++++++++++++++++++++++++++
 mysql-test/r/join_nested_jcl6.result | 33 +++++++++++++++++++++++++++++++++
 mysql-test/t/join_nested.test        | 34 ++++++++++++++++++++++++++++++++++
 sql/sql_join_cache.cc                |  3 ++-
 4 files changed, 102 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 6ddd39c..d618f7c 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1966,3 +1966,36 @@ Note	1003	select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS
 DROP VIEW v1;
 DROP TABLE t1,t2;
 set optimizer_search_depth= @tmp_mdev621;
+#
+# MDEV-19588: Nested left joins using optimized join cache
+#
+set optimizer_switch='optimize_join_buffer_size=on';
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=2;
+CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam;
+CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5);
+CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t3 VALUES
+(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146),
+(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),
+(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL);
+SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+pk	c1	i1
+7	a	NULL
+17	a	NULL
+26	a	NULL
+explain extended SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	18	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	0	0.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t2`.`i1` = `test`.`t3`.`i1`))) on((`test`.`t1`.`i1` = `test`.`t3`.`i1`)) where ((`test`.`t2`.`pk` < 13) or isnull(`test`.`t3`.`i1`))
+DROP TABLE t1,t2,t3;
+set join_cache_level= @save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index bac8e1c..b99b020 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -1977,6 +1977,39 @@ Note	1003	select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS
 DROP VIEW v1;
 DROP TABLE t1,t2;
 set optimizer_search_depth= @tmp_mdev621;
+#
+# MDEV-19588: Nested left joins using optimized join cache
+#
+set optimizer_switch='optimize_join_buffer_size=on';
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=2;
+CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam;
+CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5);
+CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t3 VALUES
+(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146),
+(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),
+(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL);
+SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+pk	c1	i1
+7	a	NULL
+17	a	NULL
+26	a	NULL
+explain extended SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	18	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	0	0.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t2`.`i1` = `test`.`t3`.`i1`))) on((`test`.`t1`.`i1` = `test`.`t3`.`i1`)) where ((`test`.`t2`.`pk` < 13) or isnull(`test`.`t3`.`i1`))
+DROP TABLE t1,t2,t3;
+set join_cache_level= @save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
 CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
 CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
 CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index e60b782..cfb24a6 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -1380,3 +1380,37 @@ DROP VIEW v1;
 DROP TABLE t1,t2;
 
 set optimizer_search_depth= @tmp_mdev621;
+
+--echo #
+--echo # MDEV-19588: Nested left joins using optimized join cache
+--echo #
+
+set optimizer_switch='optimize_join_buffer_size=on';
+
+set @save_join_cache_level= @@join_cache_level;
+set join_cache_level=2;
+
+CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam;
+
+CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5);
+
+CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam;
+INSERT INTO t3 VALUES
+(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146),
+(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),
+(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL);
+
+let $q=
+SELECT t3.*
+FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
+WHERE t2.pk < 13 OR t3.i1 IS NULL;
+
+eval $q;
+eval explain extended $q;
+
+DROP TABLE t1,t2,t3;
+
+set join_cache_level= @save_join_cache_level;
+
+set optimizer_switch=@save_optimizer_switch;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index 73ee201..909eeb8 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -2150,7 +2150,8 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last)
   }
 
 finish:
-  if (outer_join_first_inner)
+  if (outer_join_first_inner &&
+      join_tab->first_inner == join_tab->first_unmatched)
   {
     /* 
       All null complemented rows have been already generated for all


More information about the commits mailing list