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)
• Status: Closed
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.76, 5.1.31, 6.0.9
• OS: Any
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any
• Tags: regression

[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] MySQL Verification Team

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:[e-mail]-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:[e-mail]-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:[e-mail]-20090327143448-wuuuycetc562ty6o) (version source revid:[e-mail]-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:[e-mail]-20090508185236-p9b3as7qyauybefl) (version source revid:[e-mail]-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:[e-mail]-20090509063138-1u3q3v09wnn2txyt) (version source revid:[e-mail]-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:[e-mail]-20090509154927-im9a7g846c6u1hzc) (version source revid:[e-mail]-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)