MySQL Bugs: #55615: debug assertion after using variable in assignment and referred to

Bug #55615	debug assertion after using variable in assignment and referred to
Submitted:	28 Jul 2010 18:15	Modified:	20 Nov 2010 18:19
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0.91-debug, 5.1.49-debug	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
In similar class as bug #55564 and bug #55565

Version: '5.1.49-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: !table->file || table->file->inited == handler::NONE, file .\sql_base.cc, line 1381

mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2083]
mysqld-debug.exe!raise()[winsig.c:597]
mysqld-debug.exe!abort()[abort.c:78]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!close_thread_table()[sql_base.cc:1381]
mysqld-debug.exe!close_open_tables()[sql_base.cc:1199]
mysqld-debug.exe!close_thread_tables()[sql_base.cc:1359]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1669]
mysqld-debug.exe!do_command()[sql_parse.cc:882]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1136]
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 debug build: the query returns a result but crash happens afterwards.

drop table if exists t1;
create table t1(a int)engine=myisam;
insert into t1 values (0),(0);
select (@a:=((select @a:=1 from t1 limit 1))) and count(1)
from `t1` group by @a;

Verified on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.50-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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 (0.01 sec)

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

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

mysql> select (@a:=((select @a:=1 from t1 limit 1))) and count(1)
    -> from `t1` group by @a;
+-----------------------------------------------------+
| (@a:=((select @a:=1 from t1 limit 1))) and count(1) |
+-----------------------------------------------------+
|                                                   1 |
|                                                   1 |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> bin/mysqld_safe: line 137: 94342 Abort trap              nohup /Users/openxs/dbs/5.1/libexec/mysqld --basedir=/Users/openxs/dbs/5.1 --datadir=/Users/openxs/dbs/5.1/var --user=mysql --log-error=/Users/openxs/dbs/5.1/var/macbook-pro.err --pid-file=/Users/openxs/dbs/5.1/var/macbook-pro.pid < /dev/null >> /Users/openxs/dbs/5.1/var/macbook-pro.err 2>&1
100728 21:38:28 mysqld_safe mysqld restarted

mysql> exit
Bye
macbook-pro:5.1 openxs$ tail -60 var/macbook-pro.err 
100727 16:51:15 [Note] Got signal 15 to shutdown mysqld
100727 16:51:15 [Note] Event Scheduler: Purging the queue. 0 events
100727 16:51:15  InnoDB: Starting shutdown...
100727 16:51:19  InnoDB: Shutdown completed; log sequence number 0 43766502
100727 16:51:19 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: Shutdown complete

100727 16:51:19 mysqld_safe mysqld from pid file /Users/openxs/dbs/5.1/var/macbook-pro.pid ended
100728 21:38:21 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.1/var
100728 21:38:21 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/5.1/var/ is case insensitive
100728 21:38:21 [Warning] One can only use the --user switch if running as root

100728 21:38:22 [Note] Plugin 'FEDERATED' is disabled.
100728 21:38:22 [Note] Plugin 'ndbcluster' is disabled.
100728 21:38:22  InnoDB: Started; log sequence number 0 43766502
100728 21:38:22 [Note] Event Scheduler: Loaded 0 events
100728 21:38:22 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.50-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (!table->file || table->file->inited == handler::NONE), function unknown function, file sql_base.cc, line 1381.
...

Noted in 5.1.51, 5.5.6 changelogs.

Incorrect handling of user variable assignments as subexpressions 
could lead to incorrect results or server crashes. 

Setting report to Need Merge pending push to 5.6.x.

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Already documented for 5.1; reverting to Need Merge state, waiting as noted above.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (pib:21)