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
Tags: ONLY_FULL_GROUP_BY, regression
Triage: Triaged: D2 (Serious)

[25 Aug 2009 18:07] Shane Bester
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
[25 Aug 2009 18:18] Valerii Kravchuk
Thank you for the bug report. Verified just as described (also with recent 5.4.x).
[28 Sep 2009 20:25] 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/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'
[3 Nov 2009 8:22] 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/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'
[18 Nov 2009 11:19] Mattias Jonsson
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).
[2 Dec 2009 8:06] Bugs System
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)
[3 Dec 2009 15:30] Jon Stephens
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.
[16 Dec 2009 8:38] Bugs System
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)
[16 Dec 2009 8:45] Bugs System
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)
[16 Dec 2009 8:51] Bugs System
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)
[16 Dec 2009 15:22] Jon Stephens
Also documented in the 5.5.1, 5.6.0, and 6.0.14 changelogs. Closed.
[7 Mar 2010 23:54] Paul Dubois
5.6.0 changelog entry unneeded.
[12 Mar 2010 14:10] Bugs System
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)
[12 Mar 2010 14:26] Bugs System
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)
[12 Mar 2010 14:40] Bugs System
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)
[15 Mar 2010 5:21] Jon Stephens
No additional changelog entries required. Returning to Closed state.