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:
None 
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:18] Alexander Barkov
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.
[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.