Bug #52088 Comments are sometimes incorrectly parsed as column names in results view
Submitted: 16 Mar 2010 12:14 Modified: 6 Jul 2010 14:46
Reporter: Craig Fowler Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1.49-bzr OS:Any (Debian Squeeze 32-bit/Ubuntu 9.10 .deb package, Windows XP)
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[16 Mar 2010 12:14] Craig Fowler
Description:
This seems to be an edge scenario and the effect is only visual, however I have been able to repeat it.

When performing a SQL SELECT command against a db connection in the SQL editor, if the SQL statement does not contain aliases for columns then the editor parses the columns to create the column headings for the 'results' view.

When the SQL statement contains comments and the following column contains a function, the parsing of the column name is incorrect in the results view and includes the full comment.

I can only reproduce this where the "offending results column" is preceeded by both another column and also one or more comment lines.

Looks like an edge-case parsing error when parsing the names of the columns to display in the results pane.  It works as it should if I add an alias to the offending column.

How to repeat:
Here is a SQL SELECT statement that I can use to reproduce this bug:

-- BEGIN SAMPLE SQL STATEMENT
SELECT
name,
-- This is a comment
MAX(product_id)
FROM
  product
GROUP BY
  product_id;
-- END SAMPLE SQL STATEMENT

The output columns are listed in the workbench user interface as:
* name
* -- This is a comment
  MAX(product_id)

Wheras the same SELECT statement performed through the MySQL CLI gives output columns:
* name
* MAX(product_id)
[16 Mar 2010 12:22] Valeriy Kravchuk
Verified just as described on Windows XP. Just try to execute this query:

-- comment1
select 1,
-- comment2
max(2)
-- comment3
[19 Apr 2010 19:53] Alfredo Kojima
Recategorizing bug to Connector/C++
[19 Apr 2010 20:44] Alfredo Kojima
Marked bug #52635 as duplicate of this one
[28 Jun 2010 12:32] Ulf Wendel
This is not a C/C++ specific bug. 

You can, for example, reproduce it using PHP and mysqlnd.

If at all, this can be called a server bug. The driver takes the string as is and passes it to the server using COM_QUERY. It is not the responsibility of the driver to parse the string and do anything with comments. Filtering the comment is a server task. 

Accoding to the documentation the command line client has a special handling of such comments: "Another safe feature is that the mysql  command-line client ignores lines that start with “--”.http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-comments.html
[1 Jul 2010 15:33] Ulf Wendel
Discussed this with Andrey as well. Not a Connector/C++ bug.
[1 Jul 2010 15:45] Craig Fowler
Sorry to instantly re-open this but it appears that responsibility for the bug has been shuffled from MySQL Workbench over to Connector/C++ and then they have closed it from their side (perhaps they are right and it is not their problem).

I have just downloaded Workbench 5.2.25 GA and have been able to replicate this bug there, so it is still valid - even if it is not related to the Connector/C++.

I have changed the bug category back to MySQL Workbench/SQL Editor to ensure that the bug doesn't get lost by its original owners.
[2 Jul 2010 7:18] Georg Richter
Valeriy,

this bug should be re-classified as a server metadata bug:

#include <mysql.h>
#include <stdio.h>

int main()
{
  char *query = 
"-- BEGIN SAMPLE SQL STATEMENT\n \
SELECT\n \
name,\n \
-- This is a comment\n \
MAX(product_id)\n \
FROM\n \
  product\n \
GROUP BY\n \
  product_id;\n\
-- END SAMPLE SQL STATEMENT ";

  MYSQL *mysql;
  MYSQL_ROW row;
  MYSQL_RES *res;

  mysql= mysql_init(NULL);
  mysql_real_connect(mysql, "localhost", "root", "", "test", 3306, NULL, 0);

  printf("Host info: %s\n", mysql_get_server_info(mysql));

  mysql_query(mysql, "DROP TABLE IF EXISTS product_info");
  mysql_query(mysql, "CREATE TABLE product_info (name varchar(20), product_id int");
  mysql_query(mysql, "INSERT INTO product_info VALUES('foo', 10)");

  if (mysql_query(mysql,  query))
    printf("Error: %s\n", mysql_error(mysql));

  if ((res= mysql_store_result(mysql)))
  {
    int i;
    MYSQL_FIELD *fields= mysql_fetch_fields(res);
    int num_fields= mysql_num_fields(res);
    row= mysql_fetch_row(res);

    for (i=0; i < num_fields; i++)
      printf("%s: %s\n", fields[i].name, row[i]);

    mysql_free_result(res);
  }
 
  mysql_close(mysql);
  return 0;
}

Output:
georg@bing:~/work/mysql/test> ./52088
Host info: 5.1.49
name: foo
-- This is a comment
 MAX(product_id): 1
[2 Jul 2010 9:24] Georg Richter
Testcase

Attachment: 52088.c (text/x-csrc), 1.08 KiB.

[5 Jul 2010 8:04] Valeriy Kravchuk
OK, but why mysql command line client does NOT have this problem:

mysql> -- BEGIN SAMPLE SQL STATEMENT
mysql> SELECT
    -> name,
    -> -- This is a comment
    -> MAX(product_id)
    -> FROM
    ->   product
    -> GROUP BY
    ->   product_id;
+------+-----------------+
| name | MAX(product_id) |
+------+-----------------+
| foo  |              10 |
+------+-----------------+
1 row in set (0.00 sec)

mysql> -- END SAMPLE SQL STATEMENT
mysql> 

My point is: Workbench should produce the same result as mysql command line client in this case. Something should be fixed, Workbench if not connector.
[6 Jul 2010 14:31] Alfredo Kojima
The command line client simply ignores lines that are comments, you can easily verify that by pressing the Up arrow key after executing your test query (the comments will be gone). You can also see in the general log:

mysql> SELECT  
    -> 1
    -> ,
    -> -- abc
    -> 2
    -> ;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

100706 11:29:00	   55 Query	SELECT 
1
,

2
[6 Jul 2010 14:36] Alfredo Kojima
Changing Category as it's a server bug.
[6 Jul 2010 14:50] Valeriy Kravchuk
Yes, you all are right about comments:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot -c --column-type testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1,
    -> -- comment2
    -> max(2)
    -> -- comment3
    -> ;
Field   1:  `1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

Field   2:  `-- comment2
max(2)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 1
Decimals:   0
Flags:      BINARY NUM 

+---+--------------------+
| 1 | -- comment2
max(2) |
+---+--------------------+
| 1 |                  2 |
+---+--------------------+
1 row in set (0.00 sec)

So, this is a server bug, indeed.