MySQL Bugs: #43696: Innodb different from other engines on delete time data type

Bug #43696	Innodb different from other engines on delete time data type
Submitted:	17 Mar 2009 10:38	Modified:	17 Mar 2009 11:03
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	6.0.11, 5.0, 5.1 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As mentioned in How to repeat section.

How to repeat:
CREATE TABLE t2(c1 TIME NOT NULL, c2 TIME NULL, PRIMARY KEY(c1,c2));
INSERT INTO t2 VALUES ('00:00:00.00','00:00:00.00'),('01:01:01.01','01:01:01.00'),('838:59:59.00','838:59:59.00'),('00:00:01','00:00:01'),('02:02:02','02:02:02'),('838:59:58','838:59:58'),('-838:59:59','-838:59:59'),('00:03','00:03'),('03:03','03:03'),('838:58','838:58'),('00 00:00:04','00 00:00:04'),('04 04:04:04','04 04:04:04'),('34 22:59:57','34 22:59:57'),('00 00:04','00 00:04'),('05 05:05','05 05:05'),('34 22:56','34 22:56'),('05 05','05 05'),('06 06','06 06'),('34 22','34 22'),('07','07'),('59','59');
INSERT INTO t2 VALUES('000008','000008'),('080808','080808'),('8385955','8385955'),('-8385955','-8385955'),('0009','0009'),('0909','0909'),('5454','5454'),('10','10'),('53','53');
INSERT INTO t2 VALUES (000011.00,000011.00),(111111.11,111111.11),(8385952.00,8385952.00),(000012,000012),(121212,121212),(8385951,8385951),(1313,1313),(5151,5151),(14,14),(50,50);
SET TIMESTAMP=1233216687;
INSERT INTO t2 VALUES(CURRENT_TIME(),CURRENT_TIME());
SELECT * FROM t2;
DELETE FROM t2 WHERE c1 IN ('100:04:04',005454,'2:2:2',111111) LIMIT 2;

difference in result with other engines (MyISAM, falcon, Maria)
-------------------
On query SELECT * FROM t2;

 02:02:02	02:02:02
 03:03:00	03:03:00
 08:08:08	08:08:08
-11:11:11	11:11:11
+100:04:04	100:04:04
 11:11:27	11:11:27
 125:00:00	125:00:00
 125:05:00	125:05:00

Please find attached the test case.

Test Case

Attachment: delete_time_bug_innodb.test (application/octet-stream, text), 1.15 KiB.

Thank you for the report.

Verified as described.

I am sorry : I was hurry.

You use LIMIT without ORDER BY in the DELETE statement, so it is expected what any 2 rows would be deleted, because different storage engine can internally sort rows in any way. So this is not a bug.

You're right, Thanks!