| Bug #108938 | Date value change caused by ON | ||
|---|---|---|---|
| Submitted: | 31 Oct 2022 13:59 | Modified: | 13 Dec 2022 4:08 |
| Reporter: | ZongYin Hao | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | date, ON clause | ||
[31 Oct 2022 14:12]
MySQL Verification Team
Hello ZongYin Hao, Thank you for the report and test case. regards, Umesh
[13 Dec 2022 4:08]
ZongYin Hao
We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mysql/tags We found that the bug first occurred in mysql:5.7.6, it cannot be reproduced in mysql:5.7.5: mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.5-m15 | +-----------+ 1 row in set (0.01 sec) mysql> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1 +-------------------+ | f1 | +-------------------+ | 20180516222895.9 | | 20180516235959.9 | | 20180516236030.95 | +-------------------+ 3 rows in set (0.00 sec) mysql> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- sql2 +-------------------+ | f1 | +-------------------+ | 20180516222895.9 | | 20180516235959.9 | | 20180516236030.95 | +-------------------+ 3 rows in set (0.00 sec) Hope it can be helpful for your debugging.
Description: In theory, the result of sql1 ⊆ the result of sql2: SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1 SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- sql2 Because the 'ON 1' in sql2 is always true, but the 'ON f1 != 0' in sql1 may be false. However, the date value changed after changing 'ON f1 != 0' to 'ON 1', seems like a logical bug: mysql> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1 +----------+ | f1 | +----------+ | -11046 | | 2018 | | 2089.051 | +----------+ 3 rows in set (0.00 sec) mysql> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- sql2 +-------------------+ | f1 | +-------------------+ | 20180516222895.9 | | 20180516235959.9 | | 20180516236030.95 | +-------------------+ 3 rows in set (0.00 sec) How to repeat: drop table if exists t; create table t (c1 double); insert into t values (-13064),(0),(71.051); SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1 SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- sql2 Suggested fix: I think sql1 should return the same result as sql2.