#At file:///home/oysteing/mysql/mysql-6.0-codebase-bugfixing/ based on revid:davi.arnaut@stripped
2838 oystein.grovlen@stripped 2009-09-24
Bug#47217 Simple Inner join gives wrong result when order by is used
Problem: Join matches on NULL values when filesort select predicate contain
NULL value from const table propagation.
Solution: When creating a condition for a const reference, make sure to use
an equal comparison that does not consider NULL values to be equal.
@ mysql-test/r/join_optimizer.result
Result for new test case
@ mysql-test/t/join_optimizer.test
Add test case for Bug#47217
@ sql/sql_select.cc
When creating a condition for a const reference, use Item_func_eq for
comparison if reference should not match null values. This operator not
consider NULL values to be equal.
modified:
mysql-test/r/join_optimizer.result
mysql-test/t/join_optimizer.test
sql/sql_select.cc
=== modified file 'mysql-test/r/join_optimizer.result'
--- a/mysql-test/r/join_optimizer.result 2009-01-26 19:42:59 +0000
+++ b/mysql-test/r/join_optimizer.result 2009-09-24 13:01:27 +0000
@@ -35,3 +35,17 @@ id select_type table type possible_keys
1 SIMPLE a ALL NULL NULL NULL NULL 2
1 SIMPLE g ref groups_dt groups_dt 70 const,test.a.type 13 Using index condition
drop table t0,t1,t2,t3;
+#
+# BUG#47217 Simple Inner join gives wrong result when order by is used
+#
+CREATE TABLE t1 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix1 ON t1(v);
+CREATE TABLE t2 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix2 ON t2(v);
+INSERT INTO t1 VALUES (10,'a'),(11,NULL);
+INSERT INTO t2 VALUES (10,NULL);
+SELECT * FROM t1 JOIN t2 ON t1.v = t2.v ORDER BY 1;
+pk v pk v
+SELECT * FROM t1 JOIN t2 ON t1.v = t2.v;
+pk v pk v
+DROP TABLE t1, t2;
=== modified file 'mysql-test/t/join_optimizer.test'
--- a/mysql-test/t/join_optimizer.test 2009-01-26 19:42:59 +0000
+++ b/mysql-test/t/join_optimizer.test 2009-09-24 13:01:27 +0000
@@ -43,3 +43,21 @@ SELECT STRAIGHT_JOIN g.id FROM t2 a, t3
WHERE g.domain = 'queue' AND g.type = a.type;
drop table t0,t1,t2,t3;
+
+--echo #
+--echo # BUG#47217 Simple Inner join gives wrong result when order by is used
+--echo #
+CREATE TABLE t1 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix1 ON t1(v);
+CREATE TABLE t2 (pk INT, v VARCHAR(2), PRIMARY KEY(pk));
+CREATE INDEX ix2 ON t2(v);
+
+INSERT INTO t1 VALUES (10,'a'),(11,NULL);
+INSERT INTO t2 VALUES (10,NULL);
+
+SELECT * FROM t1 JOIN t2 ON t1.v = t2.v ORDER BY 1;
+SELECT * FROM t1 JOIN t2 ON t1.v = t2.v;
+
+DROP TABLE t1, t2;
+
+
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-09-21 09:09:24 +0000
+++ b/sql/sql_select.cc 2009-09-24 13:01:27 +0000
@@ -20808,7 +20808,13 @@ static bool add_ref_to_table_cond(THD *t
Field *field=table->field[table->key_info[join_tab->ref.key].key_part[i].
fieldnr-1];
Item *value=join_tab->ref.items[i];
- cond->add(new Item_func_equal(new Item_field(field), value));
+
+ /* Which equal comparator to use, depend on whether nulls is to be
+ considered equal or not. */
+ if (join_tab->keyuse[i].null_rejecting)
+ cond->add(new Item_func_eq(new Item_field(field), value));
+ else
+ cond->add(new Item_func_equal(new Item_field(field), value));
}
if (thd->is_fatal_error)
DBUG_RETURN(TRUE);
Attachment: [text/bzr-bundle]
