| Bug #3975 | Using a field and +/- for date calculation gives wrong result | ||
|---|---|---|---|
| Submitted: | 2 Jun 2004 22:24 | Modified: | 25 Jun 2004 19:08 |
| Reporter: | Brian Moon | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.0.16 | OS: | Linux (RedHat 9 Linux (2.4.20)) |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[2 Jun 2004 23:23]
Brian Moon
FYI, I installed 4.0.20 and it is still happening.
[2 Jun 2004 23:47]
Matthew Lord
I was able to repeat the problem in 4.0.20 but not in 4.1.2. The problem seems to be from not converting both values used in the comparison to the same type (timestamp) before converting again to do the > <.
[3 Jun 2004 0:01]
Matthew Lord
You can use the --new option with 4.0.16 or greater to make 4.0 behave more like 4.1. [mysqld] new Among other things the format for timestamps changed so that it's the same as datetimes and the problem does not surface.
[25 Jun 2004 19:08]
Dmitry Lenev
Hi, Brian! In non trivial cases MySQL compares datetime expressions either like integers (converting datetime '2003-01-01 00:00:00' to 20030101000000) or like strings. This approach works great in most cases. Unfortunately it does not work so well in your case. In query: select * from tsbug where ts >= (dt - interval 30 day); ts value is treated as integer and (dt - interval 30 day) as integer too. But since (dt - interval 30 day) is date value (not full blown date-time value) it converted to something like 20040503 and ts value is converted to something like 20030603152236, thus the result you get... To fix this behavior we either have to make both values to be treated as strings or add separate internal datetime type which should be used in comparisons... Both of these approaches are too intrusive to be implemented in 4.0. And 4.1 takes first approach... But I think in the long run we will actually take 2nd approach. So you probably should stick to 4.1 or use some workaround like: select * from tsbug where ts >= (dt - interval 30 day)*1000000; Thank you for raising up this issue!
Description: This is a bit obscure so bare with me. When I compare a timestamp field to an INTERVAL expression that involves a date field in conjunction with a - I get erroneous results. Example query: select * from tsbug where ts >= dt - interval 30 day; How to repeat: I am actually doing this in a join, so it makes more sense. In my case, I don't want rows where timstamp field in table A is older than 30 days before date field in table B. But this simple, one table example does reproduce the bug: Create a simple table: mysql> create table tsbug (id int auto_increment primary key, ts timestamp, dt date); Query OK, 0 rows affected (0.36 sec) insert some data into it for testing. mysql> insert into tsbug set ts=now() - interval 365 day, dt=now(); Query OK, 1 row affected (0.01 sec) mysql> select * from tsbug -> ; +----+----------------+------------+ | id | ts | dt | +----+----------------+------------+ | 1 | 20030603152236 | 2004-06-02 | +----+----------------+------------+ 1 row in set (0.00 sec) Now, try and get rows where ts is greater than dt - 30 days. mysql> select * from tsbug where ts >= dt - interval 30 day; +----+----------------+------------+ | id | ts | dt | +----+----------------+------------+ | 1 | 20030603152236 | 2004-06-02 | +----+----------------+------------+ 1 row in set (0.00 sec) As you see the result is wrong. At first I thought this was just bad SQL and using the - and INTERVAL was bad, but I found text in the docs that seemed to say it was fine. There were very similar examples in the docs after this text. --- http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html As of MySQL 3.23, INTERVAL expr type is allowed on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr type is allowed only on the right side, because it makes no sense to subtract a date or datetime value from an interval. (See examples below.) --- So, perhaps I did not realize what the subtraction clause was returning, so I added it to the field list: mysql> select tsbug.*, dt - interval 30 day as cutoff from tsbug where ts >= dt - interval 30 day; +----+----------------+------------+------------+ | id | ts | dt | cutoff | +----+----------------+------------+------------+ | 1 | 20030603152236 | 2004-06-02 | 2004-05-03 | +----+----------------+------------+------------+ 1 row in set (0.00 sec) That is what I expected. Hmmm, maybe I should just use DATE_SUB and not fiddle with fuzzy syntax: mysql> select tsbug.*, date_sub(dt, interval 30 day) as cutoff from tsbug where ts >= date_sub(dt, interval 30 day); +----+----------------+------------+------------+ | id | ts | dt | cutoff | +----+----------------+------------+------------+ | 1 | 20030603152236 | 2004-06-02 | 2004-05-03 | +----+----------------+------------+------------+ 1 row in set (0.00 sec) Nope, still wrong. So, next, I nixed the subtraction clause and just used a string date. mysql> select tsbug.*, dt - interval 30 day as cutoff from tsbug where ts >= '2004-05-03'; Empty set (0.00 sec) That worked, so I tried one last thing, using a string date in place of the field name in the subtraction clause. mysql> select tsbug.*, dt - interval 30 day as cutoff from tsbug where ts >= ('2004-06-02' - interval 30 day); Empty set (0.00 sec) Viola, that works too. The only thing I could find that would let me compare the date field to the timestamp field while using the INTERVAL subtraction was to format the result of the subtraction into a timestamp format: Workaround: mysql> select tsbug.*, dt - interval 30 day as cutoff from tsbug where ts >= date_format(dt - interval 30 day, '%Y%m%d000000'); Empty set (0.00 sec) Suggested fix: IMHO, there should be no difference between using a string date and a date field in this situation. If the string date would not have worked, I would have chalked it up to bad SQL. But, since the string date works I would expect that using the date field should work.