Bug #27874 sql-mode: only full group by mode not working
Submitted: 17 Apr 2007 6:45 Modified: 8 May 2007 1:25
Reporter: Saravanan Ramamoorthy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.40-BK, 5.0.37- Community OS:Microsoft Windows (2000)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: regression

[17 Apr 2007 6:45] Saravanan Ramamoorthy
Description:
I have sql-mode as 'ONLY_FULL_GROUP_BY'.
But Group by is not working properly.

It's working fine with 5.0.21 Community version.

How to repeat:
create table abc(col1 int, col2 int , col3 int);

select * from abc group by col1; -- It will work, but it is wrong.

select col1,clo2 from abc group by col1; -- It will through error as abc.col2 is not in group by -- this is correct.
[17 Apr 2007 8:27] Valeriy Kravchuk
Thank you for a problem report. On latest 5.0.40-BK on Linux I've got:

mysql> show variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| sql_mode      | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql> select col1, col2, col3 from abc group by col1;
ERROR 1055 (42000): 'test.abc.col2' isn't in GROUP BY
mysql> select col1, col2 from abc group by col1;
ERROR 1055 (42000): 'test.abc.col2' isn't in GROUP BY

Note that the above is correct, and different from what you described. But:

mysql> select * from abc group by col1;
Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.40    |
+-----------+
1 row in set (0.00 sec)

I think, that SELECT * for this table should be treated as SELECT col1, col2, col3. So, this is a bug.
[24 Apr 2007 16: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/25317

ChangeSet@1.2438, 2007-04-24 20:12:54+04:00, evgen@moonbone.local +4 -0
  Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
  
  When fields are inserted instead of * in the select list the wasn't marked for
  check for ONLY_FULL_GROUP_BY mode.
  
  The Field_iterator_table::create_item() function now marks newly created
  items for check when in the ONLY_FULL_GROUP_BY_MODE.
  The setup_wild() and the insert_fields() functions now maintains the
  cur_pos_in_select_list counter for the ONLY_FULL_GROUP_BY mode.
[24 Apr 2007 18:38] 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/25346

ChangeSet@1.2438, 2007-04-24 22:35:57+04:00, evgen@moonbone.local +4 -0
  Bug#27874: Non-grouped columns are allowed by * in ONLY_FULL_GROUP_BY mode.
  
  When fields are inserted instead of * in the select list they were not marked
  for check for the ONLY_FULL_GROUP_BY mode.
  
  The Field_iterator_table::create_item() function now marks newly created
  items for check when in the ONLY_FULL_GROUP_BY mode.
  The setup_wild() and the insert_fields() functions now maintain the
  cur_pos_in_select_list counter for the ONLY_FULL_GROUP_BY mode.
[30 Apr 2007 12:57] Bugs System
Pushed into 5.0.42
[30 Apr 2007 12:58] Bugs System
Pushed into 5.1.18-beta
[8 May 2007 1:25] Paul Dubois
Noted in 5.0.42, 5.1.18 changelogs.