AliSQL: adding OR FALSE evaluates an expensive subquery despite an impossible HAVING clause

[chen8908917]

Description

The boolean identity P OR FALSE is equivalent to P, including under SQL three-valued logic. Adding OR FALSE around a WHERE predicate should not change which parts of a query must be executed.

The query in this report has a constant-false HAVING clause, so it cannot return any rows. Without OR FALSE, AliSQL detects Impossible HAVING and does not execute the expensive uncorrelated EXISTS subquery. After adding OR FALSE, AliSQL still knows that the outer query is impossible, but nevertheless evaluates the subquery by scanning a one-million-row Cartesian product.

Expected behavior

AliSQL should simplify:

P OR FALSE

to P, or propagate the constant-false HAVING condition before executing uncorrelated subqueries. Both queries should return immediately without scanning the two copies of t1 inside EXISTS.

Actual behavior

The original plan contains only:

Impossible HAVING

The equivalent query containing OR FALSE reports Impossible WHERE, but also retains and executes the subquery:

PRIMARY   Impossible WHERE
SUBQUERY  a  type=index  rows=1000
SUBQUERY  b  type=index  rows=1000  Using where; Using index; Using join buffer (hash join)

With 1,000 rows, warm-up executions, and alternating execution order, 14 measured executions of each query produced:

Query	Median	Average	Range
Original	0.513 ms	0.553 ms	0.309–1.228 ms
With OR FALSE	441.987 ms	441.246 ms	430.663–450.180 ms

The query containing OR FALSE was approximately 861.15 times slower. Both queries returned zero rows.

How to repeat

Run the following complete SQL script in AliSQL:

SELECT VERSION(), @@version_comment, @@port;

DROP DATABASE IF EXISTS alisql_or_false_mre;
CREATE DATABASE alisql_or_false_mre;
USE alisql_or_false_mre;

CREATE TABLE t1 (
    id INT PRIMARY KEY,
    padding VARCHAR(20)
);

SET cte_max_recursion_depth = 2000;

INSERT INTO t1
WITH RECURSIVE seq(i) AS (
    SELECT 1
    UNION ALL
    SELECT i + 1 FROM seq WHERE i < 1000
)
SELECT i, CONCAT('value_', i)
FROM seq;

ANALYZE TABLE t1;

-- Returns immediately. The plan reports only "Impossible HAVING".
EXPLAIN
SELECT COUNT(*)
FROM t1 AS o
WHERE EXISTS (
    SELECT 1
    FROM t1 AS a
    CROSS JOIN t1 AS b
    WHERE SHA2(CONCAT(a.id, ':', b.id), 256) = 'never'
)
GROUP BY o.id
HAVING FALSE;

SELECT COUNT(*)
FROM t1 AS o
WHERE EXISTS (
    SELECT 1
    FROM t1 AS a
    CROSS JOIN t1 AS b
    WHERE SHA2(CONCAT(a.id, ':', b.id), 256) = 'never'
)
GROUP BY o.id
HAVING FALSE;

-- Logically equivalent, but evaluates the one-million-row subquery join.
EXPLAIN
SELECT COUNT(*)
FROM t1 AS o
WHERE EXISTS (
    SELECT 1
    FROM t1 AS a
    CROSS JOIN t1 AS b
    WHERE SHA2(CONCAT(a.id, ':', b.id), 256) = 'never'
) OR FALSE
GROUP BY o.id
HAVING FALSE;

SELECT COUNT(*)
FROM t1 AS o
WHERE EXISTS (
    SELECT 1
    FROM t1 AS a
    CROSS JOIN t1 AS b
    WHERE SHA2(CONCAT(a.id, ':', b.id), 256) = 'never'
) OR FALSE
GROUP BY o.id
HAVING FALSE;

Confirm that both SELECT statements return zero rows. For stable timing results, warm up both statements and alternate their execution order over multiple repetitions.

Version

AliSQL source commit: 9e121f27d404cad6cdb1cc695eab4276469adace
VERSION(): 8.0.44
@@version_comment: Source distribution
Platform: Linux x86_64