| Bug #11887 | Inconsistant handling of truncated DateTime values | ||
|---|---|---|---|
| Submitted: | 12 Jul 2005 15:21 | Modified: | 22 Aug 2005 20:33 |
| Reporter: | Kevin Campbell | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.11 | OS: | Linux (Fedora Core 3) |
| Assigned to: | CPU Architecture: | Any | |
[12 Jul 2005 17:48]
Alexander Keremidarski
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: This is actually expected behaviour although not very obvious. What happens is combination of two facts: 1) MySQL does not support temporal types with time zone 2) MySQL allows implicit type conversions To understand better what happens with the query resuting in empty set try the following: mysql> SELECT ts, '2001/01/01 12:00:00 +0100' AS v, GREATEST(ts, '2001/01/01 12:00:00 +0100') AS c FROM example\G *************************** 1. row *************************** ts: 2001-01-01 12:00:00 v: 2001/01/01 12:00:00 +0100 c: 2001/01/01 12:00:00 +0100 What happens here is that TIMESTAMP column is convered to string before comparison. MySQL does bot attempt to covert the string value to TIMESTAMP. Obviosuly these two string values are different and according to string comparison rules '2001/01/01 12:00:00 +0100' is "bigger" than '2001-01-01 12:00:00' simply because: mysql> SELECT GREATEST('-', '/') AS g; +---+ | g | +---+ | / | +---+
[13 Jul 2005 12:49]
Kevin Campbell
1) MySQL does not support temporal types with time zone
2) MySQL allows implicit type conversions
"What happens here is that TIMESTAMP column is convered to string before
comparison. MySQL does bot attempt to covert the string value to TIMESTAMP."
------------------------------------------------------
The above comments are noted, but the given explanation does not match the resulting behaviour. If the comparison was done as strings and not DATETIME objects, the following would be expected to fail.
mysql> SELECT * FROM example WHERE ts = '2001/01/01 12:00:00';
+---------------------+
| ts |
+---------------------+
| 2001-01-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)
From the warning messages on the previous bug submission, it would appear that MySQL is attempting to cast the supplied value to a DATETIME type before doing the comparison. If the default behaviour is to compare as strings, why is the given warning raised? The warning is not raised when the cast is done on a string without the timestamp. Examples are as follows:
SELECT * FROM example WHERE ts >= '2001/01/01 12:00:00 +0100';
Empty set, 1 warning (0.00 sec)
mysql> show warnings;
| Warning | 1292 | Truncated incorrect datetime value: '2001/01/01 12:00:00 +0100' |
mysql> SELECT * FROM example WHERE ts = (SELECT CAST('2001/01/01 12:00:00' AS DATETIME));
+---------------------+
| ts |
+---------------------+
| 2001-01-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM example WHERE ts = '2001/01/01 12:00:00';
+---------------------+
| ts |
+---------------------+
| 2001-01-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)
Apologies is this is down my misunderstanding of the documentation.
Regards,
Kevin
[22 Aug 2005 20:33]
Sergei Golubchik
Manual (http://dev.mysql.com/doc/mysql/en/comparison-operators.html) tells * If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. So, '2001/01/01 12:00:00 +0100' is converted to a timestamp value, timezone is not supported, thus '+0100' is considered a garbage, and a warning is issued.
Description: Handling of parsing of DateTime fields varies depending on context. Inputs supplied with timezone information have that timezone information silently dropped on INSERT statements, and the Date and Time are used without the timezone. When parsed as part of a comparison operator, however, the parsing fails and the Date and Time without the timezone are not returned. It is not known what the expected behaviour from the SQL standards for this are, but the inconsitancy in handling the parsing is presumably not the intended behaviour. This is currently breaking code written in zope which connects to the MySQL using the ZMySQLDA database adapter. How to repeat: CREATE TABLE example ( ts DATETIME ); Query OK, 0 rows affected (0.00 sec) INSERT INTO example (ts) VALUES ('2001-01-01 12:00:00 +0100'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------+ | Warning | 1264 | Data truncated; out of range for column 'ts' at row 1 | +---------+------+-------------------------------------------------------+ SELECT * FROM example WHERE ts >= (SELECT CAST('2001/01/01 12:00:00 +0100' AS DATETIME)); +---------------------+ | ts | +---------------------+ | 2001-01-01 12:00:00 | +---------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect datetime value: '2001/01/01 12:00:00 +0100' | +---------+------+-----------------------------------------------------------------+ SELECT * FROM example WHERE ts >= '2001/01/01 12:00:00 +0100'; Empty set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect datetime value: '2001/01/01 12:00:00 +0100' | +---------+------+-----------------------------------------------------------------+ Suggested fix: Workaround is possible by altering all SQL comparions using DATETIME fields so that the input to the comparisons has already been casted to a DATETIME type. (SELECT CAST('2001/01/01 12:00:00 +0100' AS DATETIME));