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
