MySQL Bugs: #24886: Wrong data type in the resultset if using GROUP BY and CONCAT

Bug #24886	Wrong data type in the resultset if using GROUP BY and CONCAT
Submitted:	7 Dec 2006 14:41	Modified:	23 Jul 3:06
Reporter:	Milosz Marian Hulboj
Status:	Verified
Category:	Server: Types	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	Target Version:
Tags:	bfsm_2007_07_19, bfsm_2007_05_31, bfsm_2007_06_21, bfsm_2007_06_28
Triage:	Triaged: D5 (Feature request)

Description:
I am observing a strange behaviour of JDBC resultset type assignment.
The behaviour seems to be induced by using GROUP BY and CONCAT.

Having a table
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| sepallength | double     | YES  |     | NULL    |       |
| sepalwidth  | double     | YES  |     | NULL    |       |
| petallength | double     | YES  |     | NULL    |       |
| petalwidth  | double     | YES  |     | NULL    |       |
| Class       | mediumtext | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+

I issue a query:
SELECT concat(Class,petallength), COUNT(*) FROM `iris` GROUP BY
`concat(Class,petallength)`

And the datatype for the first returned column is set to LONGVARBINARY instead of VARCHAR
(for example). This causes obvious problems for data retrieval...

Issuing the query withou GROUP BY:
SELECT concat(Class,petallength) FROM `iris`
Gives the proper datatype - VARCHAR.

How to repeat:
http://lists.mysql.com/java/8997

Sample code to reproduce the error

Attachment: example.java (text/x-java), 1.83 KiB.

I have forgotten to mention that this behaviour happens if I concatenate columns of
different datatypes.

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>

It is a server bug. Verified on 5.0.27. See Tonci's last private comment for the details.

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.

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.

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".

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".

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.

Possible workaround is to use:

SELECT
  concat(Class,CAST(petallength AS CHAR)) as c,
  COUNT(*)
FROM `iris`
GROUP BY
  c;

Bug#27475 is what is killing me here when replying to Ramil...

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)