| Bug #7557 | 4.0 - 4.1: comparison with wrong datetime value: behaviour has changed | ||
|---|---|---|---|
| Submitted: | 28 Dec 2004 14:55 | Modified: | 30 Jun 2007 0:55 |
| Reporter: | Alexander Y. Fomichev | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 4.0.x 4.1 | OS: | Linux (linux) |
| Assigned to: | Lars Thalmann | CPU Architecture: | Any |
[28 Dec 2004 19:53]
MySQL Verification Team
This is the expected behavior of 4.1 like displayed in the warning message, however I changed the status to verified for the replication team analyze. mysql> SELECT * FROM t1 WHERE a > '9999-99-99 00:00:00'; Empty set, 1 warning (0.02 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------+ | Warning | 1292 | Truncated incorrect datetime value: '9999-99-99 00:00:00' | +---------+------+-----------------------------------------------------------+ 1 row in set (0.00 sec)
[23 Jun 2005 23:25]
Jim Winstead
This happens because Field_datetime:save_in_field() now reports a failure if it the data is invalid, so convert_constant_item() doesn't succeed, and the 'dt' field is compared against the binary string '9999-99-99 etc' instead of a datetime of '0000-00-00 00:00:00'. As Miguel guessed, someone from the replication team needs to take a closer look at this to see if anything could or should be done.
[9 Aug 2005 23:55]
Jim Winstead
The same thing now happens for a date specified with a trailing 'Z' as in the ISO-8601 format.
create table t1 (dt datetime);
insert into t1 values ('2005-02-16T18:00:00Z');
Warnings:
Warning 1264 Data truncated; out of range for column 'dt' at row 1
select * from t1 where dt > '2005-02-16T17:00:00';
dt
2005-02-16 18:00:00
select * from t1 where dt > '2005-02-16T17:00:00Z';
dt
Warnings:
Warning 1292 Truncated incorrect datetime value: '2005-02-16T17:00:00Z'
drop table t1;
[30 Jun 2007 0:55]
Trudy Pelzer
The behaviour change seems to have been intentional; both MySQL 4.0 and 4.1 are past their active-maintenance life cycle. So there will be no change made.
Description: 4.0.x and 4.1 returns different results of comparison of 'datetime' when operand(s) has an invalid value(s). According to documentation: "The MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to `'0000-00-00''. and 4.0 as I can see, exactly does it - converts an operand to '0000-00-00' and compares, but 4.1 always returns warning and an empty set. For this reason replication between 4.0(server) & 4.1 can be broken under some conditions. Is this a known problem or problem at all? How to repeat: 4.0.x mysql> CREATE TABLE t1 ( -> a datetime -> ) TYPE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES (now()); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM t1 WHERE a > '9999-99-99 00:00:00'; +---------------------+ | a | +---------------------+ | 2004-12-28 15:50:50 | +---------------------+ 1 row in set (0.00 sec) 4.1.7 mysql> CREATE TABLE `t1` ( -> `a` datetime default NULL -> ) TYPE=MyISAM; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> INSERT INTO t1 VALUES (now()); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t1 WHERE a > '9999-99-99 00:00:00'; Empty set, 1 warning (0.00 sec)