Details
Major Description
Two logically equivalent queries return different results after adding OR FALSE to the WHERE clause.
The original query uses:
WHERE s.x IN (SELECT d.y FROM d)
|
The mutated query uses:
WHERE s.x IN (SELECT d.y FROM d) OR FALSE
|
These predicates are logically equivalent. However, MariaDB returns different values for the scalar subquery:
SELECT BIT_AND(c) FROM t
|
when table t is empty.
In my test, both queries return COUNT
= 0, but the BIT_AND() value differs:
original: 9223372036854775807 |
mutated : 18446744073709551615 |
This appears to be a logic bug because adding OR FALSE should not change the result of the query.
How to repeat
DROP DATABASE IF EXISTS logic_or_false_bitand;
|
CREATE DATABASE logic_or_false_bitand;
|
USE logic_or_false_bitand;
|
|
|
CREATE TABLE t(
|
c TINYINT(1) |
);
|
|
|
CREATE TABLE d(
|
y INT
|
);
|
|
|
INSERT INTO d VALUES (1); |
|
|
SELECT
|
(SELECT BIT_AND(c) FROM t) AS b,
|
COUNT(*) AS cnt
|
FROM (SELECT 63 AS x) AS s |
WHERE s.x IN (SELECT d.y FROM d);
|
|
|
SELECT
|
(SELECT BIT_AND(c) FROM t) AS b,
|
COUNT(*) AS cnt
|
FROM (SELECT 63 AS x) AS s |
WHERE s.x IN (SELECT d.y FROM d) OR FALSE;
|
Observed result
b cnt
|
9223372036854775807 0 |
|
|
b cnt
|
18446744073709551615 0 |
Expected result
Both queries should return the same result because P and P OR FALSE are logically equivalent.