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: | |
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
[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)