| Bug #14813 | Small parser bug in strict mode | ||
|---|---|---|---|
| Submitted: | 10 Nov 2005 8:20 | Modified: | 26 Oct 2006 13:52 |
| Reporter: | [ name withheld ] (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 5.0-BK, 5.0.13-rc-nt | OS: | Linux (Linux, Windows) |
| Assigned to: | Antony Curtis | CPU Architecture: | Any |
[10 Nov 2005 9:05]
Valeriy Kravchuk
Thank you for a bug report. Verified on 5.0.16-BK (ChangeSet@1.1972, 2005-11-05 22:45:54-08:00, igor@rurik.mysql.com) on Linux:
mysql> set sql_mode=traditional;
Query OK, 0 rows affected (0,00 sec)
mysql> CREATE TABLE `errors` (
-> `error_name` int(10) unsigned default '0',
-> `error_time` time NOT NULL default '00:00:00'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0,08 sec)
mysql> insert into errors values (456, '01:23:23'), (456, '01:23:46'), (456, '02
:23:46');
Query OK, 3 rows affected (0,00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from errors;
+------------+------------+
| error_name | error_time |
+------------+------------+
| 456 | 01:23:23 |
| 456 | 01:23:46 |
| 456 | 02:23:46 |
+------------+------------+
3 rows in set (0,01 sec)
mysql> select error_name, max(error_time) from errors;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql> select error_name, max(error_time) from errors group by hour(error_time), minute(error_time);
+------------+-----------------+
| error_name | max(error_time) |
+------------+-----------------+
| 456 | 01:23:46 |
| 456 | 02:23:46 |
+------------+-----------------+
2 rows in set (0,00 sec)
Moreover:
mysql> select error_name, max(error_time) from errors group by error_time;
+------------+-----------------+
| error_name | max(error_time) |
+------------+-----------------+
| 456 | 01:23:23 |
| 456 | 01:23:46 |
| 456 | 02:23:46 |
+------------+-----------------+
3 rows in set (0,00 sec)
This, may be, OK, but the following:
mysql> select error_name, error_time, max(error_time) from errors group by 2;
+------------+------------+-----------------+
| error_name | error_time | max(error_time) |
+------------+------------+-----------------+
| 456 | 01:23:23 | 01:23:23 |
| 456 | 01:23:46 | 01:23:46 |
| 456 | 02:23:46 | 02:23:46 |
+------------+------------+-----------------+
3 rows in set (0,00 sec)
mysql> set sql_mode=ansi;
Query OK, 0 rows affected (0,00 sec)
mysql> select error_name, error_time, max(error_time) from errors group by 2;
+------------+------------+-----------------+
| error_name | error_time | max(error_time) |
+------------+------------+-----------------+
| 456 | 01:23:23 | 01:23:23 |
| 456 | 01:23:46 | 01:23:46 |
| 456 | 02:23:46 | 02:23:46 |
+------------+------------+-----------------+
3 rows in set (0,00 sec)
mysql> select error_name, error_time, max(error_time) from errors group by error_name;
+------------+------------+-----------------+
| error_name | error_time | max(error_time) |
+------------+------------+-----------------+
| 456 | 01:23:23 | 02:23:46 |
+------------+------------+-----------------+
1 row in set (0,00 sec)
Looks like a bug for me really. How can it happen, that "scalar" column (without aggregation function) may not appear in GROUP BY at all in ANSI mode? If this is the intended behaviour, it should be described in http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html. But now it clearly states:
"mysql> SELECT order.custid, customer.name, MAX(payments)
-> FROM order,customer
-> WHERE order.custid = customer.custid
-> GROUP BY order.custid;
In standard SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the name is redundant if you do not run in ANSI mode."
[12 Jul 2006 3:49]
Marcelo Machado
I think that's a bug really. I had the same problem working in my classes and is hard try to explain that kind of "flexibility" of mysql. Create 2 tables, city and customer. One city has many customers and a customer has only 1 city. Create table city ( citycode int primary key, cityname varchar(50)); create table customer ( customercode int primary key, customername varchar(50), customercitycode int not null, foreign key (customercitycode) references city(citycode) ); Then insert some records in each table and run the querie: Select cityname, customername, count(*) from city inner join customer on citycode=customercitycode group by cityname; The field customername is not being aggregated and is not part of the group by, but no error is returned by mysql returns a customername to each city;
[26 Jul 2006 9:26]
Antony Curtis
This odd effect of GROUP BY is something which has existed for a long time. I recall seeing it in the MySQL 3.x days - a column specified in the result set which is not an aggregate function nor a column specified in the grouping list would end up being a value from a random row in that group. I don't know if anyone erronously uses such syntax.
[26 Oct 2006 13:52]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php In particular, see http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
Description: Parser doesn't seem to enforce grouping for non aggregate columns in this case How to repeat: Create table: CREATE TABLE `errors` ( `error_name` int(10) unsigned default '0', `error_time` time NOT NULL default '00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Populate it with three rows like this: mysql> select * from errors -> ; +------------+------------+ | error_name | error_time | +------------+------------+ | 456 | 01:23:23 | | 456 | 01:23:46 | | 456 | 02:23:46 | +------------+------------+ 3 rows in set (0.23 sec) Now issue this query: mysql> select error_name, max(error_time) from errors; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR OUP columns is illegal if there is no GROUP BY clause Grouping is correctly enforced. Now issue this other one: mysql> select error_name, max(error_time) from errors group by hour(error_time), minute(error_time); +------------+-----------------+ | error_name | max(error_time) | +------------+-----------------+ | 456 | 01:23:46 | | 456 | 02:23:46 | +------------+-----------------+ 2 rows in set (0.61 sec) Grouping for error name should be enforced, but it's not.