Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.2.2
-
OS: Any (Docker container)
CPU Architecture: Any (x86_64)
MariaDB version: 12.2.2-MariaDB-ubu2404
-
Not for Release Notes
Description
The original query counts rows in posts using a complex REGEXP_REPLACE condition that resolves many nested expressions (CASE, COALESCE, NULLIF, LPAD, UPPER, etc.). Most of these subexpressions evaluate to constants or deterministic values.
- The unoptimized query (NoREC) returns COUNT( * ) = 5.
- When converted into a boolean aggregation (SUM(CASE WHEN <predicate> IS TRUE THEN 1 ELSE 0 END + 0)), the result is 1.
- The discrepancy indicates that the NoREC query’s predicate evaluates differently when interpreted in a boolean context, likely due to SQL semantics of comparing strings, nulls, or constant expressions.
|
|
-- SCHEMA
|
|
|
CREATE TABLE posts ( |
id INT, |
user_id INT, |
title VARCHAR(255), |
content VARCHAR(1000), |
views INT, |
likes INT, |
created_at TIMESTAMP NULL, |
rating DOUBLE |
);
|
|
|
INSERT INTO posts VALUES |
(1, 1, 'Hello World', 'First post', 100, 10, '2022-01-10 10:00:00', 4.5), |
(2, 1, 'Another Post', NULL, 150, 20, '2022-01-11 11:00:00', 3.0), |
(3, 2, 'Bob Post', 'Content', NULL, 5, '2022-01-12 12:00:00', NULL), |
(4, 3, NULL, 'Empty', 50, 2, '2022-01-13 13:00:00', 5.0), |
(5, 4, 'Last Post', 'Last', 300, 30,'2022-01-14 14:00:00', 4.9); |
|
|
-- TRIGGER SQLs:
|
|
|
SELECT COUNT(*) |
FROM posts AS ref_0 |
WHERE REGEXP_REPLACE( |
'mt8d', |
CASE |
WHEN ref_0.views IS NOT NULL |
THEN REGEXP_SUBSTR('lij', COALESCE('skls', 'dzdd')) |
ELSE 'kmj8x3' |
END, |
LPAD(
|
COALESCE(UPPER('zixd'), '8wtem6'), |
86,
|
CASE |
WHEN NULLIF(69.24, 36.37) != EXP(61.83) |
THEN 'ct5d27' |
ELSE NULLIF(REGEXP_SUBSTR('le2mg','c'), 'xxme3') |
END |
)
|
) != CASE |
WHEN 'qis7p2' < COALESCE('1f3', NULLIF('y','7nqz1')) |
THEN NULLIF( |
CASE |
WHEN (5.48 >= (SELECT STDDEV_POP(id) FROM posts)) |
THEN UPPER('x43xf') |
ELSE 'fx2t' |
END, |
FORMAT(8.59, 25)
|
)
|
ELSE NULLIF('cftcju','qpvsh') |
END; |
|
|
-- RESULT: {5}
|
|
|
SELECT SUM(CASE WHEN (REGEXP_REPLACE( |
'mt8d', |
CASE |
WHEN ref_0.views IS NOT NULL |
THEN REGEXP_SUBSTR('lij', COALESCE('skls', 'dzdd')) |
ELSE 'kmj8x3' |
END, |
LPAD(
|
COALESCE(UPPER('zixd'), '8wtem6'), |
86,
|
CASE |
WHEN NULLIF(69.24, 36.37) != EXP(61.83) |
THEN 'ct5d27' |
ELSE NULLIF(REGEXP_SUBSTR('le2mg','c'), 'xxme3') |
END |
)
|
) != CASE |
WHEN 'qis7p2' < COALESCE('1f3', NULLIF('y','7nqz1')) |
THEN NULLIF( |
CASE |
WHEN (5.48 >= (SELECT STDDEV_POP(id) FROM posts)) |
THEN UPPER('x43xf') |
ELSE 'fx2t' |
END, |
FORMAT(8.59, 25)
|
)
|
ELSE NULLIF('cftcju','qpvsh') |
END) IS TRUE THEN 1 ELSE 0 END + 0) |
FROM
|
posts AS ref_0; |
|
|
-- RESULT: {1}
|
|
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-