Bug #22794 When puttin a CONCAT with a string and an integer into temporary table, it fails

• Submitted: 28 Sep 2006 16:42
• Modified: 29 Sep 2006 13:35
• Reporter: Martijn Korse
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.11
• OS: Linux (Linux Debian)
• CPU Architecture: Any
• Tags: concat, create, fields, INTEGER, string, temporary

[28 Sep 2006 16:42] Martijn Korse

Description:
When attempting to put the result of a CONCAT with a string and an integer into a temporary table, it (silently) fails to do so.

This:
SELECT 2006,  CONCAT('0', '9') AS WeekNR;
Will give 2 results: 2006 and 09

This:
SELECT 2006,  CONCAT('0', 9) AS WeekNR;
Will give the same result. The only difference is that in the first instance i'm concatting 2 strings and in the second a string and an integer.

Now, we're gonna create a temporary table with the results

This:
CREATE TEMPORARY TABLE testing SELECT 2006,  CONCAT('0', '9') AS WeekNR;
SELECT * FROM testing;
2006 | 06
will give the expected result

This however:
CREATE TEMPORARY TABLE testing2 SELECT 2006,  CONCAT('0', 9) AS WeekNR;
SELECT * FROM testing2;
2006
doesn't give the expected result. Only 2006 has been added to the temporary table, which now consists of only 1 column, instead of two

quoting the integer is be easy, but the same behaviour occurs when doing a CONCAT on a string and a field of the INT type:

CREATE TEMPORARY TABLE testing2 SELECT 2006,  CONCAT('0', WeekNR) AS WeekNR FROM a_table_with_data

This fails when WeekNR is of the INT (or TINYINT, etc.) type

How to repeat:
This:
CREATE TEMPORARY TABLE testing SELECT 2006,  CONCAT('0', '9') AS WeekNR;
SELECT * FROM testing;
2006 | 06
will give the expected result

This however:
CREATE TEMPORARY TABLE testing2 SELECT 2006,  CONCAT('0', 9) AS WeekNR;
SELECT * FROM testing2;
2006
doesn't give the expected result. Only 2006 has been added to the temporary table, which now consists of only 1 column, instead of two

[29 Sep 2006 13:35] Valeriy Kravchuk

Sorry, but I was not able to repeat the behaviour described with 4.1.22-BK on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.22    |
+-----------+
1 row in set (0.01 sec)

mysql> CREATE TEMPORARY TABLE testing2 SELECT 2006,  CONCAT('0', 9) AS WeekNR;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testing2;
+------+--------+
| 2006 | WeekNR |
+------+--------+
| 2006 | 09     |
+------+--------+
1 row in set (0.00 sec)

So, upgrade to latest GA version, 4.1.21, please.