MySQL Bugs: #57703: create view cause Assertion failed: 0, file .\item

Bug #57703	create view cause Assertion failed: 0, file .\item_subselect.cc, line 846
Submitted:	25 Oct 2010 3:27	Modified:	11 Jan 2011 16:14
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.1.53-debug, 5.5.8-debug,5.6.1-debug	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
similar to closed bug #54568 and bug #52120 whose testcases no longer crash on current sources.

Version: '5.6.1-m4-valgrind-max-debug'  socket: '/tmp/mysql.sock'  port: 3307  Source distribution
[New Thread 0x7ffff429f710 (LWP 26340)]
mysqld: /home/sbester/build/bzr/mysql-trunk/sql/item_subselect.cc:846: virtual longlong Item_in_subselect::val_int(): Assertion `0' failed.

5.6.1-debug stack:
#0  in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#1  in abort () at abort.c:92
#2  in __assert_fail  at assert.c:81
#3  in Item_in_subselect::val_int  at .sql/item_subselect.cc:846
#4  in Item_func_nop_all::val_int  at .sql/item_cmpfunc.cc:359
#5  in eval_const_cond  at .sql/item_func.cc:78
#6  in internal_remove_eq_conds  at .sql/sql_select.cc:9440
#7  in remove_eq_conds  at .sql/sql_select.cc:9533
#8  in optimize_cond  at .sql/sql_select.cc:9315
#9  in JOIN::optimize  at .sql/sql_select.cc:909
#10 in subselect_single_select_engine::exec  at .sql/item_subselect.cc:1905
#11 in Item_subselect::exec  at .sql/item_subselect.cc:288
#12 in Item_singlerow_subselect::val_int  at .sql/item_subselect.cc:601
#13 in Item_func_substr::fix_length_and_dec  at .sql/item_strfunc.cc:1417
#14 in Item_func::fix_fields  at .sql/item_func.cc:219
#15 in Item_str_func::fix_fields  at .sql/item_strfunc.cc:117
#16 in find_order_in_list at .sql/sql_select.cc:14720
#17 in setup_group  at .sql/sql_select.cc:14796
#18 in setup_without_group  at .sql/sql_select.cc:466
#19 in JOIN::prepare  at .sql/sql_select.cc:536
#20 in st_select_lex_unit::prepare  at .sql/sql_union.cc:266
#21 in mysql_create_view  at .sql/sql_view.cc:553
#22 in mysql_execute_command  at .sql/sql_parse.cc:4244
#23 in mysql_parse  at .sql/sql_parse.cc:5586
#24 in dispatch_command at .sql/sql_parse.cc:1130
#25 in do_command at .sql/sql_parse.cc:802
#26 in do_handle_one_connection at .sql/sql_connect.cc:1192
#27 in handle_one_connection at .sql/sql_connect.cc:1131
#28 in start_thread at pthread_create.c:301
#29 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115
(gdb) 

How to repeat:
#on debug build:

drop table if exists `t1`;
create table `t1`(`a` int)engine=myisam;
create or replace view `v1` as 
select 1 from `t1` group by
substring(1 from (select 3 from `t1` where `a`>=any(select 1)));

some details....

Attachment: bug57703_5.6.1-debug_gdb_output.txt (text/plain), 8.94 KiB.

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.52-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.00 sec)

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

mysql> create or replace view `v1` as 
    -> select 1 from `t1` group by
    -> substring(1 from (select 3 from `t1` where `a`>=any(select 1)));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
mysql> 101025 06:47:58 mysqld_safe mysqld restarted

mysql> exit
Bye
macbook-pro:5.1 openxs$ tail -80 var/macbook-pro.err 
...
01024 17:00:30 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/5.1/var/ is case insensitive
101024 17:00:30 [Warning] One can only use the --user switch if running as root

101024 17:00:30 [Note] Plugin 'FEDERATED' is disabled.
101024 17:00:30 [Note] Plugin 'ndbcluster' is disabled.
101024 17:00:31  InnoDB: Started; log sequence number 0 88312303
101024 17:00:31 [Note] Event Scheduler: Loaded 0 events
101024 17:00:31 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.52-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (0), function unknown function, file item_subselect.cc, line 836.
101025  6:47:58 - mysqld got signal 6 ;
...

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

3514 Sergey Glukhov	2010-12-07
      Fixed following problems:
      --Bug#52157 various crashes and assertions with multi-table update, stored function
      --Bug#54475 improper error handling causes cascading crashing failures in innodb/ndb
      --Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846
      --Bug#57352 valgrind warnings when creating view
      --Recently discovered problem when a nested materialized derived table is used
        before being populated and it leads to incorrect result
      
      We have several modes when we should disable subquery evaluation.
      The reasons for disabling are different. It could be
      uselessness of the evaluation as in case of 'CREATE VIEW'
      or 'PREPARE stmt', or we should disable subquery evaluation
      if tables are not locked yet as it happens in bug#54475, or
      too early evaluation of subqueries can lead to wrong result
      as it happened in Bug#19077.
      Main problem is that if subquery items are treated as const
      they are evaluated in ::fix_fields(), ::fix_length_and_dec()
      of the parental items as a lot of these methods have
      Item::val_...() calls inside.
      We have to make subqueries non-const to prevent unnecessary
      subquery evaluation. At the moment we have different methods
      for this. Here is a list of these modes:
      
      1. PREPARE stmt;
      We use UNCACHEABLE_PREPARE flag.
      It is set during parsing in sql_parse.cc, mysql_new_select() for
      each SELECT_LEX object and cleared at the end of PREPARE in
      sql_prepare.cc, init_stmt_after_parse(). If this flag is set
      subquery becomes non-const and evaluation does not happen.
      
      2. CREATE|ALTER VIEW, SHOW CREATE VIEW, I_S tables which
         process FRM files
      We use LEX::view_prepare_mode field. We set it before
      view preparation and check this flag in
      ::fix_fields(), ::fix_length_and_dec().
      Some of bugs are fixed using this approach,
      some are not(Bug#57352, Bug#57703). The problem here is.
      that we have a lot of ::fix_fields(), ::fix_length_and_dec()
      where we use Item::val_...() calls for const items.
      
      3. Derived tables with subquery = wrong result(Bug19077)
      The reason of this bug is too early subquery evaluation.
      It's fixed by adding Item::with_subselect field
      The check of this field in appropriate places prevents
      const item evaluation if the item have subquery.
      The fix for Bug19077 fixes only the problem with.
      convert_constant_item() function and does not cover
      other places(::fix_fields(), ::fix_length_and_dec() again)
      where subqueries could be evaluated.
      
      Example:
      CREATE TABLE t1 (i INT, j BIGINT);
      INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2);
      SELECT * FROM (SELECT MIN(i) FROM t1
      WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3;
      DROP TABLE t1;
      
      4. Derived tables with subquery where subquery
         is evaluated before table locking(Bug#54475, Bug#52157)
      
      Suggested solution is following:
      
      -Introduce new field LEX::context_analysis_only with the following
       possible flags:
       #define CONTEXT_ANALYSIS_ONLY_PREPARE 1
       #define CONTEXT_ANALYSIS_ONLY_VIEW    2
       #define CONTEXT_ANALYSIS_ONLY_DERIVED 4
      -Set/clean these flags when we perform
       context analysis operation
      -Item_subselect::const_item() returns
       result depending on LEX::context_analysis_only.
       If context_analysis_only is set then we return
       FALSE that means that subquery is non-const.
       As all subquery types are wrapped by Item_subselect
       it allow as to make subquery non-const when
       it's necessary.
     @ mysql-test/r/derived.result
        test case
     @ mysql-test/r/multi_update.result
        test case
     @ mysql-test/r/view.result
        test case
     @ mysql-test/suite/innodb/r/innodb_multi_update.result
        test case
     @ mysql-test/suite/innodb/t/innodb_multi_update.test
        test case
     @ mysql-test/suite/innodb_plugin/r/innodb_multi_update.result
        test case
     @ mysql-test/suite/innodb_plugin/t/innodb_multi_update.test
        test case
     @ mysql-test/t/derived.test
        test case
     @ mysql-test/t/multi_update.test
        test case
     @ mysql-test/t/view.test
        test case
     @ sql/item.cc
        --removed unnecessary code
     @ sql/item_cmpfunc.cc
        --removed unnecessary checks
        --refactored context analysis checks
     @ sql/item_func.cc
        --refactored context analysis checks
     @ sql/item_row.cc
        --removed unnecessary checks
     @ sql/item_subselect.cc
        --removed unnecessary code
        --added DBUG_ASSERT into Item_subselect::exec()
          which asserts that subquery execution can not happen
          if LEX::context_analysis_only is set, i.e. at context
          analysis stage.
        --Item_subselect::const_item()
          Return FALSE if LEX::context_analysis_only is set.
          It prevents subquery evaluation in ::fix_fields &
          ::fix_length_and_dec at context analysis stage.
     @ sql/item_subselect.h
        --removed unnecessary code
     @ sql/mysql_priv.h
        --Added new set of flags.
     @ sql/sql_class.h
        --removed unnecessary code
     @ sql/sql_derived.cc
        --added LEX::context_analysis_only analysis intialization/cleanup
     @ sql/sql_lex.cc
        --init LEX::context_analysis_only field
     @ sql/sql_lex.h
        --New LEX::context_analysis_only field
     @ sql/sql_parse.cc
        --removed unnecessary code
     @ sql/sql_prepare.cc
        --removed unnecessary code
        --added LEX::context_analysis_only analysis intialization/cleanup
     @ sql/sql_select.cc
        --refactored context analysis checks
     @ sql/sql_show.cc
        --added LEX::context_analysis_only analysis intialization/cleanup
     @ sql/sql_view.cc
        --added LEX::context_analysis_only analysis intialization/cleanup

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

3520 Sergey Glukhov	2010-12-14
      Fixed following problems:
      --Bug#52157 various crashes and assertions with multi-table update, stored function
      --Bug#54475 improper error handling causes cascading crashing failures in innodb/ndb
      --Bug#57703 create view cause Assertion failed: 0, file .\item_subselect.cc, line 846
      --Bug#57352 valgrind warnings when creating view
      --Recently discovered problem when a nested materialized derived table is used
        before being populated and it leads to incorrect result
      
      We have several modes when we should disable subquery evaluation.
      The reasons for disabling are different. It could be
      uselessness of the evaluation as in case of 'CREATE VIEW'
      or 'PREPARE stmt', or we should disable subquery evaluation
      if tables are not locked yet as it happens in bug#54475, or
      too early evaluation of subqueries can lead to wrong result
      as it happened in Bug#19077.
      Main problem is that if subquery items are treated as const
      they are evaluated in ::fix_fields(), ::fix_length_and_dec()
      of the parental items as a lot of these methods have
      Item::val_...() calls inside.
      We have to make subqueries non-const to prevent unnecessary
      subquery evaluation. At the moment we have different methods
      for this. Here is a list of these modes:
      
      1. PREPARE stmt;
      We use UNCACHEABLE_PREPARE flag.
      It is set during parsing in sql_parse.cc, mysql_new_select() for
      each SELECT_LEX object and cleared at the end of PREPARE in
      sql_prepare.cc, init_stmt_after_parse(). If this flag is set
      subquery becomes non-const and evaluation does not happen.
      
      2. CREATE|ALTER VIEW, SHOW CREATE VIEW, I_S tables which
         process FRM files
      We use LEX::view_prepare_mode field. We set it before
      view preparation and check this flag in
      ::fix_fields(), ::fix_length_and_dec().
      Some bugs are fixed using this approach,
      some are not(Bug#57352, Bug#57703). The problem here is
      that we have a lot of ::fix_fields(), ::fix_length_and_dec()
      where we use Item::val_...() calls for const items.
      
      3. Derived tables with subquery = wrong result(Bug19077)
      The reason of this bug is too early subquery evaluation.
      It was fixed by adding Item::with_subselect field
      The check of this field in appropriate places prevents
      const item evaluation if the item have subquery.
      The fix for Bug19077 fixes only the problem with
      convert_constant_item() function and does not cover
      other places(::fix_fields(), ::fix_length_and_dec() again)
      where subqueries could be evaluated.
      
      Example:
      CREATE TABLE t1 (i INT, j BIGINT);
      INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2);
      SELECT * FROM (SELECT MIN(i) FROM t1
      WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3;
      DROP TABLE t1;
      
      4. Derived tables with subquery where subquery
         is evaluated before table locking(Bug#54475, Bug#52157)
      
      Suggested solution is following:
      
      -Introduce new field LEX::context_analysis_only with the following
       possible flags:
       #define CONTEXT_ANALYSIS_ONLY_PREPARE 1
       #define CONTEXT_ANALYSIS_ONLY_VIEW    2
       #define CONTEXT_ANALYSIS_ONLY_DERIVED 4
      -Set/clean these flags when we perform
       context analysis operation
      -Item_subselect::const_item() returns
       result depending on LEX::context_analysis_only.
       If context_analysis_only is set then we return
       FALSE that means that subquery is non-const.
       As all subquery types are wrapped by Item_subselect
       it allow as to make subquery non-const when
       it's necessary.
     @ mysql-test/r/derived.result
        test case
     @ mysql-test/r/multi_update.result
        test case
     @ mysql-test/r/view.result
        test case
     @ mysql-test/suite/innodb/r/innodb_multi_update.result
        test case
     @ mysql-test/suite/innodb/t/innodb_multi_update.test
        test case
     @ mysql-test/suite/innodb_plugin/r/innodb_multi_update.result
        test case
     @ mysql-test/suite/innodb_plugin/t/innodb_multi_update.test
        test case
     @ mysql-test/t/derived.test
        test case
     @ mysql-test/t/multi_update.test
        test case
     @ mysql-test/t/view.test
        test case
     @ sql/item.cc
        --removed unnecessary code
     @ sql/item_cmpfunc.cc
        --removed unnecessary checks
        --THD::is_context_analysis_only() is replaced with LEX::is_ps_or_view_context_analysis()
     @ sql/item_func.cc
        --refactored context analysis checks
     @ sql/item_row.cc
        --removed unnecessary checks
     @ sql/item_subselect.cc
        --removed unnecessary code
        --added DBUG_ASSERT into Item_subselect::exec()
          which asserts that subquery execution can not happen
          if LEX::context_analysis_only is set, i.e. at context
          analysis stage.
        --Item_subselect::const_item()
          Return FALSE if LEX::context_analysis_only is set.
          It prevents subquery evaluation in ::fix_fields &
          ::fix_length_and_dec at context analysis stage.
     @ sql/item_subselect.h
        --removed unnecessary code
     @ sql/mysql_priv.h
        --Added new set of flags.
     @ sql/sql_class.h
        --removed unnecessary code
     @ sql/sql_derived.cc
        --added LEX::context_analysis_only analysis intialization/cleanup
     @ sql/sql_lex.cc
        --init LEX::context_analysis_only field
     @ sql/sql_lex.h
        --New LEX::context_analysis_only field
     @ sql/sql_parse.cc
        --removed unnecessary code
     @ sql/sql_prepare.cc
        --removed unnecessary code
        --added LEX::context_analysis_only analysis intialization/cleanup
     @ sql/sql_select.cc
        --refactored context analysis checks
     @ sql/sql_show.cc
        --added LEX::context_analysis_only analysis intialization/cleanup
     @ sql/sql_view.cc
        --added LEX::context_analysis_only analysis intialization/cleanup

Pushed into mysql-5.1 5.1.55 (revid:georgi.kodinov@oracle.com-20101217124435-9imm43geck5u55qw) (version source revid:sergey.glukhov@oracle.com-20101214093303-wmo9mqcb8rz0wv9f) (merge vers: 5.1.55) (pib:24)

Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:sergey.glukhov@oracle.com-20101214104600-v0ndu721rf61nbml) (merge vers: 5.5.8) (pib:24)

Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:sergey.glukhov@oracle.com-20101214111513-9j68fg7s48a986ng) (merge vers: 5.6.1) (pib:24)

Noted in 5.1.55, 5.5.9 changelogs.

Unnecessary subquery evaluation in contexts such as statement
preparation or view creation could cause a server crash.