MySQL Bugs: #27352: outer aggregate in nested subquery, looking at some code

Bug #27352	outer aggregate in nested subquery, looking at some code
Submitted:	21 Mar 2007 18:39	Modified:	29 Aug 2007 1:45
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.40	OS:	Any (*)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	aggregate, qc, subquery

Description:
I have no exploit for this possible bug yet. It may be exploitable once Bug #27348 is fixed.

Because if my understanding is correct, looking at the below, it will still be possible to place aggregates into "where" if the nesting is deep enough

in item_sum.cc many checks like the following are performed:
 !(allow_sum_func & (1 << nest_level)

allow_sum_function is a bitmap of type "nesting_map" and nesting_map is defined as:
 typedef ulong nesting_map; 

That means there are only 64 bits, and if nest_level is bigger than 64 then "1<<nest_level" will be 0 and the result will no longer be usable.

Therefore aggregates with a deep enough nesting may still execute in positions they should not execute

How to repeat:
only by looking at the code

Suggested fix:
Either the level of nesting needs to be limited, or a different concept is needed.

Thank you for a problem report. Verified just as described with code review on latest 5.0.40-BK. 

Side note: we have some similar limit (61?) documented in http://dev.mysql.com/doc/refman/5.1/en/joins-limits.html. I am not sure it applies to subqueries also, though.

Actually , I've put the wrong figure down.
Since it is "ulong" and not "unsigned long long" it is 32 bits only.

This should at least be made a long long type.

I agree the 61 per join, may apply. But could be documented more detailed. Are you allowed 2 independend jons with 60 tables each (in a union, or in 2 independend (nested/not nested) subqueries)?

And there is an exploit to it. The subquery below has 32 nestings. The "count(b)" is aggregated in the most outer query. This means it is aggreagted in the "where" clause".

As a result the query returns one row with the value "null" for "a".
(All works fine, if you remove 1 level of nesting)

drop table if exists xt1,xt2;
create table xt1 (a int, b int);
insert into xt1 values (1,11), (2,22), (2,22);
# 32 nestings
select a from xt1 where   (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select  (select    (select   (select   (select  (select count(b))) )) )))))))))) )))))))))) )))))))) >0;

Verified just as described in a last comment with latest 5.0.42-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists xt1,xt2;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> create table xt1 (a int, b int);
insert into xt1 values (Query OK, 0 rows affected (0.06 sec)

mysql> insert into xt1 values (1,11), (2,22), (2,22);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

32 nestings:

mysql> select a from xt1 where   (select  (select  (select  (select  (select  (
select
    -> (select  (select  (select  (select  (select  (select  (select  (select
(select
    -> (select  (select  (select  (select  (select  (select  (select  (select
(select
    -> (select  (select  (select  (select    (select   (select   (select  (sele
ct
    -> count(b))) )) )))))))))) )))))))))) )))))))) >0;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

31 nesting:

mysql> select a from xt1 where   (select  (select  (select  (select  (select  (
select  (select  (select  (select  (select  (select  (select  (select  (select
 (select  (select  (select  (select  (select  (select  (select  (select  (selec
t  (select  (select  (select  (select    (select   (select   (select  (select c
ount(b))) )) )))))))))) )))))))))) ))))))) >0;
+------+
| a    |
+------+
|    1 |
|    2 |
|    2 |
+------+
3 rows in set (0.01 sec)

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/32033

ChangeSet@1.2492, 2007-08-03 01:58:21+05:00, gshchepa@gleb.loc +5 -0
  Fixed bug #27352.
  The SELECT query with more than 31 nested dependent SELECT queries returned
  wrong result.
  
  New error message has been added: ER_TOO_HIGH_LEVEL_OF_NESTING_FOR_SELECT.
  It will be reported as: "Too high level of nesting for select".

Pushed into 5.1.22-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.22 changelogs.

A SELECT with more than 31 nested dependent subqueries returned
an incorrect result.

This was pushed to 5.1.23, not 5.1.22.

Have you implemented this error correctly?

I have a big pivot table query which used to work okay however this now gives the implemented error code.

Shouldn't the following be allowed to work as it doesn't actually use "depth" nested sub-selects?

drop table if exists xt1;
create table xt1 (a int);
insert into xt1 values (1);
# 32 selected sub-queries
select (select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp),
(select a from (select a from xt1) tmp)
from xt1;

Note: this is for example purposes; however I'm actually using unions and this causes an error once the cumulative amount of sub-selects reaches 32.

Luke Pearce, we get similar bug like you, but the query works on 5.1.19。

I'm still think this is a bug.

I'm seeing a similar problem to Luke Pearce's.  My queries aren't really nested at depth 32 - they're structured just like Luke's.  I still see the "Too high level of nesting for select" error, though.  Is this expected?  I would've guessed that to trigger this error, the queries would actually need to be nested as in Valeriy's comment.

Dear Ryan Graciano, Ben Li and Luke Pearce,

Thank you for your report. This problem has been reported as bug #41156.