Greetings,
While working on a routine to clean out some duplicate entries from our IP management
table, I stumbled upon the following errant behavior...
Logging to file 'concatbug.txt'
mysql> \s
--------------
Connection id: 73465
Current database: OBFmtable
Current user: user@localhost
Current pager: stdout
Using outfile: 'concatbug.txt'
Server version: 3.23.49a-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 3 days 12 hours 29 min 58 sec
Threads: 30 Questions: 2395300 Slow queries: 4 Opens: 25968 Flush tables: 1 Open
tables: 37 Queries per second avg: 7.874
--------------
mysql> desc ips;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| OCT1 | int(3) | | | 0 | |
| OCT2 | int(3) | | | 0 | |
| OCT3 | int(3) | | | 0 | |
| OCT4 | int(3) | | MUL | 0 | |
| SID | int(11) | | MUL | 0 | |
| AVAIL | int(1) | | | 0 | |
+-------+---------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
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 |
| 3428 | 63 | 151 | 144 | 88 | 0 | 1 | 63.151.144.8 | 63.151.144.88 | 2 |
| 3429 | 63 | 151 | 144 | 89 | 0 | 1 | 63.151.144.8 | 63.151.144.89 | 2 |
| 3430 | 63 | 151 | 144 | 90 | 0 | 1 | 63.151.144.9 | 63.151.144.90 | 2 |
| 3431 | 63 | 151 | 144 | 91 | 0 | 1 | 63.151.144.9 | 63.151.144.91 | 2 |
| 3432 | 63 | 151 | 144 | 92 | 0 | 1 | 63.151.144.9 | 63.151.144.92 | 2 |
| 3433 | 63 | 151 | 144 | 93 | 0 | 1 | 63.151.144.9 | 63.151.144.93 | 2 |
| 3434 | 63 | 151 | 144 | 94 | 0 | 1 | 63.151.144.9 | 63.151.144.94 | 2 |
| 3435 | 63 | 151 | 144 | 95 | 0 | 1 | 63.151.144.9 | 63.151.144.95 | 2 |
| 3436 | 63 | 151 | 144 | 96 | 0 | 1 | 63.151.144.9 | 63.151.144.96 | 2 |
| 3437 | 63 | 151 | 144 | 97 | 0 | 1 | 63.151.144.9 | 63.151.144.97 | 2 |
| 3438 | 63 | 151 | 144 | 98 | 0 | 1 | 63.151.144.9 | 63.151.144.98 | 2 |
| 3439 | 63 | 151 | 144 | 99 | 0 | 1 | 63.151.144.9 | 63.151.144.99 | 2 |
| 3440 | 63 | 151 | 144 | 100 | 0 | 1 | 63.151.144.1 | 63.151.144.100 | 2 |
| 3441 | 63 | 151 | 144 | 101 | 0 | 1 | 63.151.144.1 | 63.151.144.101 | 2 |
| 3442 | 63 | 151 | 144 | 102 | 0 | 1 | 63.151.144.1 | 63.151.144.102 | 2 |
| 2662 | 63 | 151 | 147 | 54 | 615 | 0 | 63.151.147.5 | 63.151.147.54 | 2 |
+------+------+------+------+------+-----+-------+--------------+----------------+---+
20 rows in set (0.02 sec)
mysql> quit
Notice the difference between the result columns of 'ip' and 'test'...
-------
One of my colleagues asked me to give this a try:
mysql> select *,concat_ws('.','9999',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 | 9999.151.144 | 63.151.144.84 | 2 |
| 3425 | 63 | 151 | 144 | 85 | 0 | 1 | 9999.151.144 | 63.151.144.85 | 2 |
| 3426 | 63 | 151 | 144 | 86 | 0 | 1 | 9999.151.144 | 63.151.144.86 | 2 |
| 3427 | 63 | 151 | 144 | 87 | 0 | 1 | 9999.151.144 | 63.151.144.87 | 2 |
It is now apparent that the display width for 'ip' is being set to a max of 12 and
cutting off the full result... I would assume: Calculated Field Width ==
SUM(type_size(oct1, oct2, oct3, oct4)) or 12
It appears to only do this in a GROUP BY type of SELECT... Using HAVING, or not,
still results in same chopped effect...
------
Non GROUP BY queries work as desired...
mysql> select *,concat_ws('.','9999',oct2,oct3,oct4)
ip,concat(oct1,'.',oct2,'.',oct3,'.',oct4) test,count(*) c from ips LIMIT 2;
+------+------+------+------+------+-----+-------+-----------------+----------------+
| ID | OCT1 | OCT2 | OCT3 | OCT4 | SID | AVAIL | ip | test |
+------+------+------+------+------+-----+-------+-----------------+----------------+
| 3424 | 63 | 151 | 144 | 84 | 0 | 1 | 9999.151.144.84 | 63.151.144.84 |
| 3425 | 63 | 151 | 144 | 85 | 0 | 1 | 9999.151.144.85 | 63.151.144.85 |
As shown above, CONCAT_WS is now displaying properly without being limited to 12 char
display...
Can anyone confirm if this is a bug or not...
Thanks...
--
Terra
sysAdmin
FutureQuest, Inc.
http://www.FutureQuest.net
---------------------------------------------------------------------
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
