Bug #43763 Difference in warnings generated between engines
Submitted: 20 Mar 2009 6:30 Modified: 20 Mar 2009 8:23
Reporter: Nidhi Shrotriya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:6.0.11, 5.0, 5.1, 6.0 bzr OS:Any
Assigned to: CPU Architecture:Any

[20 Mar 2009 6:30] Nidhi Shrotriya 
Description:
There is difference in warnings generated between MyISAM/Maria & Innodb/Falcon on the query as below.
SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
 c1	c2	c3
+Warning	1292	Truncated incorrect INTEGER value: '10 01:01:01'
+Warning	1292	Truncated incorrect INTEGER value: '9 01:01:01'
 Warning	1292	Truncated incorrect INTEGER value: '9 01:01:01'
 Warnings:

How to repeat:
Please find attached the test case file to reproduce.
[20 Mar 2009 6:31] Nidhi Shrotriya 
Test Case

Attachment: date_warnings_bug.test (application/octet-stream, text), 1.25 KiB.
[20 Mar 2009 7:20] Sveta Smirnova 
Thank you for the report.

What do you mean by difference?

For example, I get 
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warnings:

with MyISAM and

Warnings:
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'

with InnoDB.

So only order is different. Is it what do you report or do you have more differencies in your environment?
[20 Mar 2009 7:59] Nidhi Shrotriya 
I get this-
With Innodb/Falcon:-
----------------------
SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
c1      c2      c3
2009-03-06      2009-03-06      18
2009-03-07      2009-03-07      19
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warnings:
DELETE FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
Warnings:
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
c1      c2      c3
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warnings:

With MyISAM/Maria:
------------------------
SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
c1      c2      c3
2009-03-06      2009-03-06      18
2009-03-07      2009-03-07      19
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warnings:
DELETE FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
Warnings:
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
c1      c2      c3
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warnings:

So I get 2 more warnings with Innodb/Falcon with the last SELECT after DELETE.
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'

Before DELETE all give same no. of warnings.
[20 Mar 2009 8:23] Sveta Smirnova 
Thank you for the feedback.

Verified with slightly different results:

Maria after DELETE - 2 warnings:

SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
c1      c2      c3
Warnings:
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'

Others - 4 warnings:

SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
c1      c2      c3
Warnings:
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'

MyISAM before DELETE - order:

SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
c1      c2      c3
2009-03-06      2009-03-06      18
2009-03-07      2009-03-07      19
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warnings:

Others - order:

SELECT * FROM t1 WHERE c1=ADDDATE(CURDATE(),'9 01:01:01') OR c2=ADDDATE(CURDATE(),'10 01:01:01');
c1      c2      c3
2009-03-06      2009-03-06      18
2009-03-07      2009-03-07      19
Warnings:
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '9 01:01:01'
Warning 1292    Truncated incorrect INTEGER value: '10 01:01:01'

Different order is repeatable with versions 5.0 and 5.1 too