Bug #66937 return type from nullif is described incorrectly as string type
Submitted: 23 Sep 2012 21:27 Modified: 14 Dec 2012 17:36
Reporter: the 6campbells Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[23 Sep 2012 21:27] the 6campbells
Description:
A column in a table is defined of a given type - in this case DATE.

The following projection is being described (in this case via MySQL JDBC) with a string and not the expected DATE type.

select tdt.rnum, tdt.cdt, nullif(tdt.cdt , tdt.cdt), case when tdt.cdt = tdt.cdt then null else tdt.cdt end  from tdt 

How to repeat:
create table tdt (rnum integer, cdt date)

Project a query such as this and note the return type of nullif is incorrect relative to the long hand case expression which is semantically equivalent.
[23 Sep 2012 22:18] MySQL Verification Team
Thank you for the bug report. Please report the exactly server version 5.5.XX and complete test case printing the result you got and expected one. Thanks.
[24 Oct 2012 1: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".
[24 Oct 2012 12:31] the 6campbells
server version 5.5.12

CREATE TABLE tdt
(
   rnum int NOT NULL,
   cdt date
)

select tdt.rnum, tdt.cdt, nullif(tdt.cdt , tdt.cdt), case when tdt.cdt = tdt.cdt then null else tdt.cdt end  from tdt 

nullif ( DATE, DATE ) should return a data type of DATE

if you look at the metadata returned by ResultSetMetadata the value returned is

3	nullif(tdt.cdt , tdt.cdt)	VARCHAR	10	0	1				java.lang.String	10	nullif(tdt.cdt , tdt.cdt)	12	false	false	false	false	true	true	false	false

if you look at the case expression the metadata is not entirely correct either

4	case when tdt.cdt = tdt.cdt then null else tdt.cdt end	UNKNOWN	10	0	1				java.sql.Date	10	case when tdt.cdt = tdt.cdt then null else tdt.cdt end	91	false	false	false	false	true	true	false	false
[14 Dec 2012 17:36] Sveta Smirnova
Thank you for the report.

NULLIF case verified as described:

mysql [localhost] {msandbox} (test) > select  nullif(tdt.cdt , tdt.cdt), case when tdt.cdt = tdt.cdt then null else tdt.cdt end  from tdt ;
Field   1:  `nullif(tdt.cdt , tdt.cdt)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     10
Max_length: 0
Decimals:   31
Flags:      

Field   2:  `case when tdt.cdt = tdt.cdt then null else tdt.cdt end`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDATE
Collation:  latin1_swedish_ci (8)
Length:     10
Max_length: 0
Decimals:   31
Flags:      

0 rows in set (0.00 sec)

Regarding to case server returns correct data type: NEWDATE. So this looks like c/J problem. Please check if this is still true with latest c/J version 5.1.22 and if yes, open separate bug report.