| Bug #24886 | Wrong data type in the resultset if using GROUP BY and CONCAT | ||
|---|---|---|---|
| Submitted: | 7 Dec 2006 13:41 | Modified: | 12 Aug 2010 7:54 |
| Reporter: | Milosz Marian Hulboj | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.27 | OS: | Linux (Linux) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
| Tags: | bfsm_2007_05_31, bfsm_2007_06_21, bfsm_2007_06_28, bfsm_2007_07_19 | ||
[7 Dec 2006 13:41]
Milosz Marian Hulboj
[7 Dec 2006 13:41]
Milosz Marian Hulboj
Sample code to reproduce the error
Attachment: example.java (text/x-java), 1.83 KiB.
[7 Dec 2006 13:47]
Milosz Marian Hulboj
I have forgotten to mention that this behaviour happens if I concatenate columns of different datatypes.
[8 Dec 2006 7:06]
Tonci Grgin
Leaving to Valeriy as it seems to be a server problem.
CREATE TABLE iris (sepallength double,
sepalwidth double,
petallength double,
petalwidth double,
Class mediumtext);
INSERT INTO iris VALUES (1,2,3,4,"1234"),(5,6,7,8,"12345678");
------------
C:\mysql507\bin>mysql -T -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.27-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE iris (sepallength double,
-> sepalwidth double,
-> petallength double,
-> petalwidth double,
-> Class mediumtext);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO iris VALUES (1,2,3,4,"1234"),(5,6,7,8,"12345678");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO iris VALUES (9,2,3,4,"1234"),(10,6,7,8,"12345678");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT concat(Class,petallength), COUNT(*) FROM `iris` GROUP BY `concat(C
lass,petallength)`;
Field 1: `concat(Class,petallength)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: BLOB
Collation: binary (63)
Length: 8192
Max_length: 9
Decimals: 0
Flags: BLOB BINARY NUM
Field 2: `COUNT(*)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 21
Max_length: 1
Decimals: 0
Flags: NOT_NULL NUM
+---------------------------+----------+
| concat(Class,petallength) | COUNT(*) |
+---------------------------+----------+
| 12343 | 2 |
| 123456787 | 2 |
+---------------------------+----------+
2 rows in set (0.02 sec)
mysql> SELECT concat(Class,petallength) FROM `iris`;
Field 1: `concat(Class,petallength)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 8192
Max_length: 9
Decimals: 31
Flags: BINARY
+---------------------------+
| concat(Class,petallength) |
+---------------------------+
| 12343 |
| 123456787 |
| 12343 |
| 123456787 |
+---------------------------+
4 rows in set (0.00 sec)
mysql> SELECT concat(Class,petallength) FROM `iris` GROUP BY `concat(Class,petal
length)`;
Field 1: `concat(Class,petallength)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: BLOB
Collation: binary (63)
Length: 8192
Max_length: 9
Decimals: 0
Flags: BLOB BINARY NUM
+---------------------------+
| concat(Class,petallength) |
+---------------------------+
| 12343 |
| 123456787 |
+---------------------------+
2 rows in set (0.00 sec)
mysql>
[27 Dec 2006 14:49]
Valeriy Kravchuk
It is a server bug. Verified on 5.0.27. See Tonci's last private comment for the details.
[14 Feb 2007 10:12]
Ramil Kalimullin
I don't think it's a bug. It's how things work. explain SELECT concat(Class,petallength) a FROM `iris` GROUP BY a; +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ | 1 | SIMPLE | iris | ALL | NULL | NULL | NULL | NULL | 8 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+ So, we use a temporary table for such queries and create a blob field if length >= 512 (hardcoded). It's related to http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html "When using a constant string or a function that generates a string result in CREATE ... SELECT, MySQL creates the result column based on the maximum length of the string or expression: Maximum Length Data type = 0 CHAR(0) < 512 VARCHAR(max_length) >= 512 TEXT " Ramil.
[21 May 2007 19:52]
Tonci Grgin
Ramil, alas it doesn't work like you described (nor like described in manual)... Instead of TEXT I get BLOB in my test (`concat(Class [mediumtext],petallength [double])`)... The text column (i.e. with a character set, I assume) is converted to a BLOB (which doesn't have one), and thus critical information is lost. This is irrespective of whether or not a temporary table is used (if one is used), it is OK for a TEXT column to become a "larger" TEXT column, but it is not OK for it to suddenly become a BLOB column. Now, you quote manual: "When using a constant string or a function that generates a string result in CREATE ... SELECT, MySQL creates the result column based on the maximum length of the string or expression: Maximum Length Data type = 0 CHAR(0) < 512 VARCHAR(max_length) >= 512 TEXT" but I don't see TEXT becoming BLOB here. Even if that was true it is still a bug as you are loosing pieces of information through conversion, agree? I went through this with Mark and we agree that this is still a server bug.
[12 Jul 2007 17:07]
Georgi Kodinov
The main problem here is from the fact that if a temporary table is used it changes the data types of the returned values. This is a know problem and it will eventually get solved, but it requires more planing and specification than the scope of a single bug. Until the bigger problem is solved I'm returning this bug to "Verified".
[16 Jul 2007 14:02]
Peter Gulutzan
According to email archives, things like CONCAT(DOUBLE) are supposed to yield [VAR]BINARY. This was originally by Monty's order, see scrum-daily thread ""Other tricky functins and collations" (2003) and dev-public thread ""RFC: charset of the int->char conversion" (2005). Happily, after a period when Monty + Sergei insisted the result should be ASCII (which never happened), and after repeated badgering in the endless thread "Re: BINARY and VARBINARY", we have ended up with WL#2649 Number-to-string conversions. Still stuck in raw idea bin, but at least it has the approval of Monty + Sergei + Bar. Until WL#2649 takes place, the fact that "CONCAT(double) RETURNS [VAR]BINARY" seems applicable here, so this is not a bug. I will wait a few days in case somebody knows about something that supersedes the above decisions, and then mark this "not a bug".
[17 Jul 2007 17:33]
Sergei Golubchik
Ok. There're three distinct problems here. 1. When a server converts a number to a string, the resulting string has charset=binary. As a result, concat(string, number) has charset=binary. This is not a bug, it's the intentional behavior. But it's undesirable, and there's WL#2649 which will fix it. See PeterG comment above. 2. If SELECT uses temporary table internally, metadata may change. This is not a bug, it's intentional. But it's undesirable, and will be fixed eventually. See Ramil and Georgi comments above. 3. JDBC returns a type VARCHAR when a server reports the data to be binary (MYSQL_TYPE_VARCHAR, collation=63), instead of VARBINARY, ignoring collation=63 metadata. But JDBC reports a type BLOB instead of TEXT when a server reports the data to be binary (MYSQL_TYPE_BLOB, collation=63), because of collation=63. The first behavior is wrong, the second is correct, and as a pair they're inconsistent. Still, it cannot be fixed because of #1 - incorrectly written Java applications will break if JDBC will be fixed.
[9 Dec 2008 7:57]
Alexander Barkov
Possible workaround is to use: SELECT concat(Class,CAST(petallength AS CHAR)) as c, COUNT(*) FROM `iris` GROUP BY c;
[20 Feb 2009 14:12]
Tonci Grgin
Bug#27475 is what is killing me here when replying to Ramil...
[11 Mar 2009 18:11]
Leandro Morgado
This also affects SELECT DISTINCT queries as this "resolves" into a group by. mysql> SELECT DISTINCT CONCAT(Class,petallength) FROM `iris` ; Field 1: `CONCAT(Class,petallength)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: BLOB Collation: binary (63) Length: 8192 Max_length: 9 Decimals: 0 Flags: BLOB BINARY NUM +---------------------------+ | CONCAT(Class,petallength) | +---------------------------+ | 12343 | | 123456787 | +---------------------------+ 2 rows in set (0.00 sec)
[12 Aug 2010 7:54]
Alexander Barkov
This bug was fixed in 5.5 under terms of WL#2649 Number-to-string conversion: http://forge.mysql.com/worklog/task.php?id=2649 mysql> DROP TABLE IF EXISTS iris; CREATE TABLE iris (sepallength double,sepalwidth double,petallength double,petalwidth double,Class mediumtext); INSERT INTO iris VALUES (1,2,3,4,"1234"),(5,6,7,8,"12345678"); INSERT INTO iris VALUES (9,2,3,4,"1234"),(10,6,7,8,"12345678"); SELECT concat(Class,petallength), COUNT(*) FROM iris GROUP BY `concat(Class,petallength)`; SELECT concat(Class,petallength) FROM `iris`; SELECT concat(Class,petallength) FROM `iris` GROUP BY `concat(Class,petallength)`; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Field 1: `concat(Class,petallength)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: BLOB Collation: utf8_general_ci (33) Length: 50331648 Max_length: 9 Decimals: 0 Flags: BLOB NUM Field 2: `COUNT(*)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 21 Max_length: 1 Decimals: 0 Flags: NOT_NULL NUM +---------------------------+----------+ | concat(Class,petallength) | COUNT(*) | +---------------------------+----------+ | 12343 | 2 | | 123456787 | 2 | +---------------------------+----------+ 2 rows in set (0.00 sec) Field 1: `concat(Class,petallength)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONG_BLOB Collation: utf8_general_ci (33) Length: 50331648 Max_length: 9 Decimals: 31 Flags: +---------------------------+ | concat(Class,petallength) | +---------------------------+ | 12343 | | 123456787 | | 12343 | | 123456787 | +---------------------------+ 4 rows in set (0.00 sec) Field 1: `concat(Class,petallength)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: BLOB Collation: utf8_general_ci (33) Length: 50331648 Max_length: 9 Decimals: 0 Flags: BLOB NUM +---------------------------+ | concat(Class,petallength) | +---------------------------+ | 12343 | | 123456787 | +---------------------------+ 2 rows in set (0.00 sec)
[2 Nov 2010 20:13]
Alexander Rubin
According to my test it is not only CONCAT or function problem. If any string is lager than 512 it will be longtext, if smaller than it will be varchar. This is important because, MEMORY engine does not support text/blob and in case of "select distinct concat(...) " mysql will create temporary table on disk, not in RAM. Test case -- ==== 512 bytes: ==== --- mysql> drop table if exists tmpmem; create table tmpmem as select _latin1 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' as a; show create table tmpmem; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 +--------+----------------------------------------------------------------------------------------+ | Table | Create Table | +--------+----------------------------------------------------------------------------------------+ | tmpmem | CREATE TABLE `tmpmem` ( `a` longtext NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--------+----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -- ==== 513 bytes: ==== --- mysql> drop table if exists tmpmem; create table tmpmem as select _latin1 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' as a; show create table tmpmem; Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 +--------+-------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------+ | tmpmem | CREATE TABLE `tmpmem` ( `a` varchar(512) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--------+-------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) VERSION: mysql> select version(); +------------+ | version() | +------------+ | 5.1.50-log | +------------+ 1 row in set (0.00 sec)
