[MDEV-40516] MariaDB scans unnecessary inputs when an absorbing set-operation branch is empty - Jira

XML

Word

Printable

MariaDB does not fully propagate an empty input through `INTERSECT` or through
an `EXCEPT` whose left input is empty.

Both expressions below are statically empty and should avoid every base-table
scan:

```text
A INTERSECT empty = empty
empty EXCEPT B = empty
```

MariaDB recognizes the local `Impossible WHERE`, but retains the set operation
and scans the opposite input.

| Case | Original set query | Mutated query after branch removal | Difference |

|---|---:|---:|---:|

| A INTERSECT empty | 30.012 ms | 0.405 ms | 74.16x |

| empty EXCEPT B | 12.278 ms | 0.389 ms | 31.58x |

All paired queries return `COUNT = 0`; timings are from seven alternating
executions. The JSON plans also expose the unnecessary work:

```text

Original A INTERSECT empty             Original empty EXCEPT B

<intersect2,3>                         <except2,3>

|- lhs: ALL, rows: 100113              |- lhs: Impossible WHERE

`- rhs: Impossible WHERE               `- rhs: ALL, rows: 100113

Mutated (both cases)

`- Impossible WHERE                    -- no base-table scan

```

The stable timing differences are therefore backed by avoidable 100,000-row
scans in the original plans.
How to repeat

DROP DATABASE IF EXISTS mariadb_empty_setop;

CREATE DATABASE mariadb_empty_setop;

USE mariadb_empty_setop;

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

CREATE TABLE rhs(id INT);

INSERT INTO lhs SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+1

FROM digits a,digits b,digits c,digits d,digits e;

INSERT INTO rhs SELECT a.d+b.d*10+c.d*100+d.d*1000+e.d*10000+1

FROM digits a,digits b,digits c,digits d,digits e;

ANALYZE TABLE lhs,rhs;

EXPLAIN FORMAT=JSON SELECT COUNT(*) FROM

 ((SELECT id FROM lhs) INTERSECT (SELECT id FROM rhs WHERE FALSE)) s;

SELECT COUNT(*) FROM

 ((SELECT id FROM lhs) INTERSECT (SELECT id FROM rhs WHERE FALSE)) s;

EXPLAIN FORMAT=JSON SELECT COUNT(*) FROM

 ((SELECT id FROM lhs WHERE FALSE) EXCEPT (SELECT id FROM rhs)) s;

SELECT COUNT(*) FROM

 ((SELECT id FROM lhs WHERE FALSE) EXCEPT (SELECT id FROM rhs)) s;

-- Mutated form for both original queries.

EXPLAIN FORMAT=JSON SELECT COUNT(*) FROM (SELECT id FROM lhs WHERE FALSE) s;

SELECT COUNT(*) FROM (SELECT id FROM lhs WHERE FALSE) s;

relates to

MDEV-40474 Redundant outer DISTINCT creates an additional temporary table above EXCEPT in MariaDB

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.