3909 Roy Lyseng 2012-05-21
Bug#14058892: Extra rows returned when variable is used in subquery in
ON clause of RIGHT JOIN.
The fix for bug#13980954 simplified the way table bits were assigned
for the tables involved in a join operation. Unfortunately, it missed
that RAND_TABLE_BIT had to be set for the last inner table of an outer
join operation, otherwise non-deterministic functions in the join
condition would be missed.
This fix adds back this bit.
Notice that there is a proposed refactoring of pushdown_on_conditions()
that will significantly reduce the size of this function.
mysql-test/t/user_var.test
Added test case for bug#14058892.
mysql-test/r/user_var.result
Added test case results for bug#14058892.
sql/sql_optimizer.cc
In pushdown_on_conditions(), add RAND_TABLE_BIT for last inner table
of an outer join.
modified:
mysql-test/r/user_var.result
mysql-test/t/user_var.test
sql/sql_optimizer.cc
3908 Roy Lyseng 2012-05-18
Bug#14048292: Segfault in Item_field::result_type on 2nd execution of prep stmt
This problem may occur for queries which uses a view as the sole inner
table of an outer join operation and where the join condition contains
an IN subquery predicate that is transformed to a semi-join.
The problem lies in simplify_joins() which fails to record the
transformed join condition for this kind of TABLE_LIST object into
prep_join_cond. I have not investigated in detail why it fails,
but rather made the recording as a post-processing stage after
simplify_joins(), which seems like a simplification of the code.
Thus, prep_join_cond is now populated unconditionally for all
TABLE_LIST objects with a join condition after simplify_joins().
Notice that select_lex::fix_prepare_information() does a similar
thing ahead of this call, unfortunately it cannot be eliminated
because JOIN::optimize() (and thus simplify_joins()) is not
necessarily called for every query block of a query expression.
This is a redundancy that should be fixed as part of refactoring.
mysql-test/include/subquery_sj.inc
Added test case for bug#14048292.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Added test case results for bug#14048292.
sql/sql_optimizer.cc
In record_join_nest_info(), also record permanent join conditions
after simplify_joins() has been called.
Renamed record_semijoin_nests() to record_join_nest_info().
Also completed earlier renaming of 'on_expr" to 'join_cond' by
refreshing some comments in simplify_joins().
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_optimizer.cc
=== modified file 'mysql-test/r/user_var.result'
--- a/mysql-test/r/user_var.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/user_var.result 2012-05-21 09:14:15 +0000
@@ -516,3 +516,68 @@ SELECT @var;
@var
NULL
DROP TABLE t1, t2;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(a INT);
+INSERT INTO t2 VALUES (1), (3), (5), (7), (9);
+CREATE TABLE t3(a INT);
+INSERT INTO t3 VALUES (1), (4), (7), (10);
+SET @var1 = 6;
+explain format=json SELECT t1.a, t2.a, t3.a, (@var1:= @var1+0) AS var
+FROM t1
+LEFT JOIN t2 ON t1.a=t2.a AND t2.a < @var1
+LEFT JOIN t3 ON t1.a=t3.a AND t3.a < @var1;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 10,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "rows": 5,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "trigcond_if(is_not_null_compl(t2), ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t2`.`a` < (@var1))), true)"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t3",
+ "access_type": "ALL",
+ "rows": 4,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "trigcond_if(is_not_null_compl(t3), ((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`a` < (@var1))), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,(@var1:=((@var1) + 0)) AS `var` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t2`.`a` < (@var1)))) left join `test`.`t3` on(((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`a` < (@var1)))) where 1
+SELECT t1.a, t2.a, t3.a, (@var1:= @var1+0) AS var
+FROM t1
+LEFT JOIN t2 ON t1.a=t2.a AND t2.a < @var1
+LEFT JOIN t3 ON t1.a=t3.a AND t3.a < @var1;
+a a a var
+1 1 1 6
+10 NULL NULL 6
+2 NULL NULL 6
+3 3 NULL 6
+4 NULL 4 6
+5 5 NULL 6
+6 NULL NULL 6
+7 NULL NULL 6
+8 NULL NULL 6
+9 NULL NULL 6
+DROP TABLE t1, t2, t3;
=== modified file 'mysql-test/t/user_var.test'
--- a/mysql-test/t/user_var.test 2012-03-06 14:29:42 +0000
+++ b/mysql-test/t/user_var.test 2012-05-21 09:14:15 +0000
@@ -436,3 +436,31 @@ SELECT @var:=(SELECT f2 FROM t2 WHERE @v
LIMIT 1;
SELECT @var;
DROP TABLE t1, t2;
+
+#
+# Bug#14058892: Extra rows returned when variable is used in subquery in
+# ON clause of RIGHT JOIN
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+CREATE TABLE t2(a INT);
+INSERT INTO t2 VALUES (1), (3), (5), (7), (9);
+
+CREATE TABLE t3(a INT);
+INSERT INTO t3 VALUES (1), (4), (7), (10);
+
+SET @var1 = 6;
+
+let $query=
+SELECT t1.a, t2.a, t3.a, (@var1:= @var1+0) AS var
+FROM t1
+ LEFT JOIN t2 ON t1.a=t2.a AND t2.a < @var1
+ LEFT JOIN t3 ON t1.a=t3.a AND t3.a < @var1;
+
+eval explain format=json $query;
+
+--sorted_result
+eval $query;
+
+DROP TABLE t1, t2, t3;
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-05-18 09:50:03 +0000
+++ b/sql/sql_optimizer.cc 2012-05-21 09:14:15 +0000
@@ -6003,8 +6003,20 @@ static bool pushdown_on_conditions(JOIN*
for (JOIN_TAB *join_tab= join->join_tab+join->const_tables;
join_tab <= last_tab ; join_tab++)
{
- Item *tmp_cond= make_cond_for_table(on_expr, join_tab->prefix_tables(),
- join_tab->added_tables(), 0);
+ table_map prefix_tables= join_tab->prefix_tables();
+ table_map added_tables= join_tab->added_tables();
+
+ if (join_tab == last_tab)
+ {
+ /*
+ Need RAND_TABLE_BIT on the last inner table, in case there is a
+ non-deterministic function in the join condition.
+ */
+ prefix_tables|= RAND_TABLE_BIT;
+ added_tables|= RAND_TABLE_BIT;
+ }
+ Item *tmp_cond= make_cond_for_table(on_expr, prefix_tables, added_tables,
+ false);
if (!tmp_cond)
continue;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3908 to 3909) Bug#14058892 | Roy Lyseng | 22 May |
