Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.3.2
-
None
-
Not for Release Notes
Description
Description
Expected Behaviour
MariaDB should recognize that `INTERSECT` already guarantees distinct output and remove the outer `DISTINCT`. The two equivalent queries should have the same plan and should not require an additional temporary table.
Actual Behaviour
The JSON plan for the DISTINCT form contains a top-level `temporary_table`; the equivalent plain form has a direct `nested_loop` scan of `<intersect2,3>`. Representative measured times were 24.31 ms with DISTINCT and 25.04 ms without it. The difference is within run-to-run noise at 701 output rows, but the redundant temporary-table operation is deterministic.
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) INTERSECT (SELECT v FROM rhs)) s;
|
ANALYZE FORMAT=JSON SELECT s.v
|
FROM ((SELECT v FROM lhs) INTERSECT (SELECT v FROM rhs)) s;
|
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-