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

[13 Oct 2005 22:45] Martin Friebe
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:
-
[13 Oct 2005 22:56] Miguel Solorzano
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)
[26 Sep 2006 13:15] Bugs System
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.
[16 Oct 2006 10:16] Bugs System
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.
[21 Oct 2006 9:08] Georgi Kodinov
Pushed in 4.1.22/5.0.27/5.1.13-beta
[26 Oct 2006 3:17] Paul Dubois
Noted in 4.1.22, 5.0.30, 5.1.13 changelogs.