MySQL Bugs: #113783: only_full_group_by option does not take effect

Bug #113783	only_full_group_by option does not take effect
Submitted:	28 Jan 12:50	Modified:	30 Jan 9:52
Reporter:	peng gao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Sometimes the only_full_group_by option does not take effect.
The following query 1 is not effective,  aa and aa1 tables only differ by one primary key.

CREATE TABLE `aa` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' ,
  `balance` decimal(12,2) NOT NULL DEFAULT '0.00' ,
  PRIMARY KEY (`id`)
);

CREATE TABLE `aa1` (
  `id` bigint(20) unsigned NOT NULL ,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `balance` decimal(12,2) NOT NULL DEFAULT '0.00'
);

CREATE TABLE `bb` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `from_pool_id` bigint unsigned NOT NULL ,
  `status` tinyint(4) NOT NULL ,
  `is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

insert into aa values(1, 0, '23.23'),(2, 1, '33.40'),(3, 0, '35.40'),(4, 1, '45.41');
insert into aa1 values(1, 0, '23.23'),(2, 1, '33.40'),(3, 0, '35.40'),(4, 1, '45.41');
insert into bb values(1, '11.11', 1, 2, 0),(2, '12.21', 2, 0, 1),(3, '13.21', 3, 4, 0),(4, '23.24', 4, 1, 1);

query 1:

 select IFNULL(aa.balance,0) balance, IFNULL(sum(bb.amount),0) amount
 from aa
 left join bb
 on aa.id=bb.from_pool_id
 where aa.id=3 
 and bb.is_deleted=0
 and aa.is_deleted=0
 and bb.status =1;
 
+---------+--------+
| balance | amount |
+---------+--------+
|    0.00 |   0.00 |
+---------+--------+

query 2:
 
 select IFNULL(aa.balance,0) balance, IFNULL(sum(bb.amount),0) amount
 from aa1 aa
 left join bb
 on aa.id=bb.from_pool_id
 where aa.id=3 
 and bb.is_deleted=0
 and aa.is_deleted=0
 and bb.status =1;

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'testdb.aa.balance'; this is incompatible with sql_mode=only_full_group_by 

How to repeat:
CREATE TABLE `aa` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' ,
  `balance` decimal(12,2) NOT NULL DEFAULT '0.00' ,
  PRIMARY KEY (`id`)
);

CREATE TABLE `aa1` (
  `id` bigint(20) unsigned NOT NULL ,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `balance` decimal(12,2) NOT NULL DEFAULT '0.00'
);

CREATE TABLE `bb` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `from_pool_id` bigint unsigned NOT NULL ,
  `status` tinyint(4) NOT NULL ,
  `is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

insert into aa values(1, 0, '23.23'),(2, 1, '33.40'),(3, 0, '35.40'),(4, 1, '45.41');
insert into aa1 values(1, 0, '23.23'),(2, 1, '33.40'),(3, 0, '35.40'),(4, 1, '45.41');
insert into bb values(1, '11.11', 1, 2, 0),(2, '12.21', 2, 0, 1),(3, '13.21', 3, 4, 0),(4, '23.24', 4, 1, 1);

query 1:

 select IFNULL(aa.balance,0) balance, IFNULL(sum(bb.amount),0) amount
 from aa
 left join bb
 on aa.id=bb.from_pool_id
 where aa.id=3 
 and bb.is_deleted=0
 and aa.is_deleted=0
 and bb.status =1;
 
+---------+--------+
| balance | amount |
+---------+--------+
|    0.00 |   0.00 |
+---------+--------+

query 2:
 
 select IFNULL(aa.balance,0) balance, IFNULL(sum(bb.amount),0) amount
 from aa1 aa
 left join bb
 on aa.id=bb.from_pool_id
 where aa.id=3 
 and bb.is_deleted=0
 and aa.is_deleted=0
 and bb.status =1;

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'testdb.aa.balance'; this is incompatible with sql_mode=only_full_group_by

Hi Mr. gao,

Thank you for your bug report.

We have managed to repeat your test case:

select IFNULL(aa.balance,0) balance, IFNULL(sum(bb.amount),0) amount from aa1 aa left join bb on aa.id=bb.from_pool_id where aa.id=3  and bb.is_deleted=0 and aa.is_deleted=0 and bb.status =1;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.aa.balance'; this is incompatible with sql_mode=only_full_group_by

We should inform you that this kind of aggregated queries are not compliant with SQL Standard. However, since MySQL is still supporting this syntax we are verifying this report, but with a lower priority.

Verified as reported.

Hi Mr. gao,

We have discussed your report with a Dvelopment team in charge and we agreed that this, actually, is not a bug.

Reason for that is simple .......

The reason is that when we have a primary key, and the predicate aa.id=3, then we have only one row in the table, and thus only one aa.balance value, which is of course unique. Thus, it is correct to allow the implicitly grouped query. It does not violate only_full_group_by.

But without the primary key, we may have multiple rows, and subsequently multiple, non-distinct values for aa.balance which violates only_full_group_by.

Not a bug, after all.

Thanks for your report, any way .......

Thank you，But I don't quite understand what the return value means, 

+---------+--------+
| balance | amount |
+---------+--------+
|    0.00 |   0.00 |
+---------+--------+

there are no values that meet the criteria here，
mysql>  select *
    ->  from aa
    ->  left join bb
    ->  on aa.id=bb.from_pool_id
    ->  where aa.id=3 
    ->  and bb.is_deleted=0
    ->  and aa.is_deleted=0
    ->  and bb.status =1;
Empty set (0.00 sec)

I understand, thank you.

We are glad that you understood ......