AliSQL: redundant IS NOT NULL after BETWEEN recomputes an expensive expression

[chen8908917]

Description

In a WHERE clause, if the predicate below is true:

expression BETWEEN lower_bound AND upper_bound

then expression is necessarily non-NULL. Adding the following predicate is therefore redundant:

AND expression IS NOT NULL

AliSQL does not remove this implied non-NULL check and does not reuse the value already computed for BETWEEN. When expression is expensive, such as SHA2(pad, 256), the redundant predicate causes a substantial regression.

Expected behavior

AliSQL should infer that a true BETWEEN predicate implies that its tested expression is non-NULL and remove the redundant IS NOT NULL. Alternatively, it should compute the deterministic expression only once per row and reuse the result.

The two equivalent queries should have comparable execution times.

Actual behavior

Both queries use the same plan:

Table scan on t
Using where

However, adding the redundant predicate increases the execution time substantially. With 1,000,000 rows, warm-up executions, alternating execution order, and server-side EXPLAIN ANALYZE measurements:

Query	Median execution time
SHA2(pad,256) BETWEEN '0' AND 'f'	492.0 ms
With redundant SHA2(pad,256) IS NOT NULL	841.5 ms

The equivalent query containing IS NOT NULL was approximately 1.71 times slower. Both queries returned 1,000,000.

The same pattern was also observed with CONCAT(pad,id): 248.0 ms versus 319.5 ms, approximately 1.29 times slower. A plain indexed column showed only a small difference, indicating that repeated expression evaluation is the important cost.

How to repeat

Run the following complete SQL script in AliSQL:

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

DROP DATABASE IF EXISTS alisql_between_not_null_mre;
CREATE DATABASE alisql_between_not_null_mre;
USE alisql_between_not_null_mre;

CREATE TABLE digits(d INT PRIMARY KEY);
INSERT INTO digits VALUES
    (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE t (
    id INT PRIMARY KEY,
    pad VARCHAR(100) NOT NULL
);

INSERT INTO t(id, pad)
SELECT n + 1, REPEAT('x', 32)
FROM (
    SELECT d0.d
         + d1.d * 10
         + d2.d * 100
         + d3.d * 1000
         + d4.d * 10000
         + d5.d * 100000 AS n
    FROM digits AS d0
    CROSS JOIN digits AS d1
    CROSS JOIN digits AS d2
    CROSS JOIN digits AS d3
    CROSS JOIN digits AS d4
    CROSS JOIN digits AS d5
) AS numbers;

ANALYZE TABLE t;

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM t
WHERE SHA2(pad, 256) BETWEEN '0' AND 'f';

SELECT COUNT(*)
FROM t
WHERE SHA2(pad, 256) BETWEEN '0' AND 'f';

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM t
WHERE SHA2(pad, 256) BETWEEN '0' AND 'f'
  AND SHA2(pad, 256) IS NOT NULL;

SELECT COUNT(*)
FROM t
WHERE SHA2(pad, 256) BETWEEN '0' AND 'f'
  AND SHA2(pad, 256) IS NOT NULL;

Confirm that both SELECT statements return 1,000,000. 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