Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' with aggregates

[Philip Stoev]

The following query:

SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)
FROM t1, t2
WHERE (t2.c = t1.c)
AND (
        t1.b IN ( 4 )
        OR t1.a = 137
        AND EXISTS ( SELECT a FROM t3 )
)
GROUP BY 2;

crashes as follows:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0826497d in Field_long::val_str (this=0xae630478, val_buffer=0xae89ebb4, val_ptr=0xae513258) at field.cc:3646
#10 0x081ce347 in Item_field::val_str (this=0xae513248, str=0xae89ebb4) at item.cc:2262
#11 0x0821d319 in Item_func_concat::val_str (this=0xae513300, str=0xae89ebb4) at item_strfunc.cc:292
#12 0x081e13ed in Item::str_result (this=0xae513300, tmp=0xae89ebb4) at item.h:839
#13 0x083e8e39 in make_sortkey (param=0xae89eef4, to=0xae549f69 "", ref_pos=0xae6305b0 "") at filesort.cc:819
#14 0x083e874f in find_all_keys (param=0xae89eef4, select=0xae537758, sort_keys=0xae549f20, buffpek_pointers=0xae89ed2c, tempfile=0xae89ee10, indexfile=0x0)
    at filesort.cc:650
#15 0x083e735b in filesort (thd=0x9d4a6b8, table=0xae62e630, sortorder=0xae538660, s_length=1, select=0xae537758, max_rows=18446744073709551615,
    sort_positions=false, examined_rows=0xae89f060) at filesort.cc:240
#16 0x083427db in create_sort_index (thd=0x9d4a6b8, join=0xae529728, order=0xae514ab8, filesort_limit=18446744073709551615,
    select_limit=18446744073709551615, is_order_by=true) at sql_select.cc:18064
#17 0x0831f4d0 in JOIN::exec (this=0xae529728) at sql_select.cc:2623
#18 0x0831ffb0 in mysql_select (thd=0x9d4a6b8, rref_pointer_array=0x9d4c238, tables=0xae5133f8, wild_num=0, fields=..., conds=0xae514988, og_num=1,
    order=0x0, group=0xae514ab8, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae514b60, unit=0x9d4be1c, select_lex=0x9d4c0fc)
    at sql_select.cc:2901
#19 0x08317da3 in handle_select (thd=0x9d4a6b8, lex=0x9d4bdc0, result=0xae514b60, setup_tables_done_option=0) at sql_select.cc:283
#20 0x082b3171 in execute_sqlcom_select (thd=0x9d4a6b8, all_tables=0xae5133f8) at sql_parse.cc:5090
#21 0x082a9f75 in mysql_execute_command (thd=0x9d4a6b8) at sql_parse.cc:2234
#22 0x082b57ad in mysql_parse (thd=0x9d4a6b8,
    rawbuf=0xae512ec0 "SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)\nFROM t1, t2\nWHERE (t2.c = t1.c)\nAND (\nt1.b IN ( 4 ) \nOR t1.a = 137\nAND EXISTS ( SELECT a FROM t3 )\n)\nGROUP BY 2", length=153, found_semicolon=0xae8a0228) at sql_parse.cc:6091
#23 0x082a7bf2 in dispatch_command (command=COM_QUERY, thd=0x9d4a6b8,
    packet=0x9da3549 "SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)\nFROM t1, t2\nWHERE (t2.c = t1.c)\nAND (\nt1.b IN ( 4 ) \nOR t1.a = 137\nAND EXISTS ( SELECT a FROM t3 )\n)\nGROUP BY 2", packet_length=153) at sql_parse.cc:1211
#24 0x082a704d in do_command (thd=0x9d4a6b8) at sql_parse.cc:906
#25 0x082a40b5 in handle_one_connection (arg=0x9d4a6b8) at sql_connect.cc:1186
#26 0x00821919 in start_thread () from /lib/libpthread.so.0
#27 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index_merge PRIMARY,b b,PRIMARY 5,4 NULL 2 Using union(b,PRIMARY); Using where; Using filesort
1 PRIMARY t2 ref c c 5 test.t1.c 1 Using index
2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found

full optimizer_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=off,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

bzr version-info
revision-id: <email address hidden>
date: 2011-08-23 15:51:47 +0300
build-date: 2011-08-26 11:35:58 +0300
revno: 3166
branch-nick: maria-5.3

test case:

CREATE TABLE t1 ( a int , b int, c int, KEY (b), PRIMARY KEY (a)) ;
INSERT INTO t1 VALUES (1,4,0),(5,0,0),(6,7,0),(7,7,0),(8,1,0),(9,7,0),(10,1,0);

CREATE TABLE t2 ( b int, c int, KEY (c,b)) ;
INSERT INTO t2 VALUES (7,0),(1,0),(7,0),(1,0);

CREATE TABLE t3 ( a int ) ;

SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)
FROM t1, t2
WHERE (t2.c = t1.c)
AND (
        t1.b IN ( 4 )
        OR t1.a = 137
        AND EXISTS ( SELECT a FROM t3 )
)
GROUP BY 2;

Reproducible in maria-5.3. Not reproducible in maria-5.2, mysql-5.5

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

Type of the subquery is not important, emptiness of t3 also is not important.

A bit simplified query:
SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)
FROM t1, t2
WHERE (t2.c = t1.c)
AND (
        t1.b = 4
        OR t1.a = 137
        AND (SELECT a FROM t3 )
)
GROUP BY 2;
drop tables t1,t2,t3;

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

At the beginning the read_set bit for field 't1.c' set correctly (actually all 3 bits set), then QUICK_RANGE_SELECT::init_ror_merged_scan reset all bits.