| Bug #56025 | Weird date between behavior, not consistent between versions | ||
|---|---|---|---|
| Submitted: | 16 Aug 2010 18:25 | Modified: | 16 Aug 2010 18:37 |
| Reporter: | Kevin ultimate | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.67, 5.0, 5.1, 5.6.99 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | between, date | ||
[16 Aug 2010 18:37]
Sveta Smirnova
Thank you for the report.
Verified as described:
mysql> SELECT CAST('2005-01-01' AS DATE) BETWEEN '20090-01-01' AND '2010-01-01';
+-------------------------------------------------------------------+
| CAST('2005-01-01' AS DATE) BETWEEN '20090-01-01' AND '2010-01-01' |
+-------------------------------------------------------------------+
| 1 |
+-------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Warning (Code 1292): Truncated incorrect datetime value: '20090-01-01'
mysql> select cast('20090-01-01' as date);
+------------------------------+
| cast('20090-01-01' as date) |
+------------------------------+
| NULL |
+------------------------------+
1 row in set, 1 warning (0.00 sec)
Warning (Code 1292): Incorrect datetime value: '20090-01-01'
mysql> select 3 between null and 5;
+----------------------+
| 3 between null and 5 |
+----------------------+
| NULL |
+----------------------+
1 row in set (0.00 sec)
mysql> select cast('20090-01-01' as datetime);
+----------------------------------+
| cast('20090-01-01' as datetime) |
+----------------------------------+
| NULL |
+----------------------------------+
1 row in set, 1 warning (0.00 sec)
Warning (Code 1292): Incorrect datetime value: '20090-01-01'
mysql> SELECT CAST('2005-01-01' AS DATE) BETWEEN null AND '2010-01-01';
+-----------------------------------------------------------+
| CAST('2005-01-01' AS DATE) BETWEEN null AND '2010-01-01' |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+
1 row in set (0.01 sec)
Description: The following query returns 1 (true) on my MySQL 5.0.67 Community Edition, when it clearly should not (I cannot think of any way they could be converted that would lead to a true result): SELECT CAST('2005-01-01' AS DATE) BETWEEN '20090-01-01' AND '2010-01-01' The same query on MySQL 4.1.20 returns 0 (false). Why the change in behavior? How to repeat: SELECT CAST('2005-01-01' AS DATE) BETWEEN '20090-01-01' AND '2010-01-01'