| Bug #49869 | Group by Yields Incorrect Results with Derived Column Name with Single Quotes | ||
|---|---|---|---|
| Submitted: | 22 Dec 2009 7:45 | Modified: | 14 Jan 2010 15:57 |
| Reporter: | Brent | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
| Version: | 5.0, 5.1.39 | OS: | Any |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | GROUP BY, quotes | ||
[22 Dec 2009 7:45]
Brent
[22 Dec 2009 8:24]
Valeriy Kravchuk
In recent 5.0.x I see the same results as in 5.1 (and I consider them correct):
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.86-community-nt-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table `my_invoice` (
-> `amount` decimal(12,2) NOT NULL,
-> `approval_code` char(10),
-> `creation_method` varchar(127) NOT NULL,
-> `invoice_state_sysid` int(10) unsigned NOT NULL,
-> `when_paid` datetime
-> ) TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.14 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instea
d
1 row in set (0.03 sec)
mysql> Insert into my_invoice values
-> (4479.50 , NULL , 'End of Month Script' , 4 , '2009-06-11 13:27:00' ),
-> (1623.00 , NULL , 'End of Month Script' , 4 , '2009-06-11 13:27:10' ),
-> ( 12.00 , NULL , 'End of Month Script' , 4 , '2009-06-10 15:16:43' ),
-> ( 66.00 , '021945' , 'End of Month Script' , 4 , '2009-06-11 12:57:41' ),
-> ( 128.00 , '590169' , 'End of Month Script' , 4 , '2009-06-10 07:21:03' )
,
-> ( 96.00 , '741105' , 'End of Month Script' , 4 , '2009-06-10 07:16:05'),
-> ( 16.00 , '196549' , 'End of Month Script' , 4 , '2009-06-10 07:16:56' ),
-> ( 237.25 , '061339' , 'Customer Created' , 4 , '2009-06-11 08:25:41' ),
-> ( 24.00 , '050040' , 'Customer Created' , 4 , '2009-06-11 14:37:49' ),
-> ( 154.17 , '033722' , 'Customer Created' , 4 , '2009-06-11 15:20:34' );
Query OK, 10 rows affected (0.14 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select if(approval_code is not null,concat(creation_method,' -
'> Online'),concat(creation_method,' - Cheque')) as 'Paid Today', count(*) a
s
-> Count,sum(amount) as Amount from my_invoice where when_paid between '2009
-06-10' and
-> '2009-06-12' and invoice_state_sysid in (3,4) group by 'Paid Today';
+------------------------------+-------+---------+
| Paid Today | Count | Amount |
+------------------------------+-------+---------+
| End of Month Script - Cheque | 10 | 6835.92 |
+------------------------------+-------+---------+
1 row in set (0.03 sec)
'Paid Today' is just a string constant, it is NOT considered a column alias.
[22 Dec 2009 8:52]
Brent
Thanks for clarifying that my syntax is (no longer) correct and doing so very quickly!! However it did work just fine as I described here mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------------------------------+ | Variable_name | Value | +-------------------------+------------------------------------------+ | protocol_version | 10 | | version | 5.0.18-standard | | version_comment | MySQL Community Edition - Standard (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | +-------------------------+------------------------------------------+ Is there reference to the change in approach handling ' versus ` naming aliases?
[22 Dec 2009 14:35]
Valeriy Kravchuk
Formally your syntax was always incorrect (delimiter identified, and column alias, can be delimited either by backticks, `, or by quote marks, ", not by '). I was not able to identify any notes in changelogs that can explain difference of behavior you have with 5.0.18. In any case, if there is a bug here it is in the fact that " as 'Paid Today'" was accepted as a valid syntax for column alias. I do NOT understand why we still accept it, even in 5.1.43-bzr.
[22 Dec 2009 18:21]
Valeriy Kravchuk
So, I am making this a documentation request:
Please, document clearly and with examples that a) one can use string literal, 'a a', as column alias, and b) one can NOT reference to that column alias anywhere else in the SQL statement ('a a' will be treated as string literal, NOT as column).
a) is illustrated by 3rd SELECT at http://dev.mysql.com/doc/refman/5.1/en/problems-with-alias.html, b) is illustrated by this bug report. I want to see them both explicitly documented.
[12 Jan 2010 21:20]
Paul DuBois
The change in behavior occurs in 4.1.22, 5.0.30, and 5.1.13. It appears to be due to the fix for Bug#14019.
[14 Jan 2010 15:57]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Added to http://dev.mysql.com/doc/refman/5.1/en/problems-with-alias.html: In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters: SELECT 1 AS `one`, 2 AS 'two'; Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal. For example, this statement groups by the values in column id, referenced using the alias `a`: SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name GROUP BY `a`; But this statement groups by the literal string 'a' and will not work as expected: SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name GROUP BY 'a';
