MySQL Bugs: #14019: group by converts literal string to column name

Bug #14019	group by converts literal string to column name
Submitted:	13 Oct 2005 22:45	Modified:	26 Oct 2006 3:17
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.14 / 5.1.13	OS:	Any (*)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
Strings are quoted using single quotes '

http://dev.mysql.com/doc/refman/5.0/en/legal-names.html
columns are quoted using backticks

See examples below, mysql takes the qouted string in the group by condition as column name.

It should be taken as constant, and group everything in a single row

How to repeat:
create table tb1 ( c1 int); insert into tb1 values (1),(2),(3);

select * from tb1 group by 'c1';
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
select c1 from tb1 group by 'c1';
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
select c1 as a from tb1 group by 'a';
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+

# correct is
select c1 from tb1 group by 'c2';
+------+
| a    |
+------+
|    1 |
+------+

Suggested fix:
-

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.26-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb1 ( c1 int); insert into tb1 values (1),(2),(3);
Query OK, 0 rows affected (0.18 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select c1 from tb1 group by 'c2';
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb1 ( c1 int); insert into tb1 values (1),(2),(3);
Query OK, 0 rows affected (0.05 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select c1 as a from tb1 group by 'a';
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading 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 2 to server version: 5.0.15-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb1 ( c1 int); insert into tb1 values (1),(2),(3);
Query OK, 0 rows affected (0.10 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select c1 as a from tb1 group by 'a';
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12527

ChangeSet@1.2573, 2006-09-26 16:10:23+03:00, gkodinov@macbook.gmz +5 -0
  BUG#14019 : group by converts literal string to column name
     When resolving unqualified name references MySQL was not
     checking what is the item type for the reference. Thus
     e.g a string literal item that has by convention a name
     equal to its string value will also work as a reference to 
     a SELECT list item or a table field.
     Fixed by allowing only Item_ref or Item_field to reference by
     (unqualified) name.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13724

ChangeSet@1.2573, 2006-10-16 13:10:25+03:00, gkodinov@macbook.gmz +5 -0
  BUG#14019 : group by converts literal string to column name
     When resolving unqualified name references MySQL was not
     checking what is the item type for the reference. Thus
     e.g a string literal item that has by convention a name
     equal to its string value will also work as a reference to 
     a SELECT list item or a table field.
     Fixed by allowing only Item_ref or Item_field to referenced by
     (unqualified) name.

Pushed in 4.1.22/5.0.27/5.1.13-beta

Noted in 4.1.22, 5.0.30, 5.1.13 changelogs.