Bug #36896 Server crash on SELECT FROM DUAL

• Submitted: 22 May 2008 21:07
• Modified: 20 Nov 2010 23:28
• Reporter: Konstantin Osipov (OCA)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 6.0-BK
• OS: Any
• Assigned to: Sergey Petrunya
• CPU Architecture: Any

[22 May 2008 21:07] Konstantin Osipov

Description:
The server crashes on this query:
drop table if exists t1;
create table t1 (a int);
select 1 as res from dual where (1) in (select * from t1);
drop table t1;

How to repeat:
drop table if exists t1;
create table t1 (a int);
select 1 as res from dual where (1) in (select * from t1);
drop table t1;

Suggested fix:
Correctly handle the situation when the JOIN contains no tables
in flatten_subqueries()

[22 May 2008 21:09] Konstantin Osipov

When this bug is fixed, please enable the respective test case in ps_ddl.test
as well. It's currently commented out.

[7 Sep 2008 22:21] Sergey Petrunya

BUG#37904 has been marked as a duplicate of this bug.

[8 Sep 2008 11:02] Bugs System

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

2690 Sergey Petrunia	2008-09-06
      BUG#36896: Server crash on SELECT FROM DUAL
      - Disable semi-join and materialization if subquery's parent select is a 
        confluent SELECT ... FROM DUAL:
        = Semi-join runtime cannot handle semi-joins w/o outer tables
        = Materialization relies on parent select calling setup_subquery_materialization() 
          before trying to evaluate subquery items. SELECT ... FROM dual buypasses many 
          steps in join execution and doesn't meet this requirement.
          And it's not worth making SELECT ... FROM dual able to work with materialization 
          because there's no benefit to use materialization if the subquery predicate will
          be evaluated only once. (it is possible to construct cases where it will be evaluated 
          many times but they are not practially important)

[10 Sep 2008 11:43] Bugs System

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

2690 Sergey Petrunia	2008-09-10
      BUG#36896: Server crash on SELECT FROM DUAL
      - Disable semi-join and materialization if subquery's parent select is a
        confluent SELECT ... FROM DUAL:
         = Semi-join runtime cannot handle semi-joins w/o outer tables
         = Materialization relies on parent select calling setup_subquery_materialization()
           before trying to evaluate subquery items. SELECT ... FROM dual buypasses many
           steps in join execution and doesn't meet this requirement.
         And it's not worth making SELECT ... FROM dual able to work with materialization
         because there's no benefit to use materialization if the subquery predicate will
         be evaluated only once. (it is possible to construct cases where it will be evaluated
         many times but they are not practially important)

[10 Sep 2008 11:47] Bugs System

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

2690 Sergey Petrunia	2008-09-10
      BUG#36896: Server crash on SELECT FROM DUAL
      - Disable semi-join and materialization if subquery's parent select is a
        confluent SELECT ... FROM DUAL:
         = Semi-join runtime cannot handle semi-joins w/o outer tables
         = Materialization relies on parent select calling setup_subquery_materialization()
           before trying to evaluate subquery items. SELECT ... FROM dual buypasses many
           steps in join execution and doesn't meet this requirement.
         And it's not worth making SELECT ... FROM dual able to work with materialization
         because there's no benefit to use materialization if the subquery predicate will
         be evaluated only once. (it is possible to construct cases where it will be evaluated
         many times but they are not practially important)

[1 Nov 2008 18:30] Bugs System

Pushed into 6.0.7-alpha  (revid:[e-mail]-20080910100837-ewz8p5k1dwfde29k) (version source revid:[e-mail]-20080910100837-ewz8p5k1dwfde29k) (pib:5)

[3 Nov 2008 16:40] Paul DuBois

Noted in 6.0.7 changelog.

A server crash resulted from attempts at semi-join and
materialization optimizations for subqueries with a parent of SELECT
... FROM DUAL.

[16 Aug 2010 6:39] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100816062819-bluwgdq8q4xysmlg) (version source revid:[e-mail]-20100816062612-enatdwnv809iw3s9) (pib:20)

[13 Nov 2010 16:19] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

[20 Nov 2010 23:28] Paul DuBois

Noted in 5.6.1 changelog.

[23 Nov 2010 2:13] Paul DuBois

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.