MySQL Bugs: #27874: sql-mode: only full group by mode not working

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:	Windows (2000)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	regression

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.

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.

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.

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.

Pushed into 5.0.42

Pushed into 5.1.18-beta

Noted in 5.0.42, 5.1.18 changelogs.