Bug #37949 Crash if argument to SP is a subquery that returns more than one row

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);

Thank you for the bug report.

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

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().

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

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

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.

Pushed into 5.1.30  (revid:holyfoot@mysql.com-20080929141134-1dcz79nhp2pyedl1) (version source revid:holyfoot@mysql.com-20080930054130-gmp2qtz9ijqeh8te) (pib:4)

Setting report to NDI pending push into 5.0.x, 6.0.x.

Pushed into 6.0.8-alpha  (revid:holyfoot@mysql.com-20080929141134-1dcz79nhp2pyedl1) (version source revid:holyfoot@mysql.com-20080930060534-bjea6rsj80nwi131) (pib:5)

Noted in 6.0.8 changelog.

Setting report to NDI pending push into 6.0.x.

Noted in 5.0.72 changelog.

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)

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)

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)