diff options
author | unknown <sergefp@mysql.com> | 2006-05-06 13:15:00 +0400 |
---|---|---|
committer | unknown <sergefp@mysql.com> | 2006-05-06 13:15:00 +0400 |
commit | c13144722aaf7892a64d6fdeffc130dcda041ab3 (patch) | |
tree | 73272c9d7a5faf37a7090ebfec3c556662b7c988 /mysql-test/t/innodb_mysql.test | |
parent | 31ecb598829584944e0ea1ab6c775cfc6c51df39 (diff) | |
download | mariadb-git-c13144722aaf7892a64d6fdeffc130dcda041ab3.tar.gz |
BUG#16798: Inapplicable ref_or_null query plan and bad query result on random occasions
The bug was as follows: When merge_key_fields() encounters "t.key=X OR t.key=Y" it will
try to join them into ref_or_null access via "t.key=X OR NULL". In order to make this
inference it checks if Y<=>NULL, ignoring the fact that value of Y may be not yet known.
The fix is that the check if Y<=>NULL is made only if value of Y is known (i.e. it is a
constant).
TODO: When merging to 5.0, replace used_tables() with const_item() everywhere in merge_key_fields().
mysql-test/r/innodb_mysql.result:
Testcase for BUG16798
mysql-test/t/innodb_mysql.test:
Testcase for BUG16798
sql/sql_select.cc:
BUG#16798: Inapplicable ref_or_null query plan and bad query result on random occasions
In merge_key_fields() don't call val->is_null() if the value of val is not known.
Diffstat (limited to 'mysql-test/t/innodb_mysql.test')
-rw-r--r-- | mysql-test/t/innodb_mysql.test | 56 |
1 files changed, 55 insertions, 1 deletions
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index b942b9fbc0d..f31e4d64789 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -1,5 +1,59 @@ -- source include/have_innodb.inc --disable_warnings -drop table if exists t1; +drop table if exists t1,t2; --enable_warnings + +# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer +# (repeatable only w/innodb). +create table t1 ( + c_id int(11) not null default '0', + org_id int(11) default null, + unique key contacts$c_id (c_id), + key contacts$org_id (org_id) +) engine=innodb; +insert into t1 values + (2,null),(120,null),(141,null),(218,7), (128,1), + (151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3), + (246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4); + +create table t2 ( + slai_id int(11) not null default '0', + owner_tbl int(11) default null, + owner_id int(11) default null, + sla_id int(11) default null, + inc_web int(11) default null, + inc_email int(11) default null, + inc_chat int(11) default null, + inc_csr int(11) default null, + inc_total int(11) default null, + time_billed int(11) default null, + activedate timestamp null default null, + expiredate timestamp null default null, + state int(11) default null, + sla_set int(11) default null, + unique key t2$slai_id (slai_id), + key t2$owner_id (owner_id), + key t2$sla_id (sla_id) +) engine=innodb; +insert into t2(slai_id, owner_tbl, owner_id, sla_id) values + (1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7), + (8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12); + +flush tables; +select si.slai_id +from t1 c join t2 si on + ((si.owner_tbl = 3 and si.owner_id = c.org_id) or + ( si.owner_tbl = 2 and si.owner_id = c.c_id)) +where + c.c_id = 218 and expiredate is null; + +select * from t1 where org_id is null; +select si.slai_id +from t1 c join t2 si on + ((si.owner_tbl = 3 and si.owner_id = c.org_id) or + ( si.owner_tbl = 2 and si.owner_id = c.c_id)) +where + c.c_id = 218 and expiredate is null; + +drop table t1, t2; |