Bug #42957 no results from select where .. (col=col and col=col) or ... (false expression)
Submitted: 18 Feb 2009 6:42 Modified: 27 Mar 2009 15:28
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.76, 5.1.31, 6.0.9 OS:Any
Assigned to: Ramil Kalimullin
Tags: regression
Triage: Triaged: D2 (Serious)

[18 Feb 2009 6:42] Shane Bester
Description:
In the second query below, it is expected that entire table is returned since
(TRUE AND TRUE) or FALSE == TRUE.

mysql> select sql_no_cache * from `t1`;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
+---+------+
1 row in set (0.03 sec)

mysql> select sql_no_cache * from `t1` where  (`a`=`a` and `a`=`a`)  or `b` > 2;
Empty set (0.05 sec)

If the column `a` is changed to a nullable column, the results are returned.

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` int not null,`b` int) engine=myisam;
insert into `t1` values (1,1);
select sql_no_cache * from `t1`;
select sql_no_cache * from `t1` where  (`a`=`a` and `a`=`a`)  or `b` > 2;
[18 Feb 2009 7:02] Shane Bester
4.1.24 works as expected
[18 Feb 2009 7:20] Sveta Smirnova
Thank you for the report.

Verified as described.
[16 Mar 2009 5:02] 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/69256

2839 Ramil Kalimullin	2009-03-16
      Fix for bug #42957: no results from 
      select where .. (col=col and col=col) or ... (false expression)
      
      Problem: optimizer didn't take into account a singular case 
      when we eliminated all the predicates at the AND level of WHERE.
      That may lead to wrong results.
      
      Fix: replace (a=a AND a=a...) with TRUE if we eliminated all the
      predicates.
     @ mysql-test/r/select.result
        Fix for bug #42957: no results from 
        select where .. (col=col and col=col) or ... (false expression)
          - test result.
     @ mysql-test/t/select.test
        Fix for bug #42957: no results from 
        select where .. (col=col and col=col) or ... (false expression)
          - test case.
     @ sql/sql_select.cc
        Fix for bug #42957: no results from 
        select where .. (col=col and col=col) or ... (false expression)
          - replacing equality predicates by multiple equality items check
        if we eliminate all the predicates at the AND level and 
        replace them with TRUE if so.
[18 Mar 2009 13:21] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:joro@sun.com-20090317133112-41qn6aly7arljtlq) (merge vers: 6.0.11-alpha) (pib:6)
[19 Mar 2009 3:29] Paul Dubois
Noted in 6.0.11 changelog.

Queries of the following form returned an empty result:

SELECT ... WHERE ... (col=col AND col=col) OR ... (false expression)

Setting report to NDI pending push into 5.1.x.
[27 Mar 2009 14:57] Bugs System
Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:leonard@mysql.com-20090316090622-sr8lylqvsl1jrcnv) (merge vers: 5.1.34) (pib:6)
[27 Mar 2009 15:28] Paul Dubois
Noted in 5.1.34 changelog.
[9 May 2009 16:47] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)
[9 May 2009 17:44] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)
[9 May 2009 18:41] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)