Details
Description
Expected Behaviour
MariaDB should eliminate the outer `DISTINCT` because `EXCEPT` already returns a duplicate-free result. The original and simplified queries should have identical plans without an extra materialization step.
Actual Behaviour
The DISTINCT plan contains a top-level `temporary_table` above `<except2,3>`; the plain plan does not. Representative measured times were 22.13 ms versus 21.48 ms, about 3.0% slower with the redundant DISTINCT. Only 300 rows reach the extra operator, so the absolute difference is small.
How to repeat
 |
DROP DATABASE IF EXISTS rift_distinct_mre;
|
CREATE DATABASE rift_distinct_mre;
|
USE rift_distinct_mre;
|
CREATE TABLE digits(d INT PRIMARY KEY);
|
INSERT INTO digits VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
CREATE TABLE lhs(id INT PRIMARY KEY,v INT NULL,INDEX(v));
|
CREATE TABLE rhs(id INT PRIMARY KEY,v INT NULL,INDEX(v));
|
CREATE TABLE probe(id INT PRIMARY KEY,v INT NULL,INDEX(v));
|
INSERT INTO lhs SELECT n,IF(n%97=0,NULL,n%1000) FROM |
(SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+1 n |
FROM digits a,digits b,digits c,digits d,digits e) x;
|
INSERT INTO rhs SELECT n,IF(n%89=0,NULL,n%700+300) FROM |
(SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+1 n |
FROM digits a,digits b,digits c,digits d,digits e) x;
|
INSERT INTO probe SELECT n,IF(n%101=0,NULL,n%1200) FROM |
(SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+1 n |
FROM digits a,digits b,digits c,digits d,digits e) x;
|
ANALYZE TABLE lhs,rhs,probe;
|
ANALYZE FORMAT=JSON SELECT DISTINCT s.v
|
FROM ((SELECT v FROM lhs) EXCEPT (SELECT v FROM rhs)) s;
|
ANALYZE FORMAT=JSON SELECT s.v
|
FROM ((SELECT v FROM lhs) EXCEPT (SELECT v FROM rhs)) s;
|
|
Attachments
Issue Links
- is duplicated by
-
MDEV-40475 Redundant outer DISTINCT creates an additional temporary table above INTERSECT in MariaDB
-
- Closed
-
- relates to
-
-
- Confirmed
-
-
-
- Confirmed
-
-
-
- Confirmed
-
-
-
- Confirmed
-