Hello Igor,
Could you please review the below? The bug page
https://bugs.launchpad.net/maria/+bug/727667 has verbose explanations of how we
ended up having this bug, as well as justifications for fixing it in this
particular way.
----- Forwarded message from Sergey Petrunya -----
From: Sergey Petrunya
To: commits@mariadb.org
X-Mailer: mail (GNU Mailutils 1.2)
Date: Sat, 5 Mar 2011 12:56:26 +0300 (MSK)
Subject: [Commits] Rev 2933: BUG#727667 Wrong result with OR + NOT NULL in
maria-5.3 in file:///home/psergey/dev2/5.3/
At file:///home/psergey/dev2/5.3/
------------------------------------------------------------
revno: 2933
revision-id: psergey@askmonty.org-20110305095622-wzlnwr9srfbsu3pv
parent: psergey@askmonty.org-20110304091446-qmsezak3c0cfm9b1
committer: Sergey Petrunya
branch nick: 5.3
timestamp: Sat 2011-03-05 12:56:22 +0300
message:
BUG#727667 Wrong result with OR + NOT NULL in maria-5.3
- put the code that sets HA_NULL_PART bit back
- Fix test_if_ref/part_of_refkey() so that
= NULL-ability of lookup columns does not prevent the equality
from being removed (we now have early/late NULLs filtering which
will filter out NULL values)
= equality is not removed if it is ref_or_null access, and the value
of the lookup column can alternate between the lookup value and NULL.
=== modified file 'mysql-test/r/null.result'
--- a/mysql-test/r/null.result 2009-12-15 07:16:46 +0000
+++ b/mysql-test/r/null.result 2011-03-05 09:56:22 +0000
@@ -170,7 +170,7 @@
insert into t1 values(null);
explain select * from t1 where i=2 or i is null;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null i i 5 const 9 Using index
+1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index
select count(*) from t1 where i=2 or i is null;
count(*)
10
=== modified file 'mysql-test/r/null_key.result'
--- a/mysql-test/r/null_key.result 2010-10-06 20:27:12 +0000
+++ b/mysql-test/r/null_key.result 2011-03-05 09:56:22 +0000
@@ -21,10 +21,10 @@
1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index
explain select * from t1 where (a is null or a = 7) and b=7;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index
+1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index
explain select * from t1 where (a is null or a = 7) and b=7 order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index; Using filesort
+1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index
@@ -151,7 +151,7 @@
insert into t1 values (7,null), (8,null), (8,7);
explain select * from t1 where a = 7 and (b=7 or b is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using index
+1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index
select * from t1 where a = 7 and (b=7 or b is null);
a b
7 7
@@ -166,7 +166,7 @@
NULL 7
explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a a 5 const 5 Using index
+1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index
select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
a b
7 NULL
@@ -192,7 +192,7 @@
explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
-1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index
+1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index
select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
a a b
7 7 7
@@ -202,7 +202,7 @@
explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index
+1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index
select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
a a b
7 7 7
@@ -226,7 +226,7 @@
explain select * from t2,t1 where t1.a=t2.a or t1.a is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 4
-1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using index
+1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index
explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 4
@@ -447,3 +447,15 @@
3 12 0 12
drop table t1, t2;
End of 5.0 tests
+#
+# BUG#727667 Wrong result with OR + NOT NULL in maria-5.3
+#
+CREATE TABLE t1 (
+f3 int(11),
+f10 varchar(1),
+KEY (f3)
+);
+INSERT INTO t1 VALUES ('9','k'),(NULL,'r');
+SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL);
+f3 f10
+DROP TABLE t1;
=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result 2011-01-27 04:45:23 +0000
+++ b/mysql-test/r/order_by.result 2011-03-05 09:56:22 +0000
@@ -643,7 +643,7 @@
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
explain select * from t1 where b=1 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 3 Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort
select * from t1 where b=1 or b is null order by a;
a b
1 1
@@ -652,7 +652,7 @@
4 NULL
explain select * from t1 where b=2 or b is null order by a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null b b 5 const 4 Using filesort
+1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort
select * from t1 where b=2 or b is null order by a;
a b
3 NULL
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2011-02-22 09:15:47 +0000
+++ b/mysql-test/r/subselect.result 2011-03-05 09:56:22 +0000
@@ -906,7 +906,7 @@
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<`test`.`t1`.`a`>((`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2011-02-22 09:15:47 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2011-03-05 09:56:22 +0000
@@ -910,7 +910,7 @@
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<`test`.`t1`.`a`>((`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2011-02-22 09:15:47 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2011-03-05 09:56:22 +0000
@@ -907,7 +907,7 @@
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<`test`.`t1`.`a`>((`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2011-02-22 09:15:47 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2011-03-05 09:56:22 +0000
@@ -907,7 +907,7 @@
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<`test`.`t1`.`a`>((`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having (`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
=== modified file 'mysql-test/t/null_key.test'
--- a/mysql-test/t/null_key.test 2008-03-03 17:35:44 +0000
+++ b/mysql-test/t/null_key.test 2011-03-05 09:56:22 +0000
@@ -263,3 +263,16 @@
drop table t1, t2;
-- echo End of 5.0 tests
+--echo #
+--echo # BUG#727667 Wrong result with OR + NOT NULL in maria-5.3
+--echo #
+
+CREATE TABLE t1 (
+ f3 int(11),
+ f10 varchar(1),
+ KEY (f3)
+);
+INSERT INTO t1 VALUES ('9','k'),(NULL,'r');
+SELECT * FROM t1 WHERE (f3 = 83) OR (f10 = 'z' AND f3 IS NULL);
+DROP TABLE t1;
+
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-02-28 21:29:59 +0000
+++ b/sql/sql_select.cc 2011-03-05 09:56:22 +0000
@@ -6564,10 +6564,12 @@
j->ref.has_record= FALSE;
j->ref.null_rejecting= 0;
j->ref.disable_cache= FALSE;
+ j->ref.null_ref_part= (uint)-1;
keyuse=org_keyuse;
store_key **ref_key= j->ref.key_copy;
uchar *key_buff=j->ref.key_buff, *null_ref_key= 0;
+ uint null_ref_part= (uint)-1;
bool keyuse_uses_no_tables= TRUE;
if (ftkey)
{
@@ -6621,7 +6623,10 @@
instead of JT_REF_OR_NULL in case if field can't be null
*/
if ((keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) && maybe_null)
+ {
null_ref_key= key_buff;
+ null_ref_part= i;
+ }
key_buff+= keyinfo->key_part[i].store_length;
}
} /* not ftkey */
@@ -6636,6 +6641,7 @@
/* Must read with repeat */
j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
j->ref.null_ref_key= null_ref_key;
+ j->ref.null_ref_part= null_ref_part;
}
else if (keyuse_uses_no_tables)
{
@@ -15287,8 +15293,12 @@
*****************************************************************************/
/**
+ Check if "left_item=right_item" equality is guaranteed to be true by use of
+ [eq]ref access on left_item->field->table.
+
@return
- 1 if right_item is used removable reference key on left_item
+ TRUE if right_item is used removable reference key on left_item
+ FALSE otherwise
*/
bool test_if_ref(Item *root_cond, Item_field *left_item,Item *right_item)
@@ -15301,7 +15311,11 @@
(!join_tab->first_inner ||
*join_tab->first_inner->on_expr_ref == root_cond))
{
- // Cond guards
+ /*
+ If ref access uses "Full scan on NULL key" (i.e. it actually alternates
+ between ref access and full table scan), then no equality can be
+ guaranteed to be true.
+ */
for (uint i = 0; i < join_tab->ref.key_parts; i++)
{
if (join_tab->ref.cond_guards[i])
@@ -15309,7 +15323,7 @@
return FALSE;
}
}
- //
+
Item *ref_item=part_of_refkey(field->table,field);
if (ref_item && ref_item->eq(right_item,1))
{
@@ -15611,6 +15625,29 @@
}
+/*
+ @brief
+
+ Check if
+ - @table uses "ref"-like access
+ - it is based on "@field=certain_item" equality
+ - the equality will be true for any record returned by the access method
+ and return the certain_item if yes.
+
+ @detail
+
+ Equality won't necessarily hold if:
+ - the used index covers only part of the @field.
+ Suppose, we have a CHAR(5) field and INDEX(field(3)). if you make a lookup
+ for 'abc', you will get both record with 'abc' and with 'abcde'.
+ - The type of access is actually ref_or_null, and so @field can be either
+ a value or NULL.
+
+ @return
+ Item that the field will be equal to
+ NULL if no such item
+*/
+
static Item *
part_of_refkey(TABLE *table,Field *field)
{
@@ -15619,17 +15656,30 @@
return (Item*) 0; // field from outer non-select (UPDATE,...)
uint ref_parts= join_tab->ref.key_parts;
- if (ref_parts)
+ if (ref_parts) /* if it's ref/eq_ref/ref_or_null */
{
-
uint key= join_tab->ref.key;
KEY *key_info= join_tab->get_keyinfo_by_key_no(key);
KEY_PART_INFO *key_part= key_info->key_part;
for (uint part=0 ; part < ref_parts ; part++,key_part++)
- if (field->eq(key_part->field) &&
- !(key_part->key_part_flag & (HA_PART_KEY_SEG | HA_NULL_PART)))
- return join_tab->ref.items[part];
+ {
+ if (field->eq(key_part->field))
+ {
+ /*
+ Found the field in the key. Check that
+ 1. ref_or_null doesn't alternate this component between a value and
+ a NULL
+ 2. index fully covers the key
+ */
+ if (part != join_tab->ref.null_ref_part && // (1)
+ !(key_part->key_part_flag & HA_PART_KEY_SEG)) // (2)
+ {
+ return join_tab->ref.items[part];
+ }
+ break;
+ }
+ }
}
return (Item*) 0;
}
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2011-02-24 06:23:12 +0000
+++ b/sql/sql_select.h 2011-03-05 09:56:22 +0000
@@ -109,8 +109,15 @@
*/
key_part_map null_rejecting;
table_map depend_map; ///< Table depends on these tables.
+
/* null byte position in the key_buf. Used for REF_OR_NULL optimization */
uchar *null_ref_key;
+ /*
+ ref_or_null optimization: number of key part can be NULL (there's only one)
+ uint(-1) if no part can be NULL.
+ */
+ uint null_ref_part;
+
/*
The number of times the record associated with this key was used
in the join.
=== modified file 'sql/table.cc'
--- a/sql/table.cc 2011-02-22 09:15:47 +0000
+++ b/sql/table.cc 2011-03-05 09:56:22 +0000
@@ -1581,6 +1581,8 @@
#endif
key_part->key_part_flag|= HA_PART_KEY_SEG;
}
+ if (field->real_maybe_null())
+ key_part->key_part_flag|= HA_NULL_PART;
/*
Sometimes we can compare key parts for equality with memcmp.
But not always.
_______________________________________________
commits mailing list
commits@mariadb.org
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
----- End forwarded message -----
--
BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
