Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Component/s | Optimizer [ 10200 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Assignee | Sergei Petrunia [ psergey ] | |
| Labels | upstream |
| Sprint | 10.1.15 [ 75 ] |
| Rank | Ranked lower |
| Assignee | Sergei Petrunia [ psergey ] | Oleksandr Byelkin [ sanja ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
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.
the subquery returns correct value (2), but only once when it compared with 1, then it return nothing.
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...
yes, the aggregate function found to belong to SELECT #1
And it is very strange, because 'c' is not defined in SELECT #1
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.
—
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
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
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.
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.
| Sprint | 10.1.15 [ 75 ] | 10.1.15, 10.1.16 [ 75, 76 ] |
I found other big chunk of code in 5.7 which takes care about used table mask, probably it fixes 5.7.
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
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
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Sprint | 10.1.15, 1.0.2 [ 75, 76 ] | 10.1.15 [ 75 ] |
| Rank | Ranked higher |
| Sprint | 10.1.15 [ 75 ] | 10.1.15, 10.1.17-1 [ 75, 87 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
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
—
| Assignee | Oleksandr Byelkin [ sanja ] | Igor Babaev [ igor ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
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
—
| Fix Version/s | 10.2.2 [ 22013 ] | |
| Fix Version/s | 10.1.19 [ 22111 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 75514 ] | MariaDB v4 [ 150395 ] |
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 clauseor, 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_byThe 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.