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: | |
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
[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 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 ;)