List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 21 1999 8:46pm
Subject:Re: join + group by + concat(INT,...) Problem! (3.22.26a)
View as plain text  
>>>>> "Martin" == Martin Ramsch <m.ramsch@stripped> writes:

Martin> On Tue, 1999-09-21 02:18:12 +0200, Benjamin Pflugmann wrote:
>> What result did you expect? IMHO, the result below is as it should be
>> correct. You group by test2PTR, which is different for each row, so
>> all rows are displayed. Did you want to group by something like the
>> first column?
Martin> [...]
>> 
>> On Tue, Sep 21, 1999 at 12:42:47AM +0400, andrews@stripped wrote:
>> [...]
>> > mysql> create table test ( i1 int(1), i2 int(2), i4 int(4), test2PTR
> int(4));
>> > Query OK, 0 rows affected (0.00 sec)
>> > 
>> > mysql> create table test2 (test2ID int(4));
>> > Query OK, 0 rows affected (0.00 sec)
>> > 
>> > mysql> insert into test2 (test2ID) values (1),(2),(3);
>> > Query OK, 3 rows affected (0.00 sec)
>> > Records: 3  Duplicates: 0  Warnings: 0
>> > 
>> > mysql> insert into test (i1,i2,i4,test2PTR) values
> (1,1,1,1),(10,10,10,2),(100,100,100,3);
>> > Query OK, 3 rows affected (0.00 sec)
>> > Records: 3  Duplicates: 0  Warnings: 0
>> [...]
>> > ##########  Result of CONCAT -- wrong :((( 
>> > mysql> select
> concat("---",i1,"---"),concat("---",i2,"---"),concat("---",i4,"---")
>> >     ->  from test,test2 WHERE test2PTR=test2ID GROUP BY test2PTR;
>> >
> +------------------------+------------------------+------------------------+
>> > | concat("---",i1,"---") | concat("---",i2,"---") | concat("---",i4,"---")
> |
>> >
> +------------------------+------------------------+------------------------+
>> > | ---1---                | ---1---                | ---1---               
> |
>> > | ---10--                | ---10---               | ---10---              
> |
>> > | ---100-                | ---100--               | ---100---             
> |
>> >
> +------------------------+------------------------+------------------------+

Martin> It's the number of dashes, not the numbers, what Andrew probably meant
Martin> (but he better should have said explicitly).

Martin> Obviously the root of the problem (or call it bug) is, that the field
Martin> i1 is defined to have a display width of only 1 character, but the
Martin> values 10 and 100 don't fit this display width.  I don't know, what
Martin> the "standard" behaviour than should be ...?

Hi!

Yes, you are right.

To do the last SELECT, MySQL will create a temporary table. When
creating this, it calculates the max column widths it will nead for
each column.  In the above case, the first column in the temporary
table will be:

max_length(concat("---",i1,"---"))  = 

max_length("---") + max_length(i1) + max_length("---") = 3+1+3 = 7

This is one case when you will loose if your data doesn't fit the
column width you have specified.

We have to add something about this in the manual.  The fix is of
course to extend the column with an ALTER TABLE...

Regards,
Monty
Thread
join + group by + concat(INT,...) Problem! (3.22.26a)Andrew Shirrayev21 Sep
  • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Benjamin Pflugmann21 Sep
    • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Martin Ramsch21 Sep
      • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Bob Kline21 Sep
        • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Michael Widenius22 Sep
          • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Bob Kline22 Sep
      • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Benjamin Pflugmann21 Sep
        • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Martin Ramsch21 Sep
      • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Michael Widenius22 Sep
    • Re: join + group by + concat(INT,...) Problem! (3.22.26a)Andrew Shirrayev21 Sep