Bug #1433 Incorrect data type encountered from SQL query
Submitted: 29 Sep 2003 12:37 Modified: 16 Oct 2003 12:03
Reporter: Vince Bryant Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:4.0.14 OS:Linux (linux)
Assigned to: Mark Matthews CPU Architecture:Any

[29 Sep 2003 12:37] Vince Bryant
Executing a query that uses an IF function with an ORDER BY statement on the result of the IF is not returning the expected data type.

How to repeat:
When a query of the following form is executed:

SELECT IF(timestamp1 IS NOT NULL, timestamp1, timestamp2) AS TS FROM my_table ORDER BY TS;

The value for the column returned by Connector/J's getObject method is an array of bytes (byte[]), rather than an expected String or java.sql.Timestamp.  However, if the ORDER BY phrase is dropped from the query then the expected String or java.sql.Timestamp is returned.  

It is not obvious if this is an issue with Connector/J or the MySQL database server.
[30 Sep 2003 2:20] Alexander Keremidarski
As you say it is not clear if problem is with Connector/J or at server side.

If you try


and check type of newly created table column TS becomes:

 `TS` bigint(14) NOT NULL default '0'

and ORDER BY does not affect this behaviour.
But I have side question which may be important too.

What is your table structure? If both columns are TIMESTAMPs they can never be NULL

IF(timestamp1 IS NOT NULL, timestamp1, timestamp2) will always return timestamp1 in case it is of TIMESTAMP type.

In any case ORDER clause should not affect returned type
[30 Sep 2003 8:33] Vince Bryant
Sorry, I mistakenly left the impression that the two columns are of type Timestamp, they are actually of type DateTime (which allows for null values).  When retrieving DateTime values through JDBC's getObject we typically get a java.sql.Timestamp which led to my confusion.
[30 Sep 2003 8:49] Mark Matthews
I wasn't able to repeat this using the nightly builds of 3.0.x or 3.1.x from http://downloads.mysql.com/snapshots.php

Could you test one of these versions please?
[2 Oct 2003 16:35] Vince Bryant
You are correct, this problem has been fixed in the recent stable release of Connector/J (we were using an earlier release).  Please go ahead and close this bug.