MySQL Bugs: #52824: Comparing DATE against the result of ADDTIME is inconsistent, non-deterministic

Bug #52824	Comparing DATE against the result of ADDTIME is inconsistent, non-deterministic
Submitted:	14 Apr 2010 14:08	Modified:	1 Oct 2010 7:23
Reporter:	Nirbhay Choubey	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.5.5-m3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A SELECT query when uses ADDTIME() in WHERE condition, gives different output
for 5.5.5-m3 and 5.1.45 server releases, for a table that has 2 or more rows. 

BUT, this is noticed only when the statement is run using *MTR* (i.e. as part of .test), while when the same statement is executed using mysql client, output for both the server releases looks similar.
 

How to repeat:
Run the attached testsuite using MTR against the above mentioned server releases.

I do not see anything attached. Please, check.

MTR testsuite for the bug

Attachment: bug_52824.tar.gz (application/x-gzip, text), 463 bytes.

Thank you for the report.

Verified as described. Command line produces same results.

I do get inconsistent results in recent 5.5, looks like comparing time against date gives unpredictable results.

CREATE TABLE t1(c1 DATE NOT NULL PRIMARY KEY);
INSERT INTO t1 VALUES('2001-01-01'),('2001-01-02');

Now try some variants:

SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),0);

This AT FIRST gives the expected result (2 rows) from mysqltest (mtr), but this variant gives 0 rows:

SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),'0');

However, when I try again a few minutes later it DOES NOT.

Even turning the inequality gives 0 rows:

SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'0');

Only != gives both rows.

Then I try with mysql and get the consistently inconsistent result:

mysql> INSERT INTO t1 VALUES('2001-01-01'),('2001-01-02');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE c1 < ADDTIME(NOW(),0);
Empty set (0.00 sec)

mysql> SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),0);
+------------+
| c1         |
+------------+
| 2001-01-01 |
| 2001-01-02 |
+------------+
2 rows in set (0.00 sec)

In other words, it's wrong also in mysql (in 5.5), and what's more, the result appears to be non-deterministic. The first times I tried via mtr, it appeared to give the correct result if I dropped the ' around the 0 argument to ADDTIME, but later it did not.

This looks similar to Bug#57039

Duplicate of the bug#57039.