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-03-11 23:34:40-07:00, igor@stripped +3 -0
Fixed bug #26963: invalid optimization of the pushdown conditions
after single-row table substitution could lead to a wrong result set.
The bug happened because the function Item_field::replace_equal_field
erroniously assumed that any field included in a multiple equality
with a constant has been already substituted for this constant.
This not true for fields becoming constant after row substitutions
for constant tables.
mysql-test/r/select.result@stripped, 2007-03-11 23:34:38-07:00, igor@stripped +38 -0
Added a test case for bug #26963.
mysql-test/t/select.test@stripped, 2007-03-11 23:34:39-07:00, igor@stripped +47 -0
Added a test case for bug #26963.
sql/item.cc@stripped, 2007-03-11 23:34:39-07:00, igor@stripped +13 -2
Fixed bug #26963: invalid optimization of the pushdown conditions
after single-row table substitution could lead to a wrong result set.
The bug happened because the function Item_field::replace_equal_field
erroneously assumed that any field included in a multiple equality
with a constant has been already substituted for this constant.
This not true for fields becoming constant after row substitutions
for constant tables.
# 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-bug26963
--- 1.255/sql/item.cc 2007-03-11 23:34:46 -07:00
+++ 1.256/sql/item.cc 2007-03-11 23:34:46 -07:00
@@ -4075,7 +4075,9 @@
DESCRIPTION
The function returns a pointer to an item that is taken from
the very beginning of the item_equal list which the Item_field
- object refers to (belongs to).
+ object refers to (belongs to) unless item_equal contains a constant
+ item. In this case the function returns this constant item,
+ (if the substitution does not require conversion).
If the Item_field object does not refer any Item_equal object
'this' is returned
@@ -4084,7 +4086,8 @@
of the thransformer method.
RETURN VALUES
- pointer to a replacement Item_field if there is a better equal item;
+ pointer to a replacement Item_field if there is a better equal item or
+ a pointer to a constant equal item;
this - otherwise.
*/
@@ -4092,6 +4095,14 @@
{
if (item_equal)
{
+ Item *const_item= item_equal->get_const();
+ if (const_item)
+ {
+ if (cmp_context != (Item_result)-1 &&
+ const_item->cmp_context != cmp_context)
+ return this;
+ return const_item;
+ }
Item_field *subst= item_equal->get_first();
if (subst && !field->eq(subst->field))
return subst;
--- 1.151/mysql-test/r/select.result 2007-03-11 23:34:46 -07:00
+++ 1.152/mysql-test/r/select.result 2007-03-11 23:34:46 -07:00
@@ -3933,4 +3933,42 @@
aa aa 2
aa aa 2
DROP TABLE t1,t2;
+CREATE TABLE t1 (
+access_id int NOT NULL default '0',
+name varchar(20) default NULL,
+rank int NOT NULL default '0',
+KEY idx (access_id)
+);
+CREATE TABLE t2 (
+faq_group_id int NOT NULL default '0',
+faq_id int NOT NULL default '0',
+access_id int default NULL,
+UNIQUE KEY idx1 (faq_id),
+KEY idx2 (faq_group_id,faq_id)
+);
+INSERT INTO t1 VALUES
+(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+(261,265,1),(490,494,1);
+SELECT t2.faq_id
+FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+SELECT t2.faq_id
+FROM t1 INNER JOIN t2
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+DROP TABLE t1,t2;
End of 5.0 tests
--- 1.125/mysql-test/t/select.test 2007-03-11 23:34:46 -07:00
+++ 1.126/mysql-test/t/select.test 2007-03-11 23:34:46 -07:00
@@ -3299,4 +3299,51 @@
DROP TABLE t1,t2;
+
+#
+# Bug #26963: join with predicates that contain fields from equalities evaluated
+# to constants after constant table substitution
+#
+
+CREATE TABLE t1 (
+ access_id int NOT NULL default '0',
+ name varchar(20) default NULL,
+ rank int NOT NULL default '0',
+ KEY idx (access_id)
+);
+
+CREATE TABLE t2 (
+ faq_group_id int NOT NULL default '0',
+ faq_id int NOT NULL default '0',
+ access_id int default NULL,
+ UNIQUE KEY idx1 (faq_id),
+ KEY idx2 (faq_group_id,faq_id)
+);
+
+INSERT INTO t1 VALUES
+ (1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+ (261,265,1),(490,494,1);
+
+
+SELECT t2.faq_id
+ FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+ ON (t1.access_id = t2.access_id)
+ LEFT JOIN t2 t
+ ON (t.faq_group_id = t2.faq_group_id AND
+ find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+ WHERE
+ t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+SELECT t2.faq_id
+ FROM t1 INNER JOIN t2
+ ON (t1.access_id = t2.access_id)
+ LEFT JOIN t2 t
+ ON (t.faq_group_id = t2.faq_group_id AND
+ find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+ WHERE
+ t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests
| Thread |
|---|
| • bk commit into 5.0 tree (igor:1.2475) BUG#26963 | igor | 12 Mar |
