Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-05-26 10:33:01-07:00, igor@stripped +3 -0
Fixed bug #28571. Outer join queries with ON conditions over
constant outer tables did not return null complemented
rows when conditions were evaluated to FALSE.
Wrong results were returned because the conditions over constant
outer tables, when being pushed down, were erroneously enclosed
into the guard function used for WHERE conditions.
mysql-test/r/join_outer.result@stripped, 2007-05-26 10:32:59-07:00, igor@stripped +15 -0
Added a test case for bug #28571.
mysql-test/t/join_outer.test@stripped, 2007-05-26 10:32:59-07:00, igor@stripped +16 -0
Added a test case for bug #28571.
sql/sql_select.cc@stripped, 2007-05-26 10:32:59-07:00, igor@stripped +29 -25
Fixed bug #28571. Outer join queries with ON conditions over
constant outer tables did not return null complemented
rows when conditions were evaluated to FALSE.
Wrong results were returned because the conditions over constant
outer tables, when being pushed down, were erroneously enclosed
into the guard function used for WHERE conditions.
The problem is fixed in the function make_join_select. Now the
conditions over constant tables from ON expressions are pushed
down after the conditions from WHERE has been pushed down.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: igor
# Host: olga.mysql.com
# Root: /home/igor/dev-opt/mysql-5.0-opt-bug28571
--- 1.524/sql/sql_select.cc 2007-05-26 10:33:06 -07:00
+++ 1.525/sql/sql_select.cc 2007-05-26 10:33:06 -07:00
@@ -5666,28 +5666,6 @@
join->const_table_map,
(table_map) 0);
DBUG_EXECUTE("where",print_where(const_cond,"constants"););
- for (JOIN_TAB *tab= join->join_tab+join->const_tables;
- tab < join->join_tab+join->tables ; tab++)
- {
- if (*tab->on_expr_ref)
- {
- JOIN_TAB *cond_tab= tab->first_inner;
- COND *tmp= make_cond_for_table(*tab->on_expr_ref,
- join->const_table_map,
- ( table_map) 0);
- if (!tmp)
- continue;
- tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
- if (!tmp)
- DBUG_RETURN(1);
- tmp->quick_fix_field();
- cond_tab->select_cond= !cond_tab->select_cond ? tmp :
- new Item_cond_and(cond_tab->select_cond,tmp);
- if (!cond_tab->select_cond)
- DBUG_RETURN(1);
- cond_tab->select_cond->quick_fix_field();
- }
- }
if (const_cond && !const_cond->val_int())
{
DBUG_PRINT("info",("Found impossible WHERE condition"));
@@ -5918,13 +5896,39 @@
}
/*
- Push down all predicates from on expressions.
- Each of these predicated are guarded by a variable
+ Push down conditions from all on expressions.
+ Each of these conditions are guarded by a variable
that turns if off just before null complemented row for
- outer joins is formed. Thus, the predicates from an
+ outer joins is formed. Thus, the condition from an
'on expression' are guaranteed not to be checked for
the null complemented row.
*/
+
+ /* First push down constant conditions from on expressions */
+ for (JOIN_TAB *tab= join->join_tab+join->const_tables;
+ tab < join->join_tab+join->tables ; tab++)
+ {
+ if (*tab->on_expr_ref)
+ {
+ JOIN_TAB *cond_tab= tab->first_inner;
+ COND *tmp= make_cond_for_table(*tab->on_expr_ref,
+ join->const_table_map,
+ (table_map) 0);
+ if (!tmp)
+ continue;
+ tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
+ if (!tmp)
+ DBUG_RETURN(1);
+ tmp->quick_fix_field();
+ cond_tab->select_cond= !cond_tab->select_cond ? tmp :
+ new Item_cond_and(cond_tab->select_cond,tmp);
+ if (!cond_tab->select_cond)
+ DBUG_RETURN(1);
+ cond_tab->select_cond->quick_fix_field();
+ }
+ }
+
+ /* Push down non-constant conditions from on expressions */
JOIN_TAB *last_tab= tab;
while (first_inner_tab && first_inner_tab->last_inner == last_tab)
{
--- 1.54/mysql-test/r/join_outer.result 2007-05-26 10:33:06 -07:00
+++ 1.55/mysql-test/r/join_outer.result 2007-05-26 10:33:06 -07:00
@@ -1239,3 +1239,18 @@
Handler_read_rnd 0
Handler_read_rnd_next 6
DROP TABLE t1,t2;
+CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL);
+INSERT INTO t1 VALUES (1,0), (2,1);
+CREATE TABLE t2 (d int PRIMARY KEY);
+INSERT INTO t2 VALUES (1), (2), (3);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
+1 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Not exists
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+c e d
+1 0 NULL
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
+c e d
+1 0 NULL
+DROP TABLE t1,t2;
--- 1.40/mysql-test/t/join_outer.test 2007-05-26 10:33:06 -07:00
+++ 1.41/mysql-test/t/join_outer.test 2007-05-26 10:33:06 -07:00
@@ -845,3 +845,19 @@
show status like 'Handler_read%';
DROP TABLE t1,t2;
+
+#
+# Bug 28571: outer join with false on condition over constant tables
+#
+
+CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL);
+INSERT INTO t1 VALUES (1,0), (2,1);
+CREATE TABLE t2 (d int PRIMARY KEY);
+INSERT INTO t2 VALUES (1), (2), (3);
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
+
+DROP TABLE t1,t2;
+
| Thread |
|---|
| • bk commit into 5.0 tree (igor:1.2503) BUG#28571 | igor | 26 May |
