Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3, 13.0.2, 13.1.1
-
Platform: Ubuntu 24.04, x86_64
Description
The predicate a.x <= b.y with a.x REAL = 9223372036854775807 (stored as 9.223372036854776e18) and b.y BIGINT UNSIGNED = 9223372036854775807 evaluates to 1 as an expression and in EXISTS, but the JOIN using the same predicate returns no row. The JOIN plan reads b with "Range checked for each record".
How to reproduce
CREATE TABLE a(x REAL UNIQUE NOT NULL); |
CREATE TABLE b(y BIGINT UNSIGNED PRIMARY KEY); |
INSERT INTO a VALUES (9223372036854775807); |
INSERT INTO b VALUES (0), (9223372036854775807); |
|
|
SELECT a.x, b.y, a.x <= b.y FROM a, b; |
-- 9.223372036854776e18 0 0
|
-- 9.223372036854776e18 9223372036854775807 1
|
|
|
SELECT b.y FROM b WHERE EXISTS (SELECT 1 FROM a WHERE a.x <= b.y); |
-- 9223372036854775807
|
|
|
SELECT b.y FROM b JOIN a ON a.x <= b.y; |
-- expected: 9223372036854775807 (same predicate)
|
-- actual: Empty set
|
|
|
EXPLAIN SELECT b.y FROM b JOIN a ON a.x <= b.y; |
-- a: ALL
|
-- b: ALL possible_keys=PRIMARY Extra: Range checked for each record (index map: 0x1) |
Attachments
Issue Links
- relates to
-
MDEV-40273 Optimizer misses equality from x >= y AND x <= y join predicate
-
- Confirmed
-
-
MDEV-40680 MIN()/MAX() over BIGINT UNSIGNED loses the UNSIGNED flag when converted to a float
-
- Confirmed
-