Details
Minor
Description
The bug also exists in MariaDB 5.3.12, but it has been fixed in the current 5.3 tree. It still exists in 5.1 and 5.2 tree though. It also exists in all of 5.1-5.7 to date. I'm filing it for the record, to make it easier to find.
CREATE TABLE t1 (a INT, b VARCHAR(1), INDEX(b,a)) ENGINE=MyISAM; INSERT INTO t1 VALUES (2,'s'),(5,'h'),(3,'q'),(1,'a'),(3,'v'), (6,'u'),(7,'s'),(5,'y'),(1,'z'),(5,'i'),(2,'y'); SELECT b, max(a) FROM t1 WHERE b = 'i' OR a = 2 GROUP BY b;
Actual result:
SELECT b, max(a) FROM t1 WHERE b = 'i' OR a = 2 GROUP BY b; b max(a) i 5
Expected result:
b max(a) i 5 s 2 y 2
EXPLAIN:
EXPLAIN EXTENDED SELECT b, max(a) FROM t1 WHERE b = 'i' OR a = 2 GROUP BY b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range b b 4 NULL 6 100.00 Using where; Using index for group-by Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,max(`test`.`t1`.`a`) AS `max(a)` from `test`.`t1` where ((`test`.`t1`.`b` = 'i') or (`test`.`t1`.`a` = 2)) group by `test`.`t1`.`b`
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The bug was fixed on 5.3 tree by the following revision:
revno: 3622
revision-id: timour@askmonty.org-20130204153548-njv08hcdskv6ttjk
parent: sergii@pisem.net-20130128081223-mp9rsd3t9soz8lly
fixes bug: https://mariadb.atlassian.net/browse/MDEV-765
committer: timour@askmonty.org
branch nick: 5.3-md765
timestamp: Mon 2013-02-04 17:35:48 +0200
message:
Fix for bug MDEV-765 (LP:825075)
Analys:
The cause for the wrong result was that the optimizer
incorrectly chose min/max loose scan when it is not
applicable. The applicability test missed the case when
a condition on the MIN/MAX argument was OR-ed with a
condition on some other field. In this case, the MIN/MAX
condition cannot be used for loose scan.
Solution:
Extend the test check_group_min_max_predicates() to check
that the WHERE clause is of the form: "cond1 AND cond2"
where
cond1 - does not use min_max_column at all.
cond2 - is an AND/OR tree with leaves in form "min_max_column $CMP$ const"
or $CMP$ is one of the functions between, is [not] null
I don't expect anyone will fix wrong result bugs in 5.1/5.2 at this point, thus closing as 'Won't fix'. It was filed for searching purposes, it will still serve it even in the closed state.
It's still rather difficult to find in JIRA, so I've added MySQL number to the summary and EXPLAIN to the description to extend searchable content.