List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:March 13 2001 8:55pm
Subject:Re: 3.22.32 -> 3.23.33 breaks JDBC driver (Problem with data types)
View as plain text  
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
Thread
3.22.32 -> 3.23.33 breaks JDBC driver (Problem with data types)Boris Granveaud9 Mar
  • 3.22.32 -> 3.23.33 breaks JDBC driver (Problem with data types)Jani Tolonen9 Mar
  • 3.22.32 -> 3.23.33 breaks JDBC driver (Problem with data types)Michael Widenius10 Mar
  • Re: 3.22.32 -> 3.23.33 breaks JDBC driver (Problem with data types)Boris Granveaud12 Mar
    • Re: 3.22.32 -> 3.23.33 breaks JDBC driver (Problem with data types)Michael Widenius13 Mar
  • Re: 3.22.32 -> 3.23.33 breaks JDBC driver (Problem with data types)Boris Granveaud14 Mar