Bug #53334 Incorrect result for InnoDB in LEFT JOIN with impossible condition

• Submitted: 30 Apr 2010 21:05
• Modified: 14 Oct 2010 14:53
• Reporter: Kristian Nielsen
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.1.46/5.1.47
• OS: Linux
• Assigned to: Sergei Glukhov
• CPU Architecture: Any
• Tags: regression

[30 Apr 2010 21:05] Kristian Nielsen

Description:
--source include/have_innodb.inc

create table t1 (id int primary key) engine=innodb;
create table t2 (id int) engine=innodb;
insert into t1 values (75);
insert into t1 values (79);
insert into t1 values (78);
insert into t1 values (77);
replace into t1 values (76);
replace into t1 values (76);
insert into t1 values (104);
insert into t1 values (103);
insert into t1 values (102);
insert into t1 values (101);
insert into t1 values (105);
insert into t1 values (106);
insert into t1 values (107);

insert into t2 values (107),(75),(1000);

select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
where t2.id=75 and t1.id is null;
drop table t1,t2;

# Result has no rows, where it should return a single row (NULL, 75)

How to repeat:
Run above test case with MySQL 5.1.46.

This test is takes from join.test, using innodb instead of myisam as the engine. It works correct with myisam (it also fails using pbxt).

This seems to be a regression since MySQL 5.1.44 (I tested with MariaDB 5.1.44).

Suggested fix:
Query should return the correct result (NULL, 75), not empty result.

[30 Apr 2010 21:23] Kristian Nielsen

The regression seems to have been introduced with this commit:

    revid:[e-mail]-20100319060102-57ykzjf4pc93avy1

  Bug#51494 crash with join, explain and 'sounds like' operator
  The crash happens because of discrepancy between values of
  conts_tables and join->const_table_map(make_join_statisctics).
  Calculation of conts_tables used condition with
  HA_STATS_RECORDS_IS_EXACT flag check. Calculation of
  join->const_table_map does not use this flag check.
  In case of MERGE table without union with index
  the table does not become const table and
  thus join_read_const_table() is not called
  for the table. join->const_table_map supposes
  this table is const and later in make_join_select
  this table is used for making&calculation const
  condition. As table record buffer is not populated
  it leads to crash.
  The fix is adding a check if an engine supports
  HA_STATS_RECORDS_IS_EXACT flag before updating
  join->const_table_map.

[1 May 2010 3:53] MySQL Verification Team

miguel@tikal:~$ dbs/5.1/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.47-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.13 sec)

mysql> create table t2 (id int) engine=innodb;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t1 values (75);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (79);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (78);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (77);
Query OK, 1 row affected (0.05 sec)

mysql> replace into t1 values (76);
Query OK, 1 row affected (0.04 sec)

mysql> replace into t1 values (76);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (104);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (103);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (102);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (101);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (105);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (106);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (107);
Query OK, 1 row affected (0.04 sec)

mysql> 
mysql> insert into t2 values (107),(75),(1000);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
    -> where t2.id=75 and t1.id is null;
Empty set (0.00 sec)

mysql> alter table t1 engine MyISAM;
Query OK, 12 rows affected (0.17 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> alter table t2 engine MyISAM;
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
+------+------+
| id   | id   |
+------+------+
| NULL |   75 |
+------+------+
1 row in set (0.00 sec)

mysql>

[1 May 2010 4:08] MySQL Verification Team

Thank you for the bug report. Verified as described.

miguel@tikal:~$ dbs/5.0/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> create table t2 (id int) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values (75);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (79);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (78);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (77);
Query OK, 1 row affected (0.05 sec)

mysql> replace into t1 values (76);
Query OK, 1 row affected (0.05 sec)

mysql> replace into t1 values (76);
Query OK, 2 rows affected (0.05 sec)

mysql> insert into t1 values (104);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (103);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (102);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (101);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (105);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (106);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (107);
Query OK, 1 row affected (0.05 sec)

mysql> 
mysql> insert into t2 values (107),(75),(1000);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
    -> where t2.id=75 and t1.id is null;
+------+------+
| id   | id   |
+------+------+
| NULL |   75 | 
+------+------+
1 row in set (0.00 sec)

mysql>

[3 May 2010 4:23] 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/107153

3447 Igor Babaev	2010-05-02
      Fixed bug #53334.
      The fix actually reverts the change introduced by the patch
      for bug 51494
      The fact is that the patch for bug 52177 fixes bug 51194 as well.
      modified:
        mysql-test/r/innodb_mysql.result
        mysql-test/t/innodb_mysql.test
        sql/sql_select.cc

[3 May 2010 4:32] Igor Babaev

This bug was introduced by the fix for bug #51494 that was incorrect.
The correct fix for bug #51494 was provided by the patch for bug #52177.
So if you revert the changes of the patch for bug #51494 the code of the release 5.1.46 will pass all regression tests and the test case reported for bug #53334.

[7 May 2010 6: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/107702

3372 Sergey Glukhov	2010-05-07
      Bug#53334 Incorrect result for InnoDB in LEFT JOIN with impossible condition
      The fix actually reverts the change introduced
      by the patch for bug 51494.
      The fact is that patches for bugs 52177&48419
      fix bugs 51194&50575 as well.
     @ mysql-test/r/innodb_mysql.result
        test case
     @ mysql-test/t/innodb_mysql.test
        test case
     @ sql/sql_select.cc
        reverted wrong fix for bug 51494

[28 May 2010 6:11] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100512070920-xgpmqeytp0gc183c) (pib:16)

[28 May 2010 6:39] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100514054548-91z72f0mcskr84kj) (merge vers: 6.0.14-alpha) (pib:16)

[28 May 2010 7:07] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100508220335-xsvmtj21h4yeu8mf) (merge vers: 5.5.5-m3) (pib:16)

[2 Jun 2010 8:50] Bugs System

Pushed into 5.1.48 (revid:[e-mail]-20100602084411-2yu607bslbmgufl3) (version source revid:[e-mail]-20100507071216-mxvljum43kqu3q0u) (merge vers: 5.1.47) (pib:16)

[9 Jun 2010 0:52] Paul DuBois

Noted in 5.1.48, 5.5.5, 6.0.14 changelogs.

[18 Jun 2010 11:41] Valeriy Kravchuk

Bug #54609 was marked as a duplicate of this one.

[14 Jul 2010 7:50] James Day

Bug #55198 was marked as a duplicate of this one.

[14 Oct 2010 8:32] Bugs System

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (version source revid:[e-mail]-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

[14 Oct 2010 8:47] Bugs System

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:[e-mail]-20101014083757-5qo48b86d69zjvzj) (version source revid:[e-mail]-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

[14 Oct 2010 9:01] Bugs System

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:[e-mail]-20101014084420-y54ecj85j5we27oa) (version source revid:[e-mail]-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

[14 Oct 2010 14:53] Jon Stephens

Already documented in the 5.1.48 changelog; no new changelog entries required. setting back to Closed state.