Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.0, 12.3.3
Description
The following SQL can be copied and executed as a single test case:
|
|
-- 1. Verify the value of the left-hand scalar subquery
|
SELECT TIMESTAMPADD( |
MINUTE, |
COUNT(*), |
'2025-12-31 14:30:00' |
) AS left_value |
FROM ( |
SELECT 1 |
UNION ALL SELECT 1 |
UNION ALL SELECT 1 |
UNION ALL SELECT 1 |
) AS left_rows; |
|
|
-- Expected / actual:
|
-- 2025-12-31 14:34:00
|
|
|
|
|
-- 2. Verify the value produced by the right-hand aggregate subquery
|
SELECT IFNULL( |
SUBDATE('2025-12-31 14:30:00', SUM(1)), |
'2025-12-31 14:30:00' |
) AS right_value |
FROM (SELECT 1) AS right_rows |
WHERE 0; |
|
|
-- Expected / actual:
|
-- 2025-12-31 14:30:00
|
|
|
|
|
-- 3. Replace the left scalar subquery with its evaluated constant
|
SELECT 1 |
WHERE '2025-12-31 14:34:00' IN ( |
SELECT IFNULL( |
SUBDATE('2025-12-31 14:30:00', SUM(1)), |
'2025-12-31 14:30:00' |
)
|
FROM (SELECT 1) AS right_rows |
WHERE 0 |
);
|
|
|
-- Expected / actual:
|
-- Empty set
|
|
|
|
|
-- 4. Original query producing the incorrect result
|
SELECT 1 |
FROM (SELECT 1) AS outer_row |
WHERE ( |
SELECT TIMESTAMPADD( |
MINUTE, |
COUNT(*), |
'2025-12-31 14:30:00' |
)
|
FROM ( |
SELECT 1 |
UNION ALL SELECT 1 |
UNION ALL SELECT 1 |
UNION ALL SELECT 1 |
) AS left_rows |
) IN ( |
SELECT IFNULL( |
SUBDATE('2025-12-31 14:30:00', SUM(1)), |
'2025-12-31 14:30:00' |
)
|
FROM (SELECT 1) AS right_rows |
WHERE 0 |
);
|
|
|
-- Expected:
|
-- Empty set
|
--
|
-- Actual:
|
-- +---+
|
-- | 1 |
|
-- +---+
|
-- | 1 |
|
-- +---+
|
-- 1 row in set |
The left-hand scalar aggregate subquery evaluates to `2025-12-31 14:34:00`, while the right-hand aggregate subquery evaluates to `2025-12-31 14:30:00`.
Therefore, the predicate is logically equivalent to:
`'2025-12-31 14:34:00' IN ('2025-12-31 14:30:00')`
and should evaluate to FALSE.
Replacing the left-hand scalar subquery with its evaluated constant confirms this: the query correctly returns an empty set.
However, when the scalar aggregate subquery is used directly as the left operand of `IN`, MariaDB evaluates the predicate as TRUE and returns one row.
This is a reproducible wrong-result bug. The issue occurs when a scalar aggregate subquery is used as the left operand of `IN` together with an aggregate subquery whose underlying input is empty.