#At file:///home/oysteing/mysql/mysql-6.0-codebase-bugfixing/ based on revid:jon.hauglid@stripped
3703 oystein.grovlen@stripped 2009-11-11
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 quick select for a constant reference, check whether
the ref contain null constants for key parts where NULLs should not match.
If so, create an object of the new class QUICK_EMPTY_SELECT instead of an
ordinary QUICK_SELECT_RANGE. QUICK_EMPTY_SELECT represent a quick select with 0
rows.
@ mysql-test/r/join_optimizer.result
Result for new test case
@ mysql-test/t/join_optimizer.test
Add test case for Bug#47217
@ sql/opt_range.cc
get_quick_select_for_ref now returns pointer to a QUICK_EMPTY_SELECT object if ref could not possibly match any rows. That is, if part of the ref key is a NULL constant, and NULL should not match other NULLs for this part.
Initialize members of QUICK_SELECT_I in constructor to make sure unitialized members will not create surprises for subclasses.
@ sql/opt_range.h
Added new class QUICK_EMPTY_SELECT as a subclass of QUICK_SELECT_I.
It represents a quick select with no rows.
Change *get_quick_select_for_ref to return QUICK_SELECT_I*. This way, it
may return a pointer to either a QUICK_RANGE_SELECT object or a
QUICK_EMPTY_SELECT object.
@ sql/sql_select.h
Add a method impossible_null_ref() to TABLE_REF. This method will check
whether the reference contains NULL values so that it could never match any
rows.
modified:
mysql-test/r/join_optimizer.result
mysql-test/t/join_optimizer.test
sql/opt_range.cc
sql/opt_range.h
sql/sql_select.h
=== 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-11-11 13:20:12 +0000
@@ -35,3 +35,25 @@ 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);
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v ORDER BY 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system ix2 NULL NULL NULL 1 Using filesort
+1 SIMPLE t1 ref ix1 ix1 5 const 1 Using where
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system ix2 NULL NULL NULL 1
+1 SIMPLE t1 ref ix1 ix1 5 const 1
+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-11-11 13:20:12 +0000
@@ -43,3 +43,23 @@ 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);
+
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v ORDER BY 1;
+EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.v = t2.v;
+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/opt_range.cc'
--- a/sql/opt_range.cc 2009-11-06 07:40:19 +0000
+++ b/sql/opt_range.cc 2009-11-11 13:20:12 +0000
@@ -1147,8 +1147,8 @@ SQL_SELECT::~SQL_SELECT()
#undef index // Fix for Unixware 7
QUICK_SELECT_I::QUICK_SELECT_I()
- :max_used_key_length(0),
- used_key_parts(0)
+ :records(0), read_time(0), head(0), index(0), max_used_key_length(0),
+ used_key_parts(0), last_rowid(0), record(0)
{}
QUICK_RANGE_SELECT::QUICK_RANGE_SELECT(THD *thd, TABLE *table, uint key_nr,
@@ -8115,7 +8115,7 @@ key_has_nulls(const KEY* key_info, const
NULL on error.
*/
-QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
+QUICK_SELECT_I *get_quick_select_for_ref(THD *thd, TABLE *table,
TABLE_REF *ref, ha_rows records)
{
MEM_ROOT *old_root, *alloc;
@@ -8127,6 +8127,9 @@ QUICK_RANGE_SELECT *get_quick_select_for
bool create_err= FALSE;
COST_VECT cost;
+ if (ref->impossible_null_ref())
+ return new QUICK_EMPTY_SELECT();
+
old_root= thd->mem_root;
/* The following call may change thd->mem_root */
quick= new QUICK_RANGE_SELECT(thd, table, ref->key, 0, 0, &create_err);
=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h 2009-09-30 15:24:05 +0000
+++ b/sql/opt_range.h 2009-11-11 13:20:12 +0000
@@ -203,7 +203,8 @@ public:
QS_TYPE_FULLTEXT = 3,
QS_TYPE_ROR_INTERSECT = 4,
QS_TYPE_ROR_UNION = 5,
- QS_TYPE_GROUP_MIN_MAX = 6
+ QS_TYPE_GROUP_MIN_MAX = 6,
+ QS_TYPE_EMPTY = 7
};
/* Get type of this quick select - one of the QS_TYPE_* values */
@@ -271,6 +272,27 @@ public:
};
+/**
+ * Class representing a quick select with no rows.
+ */
+class QUICK_EMPTY_SELECT: public QUICK_SELECT_I
+{
+public:
+ virtual int init() { return 0; }
+ virtual int reset() { return 0; }
+ virtual int get_next() { return HA_ERR_END_OF_FILE; }
+ virtual bool reverse_sorted() { return false; }
+ virtual void need_sorted_output() {}
+ virtual int get_type() { return QS_TYPE_EMPTY; }
+ virtual void add_keys_and_lengths(String*, String*) {}
+#ifndef DBUG_OFF
+ virtual void dbug_dump(int indent, bool verbose)
+ {
+ fprintf(DBUG_FILE, "%*squick empty selects",indent, "");
+ }
+#endif
+};
+
struct st_qsel_param;
class PARAM;
class SEL_ARG;
@@ -355,10 +377,9 @@ public:
private:
/* Default copy ctor used by QUICK_SELECT_DESC */
friend class TRP_ROR_INTERSECT;
- friend
- QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
- struct st_table_ref *ref,
- ha_rows records);
+ friend QUICK_SELECT_I *get_quick_select_for_ref(THD *thd, TABLE *table,
+ struct st_table_ref *ref,
+ ha_rows records);
friend bool get_quick_keys(PARAM *param, QUICK_RANGE_SELECT *quick,
KEY_PART *key, SEL_ARG *key_tree,
uchar *min_key, uint min_key_flag,
@@ -780,9 +801,9 @@ public:
};
FT_SELECT *get_ft_select(THD *thd, TABLE *table, uint key);
-QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
- struct st_table_ref *ref,
- ha_rows records);
+QUICK_SELECT_I *get_quick_select_for_ref(THD *thd, TABLE *table,
+ struct st_table_ref *ref,
+ ha_rows records);
uint get_index_for_order(TABLE *table, ORDER *order, ha_rows limit);
#ifdef WITH_PARTITION_STORAGE_ENGINE
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2009-10-26 14:02:26 +0000
+++ b/sql/sql_select.h 2009-11-11 13:20:12 +0000
@@ -103,6 +103,25 @@ typedef struct st_table_ref
produce different results (because of Index Condition Pushdown)
*/
bool disable_cache;
+
+ /**
+ * Check if reference contains NULL values so that it could never
+ * match any rows.
+ *
+ * @return
+ * TRUE if part of key is a NULL constant, and null_rejecting is set for
+ * this part.
+ * FALSE otherwise
+ */
+ bool impossible_null_ref()
+ {
+ for (uint part=0; part < key_parts; part++)
+ if ((null_rejecting & (1 << part)) &&
+ items[part]->const_item() && items[part]->is_null())
+ return true;
+ return false;
+ }
+
} TABLE_REF;
Attachment: [text/bzr-bundle]
