Server crashes in do_select on ALL/ANY subquery from a view, with character_set_connection=utf8

[Elena Stepanova]

#4 <signal handler called>
#5 0x0837465a in do_select (join=0x9477040, fields=0x947d54c, table=0x0, procedure=0x0)
    at sql_select.cc:15037
#6 0x08358a69 in JOIN::exec (this=0x9477040) at sql_select.cc:2730
#7 0x0827fd04 in subselect_single_select_engine::exec (this=0x945f9a8) at item_subselect.cc:3007
#8 0x08279839 in Item_subselect::exec (this=0x947e0f0) at item_subselect.cc:587
#9 0x0827ac58 in Item_singlerow_subselect::val_str (this=0x947e0f0, str=0xae94f9f0)
    at item_subselect.cc:1115
#10 0x08216066 in Item_func_conv_charset::Item_func_conv_charset (this=0x947e398, a=0x947e0f0, cs=0x8b25bc0,
    cache_if_const=true) at item_strfunc.h:753
#11 0x081fcc21 in Item::safe_charset_converter (this=0x947e0f0, tocs=0x8b25bc0) at item.cc:975
#12 0x081ff071 in agg_item_set_converter (coll=..., fname=0x8897e2b "<=", args=0x947e218, nargs=2, flags=7,
    item_sep=1) at item.cc:1946
#13 0x081ff26d in agg_item_charsets (coll=..., fname=0x8897e2b "<=", args=0x947e218, nargs=2, flags=7,
    item_sep=1) at item.cc:2026
#14 0x082386d1 in Item_func::agg_arg_charsets (this=0x947e1b8, c=..., items=0x947e218, nitems=2, flags=7,
    item_sep=1) at item_func.h:187
#15 0x0823d2ed in Item_bool_func2::fix_length_and_dec (this=0x947e1b8) at item_cmpfunc.cc:544
#16 0x082259a2 in Item_func::fix_fields (this=0x947e1b8, thd=0x93efae0, ref=0xae94fd60) at item_func.cc:207
#17 0x0827c2a7 in Item_allany_subselect::transform_into_max_min (this=0x945f898, join=0x9477040)
    at item_subselect.cc:1717
#18 0x0841e9a4 in JOIN::transform_max_min_subquery (this=0x9477040) at opt_subselect.cc:627
#19 0x0835284b in JOIN::optimize (this=0x9477040) at sql_select.cc:930
#20 0x081e72a8 in st_select_lex::optimize_unflattened_subqueries (this=0x93f1540) at sql_lex.cc:3137
#21 0x08426def in JOIN::optimize_unflattened_subqueries (this=0x9470a48) at opt_subselect.cc:4590
#22 0x0835496b in JOIN::optimize (this=0x9470a48) at sql_select.cc:1542
#23 0x08359214 in mysql_select (thd=0x93efae0, rref_pointer_array=0x93f167c, tables=0x945edc0, wild_num=1,
    fields=..., conds=0x945f9d0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2147764736, result=0x9460668, unit=0x93f1260, select_lex=0x93f1540) at sql_select.cc:2937
#24 0x08350f17 in handle_select (thd=0x93efae0, lex=0x93f1204, result=0x9460668, setup_tables_done_option=0)
    at sql_select.cc:284
#25 0x082ec071 in execute_sqlcom_select (thd=0x93efae0, all_tables=0x945edc0) at sql_parse.cc:5151
#26 0x082e344d in mysql_execute_command (thd=0x93efae0) at sql_parse.cc:2284
#27 0x082ee6ac in mysql_parse (thd=0x93efae0,
    rawbuf=0x945ec38 "SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v )", length=50,
    found_semicolon=0xae951234) at sql_parse.cc:6152
#28 0x082e108d in dispatch_command (command=COM_QUERY, thd=0x93efae0,
    packet=0x9449361 "SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v )", packet_length=50)
    at sql_parse.cc:1228
#29 0x082e0537 in do_command (thd=0x93efae0) at sql_parse.cc:923
#30 0x082dd4bd in handle_one_connection (arg=0x93efae0) at sql_connect.cc:1193
#31 0xb7766b25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-02-29 23:28:16 -0800
build-date: 2012-03-02 05:33:16 +0400
revno: 3451

Reproducible on MariaDB 5.3, 5.5. Not reproducible on MariaDB 5.2, MySQL 5.1.60, 5.5.20, 5.6.4.

EXPLAIN:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 DEPENDENT SUBQUERY <derived3> system NULL NULL NULL NULL 1 100.00
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select 'w' from `test`.`v` where trigcond((<cache>(`test`.`t1`.`a`) >= convert('w' using latin1)))))))

Minimal optimizer_switch: in_to_exists=on
Full optimizer_switch (default):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

SET optimizer_switch = 'in_to_exists=on';
SET character_set_connection = utf8;
CREATE TABLE t1 ( a VARCHAR(1) );
INSERT INTO t1 VALUES ('m'),('n');
CREATE VIEW v AS SELECT 'w' ;
SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v );

# End of test case

[Sergey Petrunia (sergefp)]

The crash itself happens on these lines:

  15035 if (join->table_count)
  15036 {
  15037 join->join_tab[join->top_join_tab_count - 1].next_select= end_select;
  15038 join_tab=join->join_tab+join->const_tables;
  15039 }

because

(gdb) p join->top_join_tab_count
  $82 = 0

If one looks at the source of the problem, they will find that the execution is an odd state. Here is the stack trace of the crash, with unneeded details removed:

(gdb) wher
  #0 in do_select (join=$JOIN1, fields=0x9f68b14, table=0x0, procedure=0x0)
  #1 in JOIN::exec (this=$JOIN1)
  #2 in subselect_single_select_engine::exec ()
  #3 in Item_subselect::exec ()
  #4 in Item_singlerow_subselect::val_str ()
  #5 in Item_func_conv_charset::Item_func_conv_charset ()
  #6 in Item::safe_charset_converter ()
  #7 in agg_item_set_converter ()
  #8 in agg_item_charsets ()
  #9 in Item_func::agg_arg_charsets ()
  #10 in Item_bool_func2::fix_length_and_dec ()
  #11 in Item_func::fix_fields ()
  #12 in Item_allany_subselect::transform_into_max_min (this=0x9f3a1f8, join=$JOIN1)
  #13 in JOIN::transform_max_min_subquery (this=$JOIN1)
  #14 in JOIN::optimize (this=$JOIN1)
  #15 in st_select_lex::optimize_unflattened_subqueries (this=0x9f30198)
  #16 in JOIN::optimize_unflattened_subqueries (this=$JOIN2)
  #17 in JOIN::optimize (this=$JOIN2)
  #18 in mysql_select ()

Note that we're inside JOIN::optimize (this=$JOIN1) , #14. And what we're trying to do is to call JOIN::exec (this=$JOIN1) for the same join object. This is wrong: optimization of a join should not involve attempts to execute the join we're trying to optimize.

I think, something is wrong with type conversions and/or MIN/MAX transformations.

[Sergey Petrunia (sergefp)]

Re-assigning to Sanja as this is closer to his domain than mine

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

IMHO the problem is that fix_fields() of the Item trigger its execution via finding charset converter (why charset converter evaluate arguments?).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

=== modified file 'sql/item_strfunc.h'
--- sql/item_strfunc.h 2011-12-11 09:34:44 +0000
+++ sql/item_strfunc.h 2012-03-05 08:42:11 +0000
@@ -747,7 +747,7 @@ class Item_func_conv_charset :public Ite
   {
     DBUG_ASSERT(args[0]->fixed);
     conv_charset= cs;
- if (cache_if_const && args[0]->const_item())
+ if (cache_if_const && args[0]->const_item() && !args[0]->is_expensive())
     {
       uint errors= 0;
       String tmp, *str= args[0]->val_str(&tmp);

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Above fixes cause of crash (we can't execute subqueries during optimization and such method to avoid it used in other parts of the code). But I am still not sure in the result

=== modified file 'mysql-test/t/ctype_recoding.test'
--- mysql-test/t/ctype_recoding.test 2007-08-07 14:30:23 +0000
+++ mysql-test/t/ctype_recoding.test 2012-03-05 09:16:49 +0000
@@ -210,3 +210,21 @@ drop table t1;
 create table t1(a char character set latin1 default _cp1251 0xFF);

 --echo End of 4.1 tests
+
+SET CHARACTER SET DEFAULT;
+--echo #
+--echo # LP BUG#944504 Item_func_conv_charset tries to execute subquery constant
+--echo #
+SET optimizer_switch = 'in_to_exists=on';
+SET character_set_connection = utf8;
+CREATE TABLE t1 ( a VARCHAR(1) );
+INSERT INTO t1 VALUES ('m'),('n');
+CREATE VIEW v1 AS SELECT 'w' ;
+--error ER_CANT_AGGREGATE_2COLLATIONS
+SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 );
+drop view v1;
+drop table t1;
+SET character_set_connection = default;
+SET optimizer_switch= default;
+
+--echo #End of 5.3 tests