MySQL Bugs: #120848: Inconsistent comparison behavior for MAKETIME() expression between VIEW/CTE and TEMPORARY TABLE

Bug #120848	Inconsistent comparison behavior for MAKETIME() expression between VIEW/CTE and TEMPORARY TABLE
Submitted:	2 Jul 17:43	Modified:	3 Jul 16:34
Reporter:	Xiaoyuan Xie	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	9.6.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
A MAKETIME() expression behaves differently when used through a VIEW or CTE compared with a TEMPORARY TABLE created by CREATE TEMPORARY TABLE ... SELECT.

The expression:

MAKETIME(HOUR(c4), MINUTE(c4), SECOND(c4))
produces the displayed value 190:40:54.

When queried through a VIEW or CTE, the predicate:

vc < '23:59:59'
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 may use different comparison semantics. The VIEW/CTE result appears consistent with string comparison:

'190:40:54' < '23:59:59'  -> true
while the TEMPORARY TABLE result appears consistent with TIME comparison:

190:40:54 < 23:59:59  -> false
This may be due to CREATE TEMPORARY TABLE ... SELECT type derivation/materialization rules.

How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0     |
+-----------+

DROP TABLE IF EXISTS t0;
DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS tmp;

CREATE TABLE t0 (
  c4 TIME
);

INSERT INTO t0 VALUES ('-190:40:54');

CREATE VIEW v0 AS
SELECT MAKETIME(HOUR(c4), MINUTE(c4), SECOND(c4)) AS vc FROM t0;

SELECT vc FROM v0 WHERE vc < '23:59:59'; 

WITH cte AS (
  SELECT MAKETIME(HOUR(c4), MINUTE(c4), SECOND(c4)) AS vc FROM t0
)
SELECT vc FROM cte WHERE vc < '23:59:59'; -- 190:40:54

CREATE TEMPORARY TABLE tmp AS
SELECT MAKETIME(HOUR(c4), MINUTE(c4), SECOND(c4)) AS vc FROM t0;

SELECT vc FROM tmp WHERE vc < '23:59:59'; 

The VIEW query returns:
+-----------+
| vc        |
+-----------+
| 190:40:54 |
+-----------+

The CTE query returns:
+-----------+
| vc        |
+-----------+
| 190:40:54 |
+-----------+

The temporary table query returns:
Empty set

Hi Xiaoyuan Xie,

Thanks for the test case. Verified as described.

Thank you Chaithra and Roy for checking these reports.

I understand that Bug #120848 is the main report for this TIME-valued expression comparison issue, and that Bug #120849 and Bug #120851 are additional reproductions of the same underlying problem using `SEC_TO_TIME()` and `TIME()`.

I really appreciate the quick triage and the constructive feedback. This is very encouraging to me, and it gives me more confidence that these optimizer/data type correctness test cases can be useful to the MySQL community.

Thanks again!