| Bug #49199 | Optimizer handles incorrectly: field='const1' AND field='const2' in some cases | ||
|---|---|---|---|
| Submitted: | 30 Nov 2009 10:18 | Modified: | 12 Mar 2010 16:41 |
| Reporter: | Alexander Barkov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.5, 5.0, 5.1 bzr | OS: | Any |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
| Tags: | regression | ||
[30 Nov 2009 10:19]
Alexander Barkov
The same happends for DATE type:
drop table if exists t1; create table t1 (a date not null); insert into t1 values ('2001-01-01'); select * from t1 where a='2001-01-01'; select * from t1 where a='2001-01-01 00:00:00'; select * from t1 where a='2001-01-01' and a='2001-01-01 00:00:00'; explain select * from t1 where a='2001-01-01' and a='2001-01-01 00:00:00';
[30 Nov 2009 10:21]
Alexander Barkov
The same happens for timestamp:
drop table if exists t1; create table t1 (a timestamp not null); insert into t1 values ('2001-01-01'); select * from t1 where a='2001-01-01'; select * from t1 where a='2001-01-01 00:00:00'; select * from t1 where a='2001-01-01' and a='2001-01-01 00:00:00'; explain select * from t1 where a='2001-01-01' and a='2001-01-01 00:00:00';
[30 Nov 2009 10:31]
Sveta Smirnova
Thank you for the report. Verified as described. Version 4.1 is not affected.
[2 Dec 2009 15:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/92500 2858 Ramil Kalimullin 2009-12-02 Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases Building multiple equality predicates containing a constant which is compared as a datetime (with a field) we should take this fact into account and compare the constant with another possible constatns as datetimes as well. E.g. for the SELECT ... WHERE a='2001-01-01' AND a='2001-01-01 00:00:00' we should compare '2001-01-01' with '2001-01-01 00:00:00' as datetimes but not as strings. @ mysql-test/r/select.result Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - test result. @ mysql-test/t/select.test Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - test case. @ sql/item_cmpfunc.cc Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - adding a constant to Item_equal compare it as a datetime value with stored one if there's a date[time] field in a equality predicate. @ sql/item_cmpfunc.h Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - adding a constant to Item_equal compare it as a datetime value with stored one if there's a date[time] field in a equality predicate. @ sql/sql_select.cc Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - adding a constant to Item_equal compare it as a datetime value with stored one if there's a date[time] field in a equality predicate.
[4 Dec 2009 17:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/92932 2860 Ramil Kalimullin 2009-12-04 Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases Building multiple equality predicates containing a constant which is compared as a datetime (with a field) we should take this fact into account and compare the constant with another possible constatns as datetimes as well. E.g. for the SELECT ... WHERE a='2001-01-01' AND a='2001-01-01 00:00:00' we should compare '2001-01-01' with '2001-01-01 00:00:00' as datetimes but not as strings. @ mysql-test/r/select.result Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - test result. @ mysql-test/t/select.test Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - test case. @ sql/item_cmpfunc.cc Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - adding a constant to Item_equal compare it as a datetime value with stored one if there's a date[time] field in a equality predicate. @ sql/item_cmpfunc.h Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - adding a constant to Item_equal compare it as a datetime value with stored one if there's a date[time] field in a equality predicate. @ sql/sql_select.cc Fix for bug#49199: Optimizer handles incorrectly: field='const1' AND field='const2' in some cases - adding a constant to Item_equal compare it as a datetime value with stored one if there's a date[time] field in a equality predicate.
[11 Dec 2009 21:07]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/93764 2944 Alexey Kopytov 2009-12-12 Streamlined the test case for bug #49199 in mysql-trunk-merge to take into account the changes introduced by the fix for bug #30302.
[19 Dec 2009 8:29]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216181636-4z4tj1h72l0j6lk6) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:32]
Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091211210654-y9l0u49361sfby1l) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:36]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[8 Jan 2010 1:13]
Paul DuBois
Noted in 5.5.1, 6.0.14 changelogs. The optimizer sometimes incorrectly handled conditions of the form WHERE col_name='const1' AND col_name='const2'. Setting report to NDI pending push to Celosia.
[14 Jan 2010 8:26]
Bugs System
Pushed into 5.0.90 (revid:joro@sun.com-20100114082402-05fod2h6z9x9wok8) (version source revid:aelkin@mysql.com-20091214144410-630vanwyllvvacad) (merge vers: 5.0.89) (pib:16)
[14 Jan 2010 18:31]
Paul DuBois
Noted in 5.0.90 changelog. Setting report to NDI pending push to 5.1.x, Celosia.
[15 Jan 2010 8:59]
Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:ramil@mysql.com-20091210063152-44ciz4iz62ctqczq) (merge vers: 5.1.42) (pib:16)
[15 Jan 2010 18:42]
Paul DuBois
Noted in 5.1.43 changelog. Setting report to NDI pending push to Celosia.
[12 Mar 2010 14:08]
Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:24]
Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:38]
Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 16:41]
Paul DuBois
Fixed in earlier 5.1.x, 5.5.x.
Description: Optimizer incorrectly reports "Impossible WHERE condition" for some data types in cases like this: SELECT .. WHERE field='const1'; -- returns one row SELECT .. WHERE field='const2'; -- returns exactly the same row SELECT .. WHERE field='const1' AND field='const2'; -- Empty set: Impossible WHERE How to repeat: mysql> drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql> create table t1 (a datetime not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values ('2001-01-01'); Query OK, 1 row affected (0.00 sec) mysql> select * from t1 where a='2001-01-01'; +---------------------+ | a | +---------------------+ | 2001-01-01 00:00:00 | -- Good result +---------------------+ 1 row in set (0.00 sec) mysql> select * from t1 where a='2001-01-01 00:00:00'; +---------------------+ | a | +---------------------+ | 2001-01-01 00:00:00 | -- Also good result, returns the same row. +---------------------+ 1 row in set (0.00 sec) -- Now mix both conditions using AND mysql> select * from t1 where a='2001-01-01' AND a='2001-01-01 00:00:00'; Empty set (0.00 sec) <-- Oops. Why empty set ??? mysql> explain select * from t1 where a='2001-01-01' and a='2001-01-01 00:00:00'; +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ 1 row in set (0.00 sec) It seems optimizer converts the above condition to: WHERE a='2001-01-01' AND '2001-01-01'='2001-01-01 00:00:00'; but then the condition fails to return the row, because the constants are compared as strings, instead of datetime values. Suggested fix: Fix optimizer to return the same row when using AND condition.