MySQL Bugs: #30645: Error occurs while accessing a MySQL column having a date in null date format.

Bug #30645	Error occurs while accessing a MySQL column having a date in null date format.
Submitted:	27 Aug 2007 13:56	Modified:	28 Aug 2007 13:23
Reporter:	Dipanwita Sarkar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	MySQL4/5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Say we have a MYSQL table MyTable with datetime field testdatetime having value (0000-00-00 00:00:00). Querying this column as 'select testdatetime from MyTable' should return null date. It does so for MySQL3 but throws error for MySQL4/5. 

How to repeat:
1. Enter a null date in MySQL table (0000-00-00 00:00:00). 
2. Query the table column.
3. Results:Error Executing Database Query. 

Expected results:Query should return null date.

Kindly let us know if you need any more information or repro details for this issue.

Sorry, but I was not able to repeat the behaviour described according to your description:

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.44-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.02 sec)

mysql> create table dt(tdt datetime);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into dt values('0000-00-00 00:00:00');
Query OK, 1 row affected (0.08 sec)

mysql> select * from dt;
+---------------------+
| tdt                 |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> show variables like 'sql%';
+-----------------+-------------------------------------------------------------
---+
| Variable_name   | Value
   |
+-----------------+-------------------------------------------------------------
---+
| sql_big_selects | ON
   |
| sql_mode        | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI
ON |
| sql_notes       | ON
   |
| sql_warnings    | OFF
   |
+-----------------+-------------------------------------------------------------
---+
4 rows in set (0.00 sec)

So, please, specify the exact version(s) used (5.0.x) and just copy-paste your exact test case, as I did above.

Java repro case to be used with MySQL4/5 and driver mysql-connector-java-commercial-5.0.5-bin.jar.

Attachment: MySqlMain.java (text/plain), 3.20 KiB.

See the configuration parameter "zeroDatetimeBehavior" in the manual. Later versions of Connector/J changed behavior to be more SQL and JDBC compliant:

http://dev.mysql.com/doc/refman/5.0/en/connector-j-installing-upgrading.html