[MDEV-10017] Get unexpected `Empty Set` for correlated subquery with aggregate functions Created: 2016-05-03  Updated: 2016-08-31  Resolved: 2016-08-31

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: 10.1.19, 10.2.2

Type: Bug Priority: Major
Reporter: Dylan Su Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: upstream

Sprint: 10.1.15, 10.1.17-1

 Description   

Description:
Output:
=====

mysql> create table t1(c1 int, c2 int, c3 int);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> insert into t1 values(1,1,1),(2,2,2),(3,3,3);
 
Query OK, 3 rows affected (0.00 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
mysql> select * from t1;
 
+------+------+------+
 
| c1   | c2   | c3   |
 
+------+------+------+
 
|    1 |    1 |    1 |
 
|    2 |    2 |    2 |
 
|    3 |    3 |    3 |
 
+------+------+------+
 
3 rows in set (0.00 sec)
 
 
 
mysql> create table t2(c1 int, c2 int);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> insert into t2 values(2,2);
 
Query OK, 1 row affected (0.00 sec)
 
 
 
mysql> select * from t2;
 
+------+------+
 
| c1   | c2   |
 
+------+------+
 
|    2 |    2 |
 
+------+------+
 
1 row in set (0.00 sec)
 
 
 
mysql> select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt));
 
Empty set (0.00 sec)
 
 
 
mysql> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.1.10-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Problem:
=====
Since (select t.c1 as c from t2 ...) can only produce one value 2, the condition c1 > 2 should not produce `Empty Set` result.

How to repeat:

drop table if exists t1,t2;
 
create table t1(c1 int, c2 int, c3 int);
 
insert into t1 values(1,1,1),(2,2,2),(3,3,3);
 
select * from t1;
 
create table t2(c1 int, c2 int);
 
insert into t2 values(2,2);
 
select * from t2;
 
select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt));

Suggested fix:
Non empty set is returned for the query.

 Comments   
Comment by Elena Stepanova [ 2016-05-09 ]

Thanks for the report and the test case.

Setting the sql_mode to ONLY_FULL_GROUP_BY makes the query fail with the error:

query 'select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt))' failed: 1463: Non-grouping field 'c1' is used in HAVING clause

or, the MySQL version of the error is

query 'select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt))' failed: 1140: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.t1.c1'; this is incompatible with sql_mode=only_full_group_by

The result is the same with MariaDB 5.1 - 10.2 and MySQL 5.5-5.6.
MySQL 5.7 has ONLY_FULL_GROUP_BY by default, so the query fails by default. Unsetting sql_mode makes it produce the same empty set.

Comment by Oleksandr Byelkin [ 2016-06-22 ]

Interesting thing is that ORDER BY in the test suite should be actually eliminated (because it has no sens without LIMIT clause) but removing it by hand fix situation.

Comment by Oleksandr Byelkin [ 2016-06-23 ]

the subquery returns correct value (2), but only once when it compared with 1, then it return nothing.

Comment by Oleksandr Byelkin [ 2016-06-23 ]

Picture is like this:

Main select decided that it is implicit grouping, so for usual field with no group it take any value (which is MySQL extension 1 in this case) then check once the chosen value and that is all.

probably min function is resolved in the main select...

Comment by Oleksandr Byelkin [ 2016-06-23 ]

yes, the aggregate function found to belong to SELECT #1

And it is very strange, because 'c' is not defined in SELECT #1

Comment by Oleksandr Byelkin [ 2016-06-23 ]

It is not fix but some user interface (to be continue)

revision-id: c584f1655ef45dafd2b5d0eb55ca21421de9a266 (mariadb-10.1.14-25-gc584f16)
parent(s): 773ce408762b5f8256d4053b6d0d418d15657b92
committer: Oleksandr Byelkin
timestamp: 2016-06-23 17:50:07 +0200
message:

MDEV-10017: Get unexpected `Empty Set` for correlated subquery with aggregate functions (part 1)

Make aggregate function dependency visible.

—

Comment by Oleksandr Byelkin [ 2016-06-28 ]

I thought that max_arg_level only limit the arguments of aggregate function, but it also points to the SELECT where to find, probably function by arguments should be limited in other way

Comment by Oleksandr Byelkin [ 2016-06-28 ]

It looks like max_sum_func_level can't play this role, because we need some low level limit. We can pull aggregate in the place where its arguments are not defined.

Comment by Oleksandr Byelkin [ 2016-06-28 ]

OK I found what is wrong:

if it is field resolved in HAVING & co then max_arg_level mover to this level (it is OK)
If it is usual field resolved in its SELECT used in aggregate function then max_arg_level muved to this level is it is maximum (is it OK???)

if it is usual field resolved in outer field then max_arg_level do not moved (WHY if above?)
if move it then stop working following:

create table t2 (a int, b int);
 
insert into t2 values (1,1), (2,2);
 
select  b x, (select group_concat(x) from t2) from  t2;
 
drop table t2;

because aggregate will be resolved in outer query instead of local.

So for now we have 2 "magnets" for aggregate, outer in HAVING&Co and local, other outer just ignored and do not checked at all.

Comment by Oleksandr Byelkin [ 2016-07-06 ]

5.7 is a bit different and there is no problems...

Comment by Oleksandr Byelkin [ 2016-07-06 ]

I found other big chunk of code in 5.7 which takes care about used table mask, probably it fixes 5.7.

Comment by Oleksandr Byelkin [ 2016-07-12 ]

it appeared thet 5.7 also have problem of trying to pull aggregate function in the place where arguments are not defined

so I'll roll back to very first fix and decision

Comment by Oleksandr Byelkin [ 2016-08-22 ]

revision-id: 4ea7865195b8af02d61c1efe0ce60428ce53766e (mariadb-10.1.16-16-g4ea7865)
parent(s): dedb8e436fc087f1308e50507be2583f915f3239
committer: Oleksandr Byelkin
timestamp: 2016-08-22 15:07:31 +0200
message:

MDEV-10017: Get unexpected `Empty Set` for correlated subquery with aggregate functions

take into account all arguments of aggregate function

—

Comment by Oleksandr Byelkin [ 2016-08-23 ]

revision-id: 39c1ac14a80fcaeaac0ca40cbad5aff337fab157 (mariadb-10.1.16-16-g39c1ac1)
parent(s): dedb8e436fc087f1308e50507be2583f915f3239
committer: Oleksandr Byelkin
timestamp: 2016-08-23 19:46:37 +0200
message:

MDEV-10017: Get unexpected `Empty Set` for correlated subquery with aggregate functions

take into account all arguments of aggregate function

—

Generated at Thu Feb 08 07:39:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.