Description:
In MySQL, a query using the FROM_UNIXTIME function and a NOT condition behaves inconsistently between a materialized table and a view with the same definition.
How to repeat:
1. Set up table t0:
CREATE TABLE t0 (c0 SMALLINT, c1 BOOLEAN, c2 INT);
INSERT INTO t0 (c0, c1, c2) VALUES (-30011, 68, -1948448895);
INSERT INTO t0 (c0, c1, c2) VALUES (-1007, 127, 2078435568);
INSERT INTO t0 (c0, c1, c2) VALUES (11406, 125, 684249916);
INSERT INTO t0 (c0, c1, c2) VALUES (-9955, -42, -403124655);
INSERT INTO t0 (c0, c1, c2) VALUES (2654, -17, -110740575);
INSERT INTO t0 (c0, c1, c2) VALUES (17815, 52, 525250067);
INSERT INTO t0 (c0, c1, c2) VALUES (24384, -55, 875534766);
INSERT INTO t0 (c0, c1, c2) VALUES (-9483, 4, 615742521);
INSERT INTO t0 (c0, c1, c2) VALUES (-11317, -35, 1781339192);
INSERT INTO t0 (c0, c1, c2) VALUES (-11633, 39, -1363181180);
INSERT INTO t0 (c0, c1, c2) VALUES (1638, -57, 1211495430);
INSERT INTO t0 (c0, c1, c2) VALUES (-18167, -52, 1452061860);
2. Create materialized table t1 and query it:
CREATE TABLE t1 AS (SELECT (FROM_UNIXTIME(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 );
SELECT (c0), c2 FROM t1 WHERE NOT c0 <= 116 ORDER BY c1 DESC, (c0) DESC;
Empty set (0.00 sec)
3. Create view t1 instead and query it:
CREATE VIEW t1 AS (SELECT (FROM_UNIXTIME(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 );
SELECT (c0), c2 FROM t1 WHERE NOT c0 <= 116 ORDER BY c1 DESC, (c0) DESC;
+---------------------+------------+
| c0 | c2 |
+---------------------+------------+
| 1970-01-01 03:10:06 | 684249916 |
| 1970-01-01 04:56:55 | 525250067 |
| 1970-01-01 00:44:14 | -110740575 |
| 1970-01-01 06:46:24 | 875534766 |
| 1970-01-01 00:27:18 | 1211495430 |
+---------------------+------------+
5 rows in set (0.00 sec)
Expected Behavior:
Both the materialized table and the view should return consistent results based on the query conditions.
