Bug #7833 Wrong datatype of aggregate column is returned
Submitted: 12 Jan 2005 11:48 Modified: 4 Feb 2005 13:26
Reporter: Dmitri Blinov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7/4.1.8 OS:Linux (Linux RH9)
Assigned to: Sergei Glukhov CPU Architecture:Any

[12 Jan 2005 11:48] Dmitri Blinov
Description:
Wrong datatype of aggregate column is returned when using aggregation of datetime columns within the subquery. This issue does not depend on whether "group by" in subquery was used or not. The workaround is to explicitly cast a column to datetime in outer query

How to repeat:
The following statement reports CalendarDate column to be of CHAR datatype instead of Datetime.

select CalendarDate from (select max(now()) CalendarDate) a

4.1.7 and 4.1.8 servers were tested under InnoDB database though engine type may be irrelevant here

Suggested fix:
None that I am aware of
[12 Jan 2005 12:31] Aleksey Kishkin
Dmitri, which client did you use for determine type of result?
[12 Jan 2005 13:01] Dmitri Blinov
I used both Connector/J 3.0.15ga and 3.1.6. I used both ResultSetMetaData.getColumnType and getColumnTypeName. I also turned on parameter traceProtocol=true and peeped into trace file. Though I'm not familiar with intrinsics of on-the-wire protocol, I noticed that there was 0xfe code in responce. This is a little bit naive, so I didn't post it at first, but now that you have asked... 

Here is the excerpt:

Wed Jan 12 12:25:45 MSK 2005 TRACE: at  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) readPacket() payload:
03 64 65 66 00 0b 23 73     . d e f . . # s
71 6c 5f 34 37 66 30 5f     q l _ 4 7 f 0 _
31 10 2f 74 6d 70 2f 23     1 . / t m p / #
73 71 6c 5f 34 37 66 30     s q l _ 4 7 f 0
5f 31 0c 43 61 6c 65 6e     _ 1 . C a l e n
64 61 72 44 61 74 65 0c     d a r D a t e .
43 61 6c 65 6e 64 61 72     C a l e n d a r
44 61 74 65 0c 3f 00 0a     D a t e . ? . .
00 00 00 fe 80 00 00 00     . . . . . . . .
00
[12 Jan 2005 14:35] Mark Matthews
Looks like another case of the server dropping the datatype of an aggregate. The '0xfe' you see in the datatype means the server is telling the JDBC driver that the type is a string (i.e. varchar).
[13 Jan 2005 20:54] Aleksey Kishkin
the test case

Attachment: bug7833.c (text/x-csrc), 684 bytes.

[13 Jan 2005 21:09] Aleksey Kishkin
actually query you provided must return NULL (aggregate function max without any records). I little bit changed your query,

select CalendarDate from (select max(now()) CalendarDate from demo) a

created table demo, added 1 record and wrote short testcase (attached) on C in order  to check what type returns this query. 
 
You are right, it returns 254 (MYSQL_TYPE_STRING) instead of MYSQL_TYPE_DATE.
[14 Jan 2005 7:53] Dmitri Blinov
You are right, sorry for wrong example: I was trying to stripp the real offensive query from any specific details of our project so as to make it as easily reproduced as it might be. I'm actually confused about the way aggregate functions work in MySQL:

select now() CalendarDate returns one row with expected value, 
select max(now()) CalendardDate returns null as was earlier pointed out, 
select max(CalendarDate) from (select now() CalendarDate ) a returns one row with expected value.

Is that a supposed behaviour ?
[4 Feb 2005 13:26] Sergei Glukhov
Fixed in 4.10 tree