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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.