Hi!
>>>>> "Boris" == Boris Granveaud <boris.granveaud@stripped>
> writes:
Boris> As you can see, the types returned by min(hitdate) and max(hitdate) are
Boris> the same as the type of the client column. But with this example, I
Boris> have just discovered that other types have changed too!
>>
>> MAX(datetime column) should return a datetime type in 3.23.
>>
>> I did just test this and it worked for me in this simple test:
>>
>> create table t1 (a datetime);
>> insert into t1 values (now());
>> select max(a) as min_hitdate from t1;
>>
>> Can you generate a total test case as above that returns string as a
>> type?
Boris> I found that incorrect data types are returned only when I use a "group
Boris> by" in my statement. Here is my test table:
Boris> create table test (
Boris> hitdate datetime not null,
Boris> client varchar(255) not null,
Boris> key hitdate(hitdate),
Boris> key client(client)
Boris> );
Boris> I insert two records:
Boris> insert into test values(now(),"1");
Boris> insert into test values(now(),"2");
Boris> If I do this:
Boris> PreparedStatement statP = connP.prepareStatement("select min(hitdate) as
Boris> min_date, max(hitdate) as max_date from test group by client");
Boris> the result is:
Boris> name=min_date
Boris> value=1
Boris> type=CHAR
Boris> class=[B
Boris> name=max_date
Boris> value=1
Boris> type=CHAR
Boris> class=[B
Boris> But if I do this:
Boris> PreparedStatement statP = connP.prepareStatement("select min(hitdate) as
Boris> min_date, max(hitdate) as max_date from test");
Boris> the result is correct:
Boris> name=min_date
Boris> value=93
Boris> type=TIMESTAMP
Boris> class=java.sql.Timestamp
Boris> name=max_date
Boris> value=93
Boris> type=TIMESTAMP
Boris> class=java.sql.Timestamp
The problem is that the optimizer is creating a temporary table to be
able to execute the 'group by' quickly and in this case all MIN/MAX
values will either be stored as BIGINT or CHAR strings.
I have now added a note that we need to change this is in MySQL 4.x
Regards,
Monty
