MySQL Bugs: #66937: return type from nullif is described incorrectly as string type

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

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.

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.

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".

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

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.