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