Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.2.2
-
None
-
None
-
OS: Any (Docker container)
CPU Architecture: Any (x86_64)
MariaDB version: 12.2.2-MariaDB-ubu2404
Description
A logic bug was found when evaluating equivalent SQL queries involving LEFT JOIN and complex expressions.
The same logical query produces inconsistent results between a simplified version (SOURCE) and a transformed version (TARGET).
The TARGET query decomposes the predicate into three mutually exclusive conditions:
1. condition is TRUE
2. condition is FALSE (NOT)
3. condition is NULL
These three branches are summed using COUNT( * ), which should be logically equivalent to the SOURCE query.
However, the results are inconsistent:
- SOURCE result: 8
- TARGET result: 5
This indicates incorrect evaluation of boolean expressions or NULL handling in the optimizer or execution engine.
Steps to Reproduce
|
|
-- SCHEMA
|
|
|
CREATE TABLE comments ( |
id INT, |
post_id INT, |
user_id INT, |
content VARCHAR(1000), |
is_spam INT, |
created_at TIMESTAMP NULL |
);
|
|
|
CREATE TABLE orders ( |
id INT, |
user_id INT, |
amount DOUBLE, |
status VARCHAR(20), |
created_at TIMESTAMP NULL |
);
|
|
|
INSERT INTO comments VALUES |
(1, 1, 2, 'Nice post', 0, '2022-01-20 10:00:00'), |
(2, 1, 3, 'Spam here', 1, '2022-01-21 11:00:00'), |
(3, 2, 1, 'Thanks', 0, '2022-01-22 12:00:00'), |
(4, 4, 5, NULL, 0, '2022-01-23 13:00:00'); |
|
|
INSERT INTO orders VALUES |
(1, 1, 100.00, 'paid', '2022-02-01 09:00:00'), |
(2, 1, 200.50, 'shipped', '2022-02-02 10:00:00'), |
(3, 2, NULL, 'failed', '2022-02-03 11:00:00'), |
(4, 3, 50.00, 'paid', '2022-02-04 12:00:00'), |
(5, 5, 999.99, 'paid', '2022-02-05 13:00:00'); |
|
|
-- TRIGGER SQLs:
|
select count(*) |
from |
comments as ref_0 |
left join orders as ref_1 |
on (ref_0.id >= ref_0.user_id); |
|
|
-- RESULT: {8}
|
|
|
select ( |
select count(*) |
from |
comments as ref_0 |
left join orders as ref_1 |
on (ref_0.id >= ref_0.user_id) |
where (QUOTE( |
FORMAT(
|
ref_1.id,
|
24)) <= sys.quote_identifier(
|
nullif(LTRIM( |
case when (21.52 <= 20.46) |
and ('cct' <> '5vfni') then RIGHT( |
'z8tsl6', |
ref_0.user_id) else 'a8o' end |
),
|
case when ref_1.amount is not NULL then 'zgzhd' else '7c678' end |
)))
|
) + (
|
select count(*) |
from |
comments as ref_0 |
left join orders as ref_1 |
on (ref_0.id >= ref_0.user_id) |
where (not (QUOTE( |
FORMAT(
|
ref_1.id,
|
24)) <= sys.quote_identifier(
|
nullif(LTRIM( |
case when (21.52 <= 20.46) |
and ('cct' <> '5vfni') then RIGHT( |
'z8tsl6', |
ref_0.user_id) else 'a8o' end |
),
|
case when ref_1.amount is not NULL then 'zgzhd' else '7c678' end |
))))
|
) + (
|
select count(*) |
from |
comments as ref_0 |
left join orders as ref_1 |
on (ref_0.id >= ref_0.user_id) |
where ((QUOTE( |
FORMAT(
|
ref_1.id,
|
24)) <= sys.quote_identifier(
|
nullif(LTRIM( |
case when (21.52 <= 20.46) |
and ('cct' <> '5vfni') then RIGHT( |
'z8tsl6', |
ref_0.user_id) else 'a8o' end |
),
|
case when ref_1.amount is not NULL then 'zgzhd' else '7c678' end |
))) is null) |
);
|
|
|
-- RESULT: {5}
|
|