MySQL Bugs: #69836: Query returns a strange column name (with the comments)

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

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
;

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)

Column name adds the comment to its name

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

Looks mostly as a bug with HeidiSQL to me!

ouch .. same problem in SQLyog (our program).

So some irregulartiy with metadata seems to be there!

Same phenomenon in SQLyog

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

And in Workbench

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

Before sending I also tested in Navicat :)

But since HeidiSQL is Open Source, everyone could replicate it.

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.

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)

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
)

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!

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
;

Ha, classic reply overlap ;)