Bug #49570 Assertion failed: !(order->used & map) on re-execution of prepared statement

• Submitted: 9 Dec 2009 20:13
• Modified: 12 Mar 2010 16:32
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.0.88-debug,5.1.41-debug,5.1.43-debug,5.6-debug
• OS: Any
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any
• Tags: assertion

[9 Dec 2009 20:13] Shane Bester

Description:
this is different to bug #48508

Version: '5.1.41-enterprise-gpl-advanced-debug-log'  socket: ''  port: 3306  MySQL
Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: !(order->used & map), file .\sql_select.cc, line 6976
mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2048]
mysqld-debug.exe!raise()[winsig.c:597]
mysqld-debug.exe!abort()[abort.c:78]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!eq_ref_table()[sql_select.cc:6976]
mysqld-debug.exe!only_eq_ref_tables()[sql_select.cc:7007]
mysqld-debug.exe!remove_const()[sql_select.cc:7131]
mysqld-debug.exe!JOIN::optimize()[sql_select.cc:1228]
mysqld-debug.exe!mysql_select()[sql_select.cc:2421]
mysqld-debug.exe!handle_select()[sql_select.cc:269]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5051]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2246]
mysqld-debug.exe!Prepared_statement::execute()[sql_prepare.cc:3581]
mysqld-debug.exe!Prepared_statement::execute_loop()[sql_prepare.cc:3256]
mysqld-debug.exe!mysql_sql_stmt_execute()[sql_prepare.cc:2529]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2256]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5974]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1233]
mysqld-debug.exe!do_command()[sql_parse.cc:872]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]

How to repeat:
#on a debug build run this:

drop table if exists `t1`;
create table `t1`(`a` int primary key)engine=myisam ;
insert into `t1` values (0),(1);
prepare `stmt` from 
"select 1  from `t1` join `t1` `t2` 
using(`a`) group by `t2`.`a`,`t1`.`a`";
execute `stmt`;
execute `stmt`;

[9 Dec 2009 21:37] MySQL Verification Team

Thank you for the bug report. Verified as described:

miguel@quetzal2:~$ dbs/5.6/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.0-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `t1`(`a` int primary key)engine=myisam ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into `t1` values (0),(1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> prepare `stmt` from 
    -> "select 1  from `t1` join `t1` `t2` 
    "> using(`a`) group by `t2`.`a`,`t1`.`a`";
Query OK, 0 rows affected (0.02 sec)
Statement prepared

mysql> execute `stmt`;
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)

mysql> execute `stmt`;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

[11 Dec 2009 2:24] MySQL Verification Team

doesn't affect non-debug binaries. this is a debug assertion, afterall. not a DoS vector, since nobody should run debug binaries in production.

[22 Dec 2009 6:40] 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/95333

2828 Ramil Kalimullin	2009-12-22
      Fix for bug#49570: Assertion failed: !(order->used & map) 
      on re-execution of prepared statement
      
      Problem: some (see eq_ref_table()) ORDER BY/GROUP BY optimization
      is called before each PS execution. However, we don't properly 
      initialize its stucture every time before the call.
      
      Fix: properly initialize the sturture used.
     @ mysql-test/r/ps.result
        Fix for bug#49570: Assertion failed: !(order->used & map) 
        on re-execution of prepared statement
          - test result.
     @ mysql-test/t/ps.test
        Fix for bug#49570: Assertion failed: !(order->used & map) 
        on re-execution of prepared statement
          - test case.
     @ sql/sql_select.cc
        Fix for bug#49570: Assertion failed: !(order->used & map) 
        on re-execution of prepared statement
          - set order->used to 0 before each eq_ref_table() call,
        as the function relies on that.

[14 Jan 2010 8:26] Bugs System

Pushed into 5.0.90 (revid:[e-mail]-20100114082402-05fod2h6z9x9wok8) (version source revid:[e-mail]-20091226105956-uzbmucnc5e88msj1) (merge vers: 5.0.89) (pib:16)

[15 Jan 2010 8:59] Bugs System

Pushed into 5.1.43 (revid:[e-mail]-20100115085139-qkh0i0fpohd9u9p5) (version source revid:[e-mail]-20091226112556-cc55rp1p01m7sndv) (merge vers: 5.1.42) (pib:16)

[22 Jan 2010 1:26] Paul DuBois

Noted in 5.0.90, 5.1.43 changelogs.

Some prepared statements could raise an assertion when re-executed.

Setting report to NDI pending push to 5.5.x+.

[5 Feb 2010 11:47] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100204063540-9czpdmpixi3iw2yb) (version source revid:[e-mail]-20100119163614-172adculixyu26j5) (pib:16)

[5 Feb 2010 11:53] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100205113942-oqovjy0eoqbarn7i) (version source revid:[e-mail]-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)

[5 Feb 2010 11:59] Bugs System

Pushed into 5.5.2-m2 (revid:[e-mail]-20100203172258-1n5dsotny40yufxw) (version source revid:[e-mail]-20100115112653-e3a24041ag1cv6v3) (merge vers: 5.5.1-m2) (pib:16)

[6 Feb 2010 1:13] Paul DuBois

Noted in 5.5.2, 6.0.14 changelogs.

Setting report to Need Merge pending push to Celosia.

[12 Mar 2010 14:13] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:29] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:45] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

[12 Mar 2010 16:32] Paul DuBois

Fixed in earlier 5.1.x, 5.5.x.