Bug #31776 CAST AS CHAR with SELECT DISTINCT
Submitted: 23 Oct 2007 10:19 Modified: 24 Oct 2007 20:24
Reporter: david beau
Status: Verified
Category:Server: DML Severity:S3 (Non-critical)
Version:5.0.45, 4.1, 5.0, 5.1, 5.2 BK OS:Any (Linux, Microsoft Windows XP SP2)
Assigned to: Gleb Shchepa Target Version:
Triage: Triaged: D2 (Serious) / R2 (Low) / E3 (Medium)

[23 Oct 2007 10:19] david beau 
Description:
CAST AS CHAR is not correct with SELECT DISTINCT on two tables.

mysql> CREATE TABLE `Table1` (  `Id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Size` BIGINT UNSIGNED NOT NULL,  CONSTRAINT pk_Id1 PRIMARY KEY (`Id`)) ENGINE
=INNODB;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE `Table2` (  `IdKey` INT UNSIGNED NOT NULL AUTO_INCREMENT,  `
Id` MEDIUMINT UNSIGNED NOT NULL,  CONSTRAINT pk_Id2 PRIMARY KEY (`IdKey`)) ENGIN
E=INNODB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO Table1(Id,Size) VALUE (1, 123456789012345);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO Table2(IdKey,Id) VALUE (1,  1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT DISTINCT(Table1.Id), Table1.Size, CAST(Table1.Size AS CHAR) FROM T
able1, Table2 WHERE Table1.Id=Table2.Id;
+----+-----------------+---------------------------+
| Id | Size            | CAST(Table1.Size AS CHAR) |
+----+-----------------+---------------------------+
|  1 | 123456789012345 | 123456                    |
+----+-----------------+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT (Table1.Id), Table1.Size, CAST(Table1.Size AS CHAR) FROM Table1, T
able2 WHERE Table1.Id=Table2.Id;
+----+-----------------+---------------------------+
| Id | Size            | CAST(Table1.Size AS CHAR) |
+----+-----------------+---------------------------+
|  1 | 123456789012345 | 123456789012345           |
+----+-----------------+---------------------------+
1 row in set (0.00 sec)

mysql>

How to repeat:

SELECT DISTINCT(Table1.Id), Table1.Size, CAST(Table1.Size AS CHAR) FROM Table1, T
able2 WHERE Table1.Id=Table2.Id;
[23 Oct 2007 18:38] Sveta Smirnova 
Thank you for the report.

I can not repeat described behaviour. Please provide your configuration file.
[24 Oct 2007 18:14] david beau 
My variables

Attachment: variables.txt (text/plain), 23.46 KiB.
[24 Oct 2007 20:22] Sveta Smirnova 
test case

Attachment: bug31776.test (application/octet-stream, text), 702 bytes.
[24 Oct 2007 20:24] Sveta Smirnova 
Thank you for the feedback.

Verified as described using attached test case. Problem was UTF8 character set.