#At file:///localhome/jl208045/mysql/mysql-6.0-codebase-bugfixing-45221/ based on revid:alik@stripped
3728 Jorgen Loland 2009-11-24
Bug#45221: Query "SELECT pk FROM C WHERE pk IN (SELECT int_key)"
failing
XOR conditions are not optimized, and Item_cond_xor therefore
acts like type Func_item even though it inherits from Item_cond.
A subtle difference between Item_func and Item_cond is that
you can get the children Items from the former by calling
arguments(), and from the latter by calling argument_list().
However, since Item_cond_xor inherits from Item_cond,
arguments() did not return any Items.
The fact that Item_cond_xor::arguments() did not return it's
children items lead to a problem for make_cond_for_index();
the method accepted that XOR items on unindexed columns were
pushed using ICP. ICP evaluation of non-indexed columns
does not (and should not) work.
The fix for this bug is to make Item_cond_xor return it's
children items when the arguments() method is used. This makes
Item_cond_xor behave more like Item_func and in turn allows
make_cond_for_index() to discover any conflicting children
Items.
This is a temporary fix and should be removed when
Item_cond_xor is optimized.
@ mysql-test/r/subselect4.result
Added test for BUG#45221
@ mysql-test/t/subselect4.test
Added test for BUG#45221
@ sql/item_cmpfunc.h
Store children items of Item_cond_xor both in list (as is done by
Item_cond) and args[] (as is done by Func_cond) as a temporary
solution until Item_cond_xor is optimized.
modified:
mysql-test/r/subselect4.result
mysql-test/t/subselect4.test
sql/item_cmpfunc.h
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result 2009-11-03 09:42:49 +0000
+++ b/mysql-test/r/subselect4.result 2009-11-24 11:53:28 +0000
@@ -131,3 +131,38 @@ set @@session.optimizer_switch
@@session.optimizer_use_mrr = @old_optimizer_use_mrr,
@@session.engine_condition_pushdown = @old_engine_condition_pushdown;
DROP TABLE t1;
+#
+# BUG#45221 Query SELECT pk FROM C WHERE pk IN (SELECT int_key) failing
+#
+CREATE TABLE t1 (
+i1_key INT,
+i2 INT,
+i3 INT,
+KEY i1_index (i1_key)
+);
+INSERT INTO t1 VALUES (9,1,2), (9,2,1);
+CREATE TABLE t2 (
+pk INT NOT NULL,
+i1 INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (9,1);
+# Enable Index condition pushdown
+SELECT @old_icp:=@@engine_condition_pushdown;
+@old_icp:=@@engine_condition_pushdown
+#
+SET SESSION engine_condition_pushdown = 'ON';
+
+SELECT pk
+FROM t2
+WHERE
+pk IN (
+SELECT i1_key
+FROM t1
+WHERE t1.i2 < t1.i3 XOR t2.i1 > 1
+ORDER BY t1.i2 desc);
+pk
+9
+# Restore old value for Index condition pushdown
+SET SESSION engine_condition_pushdown=@old_icp;
+DROP TABLE t1,t2;
=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test 2009-10-25 13:41:27 +0000
+++ b/mysql-test/t/subselect4.test 2009-11-24 11:53:28 +0000
@@ -121,3 +121,45 @@ set @@session.optimizer_switch
@@session.engine_condition_pushdown = @old_engine_condition_pushdown;
DROP TABLE t1;
+
+
+--echo #
+--echo # BUG#45221 Query SELECT pk FROM C WHERE pk IN (SELECT int_key) failing
+--echo #
+
+CREATE TABLE t1 (
+ i1_key INT,
+ i2 INT,
+ i3 INT,
+ KEY i1_index (i1_key)
+);
+
+INSERT INTO t1 VALUES (9,1,2), (9,2,1);
+
+CREATE TABLE t2 (
+ pk INT NOT NULL,
+ i1 INT,
+ PRIMARY KEY (pk)
+);
+
+INSERT INTO t2 VALUES (9,1);
+
+--echo # Enable Index condition pushdown
+--replace_column 1 #
+SELECT @old_icp:=@@engine_condition_pushdown;
+SET SESSION engine_condition_pushdown = 'ON';
+
+--echo
+SELECT pk
+FROM t2
+WHERE
+ pk IN (
+ SELECT i1_key
+ FROM t1
+ WHERE t1.i2 < t1.i3 XOR t2.i1 > 1
+ ORDER BY t1.i2 desc);
+
+--echo # Restore old value for Index condition pushdown
+SET SESSION engine_condition_pushdown=@old_icp;
+
+DROP TABLE t1,t2;
=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h 2009-11-09 10:27:46 +0000
+++ b/sql/item_cmpfunc.h 2009-11-24 11:53:28 +0000
@@ -1721,14 +1721,31 @@ inline bool is_cond_or(Item *item)
class Item_cond_xor :public Item_cond
{
public:
- Item_cond_xor() :Item_cond() {}
- Item_cond_xor(Item *i1,Item *i2) :Item_cond(i1,i2) {}
+ Item_cond_xor(Item *i1,Item *i2) :Item_cond(i1,i2)
+ {
+ /*
+ Items must be stored in args[] as well because this Item_cond is
+ treated as a FUNC_ITEM (see type()). I.e., users of it will get
+ it's children by calling arguments(), not argument_list(). This
+ is a temporary solution until XOR is optimized and treated like
+ a full Item_cond citizen.
+ */
+ arg_count= 2;
+ args= tmp_arg;
+ args[0]= i1;
+ args[1]= i2;
+ }
enum Functype functype() const { return COND_XOR_FUNC; }
/* TODO: remove the next line when implementing XOR optimization */
enum Type type() const { return FUNC_ITEM; }
longlong val_int();
const char *func_name() const { return "xor"; }
void top_level_item() {}
+ /* Since child Items are stored in args[], Items cannot be added */
+ bool add(Item *item) { return FALSE; }
+ bool add_at_head(Item *item) { return FALSE; }
+ bool add_at_head(List<Item> *nlist) { return FALSE; }
+ void copy_andor_arguments(THD *thd, Item_cond *item) { DBUG_ASSERT(FALSE); }
};
Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20091124115328-qhn3iw0axjb0ffsg.bundle
