MySQL Bugs: #50402: Optimizer producing wrong results when using Index Merge on InnoDB

Bug #50402	Optimizer producing wrong results when using Index Merge on InnoDB
Submitted:	18 Jan 2010 2:41	Modified:	2 Nov 2010 14:02
Reporter:	Leandro Morgado	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.1.31sp1, 5.1.41, 5.1.49, 5.5.7	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
When Innodb and index merge is used in the sample queries below, the results are wrongly calculated. Changing the Storage Engine to MyISAM for example, will produce the correct results. Reported on 5.1.31sp1 and verified on : 5.1.41-enterprise-gpl-advanced-log.

Perhaps a regression from Bug #25798

How to repeat:
1) Use the attached mysqldump to populate the schema. Both included tables are created as Innodb.

2) The faulty query is:
================================================================
mysql> SELECT this_.ID
FROM job_order this_
WHERE
CASE
WHEN (SELECT COUNT(*) FROM job_order jo
INNER JOIN referral ref ON jo.ID = ref.job_order_ID
WHERE ref.REF_RESULT = 'h' AND jo.ID = this_.id)
>= this_.NBR_JOB_OPENINGS
THEN 'p'
WHEN CURDATE() >= this_.DATE_ORDER_OPEN AND CURDATE() < this_.DATE_ORDER_CLOSE
THEN 'o'
END
='o' AND this_.ID = 6786;
================================================================

The failure with the index merge is when that subquery is used, even though it is not relevant to the answer. That is, running the subquery separately shows that it returns false, so it is not a case of matching 'p' or 'o' in different cases.

This can be shown from the extended explain of the query:
================================================================
mysql> EXPLAIN EXTENDED SELECT this_.ID FROM job_order this_ WHERE CASE WHEN (SELECT COUNT(*) FROM job_order jo INNER JOIN referral ref ON jo.ID = ref.job_order_ID WHERE ref.REF_RESULT = 'h' AND jo.ID = this_.id) >= this_.NBR_JOB_OPENINGS THEN 'p' WHEN CURDATE() >= this_.DATE_ORDER_OPEN AND CURDATE() < this_.DATE_ORDER_CLOSE THEN 'o' END ='o' AND this_.ID = 6786\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: this_
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: 
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: jo
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: 
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: ref
         type: index_merge
possible_keys: JOB_ORDER_ID,REFERRAL_REF_RESULT
          key: JOB_ORDER_ID,REFERRAL_REF_RESULT
      key_len: 9,2
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using intersect(JOB_ORDER_ID,REFERRAL_REF_RESULT); Using where; Using index
3 rows in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Note   Code: 1276
Message: Field or reference 'test.this_.ID' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select '6786' AS `ID` from `test`.`job_order` `this_` where (((case when ((select count(0) AS `COUNT(*)` from `test`.`job_order` `jo` join `test`.`referral` `ref` where ((`test`.`ref`.`JOB_ORDER_ID` = '6786') and (`test`.`ref`.`REF_RESULT` = 'h'))) >= '5') then 'p' when ((curdate() >= '2009-10-21') and (curdate() < '2010-10-21')) then 'o' end) = 'o'))
2 rows in set (0.00 sec)
================================================================

If we place this into a new table without filtering by this_.ID = 6786, we see this is actually there:
================================================================
mysql> CREATE TABLE temp AS SELECT this_.ID FROM job_order this_ WHERE CASE WHEN (SELECT COUNT(*) FROM job_order jo INNER JOIN referral ref ON jo.ID = ref.job_order_ID WHERE ref.REF_RESULT = 'h' AND jo.ID = this_.id) >= this_.NBR_JOB_OPENINGS THEN 'p' WHEN CURDATE() >= this_.DATE_ORDER_OPEN AND CURDATE() < this_.DATE_ORDER_CLOSE THEN 'o' END ='o';
Query OK, 157 rows affected (0.75 sec)
Records: 157  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM temp WHERE ID=6786;
+------+
| ID   |
+------+
| 6786 |
+------+
1 row in set (0.01 sec)
================================================================

If we change the tables to MyISAM, we get correct results:
================================================================
mysql> ALTER TABLE JOB_ORDER ENGINE=MyISAM;
Query OK, 7599 rows affected (0.18 sec)
Records: 7599  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE REFERRAL ENGINE=MyISAM;
Query OK, 181215 rows affected (1.39 sec)
Records: 181215  Duplicates: 0  Warnings: 0

mysql> SELECT this_.ID FROM job_order this_ WHERE CASE WHEN (SELECT COUNT(*) FROM job_order jo INNER JOIN referral ref ON jo.ID = ref.job_order_ID WHERE ref.REF_RESULT = 'h' AND jo.ID = this_.id) >= this_.NBR_JOB_OPENINGS THEN 'p' WHEN CURDATE() >= this_.DATE_ORDER_OPEN AND CURDATE() < this_.DATE_ORDER_CLOSE THEN 'o' END ='o' AND this_.ID = 6786;
+------+
| ID   |
+------+
| 6786 |
+------+
1 row in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT this_.ID FROM job_order this_ WHERE CASE WHEN (SELECT COUNT(*) FROM job_order jo INNER JOIN referral ref ON jo.ID = ref.job_order_ID WHERE ref.REF_RESULT = 'h' AND jo.ID = this_.id) >= this_.NBR_JOB_OPENINGS THEN 'p' WHEN CURDATE() >= this_.DATE_ORDER_OPEN AND CURDATE() < this_.DATE_ORDER_CLOSE THEN 'o' END ='o' AND this_.ID = 6786\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: this_
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: 
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: jo
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: 
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: ref
         type: ref
possible_keys: JOB_ORDER_ID,REFERRAL_REF_RESULT
          key: JOB_ORDER_ID
      key_len: 9
          ref: 
         rows: 34
     filtered: 100.00
        Extra: Using where
3 rows in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'test.this_.ID' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select '6786' AS `ID` from `test`.`job_order` `this_` where (((case when ((select count(0) AS `COUNT(*)` from `test`.`job_order` `jo` join `test`.`referral` `ref` where ((`test`.`ref`.`JOB_ORDER_ID` = '6786') and (`test`.`ref`.`REF_RESULT` = 'h'))) >= '5') then 'p' when ((curdate() >= '2009-10-21') and (curdate() < '2010-10-21')) then 'o' end) = 'o'))
2 rows in set (0.00 sec)
================================================================

Suggested fix:
Calculate correct values.

File is too big to attach. Has been uploaded to FTP:

 http://ftp.mysql.com/pub/mysql/download/bug_data_50402.bz2

Looks like a duplicate of Bug #50389. Please, check.

I agree it's a duplicate of bug #50389 , however, I think that bug has a wrong entry in it's description, namely:

"The correct number of rows are returned in the following cases:

* storage engine is MyISAM
* query includes USE INDEX (PRIMARY)
* query is SELECT COUNT(*) instead of SELECT *" --> This last entry is not true as my test case is using COUNT(*) and producing wrong results. So it's just a bug with Intersect index merge and Innodb, seem in both bug reports.

Duplicate of bug #50389.

Also this bug is in affect with only two indexes used in the index merge rather than 3.

It also does not relate to field length, but to the number of comparisons on the primary key it seems. If you compare only  one value using IN or equality then it gives a wrong result. However, on two or more using IN or multiple comparisons using AND/OR, it returns a correct result (non-empty).

I don't think this bug is fixed. I still get missing results if the table is InnoDB:

mysql [localhost] {msandbox} (test) > ALTER TABLE JOB_ORDER ENGINE=MyISAM;
Query OK, 7599 rows affected (0.09 sec)
Records: 7599  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > ALTER TABLE REFERRAL ENGINE=MyISAM;
Query OK, 181215 rows affected (1.13 sec)
Records: 181215  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > SELECT this_.ID FROM job_order this_ WHERE CASE WHEN (SELECT COUNT(*) FROM
    -> job_order jo INNER JOIN referral ref ON jo.ID = ref.job_order_ID WHERE ref.REF_RESULT =
    -> 'h' AND jo.ID = this_.id) >= this_.NBR_JOB_OPENINGS THEN 'p' WHEN CURDATE() >=
    -> this_.DATE_ORDER_OPEN AND CURDATE() < this_.DATE_ORDER_CLOSE THEN 'o' END ='o' AND
    -> this_.ID = 6786;
+------+
| ID   |
+------+
| 6786 |
+------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > ALTER TABLE REFERRAL ENGINE=INNODB;Query OK, 181215 rows affected (2.85 sec)Records: 181215  Duplicates: 0  Warnings: 0
mysql [localhost] {msandbox} (test) > ALTER TABLE JOB_ORDER ENGINE=INNODB;Query OK, 7599 rows affected (0.15 sec)Records: 7599  Duplicates: 0  Warnings: 0
mysql [localhost] {msandbox} (test) > SELECT this_.ID FROM job_order this_ WHERE CASE WHEN (SELECT COUNT(*) FROM job_order jo INNER JOIN referral ref ON jo.ID = ref.job_order_ID WHERE ref.REF_RESULT = 'h' AND jo.ID = this_.id) >= this_.NBR_JOB_OPENINGS THEN 'p' WHEN CURDATE() >= this_.DATE_ORDER_OPEN AND CURDATE() < this_.DATE_ORDER_CLOSE THEN 'o' END ='o' AND this_.ID = 6786;
Empty set (0.01 sec)

Bug #50389 is fixed only in 5.1.49. What version is used in your sandbox?

Hi Valeriy,

I tested precisely on this version:

shell> ./my sql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.49-enterprise-gpl-advanced MySQL Enterprise Server - Advanced Edition (GPL)

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 [localhost] {msandbox} (test) > SHOW VARIABLES LIKE "version%";
+-------------------------+--------------------------------------------------+| Variable_name           | Value                                            |+-------------------------+--------------------------------------------------+
| version                 | 5.1.49-enterprise-gpl-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (GPL) |
| version_compile_machine | x86_64                                           |
| version_compile_os      | unknown-linux-gnu                                |
+-------------------------+--------------------------------------------------+
4 rows in set (0.00 sec)

Tested this again on 5.1.49 and the problem remains.

in 5.1.50, this remains a very serious problem with index_merge:

------
drop table if exists t1;
create table t1(a int,b int,key(a),key(b))engine=innodb;
insert into t1 values (1,2),(1,2),(1,2),(1,2);
select * from t1 force index(a,b) where a=1 and b=2;
select * from t1 ignore index(a,b) where a=1 and b=2;
------

Both SELECT should return same results, but the one using index_merge
gives empty result!

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/118067

3506 Sergey Glukhov	2010-09-13
      Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB 
      Subselect executes twice, at JOIN::optimize stage
      and at JOIN::execute stage. At optimize stage
      Innodb prebuilt struct which is used for the
      retrieval of column values is initialized in.
      ha_innobase::index_read(), prebuilt->sql_stat_start is true.
      After QUICK_ROR_INTERSECT_SELECT finished his job it
      restores read_set/write_set bitmaps with initial values
      and deactivates one of the handlers used by
      QUICK_ROR_INTERSECT_SELECT in JOIN::cleanup
      (it's the case when we reuse original handler as one of
       handlers required by QUICK_ROR_INTERSECT_SELECT object).
      On second subselect execution inactive handler is activated
      in  QUICK_RANGE_SELECT::reset, file->ha_index_init().
      In ha_index_init Innodb prebuilt struct is reinitialized
      with inappropriate read_set/write_set bitmaps. Further
      reinitialization in ha_innobase::index_read() does not
      happen as prebuilt->sql_stat_start is false.
      It leads to partial retrieval of required field values
      and we get a mix of field values from different records
      in the record buffer.
      The fix is to reset
      read_set/write_set bitmaps as these values
      are required for proper intialization of
      internal InnoDB struct which is used for
      the retrieval of column values
      (see build_template(), ha_innodb.cc)
     @ mysql-test/include/index_merge_ror_cpk.inc
        test case
     @ mysql-test/r/index_merge_innodb.result
        test result
     @ mysql-test/r/index_merge_myisam.result
        test result
     @ sql/opt_range.cc
        if ROR merge scan is used we need to reset
        read_set/write_set bitmaps as these values
        are required for proper intialization of
        internal InnoDB struct which is used for
        the retrieval of column values
        (see build_template(), ha_innodb.cc)

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/118395

3512 Sergey Glukhov	2010-09-16
      Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB 
      Subselect executes twice, at JOIN::optimize stage
      and at JOIN::execute stage. At optimize stage
      Innodb prebuilt struct which is used for the
      retrieval of column values is initialized in.
      ha_innobase::index_read(), prebuilt->sql_stat_start is true.
      After QUICK_ROR_INTERSECT_SELECT finished his job it
      restores read_set/write_set bitmaps with initial values
      and deactivates one of the handlers used by
      QUICK_ROR_INTERSECT_SELECT in JOIN::cleanup
      (it's the case when we reuse original handler as one of
       handlers required by QUICK_ROR_INTERSECT_SELECT object).
      On second subselect execution inactive handler is activated
      in  QUICK_RANGE_SELECT::reset, file->ha_index_init().
      In ha_index_init Innodb prebuilt struct is reinitialized
      with inappropriate read_set/write_set bitmaps. Further
      reinitialization in ha_innobase::index_read() does not
      happen as prebuilt->sql_stat_start is false.
      It leads to partial retrieval of required field values
      and we get a mix of field values from different records
      in the record buffer.
      The fix is to reset
      read_set/write_set bitmaps as these values
      are required for proper intialization of
      internal InnoDB struct which is used for
      the retrieval of column values
      (see build_template(), ha_innodb.cc)
     @ mysql-test/include/index_merge_ror_cpk.inc
        test case
     @ mysql-test/r/index_merge_innodb.result
        test case
     @ mysql-test/r/index_merge_myisam.result
        test case
     @ sql/opt_range.cc
        if ROR merge scan is used we need to reset
        read_set/write_set bitmaps as these values
        are required for proper intialization of
        internal InnoDB struct which is used for
        the retrieval of column values
        (see build_template(), ha_innodb.cc)

Bug #57019 was marked as duplicate of this one

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

Noted in 5.5.7, 5.6.1 changelogs.

For some queries, the optimizer produced incorrect results using the
Index Merge access method with InnoDB tables. 

Setting report to Need Merge pending push to 5.1.x.

Setting report to Need Merge pending push to 5.1.x.

Noted in 5.1.52 changelog.

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

Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)

See comment from 16 Sep 15:46 the problem
described by Shane is fixed in Bug#56423, the fix is in 5.1.53
tree.

Marking as a dupe of Bug #56423 .