Bug #48472 Loose index scan inappropriately chosen for some WHERE conditions
Submitted: 2 Nov 2009 13:39 Modified: 12 Mar 2010 16:57
Reporter: Alexey Kopytov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.88, 5.1.41 OS:Any
Assigned to: Alexey Kopytov CPU Architecture:Any

[2 Nov 2009 13:39] Alexey Kopytov
Description:
For certain forms of WHERE predicates the optimizer may choose the loose index scan method even though it is not actually applicable for this kind of predicates. This can lead to incorrect results.

Examples:

mysql> CREATE TABLE t (a INT, b INT, INDEX (a,b));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t SELECT * FROM t;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT a, MAX(b) FROM t WHERE b+0=0 GROUP BY a; -- correct
+------+--------+
| a    | MAX(b) |
+------+--------+
|    2 |      0 |
+------+--------+
1 row in set (0.00 sec)

mysql>  SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a; -- incorrect
Empty set (0.00 sec)

mysql> EXPLAIN SELECT a, MAX(b) FROM t WHERE b+0=0 GROUP BY a\G -- correct
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: a
      key_len: 10
          ref: NULL
         rows: 8
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a\G -- incorrect
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: range
possible_keys: NULL
          key: a
      key_len: 5
          ref: NULL
         rows: 9
        Extra: Using where; Using index for group-by
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE t (a INT, b INT, INDEX (a,b));
INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
INSERT INTO t SELECT * FROM t;                                   
SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
[2 Nov 2009 22:27] MySQL Verification Team
Thank you for the bug report.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.41-Win X64-debug-log Source distribution

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

mysql 5.1 >use d4
Database changed
mysql 5.1 >CREATE TABLE t (a INT, b INT, INDEX (a,b));
Query OK, 0 rows affected (0.13 sec)

mysql 5.1 >INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.1 >INSERT INTO t SELECT * FROM t;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.1 >SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a;
Empty set (0.08 sec)

mysql 5.1 >SELECT a, MAX(b) FROM t WHERE b+0=0 GROUP BY a;
+------+--------+
| a    | MAX(b) |
+------+--------+
|    2 |      0 |
+------+--------+
1 row in set (0.00 sec)

mysql 5.1 >
[17 Nov 2009 14: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/90672

2843 Alexey Kopytov	2009-11-17
      Bug #48472: Loose index scan inappropriately chosen for some 
                  WHERE conditions 
       
      check_group_min_max() checks if the loose index scan 
      optimization is applicable for a given WHERE condition, that is 
      if the MIN/MAX attribute participates only in range predicates 
      comparing the corresponding field with constants. 
       
      The problem was that it considered the whole predicate suitable 
      for the loose index scan optimization as soon as it encountered 
      a constant as a predicate argument. This is obviously wrong for 
      cases when a constant is the first argument of a predicate 
      which does not satisfy the above condition. 
       
      Fixed check_group_min_max() so that all arguments of the input 
      predicate are considered to decide if it passes the test, even 
      though a constant has already been encountered.
     @ mysql-test/r/group_min_max.result
        Added a test case for bug #48472.
     @ mysql-test/t/group_min_max.test
        Added a test case for bug #48472.
     @ sql/opt_range.cc
        Fixed check_group_min_max() so that all arguments of the input 
        predicate are considered to decide if it passes the test, even 
        though a constant has already been encountered.
[2 Dec 2009 8:01] Bugs System
Pushed into 5.0.89 (revid:joro@sun.com-20091202075830-mzl79q7mc1v72pf1) (version source revid:alexey.kopytov@sun.com-20091123100535-lc3vxqx624yh5gvv) (merge vers: 5.0.89) (pib:13)
[2 Dec 2009 8:04] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[3 Dec 2009 2:50] Paul DuBois
Noted in 5.0.89, 5.1.42 changelogs.

Loose index scan was inappropriately chosen for some WHERE
conditions. 

Setting report to NDI pending push to 5.6.x+.
[16 Dec 2009 8:38] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:45] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:52] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 2:02] Paul DuBois
Noted in 5.5.1, 6.0.14 changelogs.
[12 Mar 2010 14:06] 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:22] 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:36] 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)