|
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (a varchar(35), b varchar(4)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES
|
('Albania','AXA'),('Australia','AUS'),('American Samoa','AMSA'),('Bahamas','BS');
|
|
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'),
|
('BS','Protestant');
|
|
let query=
|
SELECT t1.a
|
FROM (SELECT a FROM t2 GROUP BY a ORDER BY COUNT(DISTINCT b) LIMIT 1) dt
|
JOIN t1 ON dt.a=t1.b;
|
--replace_column 9 #
|
eval EXPLAIN $query;
|
eval $query;
|
|
DROP TABLES t1, t2;
|
There are two rows in the result set although there must be only one ('Australia').
This error only appears when the LATERAL DERIVED join method is employed. In earlier versions of MariaDB the DERIVED method is chosen, so the result is correct. Starting from 11.0 the optimizer chooses LATERAL DERIVED for this query, and the bug appears.
|