Bug #120971 Redundant IS NOT NULL on SHA2() expression causes extra evaluation and slower execution

• Submitted: 21 Jul 9:01
• Modified: 22 Jul 13:55
• Reporter: cl hl
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.46, 8.4.10, 9.7.1
• OS: Any
• CPU Architecture: Any

[21 Jul 9:01] cl hl

Description:
The following two predicates are semantically equivalent in a WHERE clause:
SHA2(pad, 256) BETWEEN '0' AND 'f'
and:
SHA2(pad, 256) BETWEEN '0' AND 'f'
AND SHA2(pad, 256) IS NOT NULL
For BETWEEN, if SHA2(pad, 256) returns NULL, the predicate evaluates to UNKNOWN, so the row is filtered out by WHERE. Therefore, the explicit SHA2(pad, 256) IS NOT NULL condition is redundant.
However, the optimizer keeps the redundant predicate and evaluates SHA2(pad, 256) again. This causes a measurable performance difference on large tables.
Observed results:
MySQL 9.7.1:
BETWEEN only median                    = 173.27 ms
BETWEEN + IS NOT NULL median            = 259.99 ms
slowdown                               ≈ 1.50x

How to repeat:
DROP DATABASE IF EXISTS rift_between_sha2_perf;
CREATE DATABASE rift_between_sha2_perf;
USE rift_between_sha2_perf;

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

INSERT INTO t
WITH RECURSIVE
  a(n) AS (
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM a WHERE n < 999
  ),
  b(n) AS (
    SELECT 0
    UNION ALL
    SELECT n + 1 FROM b WHERE n < 999
  )
SELECT
  a.n * 1000 + b.n + 1,
  REPEAT('x', 32)
FROM a JOIN b;

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

EXPLAIN FORMAT=JSON
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';

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

[22 Jul 13:55] Chaithra Marsur Gopala Reddy

Hi cl hl,

Thank you for the test case. Verified as described.