Details
Minor
Description
The following test case gives us a wrong result in MariaDB 5.2/5.3/5.5:
CREATE TABLE t1(
col1 int,
UNIQUE INDEX idx (col1)
);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT SQL_BIG_RESULT tbl1.col1 AS field1, tbl2.col1 AS field2
FROM t1 as tbl1, t1 as tbl2
GROUP BY field1, field2
LIMIT 3;
(see also bug #53534 in bugs.mysql.com)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Investigation of the bug on various versions, using the following test case (based on the one in MySQL 5.6):
CREATE TABLE t1m(col1 int, UNIQUE INDEX idx (col1)) engine=myisam;
INSERT INTO t1m VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
EXPLAIN
SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
FROM t1m GROUP BY field1, field2;
SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
FROM t1m GROUP BY field1, field2;
- MariaDB 5.3 - wrong result
- MariaDB 5.5 - crash in debug mode - failed assert DBUG_ASSERT(inited==INDEX)
in ha_index_end() called from QUICK_GROUP_MIN_MAX_SELECT::~QUICK_GROUP_MIN_MAX_SELECT - MySQL 5.5 - wrong result
- MySQL 5.6 - fixed
MySQL's fix looks like an optimization that hides the real problem. Investigating.
The situation described in this bug is quite uncommon, therefore the solution is to make loose scan analysis take into account that there may be duplicate columns. Since there cannot be an index with duplicate columns, loose scan will become inapplicable to group by queries with duplicate group columns.
This limitation will be lifted in 10.0 by MDEV-4119.
Moved fix to 5.1, test, push
Launchpad bug id: 1002108