MySQL Bugs: #46923: select count(*) from partitioned table fails with ONLY_FULL_GROUP

Bug #46923	select count(*) from partitioned table fails with ONLY_FULL_GROUP_BY
Submitted:	25 Aug 2009 18:07	Modified:	15 Mar 2010 5:21
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.37, 5.1.39, 5.4.x	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	ONLY_FULL_GROUP_BY, regression

Description:
Since partitioning was fixed to not crash on ONLY_FULL_GROUP_BY (bug #45807), select count(*) broke for partitioned tables when using this mode:

From 5.1.37 and 5.1.39:

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

mysql> select count(*) from `t`; #fails
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

mysql> alter table `t` remove partitioning;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from `t`; #succeeds
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.02 sec)

How to repeat:
drop table if exists `t`;
create table `t` (`i` int primary key) engine=myisam partition by hash (i) partitions 1;
set sql_mode = 'ONLY_FULL_GROUP_BY';
select count(*) from `t`; # fails
alter table `t` remove partitioning;
select count(*) from `t`; #succeeds

Thank you for the bug report. Verified just as described (also with recent 5.4.x).

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/84904

3115 Mattias Jonsson	2009-09-28
      Bug#46923: select count(*) from partitioned table fails with
      ONLY_FULL_GROUP_BY
      
      Problem was that during checking and preparation of the
      partitioining function as a side effect in fix_fields
      the full_group_by_flag was changed.
      
      Solution was to set it back to its original value after
      calling fix_fields.
     @ mysql-test/r/partition.result
        Bug#46923: select count(*) from partitioned table fails with
        ONLY_FULL_GROUP_BY
        
        Updated result file
     @ mysql-test/t/partition.test
        Bug#46923: select count(*) from partitioned table fails with
        ONLY_FULL_GROUP_BY
        
        Extended test case to cover this bug
     @ sql/sql_partition.cc
        Bug#46923: select count(*) from partitioned table fails with
        ONLY_FULL_GROUP_BY
        
        Resetting full_group_by_flag back to its original value,
        not conflicting with the sql_mode 'ONLY_FULL_GROUP_BY'

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/89030

3115 Mattias Jonsson	2009-11-03
      Bug#46923: select count(*) from partitioned table fails with
      ONLY_FULL_GROUP_BY
      
      Problem was that during checking and preparation of the
      partitioining function as a side effect in fix_fields
      the full_group_by_flag was changed.
      
      Solution was to set it back to its original value after
      calling fix_fields.
      
      Updated patch, to also exclude allow_sum_func from being
      affected of fix_fields, as requested by reviewer.
     @ mysql-test/r/partition.result
        Bug#46923: select count(*) from partitioned table fails with
        ONLY_FULL_GROUP_BY
        
        Updated result file
     @ mysql-test/t/partition.test
        Bug#46923: select count(*) from partitioned table fails with
        ONLY_FULL_GROUP_BY
        
        Extended test case to cover this bug
     @ sql/sql_partition.cc
        Bug#46923: select count(*) from partitioned table fails with
        ONLY_FULL_GROUP_BY
        
        Resetting full_group_by_flag and allow_sum_func
        back to their original values,
        not conflicting with the sql_mode 'ONLY_FULL_GROUP_BY'

Pushed to mysql-5.1-bugteam and merged to mysql-pe (null merge, only tests was added, since wl#3352 uses a lex object on the stack instead of the one in thd).

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)

Documented bugfix in the 5.1.42 changelog as follows:

      SELECT COUNT(*) from a partitioned table failed when using
      ONLY_FULL_GROUP_BY SQL mode.

Closed.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

Also documented in the 5.5.1, 5.6.0, and 6.0.14 changelogs. Closed.

5.6.0 changelog entry unneeded.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

No additional changelog entries required. Returning to Closed state.