MySQL Bugs: #7642: SELECT CONCAT( 'string',`field1`,`field2` ) AS `name` FROM tbl

Bug #7642	SELECT CONCAT( 'string',`field1`,`field2` ) AS `name` FROM tbl
Submitted:	3 Jan 2005 17:45	Modified:	13 Jan 2005 14:59
Reporter:	Diego Belotti	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
Executin the query:

SELECT CONCAT( 'string',`field1`,`field2` ) AS `name` FROM tbl

from PHP 5.0.3 with Apache 2.0.52, phpMyAdmin 2.6.0

the result is:

string+field1row1+field2row1
string+field1row1+field2row1+string+field1row2+field2row2
string+field1row1+field2row1+string+field1row2+field2row2+string+field1row3+field2row3
....                                 
and so on...

How to repeat:
Just execute the query from phpMyAdmin 2.6.0 on a test DB

Suggested fix:
To avoid the problem don't use a string as the first argument, but a field:

SELECT CONCAT(`field1`,'string',`field2` ) AS `name` FROM tbl

instead of

SELECT CONCAT( 'string',`field1`,`field2` ) AS `name` FROM tbl

Obviously the result isn't the same!

I hit this also in MySql Browser correcting a data error:

select linguisticexpression, instr(linguisticexpression, ' '),
left(linguisticexpression, instr(linguisticexpression, ' ')-1),
concat( 'Not Below ',trim(left(linguisticexpression, instr(linguisticexpression, ' ')-1)))
from antecedents where linguisticexpression like '%Above%'

Hi,

Thank you for the report, but I can't reproduce it with 4.1.8 MySQL server and command line client:

mysql> SELECT CONCAT( 'string',`field1`,`field2` ) AS `name` FROM tbl;
+------------------------+
| name                   |
+------------------------+
| stringfield1_1field2_1 |
| stringfield1_2field2_2 |
+------------------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT CONCAT(`field1`,'string',`field2` ) AS `name` FROM tbl;
+------------------------+
| name                   |
+------------------------+
| field1_1stringfield2_1 |
| field1_2stringfield2_2 |
+------------------------+
2 rows in set (0.00 sec)

Regarding MySQL Query Browser, please, provide repeatable test case for testing.

I have hit this also:
SELECT concat('www.mystore.com/',product,'.html') from web_products
produces the bug.

**However**  This happens in the MySql Query Browser (1.1.3), but NOT in the command-line mysql.exe.  Possibly a browser bug handling string termination..

 I'm running MySQL Server 4.1.8 on Windows XP SP2.

Sorry, to repeat you have to put other fields after "as..."

Example (every query terminated by ";")

CREATE TABLE `prova` (
  `A` varchar(255) NOT NULL default '',
  `B` varchar(255) NOT NULL default '',
  `C` varchar(255) NOT NULL default '',
  `D` varchar(255) NOT NULL default '',
  `E` varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `prova` VALUES ('1', '2', '3', '4', '5');
INSERT INTO `prova` VALUES ('6', '7', '8', '9', '10');
INSERT INTO `prova` VALUES ('11', '12', '13', '14', '15');
INSERT INTO `prova` VALUES ('16', '17', '18', '19', '20');
        
SELECT concat( 'tst', A, B ) AS test, C, D
FROM `prova`;

This returns:

tst12           3  	4
tst1267 	8 	9
tst12671112 	13 	14
tst12671617 	18 	19

while this:

SELECT concat(A,'tst', B) AS test, C, D
FROM `prova`

returns:
1tst2   	3  	4
6tst7 	        8 	9
11tst12 	13 	14
16tst17 	18 	19

Bye bye

Thank you for the bug report.
This is a duplicate for #7874

Please notice that #7874 is a duplicate for this:

3 Jan < 13 Jan

7642 < 7874

:) just kidding...