Description:
Query using the GREATEST function and logical conditions (1 > c0) produces inconsistent results when executed on a materialized table versus a view with the same definition.
How to repeat:
1. Set up table t0:
CREATE TABLE t0 (c0 DOUBLE, c1 TIME, c2 DOUBLE, c3 BOOLEAN);
INSERT INTO t0 (c0, c1, c2, c3) VALUES (-8.334868730580727e+29, '00:27:32', 6.509225676008042e+29, -50);
2. Create materialized table t1 and query it:
CREATE TABLE t1 AS (SELECT (GREATEST(c0,c1)) AS c0 , c2 AS c2, c3 AS c3 FROM t0 );
SELECT c2, (c0), c3 FROM t1 WHERE (1 > c0) ORDER BY (c0) DESC, c3 DESC, c2 ASC;
+----------------------+----------+------+
| c2 | c0 | c3 |
+----------------------+----------+------+
| 6.509225676008042e29 | 00:27:32 | -50 |
+----------------------+----------+------+
1 row in set, 1 warning (0.00 sec)
3. Create view t1 instead and query it:
CREATE VIEW t1 AS (SELECT (GREATEST(c0,c1)) AS c0 , c2 AS c2, c3 AS c3 FROM t0 );
SELECT c2, (c0), c3 FROM t1 WHERE (1 > c0) ORDER BY (c0) DESC, c3 DESC, c2 ASC;
Empty set (0.01 sec)
Suggested fix:
Both the materialized table and the view should return consistent results based on the query conditions.
