Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL)
Description
If a grouping derived table is specified with ORDER BY + LIMIT clauses and split optimization has been applied to this derived table then the query may return a wrong result. The following test case demonstrates this for 10.4:
--source include/have_innodb.inc
|
|
|
CREATE TABLE t1 |
(a varchar(35), b varchar(4), KEY (a)) |
ENGINE=InnoDB;
|
INSERT INTO t1 VALUES |
('Albania','AXA'), ('Australia','AUS'), ('American Samoa','AMSA'), |
('Bahamas','BS'), ('Great Britain','GBR'), ('United States','USA'), |
('New Zealand','NZL'), ('Canada','CAN'), ('India','IND'), |
('Singapore','SGP'), ('Brazil','BRA'), ('Barbados','BRB'), |
('Nigeria','NGR'), ('Uganda','UGA'), ('Ukraine','UKR'), |
('Romania','ROM'), ('Myanmar','MMR'); |
|
|
CREATE TABLE t2 |
(a varchar(4), b varchar(50), PRIMARY KEY (b,a), KEY (a)) |
ENGINE=InnoDB;
|
INSERT INTO t2 VALUES |
('BERM','African Methodist Episcopal'), ('AUS','Anglican'), |
('BERM','Anglican'), ('BS','Anglican'), ('BS','Baptist'), |
('BS','Methodist'), ('GBR','Anglican'), ('NZL','Anglican'), |
('CAN','Anglican'), ('UGA','Anglican'), ('USA','Methodist'), |
('SGP','Methodist'), ('CAN','Methodist'), ('BRB','Methodist'), |
('BRA','Baptist'), ('NGR','Baptist'), ('IND','Baptist'), |
('GBR','Baptist'), ('UKR','Baptist'), ('ROM','Baptist'), |
('USA','Baptist'), ('MMR','Baptist'); |
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL; |
ANALYZE TABLE t2 PERSISTENT FOR ALL; |
|
|
set join_cache_level=0; |
|
|
let $q=
|
SELECT t1.a |
FROM (SELECT a FROM t2 GROUP BY a ORDER BY a, COUNT(DISTINCT b) LIMIT 1) dt |
JOIN t1 ON |
dt.a=t1.b
|
WHERE t1.a LIKE 'B%'; |
|
|
set optimizer_switch='split_materialized=off'; |
eval EXPLAIN $q;
|
eval $q;
|
|
|
set optimizer_switch='split_materialized=on'; |
eval EXPLAIN $q;
|
eval $q;
|
|
|
DROP TABLE t1,t2; |
For this test case we have:
MariaDB [test]> EXPLAIN
|
-> SELECT t1.a
|
-> FROM (SELECT a FROM t2 GROUP BY a ORDER BY a, COUNT(DISTINCT b) LIMIT 1) dt
|
-> JOIN t1 ON
|
-> dt.a=t1.b
|
-> WHERE t1.a LIKE 'B%';
|
+------+-----------------+------------+-------+---------------+------+---------+-----------+------+--------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+-------+---------------+------+---------+-----------+------+--------------------------------------------------+
|
| 1 | PRIMARY | t1 | range | a | a | 38 | NULL | 3 | Using index condition; Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 6 | test.t1.b | 2 | |
|
| 2 | LATERAL DERIVED | t2 | range | a | a | 58 | NULL | 23 | Using where; Using index for group-by (scanning) |
|
+------+-----------------+------------+-------+---------------+------+---------+-----------+------+--------------------------------------------------+
|
3 rows in set (0.002 sec)
|
|
|
MariaDB [test]> SELECT t1.a
|
-> FROM (SELECT a FROM t2 GROUP BY a ORDER BY a, COUNT(DISTINCT b) LIMIT 1) dt
|
-> JOIN t1 ON
|
-> dt.a=t1.b
|
-> WHERE t1.a LIKE 'B%';
|
+----------+
|
| a |
|
+----------+
|
| Bahamas |
|
| Barbados |
|
| Brazil |
|
+----------+
|
3 rows in set (0.002 sec)
|
|
|
MariaDB [test]> set optimizer_switch='split_materialized=off';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> EXPLAIN
|
-> SELECT t1.a
|
-> FROM (SELECT a FROM t2 GROUP BY a ORDER BY a, COUNT(DISTINCT b) LIMIT 1) dt
|
-> JOIN t1 ON
|
-> dt.a=t1.b
|
-> WHERE t1.a LIKE 'B%';
|
+------+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
|
| 1 | PRIMARY | t1 | range | a | a | 38 | NULL | 3 | Using index condition; Using where |
|
| 2 | DERIVED | t2 | index | NULL | a | 6 | NULL | 22 | Using index; Using temporary; Using filesort |
|
+------+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
3 rows in set (0.001 sec)
|
|
|
MariaDB [test]> SELECT t1.a
|
-> FROM (SELECT a FROM t2 GROUP BY a ORDER BY a, COUNT(DISTINCT b) LIMIT 1) dt
|
-> JOIN t1 ON
|
-> dt.a=t1.b
|
-> WHERE t1.a LIKE 'B%';
|
Empty set (0.003 sec)
|