MySQL Bugs: #25027: Assertion abort on PS for a query with single-row subquery and IS NULL predicate

Bug #25027	Assertion abort on PS for a query with single-row subquery and IS NULL predicate
Submitted:	13 Dec 2006 6:43	Modified:	24 Jan 2007 20:56
Reporter:	Igor Babaev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S1 (Critical)
Version:	5.0.32-debug	OS:
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
The debug version of the server receives a signal SIGABRT for the following prepared statement
PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL':

Version: '5.0.32-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1218753632 (LWP 12558)]
mysqld: sql_lex.cc:1906: void st_select_lex_unit::set_limit(SELECT_LEX*): Assertion `! thd->stmt_arena->is_stmt_prepare()' failed.

Program received signal SIGABRT, Aborted.

How to repeat:
The following sequence of commands demonstrates the problem:

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

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

mysql> CREATE TABLE t2 (b int);
Query OK, 0 rows affected (0.47 sec)

mysql> INSERT INTO t2 VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
ERROR 2013 (HY000): Lost connection to MySQL server during query

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

ChangeSet@1.2348, 2006-12-13 00:39:13-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #25027.
  Removed an assertion that was not valid for the cases where the query
  in a prepared statement contained a single-row non-correlated
  subquery that was used as an argument of the IS NULL predicate.

Ok, the removal of the assertion that caused the abort for the reported prepared statement does not help with the following statement submitted by Konstantin:

mysql> PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL';
ERROR 2013 (HY000): Lost connection to MySQL server during query

Version: '5.0.32-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1219581024 (LWP 27374)]
mysqld: item.cc:2697: virtual longlong Item_param::val_int(): Assertion `0' failed.

Program received signal SIGABRT, Aborted.

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

ChangeSet@1.2349, 2006-12-13 21:08:25-08:00, igor@olga.mysql.com +5 -0
  Fixed bug #25027.
  Blocked evaluation of constant objects of the classes
  Item_func_is_null and Item_is_not_null_test at the
  prepare phase in the cases when the objects used subqueries.

Returning to patch pending. Please confirm that the patch has been pushed. Thanks.

I confirm that that patch is now in

I confirm that that patch is now in 5.0.34, 5.1.15 main trees.

Noted in 5.0.34, 5.1.15 changelogs.

An assertion failed incorrectly for prepared statements that
contained a single-row non-correlated subquery that was used as an
argument of the IS NULL predicate.