MySQL Bugs: #41156: List of derived tables acts like a chain of mutually-nested subqueries

Bug #41156	List of derived tables acts like a chain of mutually-nested subqueries
Submitted:	1 Dec 2008 18:44	Modified:	4 Aug 2009 23:49
Reporter:	Gleb Shchepa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.72, 5.1.30, 6.0.7	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	derived table, regression, subquery

Description:
This problem was discovered after the fix for bug #27352, so I set "regression" tag.
See Luke Pearce's and others messages at bug #27352 for original description of the problem.

Short explanation is: a query containing list of derived tables like

   SELECT * FROM (SELECT 1) AS t1, (SELECT 2) AS t2, (SELECT 3) AS t3, ...
                 (SELECT 32) AS t32

fails on 32bit platform (on 64bit we need t64) with a error:

  "Too high level of nesting for select"

as if we run query like SELECT * FROM (SELECT 1, (SELECT 2, (SELECT 3,...

So, FROM list can't contain more than 31 (or 63 on 64bit) derived tables.

Actually this is not a quite regression of bug #27352 fix: without that fix we may have 32/64-bitmask overflows for long lists of derived tables (as reported) containing aggregate functions.

How to repeat:
SELECT * FROM
(SELECT 1) t1,
(SELECT 2) t2,
(SELECT 3) t3,
(SELECT 4) t4,
(SELECT 5) t5,
(SELECT 6) t6,
(SELECT 7) t7,
(SELECT 8) t8,
(SELECT 9) t9,
(SELECT 10) t10,
(SELECT 11) t11,
(SELECT 12) t12,
(SELECT 13) t13,
(SELECT 14) t14,
(SELECT 15) t15,
(SELECT 16) t16,
(SELECT 17) t17,
(SELECT 18) t18,
(SELECT 19) t19,
(SELECT 20) t20,
(SELECT 21) t21,
(SELECT 22) t22,
(SELECT 23) t23,
(SELECT 24) t24,
(SELECT 25) t25,
(SELECT 26) t26,
(SELECT 27) t27,
(SELECT 28) t28,
(SELECT 29) t29,
(SELECT 30) t30,
(SELECT 31) t31,
(SELECT 32) t32; # on 64bit platform add 32 subqueries more

Suggested fix:
sql_yacc.yy: add "Lex->nest_level--;" at the end of "select_derived" or "'(' get_select_lex select_derived union_opt ')' opt_table_alias" rule.

Thank you for a bug report. Verified just as described with 5.0.72, 5.1.30 and 6.0.7.

We have just recently updated to 5.0.79 to get around another bug in 5.0.44 and I believe we have hit this issue with a number of queries that are used for reports.
The queries do use sub-queries, but at most there are 3 nested queries, then the sets of queries are joined so I don't think they are nested too deep. 
On 5.0.44 they return the correct results. Please let me know if you would like some example queries.
As this bug is going to effect our ability to update it is deemed critical for us as the bug in 5.0.44 is causing crashing. This puts us between a rock and a hard place.
Is there any chance this is going to get fixed soon? And preferably in the 5.0 branch?

Cheers,
Jason

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

2727 Gleb Shchepa	2009-04-10
      Bug #41156: List of derived tables acts like a chain of
                  mutually-nested subqueries
      
      Queries of the form
      
        SELECT * FROM (SELECT 1) AS t1,
                      (SELECT 2) AS t2,...
                      (SELECT 32) AS t32
      
      caused the "Too high level of nesting for select" error 
      as if the query has a form 
      
        SELECT * FROM (SELECT 1 FROM (SELECT 2 FROM (SELECT 3 FROM... 
      
      
      The table_factor parser rule has been modified to adjust
      the LEX::nest_level variable value after every derived table.
      modified:
        mysql-test/r/derived.result
        mysql-test/t/derived.test
        sql/sql_yacc.yy

Hi.
Is there any update as to what release this bug maybe fixed in?
Having to work around this limitation is really hitting our reports.

Cheers,
Jason

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

2793 Gleb Shchepa	2009-07-03
      Bug #41156: List of derived tables acts like a chain of
                  mutually-nested subqueries
      
      The select_derived2 parser rule calls mysql_new_select()
      calls push_context() and nest_level++, however only
      the pop_context() was called at the end of derived table
      parsing at the table_factor rule.
      
      The table_factor parser rule has been modified to adjust
      the LEX::nest_level variable value after every derived table.
     @ mysql-test/r/derived.result
        Added test case for bug #41156.
     @ mysql-test/t/derived.test
        Added test case for bug #41156.
     @ sql/sql_yacc.yy
        Bug #41156: List of derived tables acts like a chain of
                    mutually-nested subqueries
          
        The table_factor parser rule has been modified to adjust
        the LEX::nest_level variable value after every derived table.

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

3017 Gleb Shchepa	2009-07-11
      Bug #41156: List of derived tables acts like a chain of
                  mutually-nested subqueries
      
      Queries of the form
      
        SELECT * FROM (SELECT 1) AS t1,
                      (SELECT 2) AS t2,...
                      (SELECT 32) AS t32
      
      caused the "Too high level of nesting for select" error
      as if the query has a form
      
        SELECT * FROM (SELECT 1 FROM (SELECT 2 FROM (SELECT 3 FROM...
      
      
      The table_factor parser rule has been modified to adjust
      the LEX::nest_level variable value after every derived table.
     @ mysql-test/r/derived.result
        Added test case for bug #41156.
     @ mysql-test/t/derived.test
        Added test case for bug #41156.
     @ sql/sql_yacc.yy
        Bug #41156: List of derived tables acts like a chain of
                    mutually-nested subqueries
        
        The select_derived2 parser rule calls mysql_new_select()
        calls push_context() and nest_level++, however only
        the pop_context() was called at the end of derived table
        parsing at the table_factor rule.
        
        The table_factor parser rule has been modified to adjust
        the LEX::nest_level variable value after every derived table.

Pushed into 5.1.37 (revid:joro@sun.com-20090713174543-cd2x7q1gi1hzoand) (version source revid:gshchepa@mysql.com-20090711184429-lsksu70kgft05r8j) (merge vers: 5.1.37) (pib:11)

Noted in 5.1.37 changelog.

For queries with a sufficient number of subqueries in the FROM clause
of this form:

SELECT * FROM (SELECT 1) AS t1, 
              (SELECT 2) AS t2,
              (SELECT 3) AS t3, ...

The query failed with a "Too high level of nesting for select" error,
as those the query had this form:

SELECT * FROM (SELECT 1 FROM (SELECT 2 FROM (SELECT 3 FROM ...

Setting report to NDI pending push into 5.4.x.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:gshchepa@mysql.com-20090711184641-xs77ljlj5xlo5nns) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelog.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed to 5.4.2.