MySQL Bugs: #4734: CAST does not seem to work on literals and count

Bug #4734	CAST does not seem to work on literals and count
Submitted:	23 Jul 2004 23:44	Modified:	26 Jul 2004 18:28
Reporter:	Jim Nusbaum	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.3	OS:	Solaris (Solaris 8)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
The CAST operator does not seem to work on literals or the value of COUNT(*).  These are always returned as LONGLONG (BIGINT) through the API no matter if CAST is used to try and change the type.  This is a REAL problem when trying to write a server independent database interface as different DBMS return literals and count(*) as different types (Sybase for instance returns them as signed 32 bit integers).

Example:

Select 1;  // returns 64 bit integer (LONGLONG)
Select CAST(1 AS SIGNED INTEGER); // still returns 64 bit integer

Select count(*) from foo;  // returns 64 bit integer
Select Cast(count(*) AS SIGNED INTEGER) from foo; // still returns 64 bit integer

How to repeat:
Execute the simple queries above through the API and check the return field's type.

Suggested fix:
Make CAST work on literals and built in function return values.

Both INTEGER casts return 64 bit integers; see:

http://dev.mysql.com/doc/mysql/en/Cast_Functions.html

I don't understand the response.  The INTEGER type in MySQL is a 32 bit integral type.  See the description of column types in the documentation.  When retrieved through the C API and INT or INTEGER type column is returned as a FIELD_TYPE_LONG, which is a 32 integral type as expected.

When returned through the C API the native result of selecting a literal or the result of executing the count function is a BIGINT, a 64 bit integral type.  This is returned through the C API as a FIELD_TYPE_LONGLONG.

I however need the type of selecting a literal or the result of a count invocation to be a 32 bit integral type (an INT or INTEGER type and a FIELD_TYPE_LONG in the API).  This is needed to match up with the type returned by other DBMS and avoid platform specific features from bleeding through a higher level API.  The obvious way to do this is to have the query change from:

Select 1;  // select a literal

to:

Select CAST(1 AS INTEGER);  // select a literal and cast to INTEGER

This does not work.  The result returned through the C API is still a FIELD_TYPE_LONGLONG even though I have specifically cast it to an INTEGER (FIELD_TYPE_LONG).  Same thing with the result of count.

Maybe this is an API problem?  Otherwise how can I get the selection of a literal number or the result of count to be an INTEGER (FIELD_TYPE_LONG) through the API?

What I meant above is that CAST() is doing what it is supposed to be doing; FIELD_TYPE_LONGLONG is the intended type.  CAST() does not support as many types as it could, and will get extended, but this is expected behavior today.

OK.  Then could I suggest changing the documentation for CAST.  The type name that is used is INTEGER.  INTEGER is a 32 bit type as defined elsewhere.  What you are saying is that when I specify INTEGER in CAST I am really getting a BIGINT.  That is confusing to say the least.  The type in CAST should not be INTEGER but BIGINT since that is what it is really casting to.