| Bug #15927 | date = 1234 equal 0 !? | ||
|---|---|---|---|
| Submitted: | 22 Dec 2005 8:21 | Modified: | 22 Jan 2006 18:48 |
| Reporter: | jo soares | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.24_Debian-10sarge1-log | OS: | Linux (Linux Debian 2.6.9 i686) |
| Assigned to: | CPU Architecture: | Any | |
[22 Dec 2005 8:21]
jo soares
[22 Dec 2005 12:20]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of the SHOW CREATE TABLE 2005_ordini statement and some rows to insert into that table. I need them to create a repeatable test case.
[22 Dec 2005 12:37]
jo soares
mysql> create table 2005ordini (id int auto_increment primary key, my_date date)
-> ;
Query OK, 0 rows affected (0.09 sec)
mysql> insert into 2005ordini (my_date) values ('2005-12-01');
Query OK, 1 row affected (0.02 sec)
mysql> insert into 2005ordini (my_date) values ('2005-12-02');
Query OK, 1 row affected (0.00 sec)
mysql> insert into 2005ordini (my_date) values ('2005-12-03');
Query OK, 1 row affected (0.00 sec)
mysql> insert into 2005ordini (my_date) values ('2005-12-04');
Query OK, 1 row affected (0.00 sec)
mysql> insert into 2005ordini (my_date) values ('2005-12-05');
Query OK, 1 row affected (0.00 sec)
mysql> insert into 2005ordini (my_date) values ('2005-11-31');
Query OK, 1 row affected (0.00 sec)
mysql> insert into 2005ordini (my_date) values ('2005-09-31');
Query OK, 1 row affected (0.00 sec)
mysql> insert into 2005ordini (my_date) values (0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into 2005ordini (my_date) values (0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from 2005ordini;
+----+------------+
| id | my_date |
+----+------------+
| 1 | 2005-12-01 |
| 2 | 2005-12-02 |
| 3 | 2005-12-03 |
| 4 | 2005-12-04 |
| 5 | 2005-12-05 |
| 6 | 2005-11-31 |
| 7 | 2005-09-31 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
+----+------------+
9 rows in set (0.00 sec)
mysql> select * from 2005ordini where my_date=123;
Empty set (0.00 sec)
mysql> select * from 2005ordini where my_date=1234;
+----+------------+
| id | my_date |
+----+------------+
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
+----+------------+
2 rows in set (0.00 sec)
[22 Dec 2005 12:39]
jo soares
2005-09-31 and 2005-11-31 doesn't exist in the Gregorian Calendar.
[22 Dec 2005 18:48]
Valeriy Kravchuk
I was not able to repeat the problem you described with newer versions of MySQL:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.19
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table 2005ordini (id int auto_increment primary key, my_date
-> date);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into 2005ordini (my_date) values ('2005-12-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into 2005ordini (my_date) values (0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from 2005ordini where my_date=123;
Empty set (0.00 sec)
mysql> select * from 2005ordini where my_date=1234;
Empty set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '1234' for column 'my_date' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)
So, please, try to upgrade to 4.0.26, at least, and check once more. Inform about the results.
As for your last comment, manual (http://dev.mysql.com/doc/refman/5.0/en/datetime.html) clearly describes, that before 5.0.3 only basic checks were performed. You have to check that incorrect dates in your application.
[23 Jan 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
