| Bug #43100 | Discrepancy between engines MyISAM/Maria wrong? with Year(2) type | ||
|---|---|---|---|
| Submitted: | 23 Feb 2009 7:54 | Modified: | 25 Feb 2009 7:01 |
| Reporter: | Nidhi Shrotriya | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 6.0.10, 4.1, 5.0, 5.1, 6.0 bzr | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[23 Feb 2009 8:02]
Valeriy Kravchuk
Thank you for the problem report. What exact version, 6.0.x, do you use?
[23 Feb 2009 8:10]
Nidhi Shrotriya
Updated version as 6.0.10.
[23 Feb 2009 16:28]
Valeriy Kravchuk
Thank you for the problem report. Please, upload a dump of data for the table.
[25 Feb 2009 6:47]
Nidhi Shrotriya
Table's dump
Attachment: 43100_data.txt (text/plain), 2.31 KiB.
[25 Feb 2009 6:48]
Nidhi Shrotriya
Attached file to reproduce.
[25 Feb 2009 7:01]
Sveta Smirnova
Thank you for the report. Verified as described.
[16 Mar 2009 9:25]
Nidhi Shrotriya
Innodb, Falcon and PBXT exhibit same behavior in this case. Problem seems to be with MyISAM & Maria.
[16 Mar 2009 9:51]
Nidhi Shrotriya
Test Case for MyISAM/Maria different than Innodb/Falcon/PBXT
Attachment: insert_year_bug_myisam_maria_43100.test (application/octet-stream, text), 1.53 KiB.
[16 Mar 2009 9:53]
Nidhi Shrotriya
There is another case where Innodb also behaves same as MyISAM/Maria and different than Falcon/PBXT. Though the results from Falcon/PBXT seem to be correct. Attaching the test case.
[16 Mar 2009 9:54]
Nidhi Shrotriya
Test Case for MyISAM/Innodb/Maria different from Falcon/PBXT
Attachment: insert_year_bug_myisam_innodb_maria_43100.test (application/octet-stream, text), 1.33 KiB.
Description: There is difference in result with MyISAM & Innodb for Year(2) type. Query: SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2; I don't know which is the correct behaviour(MyISAM or Innodb), so logging it as discrepancy. I believe the correct behavior can be identified and the bug can be moved to the particular engine. How to repeat: CREATE TABLE t2(c1 YEAR(2) NOT NULL, c2 YEAR(2) NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1,c2)); Values are inserted. MyISAM: With YEAR(4) SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2; c1 c2 c3 c4 2020 2020 1998-12-16 1998-12-16 11:30:45 2020 2030 1998-12-16 1998-12-16 11:30:45 2040 2020 1998-12-15 1998-12-15 11:30:45 2040 2040 1998-12-15 1998-12-15 11:30:45 2060 2060 1998-12-14 1998-12-14 11:30:45 2069 2069 1998-12-13 1998-12-13 11:30:45 2099 2099 1998-12-27 1998-12-27 11:30:45 2100 2100 1998-12-26 1998-12-26 11:30:45 2155 2155 1998-12-26 1998-12-26 11:30:45 With YEAR(2) SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2; c1 c2 c3 c4 70 70 1998-12-12 1998-12-12 11:30:45 90 90 1998-12-11 1998-12-11 11:30:45 99 98 1998-12-10 1998-12-10 11:30:45 99 99 1998-12-30 1998-12-30 11:30:45 20 20 1998-12-16 1998-12-16 11:30:45 20 30 1998-12-16 1998-12-16 11:30:45 40 20 1998-12-15 1998-12-15 11:30:45 40 40 1998-12-15 1998-12-15 11:30:45 60 60 1998-12-14 1998-12-14 11:30:45 69 69 1998-12-13 1998-12-13 11:30:45 99 99 1998-12-27 1998-12-27 11:30:45 55 55 1998-12-26 1998-12-26 11:30:45 Innodb: With YEAR(4) SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2; c1 c2 c3 c4 2020 2020 1998-12-16 1998-12-16 11:30:45 2020 2030 1998-12-16 1998-12-16 11:30:45 2040 2020 1998-12-15 1998-12-15 11:30:45 2040 2040 1998-12-15 1998-12-15 11:30:45 2060 2060 1998-12-14 1998-12-14 11:30:45 2069 2069 1998-12-13 1998-12-13 11:30:45 2099 2099 1998-12-27 1998-12-27 11:30:45 2100 2100 1998-12-26 1998-12-26 11:30:45 2155 2155 1998-12-26 1998-12-26 11:30:45 With YEAR(2) SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2; c1 c2 c3 c4 20 20 1998-12-16 1998-12-16 11:30:45 20 30 1998-12-16 1998-12-16 11:30:45 40 20 1998-12-15 1998-12-15 11:30:45 40 40 1998-12-15 1998-12-15 11:30:45 60 60 1998-12-14 1998-12-14 11:30:45 69 69 1998-12-13 1998-12-13 11:30:45 99 99 1998-12-27 1998-12-27 11:30:45 55 55 1998-12-26 1998-12-26 11:30:45 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx The rows that are not found in Innodb's result are: 70 70 1998-12-12 1998-12-12 11:30:45 90 90 1998-12-11 1998-12-11 11:30:45 99 98 1998-12-10 1998-12-10 11:30:45 99 99 1998-12-30 1998-12-30 11:30:45 The above rows are inserted in the table as: ('1999','1999','98-12-30','98.12.30 11:30:45'), ('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','99','98-12-10','98.12.10 11:30:45');