Description:
Querying a materialized table and a view that performs division yields inconsistent results.
How to repeat:
-- Step 1: Create table t0 and insert data
CREATE TABLE t0 (c0 BOOLEAN, c1 MEDIUMINT);
INSERT INTO t0 (c0, c1) VALUES (39, 193739);
INSERT INTO t0 (c0, c1) VALUES (2, -3481371);
INSERT INTO t0 (c0, c1) VALUES (106, -7344168);
-- Step 2: Create materialized table t1 and query it
CREATE TABLE t1 AS (SELECT (c0/c1) AS c0 FROM t0 );
SELECT (c0) FROM t1 WHERE ((((c0) > (7678030280.629463)) != ((c0) != (c0))) IN (SELECT (c0) FROM t1 WHERE (CASE WHEN ((c0) <= NULL) THEN ((-8050215088.012629)) ELSE ((-9947042529.593414)) END)) <= (((c0) = (c0)) OR ((c0) > (c0))) IN (SELECT (c0) FROM t1 WHERE (((c0) OR (c0)) <= (5495334925.845299 != -3726881417.315633))));
Empty set (0.002 sec)
-- Step 3: Create view t1 instead and query it
CREATE VIEW t1 AS (SELECT (c0/c1) AS c0 FROM t0 );
SELECT (c0) FROM t1 WHERE ((((c0) > (7678030280.629463)) != ((c0) != (c0))) IN (SELECT (c0) FROM t1 WHERE (CASE WHEN ((c0) <= NULL) THEN ((-8050215088.012629)) ELSE ((-9947042529.593414)) END)) <= (((c0) = (c0)) OR ((c0) > (c0))) IN (SELECT (c0) FROM t1 WHERE (((c0) OR (c0)) <= (5495334925.845299 != -3726881417.315633))));
+--------+
| c0 |
+--------+
| 0.0002 |
| 0.0000 |
| 0.0000 |
+--------+
3 rows in set (0.017 sec)
Suggested fix:
Expected behavior:
Both queries on the materialized table and the view should yield the same result.
Actual Behavior:
Query on Materialized Table (t1) Returns an empty set, while query on View (t1): Returns 3 rows.
