MySQL Bugs: #52088: Comments are sometimes incorrectly parsed as column names in results view

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

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)

Verified just as described on Windows XP. Just try to execute this query:

-- comment1
select 1,
-- comment2
max(2)
-- comment3

Recategorizing bug to Connector/C++

Marked bug #52635 as duplicate of this one

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

Discussed this with Andrey as well. Not a Connector/C++ bug.

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.

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

Testcase

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

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.

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

Changing Category as it's a server bug.

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.