Hi,
Same bug with MySQL-4.0.2 latest source.
Regards,
Jocelyn Fournier
----- Original Message -----
From: "Benjamin Pflugmann" <[EMAIL PROTECTED]>
To: "Alexander Keremidarski" <[EMAIL PROTECTED]>
Cc: "Terra" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Thursday, May 16, 2002 7:49 PM
Subject: Re: [BUG] CONCAT_WS + GROUP BY display problem
> Hello.
>
> On Thu, May 16, 2002 at 06:25:42PM +0300, [EMAIL PROTECTED] wrote:
> [...]
> > >mysql> select *,concat_ws('.',oct1,oct2,oct3,oct4)
> > >ip,concat(oct1,'.',oct2,'.',oct3,'.',oct4) test,count(*) c from ips
group
> > >by oct1,oct2,oct3,oct4 having c>1;
> >
>+------+------+------+------+------+-----+-------+--------------+----------
------+---+
> > >| ID | OCT1 | OCT2 | OCT3 | OCT4 | SID | AVAIL | ip | test
> > >| c |
> >
>+------+------+------+------+------+-----+-------+--------------+----------
------+---+
> > >| 3424 | 63 | 151 | 144 | 84 | 0 | 1 | 63.151.144.8 |
> > >63.151.144.84 | 2 |
> > >| 3425 | 63 | 151 | 144 | 85 | 0 | 1 | 63.151.144.8 |
> > >63.151.144.85 | 2 |
> > >| 3426 | 63 | 151 | 144 | 86 | 0 | 1 | 63.151.144.8 |
> > >63.151.144.86 | 2 |
> > >| 3427 | 63 | 151 | 144 | 87 | 0 | 1 | 63.151.144.8 |
> > >63.151.144.87 | 2 |
> > >Notice the difference between the result columns of 'ip' and 'test'...
> >
> > It is normal to have a difference there. It is not beacuse of how
concat()
> > and() concat_ws() work, but how GROUP BY works.
>
> That was also my first tought, but I beg to differ: As oct1,...oct4
> are used to construct either column, the result should be the
> same.
>
> I.e. the grouping behaviour you refer to (if I understand you
> correctly) tells us, that we cannot know the row which will be chosen
> for the columns not listed in the GROUP BY clause.
>
> Okay, more precisely, the value for a column is defined to be an
> abitrary value of the group in the manual, IIRC. But MySQLs
> implemenation will use the values of one (abitrary) row for all the
> columns, i.e.
>
> CONCAT_WS('.',oct1,oct2,oct3,oct4) AS IP
>
> and
>
> CONCAT(oct1,'.',oct2,'.',oct3,'.',oct4) AS test
>
> will come from the same row and therefore should have been constructed
> using the same values for oct1,...,oct4.
>
> > In order to get predictable result you need to use:
> >
> > select concat_ws('.',oct1,oct2,oct3,oct4) as IP, count(*) as c from
> > ips group by IP [having c > 1];
>
> I have tested this and it doesn't fix the problem.
>
> What follows is a complete test case and the output from running it on
> my system. Please note, that it has the additional (but not necessary)
> property that each group contains only one row, so there can not be
> any ambiguity which values will be used.
>
> SELECT VERSION();
> DROP TABLE IF EXISTS group_by_bug;
> CREATE TABLE group_by_bug ( number INT NOT NULL, alpha CHAR(6) NOT NULL );
> INSERT INTO group_by_bug VALUES (1413006,'idlfmv'),
> (1413065,'smpsfz'),(1413127,'sljrhx'),(1413304,'qerfnd');
>
> /* like the original exmaple */
> SELECT number, alpha, CONCAT_WS('<---->',number,alpha) AS new
> FROM group_by_bug GROUP BY number;
>
> /* with suggested, non-working fix */
> SELECT CONCAT_WS('<---->',number,alpha) AS new
> FROM group_by_bug GROUP BY new LIMIT 1;
>
> /* show depency on seperator length */
> SELECT number, alpha, CONCAT_WS('<->',number,alpha) AS new
> FROM group_by_bug GROUP BY new LIMIT 1;
>
> /* show depency on seperator, second take */
> SELECT number, alpha,
CONCAT_WS('-',number,alpha,alpha,alpha,alpha,alpha,alpha,alpha) AS new
> FROM group_by_bug GROUP BY new LIMIT 1;
>
> /* show that it even cuts the seperator */
> SELECT number, alpha, CONCAT_WS('<------------------>',number,alpha) AS
new
> FROM group_by_bug GROUP BY new LIMIT 1;
>
> ----------------------------------------------------------------------
>
> mysql> SELECT VERSION();
> +-------------+
> | VERSION() |
> +-------------+
> | 3.23.46-log |
> +-------------+
> 1 row in set (0.00 sec)
>
> mysql> DROP TABLE IF EXISTS group_by_bug;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> CREATE TABLE group_by_bug ( number INT NOT NULL, alpha CHAR(6) NOT
NULL );
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> INSERT INTO group_by_bug VALUES (1413006,'idlfmv'),
> -> (1413065,'smpsfz'),(1413127,'sljrhx'),(1413304,'qerfnd');
> Query OK, 4 rows affected (0.00 sec)
> Records: 4 Duplicates: 0 Warnings: 0
>
> mysql>
> mysql> /* like the original exmaple */
> -> SELECT number, alpha, CONCAT_WS('<---->',number,alpha) AS new
> -> FROM group_by_bug GROUP BY number;
> +---------+--------+-------------------+
> | number | alpha | new |
> +---------+--------+-------------------+
> | 1413006 | idlfmv | 1413006<---->idlf |
> | 1413065 | smpsfz | 1413065<---->smps |
> | 1413127 | sljrhx | 1413127<---->sljr |
> | 1413304 | qerfnd | 1413304<---->qerf |
> +---------+--------+-------------------+
> 4 rows in set (0.00 sec)
>
> mysql>
> mysql> /* with suggested, non-working fix */
> -> SELECT CONCAT_WS('<---->',number,alpha) AS new
> -> FROM group_by_bug GROUP BY new LIMIT 1;
> +-------------------+
> | new |
> +-------------------+
> | 1413006<---->idlf |
> +-------------------+
> 1 row in set (0.01 sec)
>
> mysql>
> mysql> /* show depency on seperator length */
> -> SELECT number, alpha, CONCAT_WS('<->',number,alpha) AS new
> -> FROM group_by_bug GROUP BY new LIMIT 1;
> +---------+--------+------------------+
> | number | alpha | new |
> +---------+--------+------------------+
> | 1413006 | idlfmv | 1413006<->idlfmv |
> +---------+--------+------------------+
> 1 row in set (0.00 sec)
>
> mysql>
> mysql> /* show depency on seperator, second take */
> -> SELECT number, alpha,
CONCAT_WS('-',number,alpha,alpha,alpha,alpha,alpha,alpha,alpha) AS new
> -> FROM group_by_bug GROUP BY new LIMIT 1;
>
+---------+--------+-------------------------------------------------------+
> | number | alpha | new
|
>
+---------+--------+-------------------------------------------------------+
> | 1413006 | idlfmv | 1413006-idlfmv-idlfmv-idlfmv-idlfmv-idlfmv-idlfmv-idl
|
>
+---------+--------+-------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> mysql>
> mysql> /* show that it even cuts the seperator */
> -> SELECT number, alpha,
CONCAT_WS('<------------------>',number,alpha) AS new
> -> FROM group_by_bug GROUP BY new LIMIT 1;
> +---------+--------+-------------------+
> | number | alpha | new |
> +---------+--------+-------------------+
> | 1413006 | idlfmv | 1413006<--------- |
> +---------+--------+-------------------+
> 1 row in set (0.00 sec)
>
>
> I had no recent version handy, so if someone could test it on the
> current MySQL version...
>
> Regards,
>
> Benjamin.
>
>
> PS: CC'ed to bugs list due to repeatable test case.
>
>
> --
> [EMAIL PROTECTED]
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
