Details
Major Description
A logic bug was found when evaluating equivalent SQL queries involving a subquery with complex expressions.
The same logical query produces inconsistent results between a simplified version (SOURCE) and a transformed version (TARGET).
The TARGET query decomposes the subquery results into three mutually exclusive conditions based on NULL evaluation:
1. column is NULL
2. column is NOT NULL
3. expression "IS NULL" evaluates to NULL
These three branches are summed using COUNT( * ), which should be logically equivalent to the SOURCE query.
However, the results are inconsistent:
SOURCE result: 4
TARGET result: 3
This indicates incorrect handling of NULLs or boolean expressions in subquery decomposition, potentially involving the optimizer or execution engine.
|
|
-- 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 |
(select |
ref_0.title as c5 |
from |
posts as ref_0 |
where LPAD('qs6co',ref_0.id) < UPPER( |
case when (ref_0.created_at is NULL) |
and ('97' > '4q') then 'wiu' else '99' end |
)) as subq_0; |
|
|
-- RESULT: {4}
|
|
|
select ( |
select count(*) |
from |
(select |
ref_0.title as c5 |
from |
posts as ref_0 |
where LPAD('qs6co',ref_0.id) < UPPER( |
case when (ref_0.created_at is NULL) |
and ('97' > '4q') then 'wiu' else '99' end |
)) as subq_0 |
where (subq_0.c5 is NULL) |
) + (
|
select count(*) |
from |
(select |
ref_0.title as c5 |
from |
posts as ref_0 |
where LPAD('qs6co',ref_0.id) < UPPER( |
case when (ref_0.created_at is NULL) |
and ('97' > '4q') then 'wiu' else '99' end |
)) as subq_0 |
where (not (subq_0.c5 is NULL)) |
) + (
|
select count(*) |
from |
(select |
ref_0.title as c5 |
from |
posts as ref_0 |
where LPAD('qs6co',ref_0.id) < UPPER( |
case when (ref_0.created_at is NULL) |
and ('97' > '4q') then 'wiu' else '99' end |
)) as subq_0 |
where ((subq_0.c5 is NULL) is null) |
);
|
|
|
-- RESULT: {3}
|
|