Bug #12799 one result returned when change from summer time causes dup unique key recs
Submitted: 25 Aug 2005 9:46 Modified: 30 Sep 2009 14:06
Reporter: James Day Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.10-log, 4.1.13a-log on win, 4.1 on RHES3.0 OS:Windows (windows xp)
Assigned to: Assigned Account CPU Architecture:Any

[25 Aug 2005 9:46] James Day 
Description:
Two parts to this, first the single result return for a unique key timestamp after a summer time change has caused it to have duplicate reported values. Second, the different behavior for the timestamp range test between the unique key field and the unindexed field in teh three column version of the test.

mysql> select version();
+-------------+
| version()   |
+-------------+
| 4.1.13a-log |
+-------------+
1 row in set (0.03 sec)

mysql> create table dates (date timestamp, unique key dates$date (date));
Query OK, 0 rows affected (0.11 sec)

mysql> set time_zone='GMT';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dates values ('2003-09-26 03:00:00'),
    -> ('2003-09-26 04:00:00'),
    -> ('2003-09-26 05:00:00'),
    -> ('2003-09-26 06:00:00'),
    -> ('2003-09-26 07:00:00'),
    -> ('2003-10-26 03:00:00'),
    -> ('2003-10-26 04:00:00'),
    -> ('2003-10-26 05:00:00'),
    -> ('2003-10-26 06:00:00'),
    -> ('2003-10-26 07:00:00');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from dates;
+---------------------+
| date                |
+---------------------+
| 2003-09-26 03:00:00 |
| 2003-09-26 04:00:00 |
| 2003-09-26 05:00:00 |
| 2003-09-26 06:00:00 |
| 2003-09-26 07:00:00 |
| 2003-10-26 03:00:00 |
| 2003-10-26 04:00:00 |
| 2003-10-26 05:00:00 |
| 2003-10-26 06:00:00 |
| 2003-10-26 07:00:00 |
+---------------------+
10 rows in set (0.00 sec)

mysql> set time_zone='EST5EDT';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dates;
+---------------------+
| date                |
+---------------------+
| 2003-09-25 23:00:00 |
| 2003-09-26 00:00:00 |
| 2003-09-26 01:00:00 |
| 2003-09-26 02:00:00 |
| 2003-09-26 03:00:00 |
| 2003-10-25 23:00:00 |
| 2003-10-26 00:00:00 |
| 2003-10-26 01:00:00 |
| 2003-10-26 01:00:00 |
| 2003-10-26 02:00:00 |
+---------------------+
10 rows in set (0.01 sec)

mysql> -- observe now have two 01:00:00 times
mysql> select * from dates where date='2003-10-26 01:00:00';
+---------------------+
| date                |
+---------------------+
| 2003-10-26 01:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> -- Why only one result returned? Looks as though unique key is eliminatin
g multiple returns.
mysql> select * from dates where ( date>='2003-10-26 01:00:00' ) and ( date < '2
003-10-26 01:00:01' );
+---------------------+
| date                |
+---------------------+
| 2003-10-26 01:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- Same tests with both unique key field and unindexed field
mysql> set time_zone='GMT';
Query OK, 0 rows affected (0.00 sec)

mysql> create table dates2 (date timestamp, datenotkey timestamp, id int auto_in
crement, unique key dates2$date (date), key sate2$id(id) );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into dates2 (date, datenotkey) select date, date from dates;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from dates2;
+---------------------+---------------------+----+
| date                | datenotkey          | id |
+---------------------+---------------------+----+
| 2003-09-26 03:00:00 | 2003-09-26 03:00:00 |  1 |
| 2003-09-26 04:00:00 | 2003-09-26 04:00:00 |  2 |
| 2003-09-26 05:00:00 | 2003-09-26 05:00:00 |  3 |
| 2003-09-26 06:00:00 | 2003-09-26 06:00:00 |  4 |
| 2003-09-26 07:00:00 | 2003-09-26 07:00:00 |  5 |
| 2003-10-26 03:00:00 | 2003-10-26 03:00:00 |  6 |
| 2003-10-26 04:00:00 | 2003-10-26 04:00:00 |  7 |
| 2003-10-26 05:00:00 | 2003-10-26 05:00:00 |  8 |
| 2003-10-26 06:00:00 | 2003-10-26 06:00:00 |  9 |
| 2003-10-26 07:00:00 | 2003-10-26 07:00:00 | 10 |
+---------------------+---------------------+----+
10 rows in set (0.00 sec)

mysql> set time_zone='EST5EDT';
Query OK, 0 rows affected (0.01 sec)

mysql> -- unique key timestamp as before
mysql> select * from dates2;
+---------------------+---------------------+----+
| date                | datenotkey          | id |
+---------------------+---------------------+----+
| 2003-09-25 23:00:00 | 2003-09-25 23:00:00 |  1 |
| 2003-09-26 00:00:00 | 2003-09-26 00:00:00 |  2 |
| 2003-09-26 01:00:00 | 2003-09-26 01:00:00 |  3 |
| 2003-09-26 02:00:00 | 2003-09-26 02:00:00 |  4 |
| 2003-09-26 03:00:00 | 2003-09-26 03:00:00 |  5 |
| 2003-10-25 23:00:00 | 2003-10-25 23:00:00 |  6 |
| 2003-10-26 00:00:00 | 2003-10-26 00:00:00 |  7 |
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  8 |
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  9 |
| 2003-10-26 02:00:00 | 2003-10-26 02:00:00 | 10 |
+---------------------+---------------------+----+
10 rows in set (0.00 sec)

mysql> select * from dates2 where date='2003-10-26 01:00:00';
+---------------------+---------------------+----+
| date                | datenotkey          | id |
+---------------------+---------------------+----+
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  8 |
+---------------------+---------------------+----+
1 row in set (0.01 sec)

mysql> select * from dates2 where ( date>='2003-10-26 01:00:00' ) and ( date < '
2003-10-26 01:00:01' );
+---------------------+---------------------+----+
| date                | datenotkey          | id |
+---------------------+---------------------+----+
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  8 |
+---------------------+---------------------+----+
1 row in set (0.00 sec)

mysql> -- now the non-unique key timestamp
mysql> select * from dates2;
+---------------------+---------------------+----+
| date                | datenotkey          | id |
+---------------------+---------------------+----+
| 2003-09-25 23:00:00 | 2003-09-25 23:00:00 |  1 |
| 2003-09-26 00:00:00 | 2003-09-26 00:00:00 |  2 |
| 2003-09-26 01:00:00 | 2003-09-26 01:00:00 |  3 |
| 2003-09-26 02:00:00 | 2003-09-26 02:00:00 |  4 |
| 2003-09-26 03:00:00 | 2003-09-26 03:00:00 |  5 |
| 2003-10-25 23:00:00 | 2003-10-25 23:00:00 |  6 |
| 2003-10-26 00:00:00 | 2003-10-26 00:00:00 |  7 |
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  8 |
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  9 |
| 2003-10-26 02:00:00 | 2003-10-26 02:00:00 | 10 |
+---------------------+---------------------+----+
10 rows in set (0.00 sec)

mysql> select * from dates2 where datenotkey='2003-10-26 01:00:00';
+---------------------+---------------------+----+
| date                | datenotkey          | id |
+---------------------+---------------------+----+
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  8 |
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  9 |
+---------------------+---------------------+----+
2 rows in set (0.00 sec)

mysql> select * from dates2 where ( datenotkey>='2003-10-26 01:00:00' ) and ( da
tenotkey < '2003-10-26 01:00:01' );
+---------------------+---------------------+----+
| date                | datenotkey          | id |
+---------------------+---------------------+----+
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  8 |
| 2003-10-26 01:00:00 | 2003-10-26 01:00:00 |  9 |
+---------------------+---------------------+----+
2 rows in set (0.00 sec)

mysql> -- notice that this returned two results but only one was returned for th
e unique key field, yet the range test should have found both?

How to repeat:
create table dates (date timestamp, unique key dates$date (date));
set time_zone='GMT';
insert into dates values ('2003-09-26 03:00:00'),
('2003-09-26 04:00:00'),
('2003-09-26 05:00:00'),
('2003-09-26 06:00:00'),
('2003-09-26 07:00:00'),
('2003-10-26 03:00:00'),
('2003-10-26 04:00:00'),
('2003-10-26 05:00:00'),
('2003-10-26 06:00:00'),
('2003-10-26 07:00:00');
select * from dates;
set time_zone='EST5EDT';
select * from dates;
-- observe now have two 01:00:00 times
select * from dates where date='2003-10-26 01:00:00';
-- Why only one result returned? Looks as though unique key is eliminating multiple returns.
select * from dates where ( date>='2003-10-26 01:00:00' ) and ( date < '2003-10-26 01:00:01' );

-- Same tests with both unique key field and unindexed field
set time_zone='GMT';
create table dates2 (date timestamp, datenotkey timestamp, id int auto_increment, unique key dates2$date (date), key sate2$id(id) );
insert into dates2 (date, datenotkey) select date, date from dates;
select * from dates2;
set time_zone='EST5EDT';
-- unique key timestamp as before
select * from dates2;
select * from dates2 where date='2003-10-26 01:00:00';
select * from dates2 where ( date>='2003-10-26 01:00:00' ) and ( date < '2003-10-26 01:00:01' );
-- now the non-unique key timestamp
select * from dates2;
select * from dates2 where datenotkey='2003-10-26 01:00:00';
select * from dates2 where ( datenotkey>='2003-10-26 01:00:00' ) and ( datenotkey < '2003-10-26 01:00:01' );
-- notice that this returned two results but only one was returned for the unique key field, yet the range test should have found both?

Suggested fix:
If this is to return duplicate results for a unique key column which has duplicate timestamp values in it, it looks as though the optimiser needs to handle this possibility.
[25 Aug 2005 10:14] James Day 
Same result with Windows XP and 5.0.10-beta-log.
[5 Sep 2005 19:58] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29337
[9 Jun 2009 16:22] Ryan Huddleston 
One note on this old bug (was created based off a support issue I submitted years ago). I think this may be related to some innodb cascading crashing issues we are having. When replicating a statement that causes a duplicate key violation on timestamp columns in innodb it causes innodb to shut down so we had a situation recently where we crashed the master server and all the slaves via replication. Seems possibly related and has something to do with the behavior of timestamp columns.
[29 Jan 2010 9:52] Manyi Lu 
Comments from epotemkin:
(10:38:58 AM) manyi: it's a very tricky bug
(10:40:31 AM) epotemkin: the problem there is that unique index over a datetime with timezone field after changing to a daylight saving time should return 2 records
(10:40:42 AM) epotemkin: ie. become a non unique
(10:41:04 AM) epotemkin: and allowing this internally isn't that easy