Bug #78508 Subquery result with aggregate function() in outer table is not understandable

• Submitted: 22 Sep 2015 3:20
• Modified: 22 Sep 2015 12:19
• Reporter: Su Dylan
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.22
• OS: Any
• CPU Architecture: Any

[22 Sep 2015 3:20] Su Dylan

Description:
Output:
=======
mysql> drop table if exists t1,t2;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(a1 int, a2 int);
create table t2(b1 int, b2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(b1 int, b2 int);
insert into t1 values(1,2);
insert intQuery OK, 0 rows affected (0.01 sec)

o t1 vamysql> insert into t1 values(1,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(2,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(3,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(1,4);
selQuery OK, 1 row affected (0.00 sec)

mysql> select (select sum(a1) from t2) from t1 group by a2;
+--------------------------+
| (select sum(a1) from t2) |
+--------------------------+
|                        1 |
|                        5 |
+--------------------------+
2 rows in set (0.00 sec)

Problem:
========
The result for "select (select sum(a1) from t2) from t1 group by a2" is not understandable.
We also did not see documentation in manual describing aggregate function with correlated subquery.

How to repeat:

drop table if exists t1,t2;
create table t1(a1 int, a2 int);
create table t2(b1 int, b2 int);
insert into t1 values(1,2);
insert into t1 values(2,4);
insert into t1 values(3,4);
insert into t2 values(1,4); 
select (select sum(a1) from t2) from t1 group by a2;

Suggested fix:
"select (select sum(a1) from t2) from t1 group by a2" should not be allowed.

[22 Sep 2015 12:19] MySQL Verification Team

Hi Su Dylan,

Thank you for the report.
Imho this a valid query because MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. To disable the MySQL GROUP BY extension, enable the ONLY_FULL_GROUP_BY SQL mode. This enables standard SQL behavior:

// 5.6.26

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

mysql> drop table if exists t1,t2;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table t1(a1 int, a2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2(b1 int, b2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(2,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(3,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(1,4);
Query OK, 1 row affected (0.00 sec)

mysql> select (select sum(a1) from t2) from t1 group by a2;
+--------------------------+
| (select sum(a1) from t2) |
+--------------------------+
|                        1 |
|                        5 |
+--------------------------+
2 rows in set (0.00 sec)

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

mysql> select (select sum(a1) from t2) from t1 group by a2;
ERROR 1055 (42000): 'test.t1.a1' isn't in GROUP BY
mysql>

## with pgsql

-bash-4.2$ psql
psql (9.4.0)
Type "help" for help.

postgres=# create table t1(a1 int, a2 int);
CREATE TABLE
postgres=# create table t2(b1 int, b2 int);
CREATE TABLE
postgres=# insert into t1 values(1,2);
INSERT 0 1
postgres=# insert into t1 values(2,4);
INSERT 0 1
postgres=# insert into t1 values(3,4);
INSERT 0 1
postgres=# insert into t2 values(1,4); 
INSERT 0 1
postgres=# select (select sum(a1) from t2) from t1 group by a2;
 sum 
-----
   5
   1
(2 rows)

postgres=# 

Thanks,
Umesh