Bug #37949 Crash if argument to SP is a subquery that returns more than one row
Submitted: 7 Jul 2008 20:36 Modified: 20 Oct 2008 15:30
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0, 5.1, 6.0 OS:Any
Assigned to: Alexey Botchkov CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[7 Jul 2008 20:36] Davi Arnaut
Description:
Passing a subquery as an argument to stored procedure will lead to a crash if the subquery returns more than one row. The crash will happen due to assertion in debug builds and for non-debug builds the bug leads to resource leaks and other problems.

The problem is that handlers associated with tables used by arguments evaluation are not being properly finalized before the table are closed.

How to repeat:
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
CREATE PROCEDURE p1(a INT) BEGIN END;
CALL p1((SELECT * FROM t1));

Suggested fix:
If possible, we need to cleanup the join used for evaluating the arguments before closing the tables. Otherwise, we need another schema to close the tables used during the evaluation.

1759│     /* 
1760│       Okay, got values for all arguments. Close tables that might be used by 
1761│       arguments evaluation. If arguments evaluation required prelocking mode, 
1762│       we'll leave it here.
1763│     */ 
1764│     if (!thd->in_sub_stmt)
1765├>      close_thread_tables(thd, 0, 0);
[7 Jul 2008 20:44] Miguel Solorzano
Thank you for the bug report.
[10 Jul 2008 17:01] Valeriy Kravchuk
5.1.25 is NOT affected:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

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

mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)

mysql> drop procedure p1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO t1 VALUES (1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE PROCEDURE p1(a INT) BEGIN END;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL p1((SELECT * FROM t1));
ERROR 1242 (21000): Subquery returns more than 1 row
[7 Aug 2008 16:13] 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/51122

2653 Alexey Botchkov	2008-08-07
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
              JOIN for the subselect wasn't cleaned if we came upon an error
              during sub_select() execution. That leads to the assertion failure
              in close_thread_tables()
      
      per-file comments:
        mysql-test/r/sp-error.result
              Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
              test case
      
        mysql-test/t/sp-error.test
              Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
              test- result
      
        sql/item_subselect.cc
              Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
              subselect_single_select_engine::exec() modified.
              added join->cleanup() call after join->exec().
[29 Sep 2008 15:23] 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/54665

2685 Alexey Botchkov	2008-09-29
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
           JOIN for the subselect wasn't cleaned if we came upon an error
           during sub_select() execution. That leads to the assertion failure
           in close_thread_tables()
      
           part of the 6.0 code backported
      
      per-file comments:
        mysql-test/r/sp-error.result
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
          test result
      
        mysql-test/t/sp-error.test
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
          test case
      
        sql/sp_head.cc
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
          lex->unit.cleanup() call added if not substatement
[30 Sep 2008 7:18] 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/54719

2685 Alexey Botchkov	2008-09-29
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
           JOIN for the subselect wasn't cleaned if we came upon an error
           during sub_select() execution. That leads to the assertion failure
           in close_thread_tables()
      
           part of the 6.0 code backported
      
      per-file comments:
        mysql-test/r/sp-error.result
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
          test result
      
        mysql-test/t/sp-error.test
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
          test case
      
        sql/sp_head.cc
      Bug#37949 Crash if argument to SP is a subquery that returns more than one row 
          lex->unit.cleanup() call added if not substatement
[7 Oct 2008 19:52] Paul Dubois
Noted in 5.1.29 changelog.

The server crashed if an argument to a stored procedure was a
subquery that returned more than one row. 

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[9 Oct 2008 18:07] Bugs System
Pushed into 5.1.30  (revid:holyfoot@mysql.com-20080929141134-1dcz79nhp2pyedl1) (version source revid:holyfoot@mysql.com-20080930054130-gmp2qtz9ijqeh8te) (pib:4)
[9 Oct 2008 18:42] Paul Dubois
Setting report to NDI pending push into 5.0.x, 6.0.x.
[17 Oct 2008 16:46] Bugs System
Pushed into 6.0.8-alpha  (revid:holyfoot@mysql.com-20080929141134-1dcz79nhp2pyedl1) (version source revid:holyfoot@mysql.com-20080930060534-bjea6rsj80nwi131) (pib:5)
[17 Oct 2008 17:48] Paul Dubois
Noted in 6.0.8 changelog.

Setting report to NDI pending push into 6.0.x.
[20 Oct 2008 15:30] Paul Dubois
Noted in 5.0.72 changelog.
[28 Oct 2008 21:06] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:holyfoot@mysql.com-20080929141134-1dcz79nhp2pyedl1) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:24] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:holyfoot@mysql.com-20080929141134-1dcz79nhp2pyedl1) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:51] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:holyfoot@mysql.com-20080929141134-1dcz79nhp2pyedl1) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)