MySQL Bugs: #1433: Incorrect data type encountered from SQL query

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:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	4.0.14	OS:	Linux (linux)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
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.

As you say it is not clear if problem is with Connector/J or at server side.

If you try

CREATE ... SELECT ...

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

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.

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?

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.