Description:
A `SEC_TO_TIME(TIME_TO_SEC(...))` expression behaves differently when used through a VIEW or CTE compared with a TEMPORARY TABLE created by `CREATE TEMPORARY TABLE ... SELECT`.
The expression:
SEC_TO_TIME(TIME_TO_SEC(c0))
produces the displayed value:
141:54:33
When queried through a VIEW or CTE, the predicate:
vc < '18:00:00'
returns true and the row is returned.
However, after the same expression is materialized into a TEMPORARY TABLE, the same predicate returns false and the result is empty.
This suggests that the VIEW/CTE form and the TEMPORARY TABLE form use different comparison semantics. The VIEW/CTE result appears consistent with string comparison:
'141:54:33' < '18:00:00' -> true
while the TEMPORARY TABLE result appears consistent with TIME comparison:
141:54:33 < 18:00:00 -> false
Since `SEC_TO_TIME()` returns a time value, I would expect the VIEW and CTE queries to use TIME comparison semantics as well. Therefore, the VIEW and CTE queries appear to return a wrong result.
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 TIME
);
INSERT INTO t0 VALUES ('141:54:33');
CREATE VIEW v0 AS
SELECT SEC_TO_TIME(TIME_TO_SEC(c0)) AS vc
FROM t0;
SELECT vc
FROM v0
WHERE vc < '18:00:00';
WITH cte AS (
SELECT SEC_TO_TIME(TIME_TO_SEC(c0)) AS vc
FROM t0
)
SELECT vc
FROM cte
WHERE vc < '18:00:00';
CREATE TEMPORARY TABLE tmp AS
SELECT SEC_TO_TIME(TIME_TO_SEC(c0)) AS vc
FROM t0;
SELECT vc
FROM tmp
WHERE vc < '18:00:00';
VIEW:
+-----------+
| vc |
+-----------+
| 141:54:33 |
+-----------+
CTE:
+-----------+
| vc |
+-----------+
| 141:54:33 |
+-----------+
TEMPORARY TABLE:
Empty set
Description: A `SEC_TO_TIME(TIME_TO_SEC(...))` expression behaves differently when used through a VIEW or CTE compared with a TEMPORARY TABLE created by `CREATE TEMPORARY TABLE ... SELECT`. The expression: SEC_TO_TIME(TIME_TO_SEC(c0)) produces the displayed value: 141:54:33 When queried through a VIEW or CTE, the predicate: vc < '18:00:00' returns true and the row is returned. However, after the same expression is materialized into a TEMPORARY TABLE, the same predicate returns false and the result is empty. This suggests that the VIEW/CTE form and the TEMPORARY TABLE form use different comparison semantics. The VIEW/CTE result appears consistent with string comparison: '141:54:33' < '18:00:00' -> true while the TEMPORARY TABLE result appears consistent with TIME comparison: 141:54:33 < 18:00:00 -> false Since `SEC_TO_TIME()` returns a time value, I would expect the VIEW and CTE queries to use TIME comparison semantics as well. Therefore, the VIEW and CTE queries appear to return a wrong result. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ DROP VIEW IF EXISTS v0; DROP TEMPORARY TABLE IF EXISTS tmp; DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 TIME ); INSERT INTO t0 VALUES ('141:54:33'); CREATE VIEW v0 AS SELECT SEC_TO_TIME(TIME_TO_SEC(c0)) AS vc FROM t0; SELECT vc FROM v0 WHERE vc < '18:00:00'; WITH cte AS ( SELECT SEC_TO_TIME(TIME_TO_SEC(c0)) AS vc FROM t0 ) SELECT vc FROM cte WHERE vc < '18:00:00'; CREATE TEMPORARY TABLE tmp AS SELECT SEC_TO_TIME(TIME_TO_SEC(c0)) AS vc FROM t0; SELECT vc FROM tmp WHERE vc < '18:00:00'; VIEW: +-----------+ | vc | +-----------+ | 141:54:33 | +-----------+ CTE: +-----------+ | vc | +-----------+ | 141:54:33 | +-----------+ TEMPORARY TABLE: Empty set