Crash in hp_movelink called from Expression_cache_tmptable::put_value with subquery cache

[Philip Stoev]

The following queries:

CREATE OR REPLACE VIEW v1 AS
SELECT t2.b
FROM t1
JOIN t2
WHERE t2 .c > (
        SELECT t2.c FROM t3
);

SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 );

crash as follows:

#3 0x082987db in handle_segfault (sig=11) at mysqld.cc:2810
#4 <signal handler called>
#5 0x08549f99 in hp_movelink (pos=0xae762da0, next_link=0x0, newlink=0xae762d98) at hp_hash.c:232
#6 0x0854962f in hp_write_key (info=0xae728380, keyinfo=0xae72eb34, record=0xae7348e0 <incomplete sequence \375>, recpos=0xae751c90 "") at hp_write.c:364
#7 0x08548d09 in heap_write (info=0xae728380, record=0xae7348e0 <incomplete sequence \375>) at hp_write.c:51
#8 0x085466ca in ha_heap::write_row (this=0xae72e578, buf=0xae7348e0 <incomplete sequence \375>) at ha_heap.cc:236
#9 0x081f2916 in handler::ha_write_tmp_row (this=0xae72e578, buf=0xae7348e0 <incomplete sequence \375>) at sql_class.h:3793
#10 0x084c1613 in Expression_cache_tmptable::put_value (this=0xae74b318, value=0xae74b298) at sql_expression_cache.cc:262
#11 0x081e618a in Item_cache_wrapper::cache (this=0xae74b220) at item.cc:7050
#12 0x081dad41 in Item_cache_wrapper::val_bool (this=0xae74b220) at item.cc:7213
#13 0x0820b1dc in Item_func_not::val_int (this=0xae713378) at item_cmpfunc.cc:333
#14 0x0833dc71 in evaluate_join_record (join=0xae735280, join_tab=0xae732868, error=0) at sql_select.cc:15202
#15 0x0833da24 in sub_select (join=0xae735280, join_tab=0xae732868, end_of_records=false) at sql_select.cc:15147
#16 0x0833d12f in do_select (join=0xae735280, fields=0x97fe1b4, table=0x0, procedure=0x0) at sql_select.cc:14770
#17 0x08321a61 in JOIN::exec (this=0xae735280) at sql_select.cc:2679
#18 0x0832228e in mysql_select (thd=0x97fc6e0, rref_pointer_array=0x97fe25c, tables=0xae712778, wild_num=1, fields=..., conds=0xae713378, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae731d78, unit=0x97fde40, select_lex=0x97fe120)
    at sql_select.cc:2900
#19 0x0831a05f in handle_select (thd=0x97fc6e0, lex=0x97fdde4, result=0xae731d78, setup_tables_done_option=0) at sql_select.cc:283
#20 0x082b482c in execute_sqlcom_select (thd=0x97fc6e0, all_tables=0xae712778) at sql_parse.cc:5112
#21 0x082ab5e9 in mysql_execute_command (thd=0x97fc6e0) at sql_parse.cc:2250
#22 0x082b6e5b in mysql_parse (thd=0x97fc6e0, rawbuf=0xae7125f0 "SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 )", length=52,
    found_semicolon=0xae8ff228) at sql_parse.cc:6112
#23 0x082a9238 in dispatch_command (command=COM_QUERY, thd=0x97fc6e0, packet=0x9855321 "", packet_length=52) at sql_parse.cc:1221
#24 0x082a8693 in do_command (thd=0x97fc6e0) at sql_parse.cc:916
#25 0x082a5677 in handle_one_connection (arg=0x97fc6e0) at sql_connect.cc:1191
#26 0x00821919 in start_thread () from /lib/libpthread.so.0
#27 0x0076acce in clone () from /lib/libc.so.6

explain of the view:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found

explain of the select:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t4 ALL NULL NULL NULL NULL 4 Using where
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
4 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found

optimizer switch: subquery_cache=on;

full switch: 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=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

revision-id: <email address hidden>
date: 2011-10-12 13:19:37 +0400
build-date: 2011-10-12 13:40:45 +0300
revno: 3225
branch-nick: maria-5.3

test case:

CREATE TABLE t1 (a int) ;

CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ;
INSERT INTO t2 VALUES (1,'x'),(2,'y');

CREATE TABLE t3 (a int) ;

CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ;
INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3);

CREATE OR REPLACE VIEW v1 AS
SELECT t2.b
FROM t1
JOIN t2
WHERE t2 .c > (
        SELECT t2.c FROM t3
);

SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 );

[Philip Stoev (pstoev-askmonty)]

Valgrind warnings:

==1034== Thread 4:
==1034== Use of uninitialised value of size 4
==1034== at 0x87BCEE7: my_hash_sort_simple (ctype-simple.c:319)
==1034== by 0x854A47E: hp_rec_hashnr (hp_hash.c:348)
==1034== by 0x8549515: hp_write_key (hp_write.c:339)
==1034== by 0x8548D08: heap_write (hp_write.c:51)
==1034== by 0x85466C9: ha_heap::write_row(unsigned char*) (ha_heap.cc:236)
==1034== by 0x81F2915: handler::ha_write_tmp_row(unsigned char*) (sql_class.h:3793)
==1034== by 0x84C1612: Expression_cache_tmptable::put_value(Item*) (sql_expression_cache.cc:262)
==1034== by 0x81E6189: Item_cache_wrapper::cache() (item.cc:7050)
==1034== by 0x81DAD40: Item_cache_wrapper::val_bool() (item.cc:7213)
==1034== by 0x820B1DB: Item_func_not::val_int() (item_cmpfunc.cc:333)
==1034== by 0x833DC70: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:15202)
==1034== by 0x833D8DB: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15107)
==1034== by 0x833D12E: do_select(JOIN*, List<Item>*, st_table*, Procedure*) (sql_select.cc:14770)
==1034== by 0x8321A60: JOIN::exec() (sql_select.cc:2679)
==1034== by 0x832228D: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, uns
igned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:2900)
==1034== by 0x831A05E: handle_select(THD*, st_lex*, select_result*, unsigned long) (sql_select.cc:283)
==1034==
==1034== Conditional jump or move depends on uninitialised value(s)
==1034== at 0x87BCEC1: my_hash_sort_simple (ctype-simple.c:313)
==1034== by 0x854A47E: hp_rec_hashnr (hp_hash.c:348)
==1034== by 0x8549D5B: hp_search (hp_hash.c:149)
==1034== by 0x854C0A0: heap_rkey (hp_rkey.c:62)
==1034== by 0x854694F: ha_heap::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (ha_heap.cc:288)
==1034== by 0x81BC16D: handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (sql_class.h:3655)
==1034== by 0x833ED7E: join_read_key2(THD*, st_join_table*, st_table*, st_table_ref*) (sql_select.cc:15704)
==1034== by 0x84C1320: Expression_cache_tmptable::check_value(Item**) (sql_expression_cache.cc:207)
==1034== by 0x81DA498: Item_cache_wrapper::check_cache() (item.cc:7034)
==1034== by 0x81DACE1: Item_cache_wrapper::val_bool() (item.cc:7207)
==1034== by 0x820B1DB: Item_func_not::val_int() (item_cmpfunc.cc:333)
==1034== by 0x833DC70: evaluate_join_record(JOIN*, st_join_table*, int) (sql_select.cc:15202)
==1034== by 0x833DA23: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:15147)
==1034== by 0x833D12E: do_select(JOIN*, List<Item>*, st_table*, Procedure*) (sql_select.cc:14770)
==1034== by 0x8321A60: JOIN::exec() (sql_select.cc:2679)
==1034== by 0x832228D: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, uns
igned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:2900)
==1034==
==1034== Use of uninitialised value of size 4
==1034== at 0x87BCEE7: my_has...

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

The problem is that a view has its owen levels numbering so direct compirison of numbers lead to adding wrong parameter (t2.c in the example). Explain does not show it as a parameter because views are materialized with the explain.