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: | |
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
[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 .....