Bug #52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817
Submitted: 17 Mar 2010 2:19 Modified: 20 Jun 2010 17:53
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1.46-debug, 5.6.99-m4-debug OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: assertion
Triage: Triaged: D2 (Serious)

[17 Mar 2010 2:19] Shane Bester
Description:
Similar to bug #48294

Version: '5.6.99-m4-debug'  socket: ''  port: 3306  Source distribution
Assertion failed: 0, file .\item_subselect.cc, line 817

mysqld.exe!my_sigabrt_handler()[my_thr_init.c:519]
mysqld.exe!raise()[winsig.c:590]
mysqld.exe!abort()[abort.c:71]
mysqld.exe!_wassert()[assert.c:212]
mysqld.exe!Item_in_subselect::val_int()[item_subselect.cc:817]
mysqld.exe!Item_func_nop_all::val_int()[item_cmpfunc.cc:357]
mysqld.exe!eval_const_cond()[item_func.cc:64]
mysqld.exe!internal_remove_eq_conds()[sql_select.cc:9354]
mysqld.exe!remove_eq_conds()[sql_select.cc:9447]
mysqld.exe!optimize_cond()[sql_select.cc:9229]
mysqld.exe!JOIN::optimize()[sql_select.cc:882]
mysqld.exe!subselect_single_select_engine::exec()[item_subselect.cc:1885]
mysqld.exe!Item_subselect::exec()[item_subselect.cc:261]
mysqld.exe!Item_singlerow_subselect::val_str()[item_subselect.cc:585]
mysqld.exe!Item_func_conv_charset::Item_func_conv_charset()[item_strfunc.h:745]
mysqld.exe!Item::safe_charset_converter()[item.cc:800]
mysqld.exe!agg_item_set_converter()[item.cc:1771]
mysqld.exe!agg_item_charsets()[item.cc:1850]
mysqld.exe!agg_item_charsets_for_comparison()[item.h:1495]
mysqld.exe!Item_func::agg_arg_charsets_for_comparison()[item_func.h:180]
mysqld.exe!Item_bool_func2::fix_length_and_dec()[item_cmpfunc.cc:486]
mysqld.exe!Item_func_equal::fix_length_and_dec()[item_cmpfunc.cc:1869]
mysqld.exe!Item_func::fix_fields()[item_func.cc:197]
mysqld.exe!setup_conds()[sql_base.cc:8048]
mysqld.exe!setup_without_group()[sql_select.cc:435]
mysqld.exe!JOIN::prepare()[sql_select.cc:524]
mysqld.exe!st_select_lex_unit::prepare()[sql_union.cc:264]
mysqld.exe!mysql_create_view()[sql_view.cc:515]
mysqld.exe!mysql_execute_command()[sql_parse.cc:4445]
mysqld.exe!mysql_parse()[sql_parse.cc:5735]
mysqld.exe!dispatch_command()[sql_parse.cc:1024]
mysqld.exe!do_command()[sql_parse.cc:710]
mysqld.exe!do_handle_one_connection()[sql_connect.cc:1174]
mysqld.exe!handle_one_connection()[sql_connect.cc:1113]
mysqld.exe!pthread_start()[my_winthread.c:61]
mysqld.exe!_callthreadstartex()[threadex.c:348]
mysqld.exe!_threadstartex()[threadex.c:331]
kernel32.dll!FlsSetValue()

How to repeat:
#on debug build run:

drop table if exists t1;
create table `t1` (`a` char(1)charset latin1,`b` char(1) charset utf8) engine=myisam;
create or replace view `v1` as select 1 from `t1`
where `t1`.`b` <=>(select `a` from `t1` where `a` <some(select '1'));
[17 Mar 2010 2:37] Miguel Solorzano
Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation.  All rights reserved.

C:\DBS>51

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.46-Win X64-debug-log Source distribution

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

mysql 5.1 >use test
Database changed
mysql 5.1 >drop table if exists t1;
Query OK, 0 rows affected (0.07 sec)

mysql 5.1 >create table `t1` (`a` char(1)charset latin1,`b` char(1) charset utf8) engine=myisam;
Query OK, 0 rows affected (0.08 sec)

mysql 5.1 >create or replace view `v1` as select 1 from `t1`
    -> where `t1`.`b` <=>(select `a` from `t1` where `a` <some(select '1'));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 5.1 >

100316 23:19:58 [Note] Plugin 'FEDERATED' is disabled.
100316 23:20:01 [Note] Event Scheduler: Loaded 0 events
100316 23:20:01 [Note] C:\DBS\5.1\bin\mysqld: ready for connections.
Version: '5.1.46-Win X64-debug-log'  socket: ''  port: 3306  Source distribution
Assertion failed: 0, file .\item_subselect.cc, line 817
100316 23:34:03 - mysqld got exception 0x80000003 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338112 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xd31f18
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
00000001400579D5    mysqld.exe!my_sigabrt_handler()[mysqld.cc:2047]
000000014000BB61    mysqld.exe!raise()[winsig.c:597]
0000000140014133    mysqld.exe!abort()[abort.c:78]
00000001405D21CA    mysqld.exe!_wassert()[assert.c:395]
000000014031A8B2    mysqld.exe!Item_in_subselect::val_int()[item_subselect.cc:817]
000000014009EE4D    mysqld.exe!Item_func_nop_all::val_int()[item_cmpfunc.cc:357]
00000001401468B6    mysqld.exe!eval_const_cond()[item_func.cc:63]
00000001402C40E6    mysqld.exe!remove_eq_conds()[sql_select.cc:9271]
00000001402C16EE    mysqld.exe!optimize_cond()[sql_select.cc:9119]
00000001402A9093    mysqld.exe!JOIN::optimize()[sql_select.cc:879]
000000014031F548    mysqld.exe!subselect_single_select_engine::exec()[item_subselect.cc:1889]
00000001403186BA    mysqld.exe!Item_subselect::exec()[item_subselect.cc:261]
0000000140319A3B    mysqld.exe!Item_singlerow_subselect::val_str()[item_subselect.cc:585]
00000001401F93E8    mysqld.exe!Item_func_conv_charset::Item_func_conv_charset()[item_strfunc.h:718]
00000001401E0296    mysqld.exe!Item::safe_charset_converter()[item.cc:767]
00000001401E3545    mysqld.exe!agg_item_set_converter()[item.cc:1703]
00000001401E37CF    mysqld.exe!agg_item_charsets()[item.cc:1783]
00000001400B487C    mysqld.exe!Item_func::agg_arg_charsets()[item_func.h:176]
000000014009EFB2    mysqld.exe!Item_bool_func2::fix_length_and_dec()[item_cmpfunc.cc:486]
00000001400A3E6D    mysqld.exe!Item_func_equal::fix_length_and_dec()[item_cmpfunc.cc:1867]
0000000140146FEB    mysqld.exe!Item_func::fix_fields()[item_func.cc:199]
0000000140179ABF    mysqld.exe!setup_conds()[sql_base.cc:8039]
00000001402DEF7A    mysqld.exe!setup_without_group()[sql_select.cc:432]
00000001402A7A86    mysqld.exe!JOIN::prepare()[sql_select.cc:521]
0000000140373724    mysqld.exe!st_select_lex_unit::prepare()[sql_union.cc:264]
00000001402E83BD    mysqld.exe!mysql_create_view()[sql_view.cc:520]
000000014020E6A7    mysqld.exe!mysql_execute_command()[sql_parse.cc:4652]
0000000140213735    mysqld.exe!mysql_parse()[sql_parse.cc:5975]
000000014020423B    mysqld.exe!dispatch_command()[sql_parse.cc:1235]
00000001402034EA    mysqld.exe!do_command()[sql_parse.cc:874]
00000001400C7235    mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
0000000140604D45    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001405DAFB5    mysqld.exe!_callthreadstart()[thread.c:295]
00000001405DAF87    mysqld.exe!_threadstart()[thread.c:277]
00000000774DBE3D    kernel32.dll!BaseThreadInitThunk()
0000000077616A51    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000000DB01A8=create or replace view `v1` as select 1 from `t1`
where `t1`.`b` <=>(select `a` from `t1` where `a` <some(select '1'))
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
[17 Mar 2010 2:42] Miguel Solorzano
Thank you for the bug report. Older bug:

C:\bugs\mysql-5.0.17-win32>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> create table `t1` (`a` char(1)charset latin1,`b` char(1) charset utf8) engine=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql> create or replace view `v1` as select 1 from `t1`
    -> where `t1`.`b` <=>(select `a` from `t1` where `a` <some(select '1'));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[23 Mar 2010 10:19] 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/104062

3413 Sergey Glukhov	2010-03-23
      Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817
      We should disable const subselect item evaluation because
      subselect transformation does not happen in view_prepare_mode
      and thus val_...() methods can not be called.
     @ mysql-test/r/ctype_ucs.result
        test case
     @ mysql-test/r/view.result
        test case
     @ mysql-test/t/ctype_ucs.test
        test case
     @ mysql-test/t/view.test
        test case
     @ sql/item.cc
        disabled const subselect item evaluation in
        view prepare mode.
     @ sql/item_subselect.cc
        added Item_subselect::safe_charset_converter which
        prevents const item evaluation in view prepare mode.
     @ sql/item_subselect.h
        added Item_subselect::safe_charset_converter which
        prevents const item evaluation in view prepare mode.
[23 Mar 2010 10:52] 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/104072

3413 Sergey Glukhov	2010-03-23
      Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817
      We should disable const subselect item evaluation because
      subselect transformation does not happen in view_prepare_mode
      and thus val_...() methods can not be called.
     @ mysql-test/r/ctype_ucs.result
        test case
     @ mysql-test/r/view.result
        test case
     @ mysql-test/t/ctype_ucs.test
        test case
     @ mysql-test/t/view.test
        test case
     @ sql/item.cc
        disabled const subselect item evaluation in
        view prepare mode.
     @ sql/item_subselect.cc
        added Item_subselect::safe_charset_converter which
        prevents const item evaluation in view prepare mode.
     @ sql/item_subselect.h
        added Item_subselect::safe_charset_converter which
        prevents const item evaluation in view prepare mode.
[23 Mar 2010 10:55] Alexander Barkov
http://lists.mysql.com/commits/104072 is Ok.
[30 Mar 2010 5:26] 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/104584

3432 Sergey Glukhov	2010-03-26
      Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817
      We should disable const subselect item evaluation because
      subselect transformation does not happen in view_prepare_mode
      and thus val_...() methods can not be called.
     @ mysql-test/r/ctype_ucs.result
        test case
     @ mysql-test/r/view.result
        test case
     @ mysql-test/t/ctype_ucs.test
        test case
     @ mysql-test/t/view.test
        test case
     @ sql/item.cc
        disabled const subselect item evaluation in
        view prepare mode.
     @ sql/item_subselect.cc
        added Item_subselect::safe_charset_converter which
        prevents const item evaluation in view prepare mode.
     @ sql/item_subselect.h
        added Item_subselect::safe_charset_converter which
        prevents const item evaluation in view prepare mode.
[6 Apr 2010 8:25] 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/105042

3446 Sergey Glukhov	2010-04-06
      Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817
      We should disable const subselect item evaluation because
      subselect transformation does not happen in view_prepare_mode
      and thus val_...() methods can not be called.
     @ mysql-test/r/ctype_ucs.result
        test case
     @ mysql-test/r/view.result
        test case
     @ mysql-test/t/ctype_ucs.test
        test case
     @ mysql-test/t/view.test
        test case
     @ sql/item.cc
        disabled const subselect item evaluation in
        view prepare mode.
     @ sql/item_subselect.cc
        added Item_subselect::safe_charset_converter which
        prevents const item evaluation in view prepare mode.
     @ sql/item_subselect.h
        added Item_subselect::safe_charset_converter which
        prevents const item evaluation in view prepare mode.
[5 May 2010 15:22] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:kristofer.pettersson@sun.com-20100416145616-vdcdyz9eu7j86lp7) (merge vers: 5.1.47) (pib:16)
[12 May 2010 19:23] Paul Dubois
Noted in 5.1.47 changelog.

For debug builds, creating a view containing a subquery that might
require collation adjustment caused an assertion to be raised. For
example, this could occur if some items had different collations but
the result collation could be adjusted to the one of them.
[28 May 2010 6:12] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100422150750-vp0n37kp9ywq5ghf) (pib:16)
[28 May 2010 6:40] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 7:08] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100411071742-a2o2anlcrj2bq14q) (merge vers: 5.5.4-m3) (pib:16)
[30 May 2010 0:27] Paul Dubois
Noted in 5.5.5, 6.0.14 changelogs.
[17 Jun 2010 12:15] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:01] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:42] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[25 Oct 2010 3:28] Shane Bester
still seeing this with different testcase after this fix. see bug #57703