Bug #31776 CAST AS CHAR with SELECT DISTINCT

• Submitted: 23 Oct 2007 8:19
• Modified: 24 Oct 2007 18:24
• Reporter: david beau
• Status: Verified
• Category: MySQL 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)
• CPU Architecture: Any

[23 Oct 2007 8: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 16:38] Sveta Smirnova

Thank you for the report.

I can not repeat described behaviour. Please provide your configuration file.

[24 Oct 2007 16:14] david beau

My variables

Attachment: variables.txt (text/plain), 23.46 KiB.

[24 Oct 2007 18:22] Sveta Smirnova

test case

Attachment: bug31776.test (application/octet-stream, text), 702 bytes.

[24 Oct 2007 18:24] Sveta Smirnova

Thank you for the feedback.

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