Bug #111675 wrong result when compare timestamp with undependent subselect that return time
Submitted: 6 Jul 2023 8:36 Modified: 6 Jul 2023 13:03
Reporter: quan chen (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[6 Jul 2023 8:36] quan chen
Description:
create table test_1(id bigint primary key, timestamp_test timestamp);
create table teacher_1(id bigint primary key, tname char(5), grade int);
create table test_datatype_list_1(id bigint primary key, time_test time);
insert into test_1 values(1, '2003-04-04 04:23:34'),(2, '2013-04-03 22:34:12');
insert into teacher_1 values(1,'t_1',4),(2,'t_2',2);
insert into test_datatype_list_1 values(1, '15:47:28');

query1:
SELECT timestamp_test from test natural join teacher  where timestamp_test < (select time_test from test_datatype_list where id=1) order by 1;

mysql [localhost:8032] {msandbox} (test) > SELECT timestamp_test from test natural join teacher  where timestamp_test < (select time_test from test_datatype_list where id=1) order by 1;
Empty set, 11 warnings (0.00 sec)

This query(query1) return empty result, but it should be return:
+---------------------+
| timestamp_test      |
+---------------------+
| 2003-04-04 04:23:34 |
| 2013-04-03 22:34:12 |
+---------------------+
2 rows in set (0.00 sec)

The semantics of this query is same to the following query(query2),
but following query can return results, while the above query return empty set.

query2:
SELECT timestamp_test from (test_1 natural join teacher_1) join  (select time_test from test_datatype_list_1 where id=1) dt where timestamp_test < dt.time_test order by 1;

mysql [localhost:8032] {msandbox} (test) > SELECT timestamp_test from (test_1 natural join teacher_1) join  (select time_test from test_datatype_list_1 where id=1) dt where timestamp_test < dt.time_test order by 1;
+---------------------+
| timestamp_test      |
+---------------------+
| 2003-04-04 04:23:34 |
| 2013-04-03 22:34:12 |
+---------------------+

How to repeat:
Run the sql in the description.
[6 Jul 2023 8:46] quan chen
Sorry, the query1 is:
SELECT timestamp_test from test_1 natural join teacher_1 where timestamp_test < (select time_test from test_datatype_list_1 where id=1) order by 1;
[6 Jul 2023 13:03] MySQL Verification Team
Hi Mr. chen,

Thank you for your bug report.

We have managed to repeat your results.

In our opinion, this is not a bug. In the first case, your query returns empty results, since you can not compare TIMESTAMP (which is very similar to DATETIME) to TIME column. You will get zero results. Hence, that is why your first query does not return anything.

In the second query, you compare the same incompatible columns, but since the result comes from the derived table, which contains both timestamp and time types, time tuples get converted to the timestamp one. This is because MySQL is not and will not be in the near future, a strict type testing software.

Both queries should return zero results.

Also, the construct of the type (test_1 natural join teacher_1) should return the syntax error.

Your test case is also very inadequate, since you mix tables with '_1' ending and without it.

It is quite possible that this report finishes as a Documentation bug, because this is a very low priority bug due to the way that type conversion is functioning.

Verified.
[7 Jul 2023 6:32] Roy Lyseng
Workaround:

  CAST(time_test AS DATETIME)

(MySQL does not currently support cast to TIMESTAMP).
But note that this cast adds today's date to the time value.
[7 Jul 2023 12:02] MySQL Verification Team
Thank you, Roy .....