Bug #69836 Query returns a strange column name (with the comments)
Submitted: 25 Jul 2013 9:30 Modified: 25 Jul 2013 13:02
Reporter: Roberto Caiola Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[25 Jul 2013 9:30] Roberto Caiola
Description:
The name of the third column is compound with the contents of the comment!

How to repeat:

SELECT
	@filename := '123456_filename_of_the_video.mp4' as filename,
	@id := SUBSTRING_INDEX(@filename,'_',1) as id, -- parse the ID until "_" character
	md5(@id),
	LEFT(md5(@id),2) as level1,
	MID(md5(@id),3,2) as level2,
	CONCAT(LEFT(md5(@id),2),'/',MID(md5(@id),3,2),'/') as folder,
	CONCAT(LEFT(md5(@id),2),'/',MID(md5(@id),3,2),'/',@filename) as full_path
;
[25 Jul 2013 9:35] MySQL Verification Team
What are you expecting the output to be?  I got this;

mysql> SELECT
    ->  @filename := '123456_filename_of_the_video.mp4' as filename,
    ->  @id := SUBSTRING_INDEX(@filename,'_',1) as id, -- parse the ID until "_" character
    ->  md5(@id),
    ->  LEFT(md5(@id),2) as level1,
    ->  MID(md5(@id),3,2) as level2,
    ->  CONCAT(LEFT(md5(@id),2),'/',MID(md5(@id),3,2),'/') as folder,
    ->  CONCAT(LEFT(md5(@id),2),'/',MID(md5(@id),3,2),'/',@filename) as full_path\G
*************************** 1. row ***************************
 filename: 123456_filename_of_the_video.mp4
       id: 123456
 md5(@id): e10adc3949ba59abbe56e057f20f883e
   level1: e1
   level2: 0a
   folder: e1/0a/
full_path: e1/0a/123456_filename_of_the_video.mp4
1 row in set (0.00 sec)
[25 Jul 2013 10:48] Roberto Caiola
Column name adds the comment to its name

Attachment: column.png (image/png, text), 75.16 KiB.

[25 Jul 2013 10:50] Peter Laursen
Looks mostly as a bug with HeidiSQL to me!
[25 Jul 2013 10:52] Peter Laursen
ouch .. same problem in SQLyog (our program).

So some irregulartiy with metadata seems to be there!
[25 Jul 2013 10:54] Peter Laursen
Same phenomenon in SQLyog

Attachment: yog.jpg (image/jpeg, text), 38.14 KiB.

[25 Jul 2013 10:58] Peter Laursen
And in Workbench

Attachment: wb.jpg (image/jpeg, text), 37.12 KiB.

[25 Jul 2013 11:04] Roberto Caiola
Before sending I also tested in Navicat :)

But since HeidiSQL is Open Source, everyone could replicate it.
[25 Jul 2013 11:17] Peter Laursen
All server versions affected.  I tried with 5.0.96, 5.1.70, 5.5.32 and 5.6.12 in SQLyog and WB. It is the same.
[25 Jul 2013 12:05] MySQL Verification Team
Client must be started with --comment then here is a minimal testcase:

mysql> select 1 a, -- p
    -> md5(1)
    -> \G
*************************** 1. row ***************************
          a: 1
-- p
md5(1): c4ca4238a0b923820dcc509a6f75849b
1 row in set (0.00 sec)
[25 Jul 2013 12:24] Hartmut Holzgraefe
Hi Shane, 

that's probably due to the mysql command line client already removing comments by itself before sending the query?

When using PHP to send the query I can reproduce the comment showing up as part of the next columns name:

<?php
$query = "SELECT
	@filename := '123456_filename_of_the_video.mp4' as filename,
	@id := SUBSTRING_INDEX(@filename,'_',1) as id, -- parse the ID until '_' character
	md5(@id),
	LEFT(md5(@id),2) as level1,
	MID(md5(@id),3,2) as level2,
	CONCAT(LEFT(md5(@id),2),'/',MID(md5(@id),3,2),'/') as folder,
	CONCAT(LEFT(md5(@id),2),'/',MID(md5(@id),3,2),'/',@filename) as full_path";

mysql_connect("127.0.0.1","root","");
mysql_select_db("test");

$res = mysql_query($query) or die(mysql_error());
print_r(mysql_fetch_assoc($res));

Array
(
    [filename] => 123456_filename_of_the_video.mp4
    [id] => 123456
    [-- parse the ID until '_' character
	md5(@id)] => e10adc3949ba59abbe56e057f20f883e
    [level1] => e1
    [level2] => 0a
    [folder] => e1/0a/
    [full_path] => e1/0a/123456_filename_of_the_video.mp4
)
[25 Jul 2013 12:31] Peter Laursen
If comments are stripped from statements client-side before sending to server then this will not happen. That is why I believe Shane posted his case for a standard client started with --comments option (http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_comments: "--comments, -c:  Whether to preserve comments in statements sent to the server. The default is --skip-comments (discard comments), enable with --comments (preserve comments).)

And it seems that no current significant GUI client strips comments (at least not comments using this specfic comment format).

This a far as I can understand at least!
[25 Jul 2013 13:02] Roberto Caiola
And how about this test case :)

SELECT
1, -- Comment 1
2, -- Comment 2
3, -- ERROR in the Column Name if the next column is a variable
@a -- OK
,4, -- ERROR in the Column Name if the next column is a variable
@b -- Comment
;
[25 Jul 2013 13:10] Hartmut Holzgraefe
Ha, classic reply overlap ;)