Bug #42900 aggregate function and column name allowed in select list without group by

• Submitted: 16 Feb 2009 19:03
• Modified: 16 Feb 2009 19:33
• Reporter: Peter Hitchcock
• Status: Not a Bug
• Category: MySQL Server: Compiling
• Severity: S3 (Non-critical)
• Version: 5.1.3.0
• OS: Windows (XP)
• CPU Architecture: Any

[16 Feb 2009 19:03] Peter Hitchcock

Description:
A statement such as 

select title, max(advance) from titles;

should be rejected as an error, instead it is executed with an erroneous result.

How to repeat:
set up a table with columns title, advance with more than 2 rows and execute the above query

[16 Feb 2009 19:33] MySQL Verification Team

Thank you for the bug report. Please read the below Manual's chapter:

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.33-Win x86-64 bzr revno:2756-log Source distribution

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

mysql 5.1 >use test
Database changed
mysql 5.1 >select title, max(advance) from titles;
+-----------------+--------------+
| title           | max(advance) |
+-----------------+--------------+
| MySQL Internals |           24 |
+-----------------+--------------+
1 row in set (0.00 sec)

mysql 5.1 >set sql_mode = ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >select title, max(advance) from titles;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql 5.1 >