Bug #59546 Assertion m_sp == __null fails in Item_func_sp::init_result_field with functions
Submitted: 17 Jan 2011 10:43 Modified: 11 Nov 2011 18:24
Reporter: John Embretsen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.93, 5.1.56, 5.5.9, 5.6.2-m5 OS:Any (Debug)
Assigned to: CPU Architecture:Any
Tags: pushbuild, rqg_pb2, sporadic

[17 Jan 2011 10:43] John Embretsen 
Description:
Version: '5.6.2-m5-debug'  socket: '/tmp/RQGmysql.19300.sock'  port: 19300  Source distribution
mysqld: /export/home/pb2/build/sb_0-2811428-1295049032.47/mysql-5.6.2-m5/sql/item_func.cc:6258: bool Item_func_sp::init_result_field(THD*): Assertion `m_sp == __null' failed.
110115  3:14:26 - mysqld got signal 6 ;

Query:

SELECT func_2 ( `col_int_key` ) AS `col_int_key` FROM  view_1;

Stacktrace:

#7  0x0074b621 in abort () from /lib/libc.so.6
#8  0x0074315b in __assert_fail () from /lib/libc.so.6
#9  0x083b0117 in Item_func_sp::init_result_field (this=0xa4dd2a40, thd=0xa3f8d68)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/item_func.cc:6258
#10 0x083b9540 in Item_func_sp::fix_fields (this=0xa4dd2a40, thd=0xa3f8d68, ref=0xa491e6c0)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/item_func.cc:6511
#11 0x0829a1f1 in create_view_field (thd=0xa3f8d68, view=0xa403c00, field_ref=0xa491e6c0, name=0xa4dd3448 "col_int_key")
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/table.cc:4398
#12 0x0829a314 in Field_iterator_view::create_item (this=0xa4c081f4, thd=0xa3f8d68)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/table.cc:4373
#13 0x0819ed50 in find_field_in_view (thd=0xa3f8d68, table_list=0xa403c00, name=0xa402f08 "col_int_key", length=11, item_name=0xa402f08 "col_int_key", 
    ref=0xa403218, register_tree_change=true) at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_base.cc:6007
#14 0x0819fb06 in find_field_in_table_ref (thd=0xa3f8d68, table_list=0xa403c00, name=0xa402f08 "col_int_key", length=11, item_name=0xa402f08 "col_int_key", 
    db_name=0x0, table_name=0x0, ref=0xa403218, check_privileges=true, allow_rowid=true, cached_field_index_ptr=0xa402fa0, register_tree_change=true, 
    actual_table=0xa4c085a8) at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_base.cc:6346
#15 0x081a01eb in find_field_in_tables (thd=0xa3f8d68, item=0xa402f30, first_table=0xa403c00, last_table=0x0, ref=0xa403218, report_error=REPORT_ALL_ERRORS, 
    check_privileges=true, register_tree_change=true) at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_base.cc:6618
#16 0x0836bf27 in Item_field::fix_outer_field (this=0xa402f30, thd=0xa3f8d68, from_field=0xa4c08728, reference=0xa403218)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/item.cc:4478
#17 0x0836f3b2 in Item_field::fix_fields (this=0xa402f30, thd=0xa3f8d68, reference=0xa403218)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/item.cc:4698
#18 0x083b923f in Item_func::fix_fields (this=0xa4031c0, thd=0xa3f8d68, ref=0xa403bc4)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/item_func.cc:200
#19 0x083b9581 in Item_func_sp::fix_fields (this=0xa4031c0, thd=0xa3f8d68, ref=0xa403bc4)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/item_func.cc:6516
#20 0x0819e94f in setup_fields (thd=0xa3f8d68, ref_pointer_array=0xa491e6c8, fields=@0xa3fa540, mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0xa491e5b8, 
    allow_sum_func=true) at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_base.cc:7789
#21 0x08243c2a in JOIN::prepare (this=0xa491e410, rref_pointer_array=0xa3fa5b0, tables_init=0xa403c00, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, 
    group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0xa3fa4ac, unit_arg=0xa3fa060)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_select.cc:570
#22 0x08244b5f in mysql_select (thd=0xa3f8d68, rref_pointer_array=0xa3fa5b0, tables=0xa403c00, wild_num=0, fields=@0xa3fa540, conds=0x0, og_num=0, order=0x0, 
    group=0x0, having=0x0, proc_param=0x0, select_options=2148305408, result=0xa491e400, unit=0xa3fa060, select_lex=0xa3fa4ac)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_select.cc:3533
#23 0x0824a95c in handle_select (thd=0xa3f8d68, lex=0xa3f9ffc, result=0xa491e400, setup_tables_done_option=0)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_select.cc:323
#24 0x081ee18e in execute_sqlcom_select (thd=0xa3f8d68, all_tables=0xa403c00)
    at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_parse.cc:4513
#25 0x081ef165 in mysql_execute_command (thd=0xa3f8d68) at /export/home/pb2/build/sb_0-2814172-1295073880.41/mysql-5.6.2-m5/sql/sql_parse.cc:2096

How to repeat:
Refer to binaries from mysql-trunk as $CODE.
Obtain the Random Query Generator from Launchpad:
bzr branch lp:randgen

cd randgen

perl runall-new.pl \
--basedir=$CODE \
--vardir=$PWD/vardir \
--grammar=conf/runtime/metadata_stability.yy \
--gendata=conf/runtime/metadata_stability.zz \
--validator=SelectStability,QueryProperties \
--queries=1M \
--duration=600 \
--reporters=Deadlock,ErrorLog,Backtrace \
--engine=Innodb \
--mysqld=--innodb \
--mysqld=--default-storage-engine=Innodb \
--mysqld=--transaction-isolation=SERIALIZABLE \
--mysqld=--innodb-flush-log-at-trx-commit=2 \
--mysqld=--loose-table-lock-wait-timeout=1 \
--mysqld=--innodb-lock-wait-timeout=1 \
--mysqld=--log-output=file \
--mysqld=--loose-skip-safemalloc \
--mysqld=--loose-lock-wait-timeout=1 \
--testname=rqg_mdl_stability

This will start a test run with 10 concurrent clients (default). The failure seems to be sporadic, more analysis is required in order to provide a simplified test case.
[17 Jan 2011 14:43] John Embretsen 
Bug verified based on manual testing on Linux 32-bit and Solaris x86 64-bit.

The bug happens with debug builds of current mysql-trunk (5.6.2), as well as 5.0, 5.1 and 5.5. There seems to be no issue with optimized (non-debug) builds (tested trunk only).

The assert occurs when doing a SELECT from a VIEW which SELECTs from a VIEW which uses a user defined FUNCTION that has been deleted between creating the view and the last SELECT.

How to reproduce:
==================

use test;

CREATE TABLE table_3 (
  col_int_key INT DEFAULT NULL,
  col_default INT DEFAULT '7',
  KEY col_int_key (col_int_key)
) ENGINE=InnoDB;

INSERT INTO table_3 VALUES (1, 6), (1, 6), (1, 6), (1, 6), (1, 6), (1, 6), (1, 6), (1, 6), (1, 6), (1, 6);

CREATE FUNCTION func_1 (in1 INTEGER) RETURNS INTEGER RETURN in1;

CREATE ALGORITHM=MERGE VIEW view_2 AS SELECT func_1(test.table_3.col_int_key) AS col_int_key from table_3 where (0 < test.table_3.col_int_key);

CREATE FUNCTION func_3 (in1 INTEGER) RETURNS INTEGER RETURN 1;

CREATE VIEW view_1 AS select func_3(view_2.col_int_key) AS col_int_key from view_2;

CREATE FUNCTION func_2 (in1 INTEGER) RETURNS INTEGER RETURN 3;

SELECT func_2 ( col_int_key ) AS col_int_key FROM view_1;

DROP FUNCTION func_1;

SELECT func_2 ( col_int_key ) AS col_int_key FROM  view_1;
[17 Jan 2011 15:21] John Embretsen 
Note that the proper server response (as observed with non-debug builds) is:

mysql> SELECT func_2 ( col_int_key ) AS col_int_key FROM  view_1;
ERROR 1356 (HY000): View 'test.view_1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
[11 Nov 2011 18:24] Paul DuBois 
Noted in 5.6.4 changelog.

An assertion was raised when selecting from a view that selects from
a view that used a user-defined function that had been deleted.