LP:826279 - Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed with subquery and derived table

The following query:

SELECT SUM( DISTINCT b ), (
SELECT t2.a
FROM t1 JOIN t2 ON t2.c != 0
WHERE alias1.a != 0 AND t2.a != 0
)
FROM ( SELECT * FROM t3 ) AS alias1
GROUP BY 2;

asserts as follows:

mysqld: field.cc:3635: virtual longlong Field_long::val_int(): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x082646b8 in Field_long::val_int (this=0x9f822a8) at field.cc:3635
#10 0x081ce360 in Item_field::val_int (this=0x9fb95f0) at item.cc:2280
#11 0x081d8f70 in Item_direct_ref::val_int (this=0x9f76e40) at item.cc:6851
#12 0x081d8f70 in Item_direct_ref::val_int (this=0x9f76cf8) at item.cc:6851
#13 0x0820c238 in Arg_comparator::compare_int_signed (this=0x9f630cc) at item_cmpfunc.cc:1161
#14 0x081f16a2 in Arg_comparator::compare (this=0x9f630cc) at item_cmpfunc.h:72
#15 0x0820dc82 in Item_func_ne::val_int (this=0x9f63048) at item_cmpfunc.cc:1832
#16 0x0833a8c3 in do_select (join=0x9fb2d38, fields=0x9f6206c, table=0x0, procedure=0x0) at sql_select.cc:14701
#17 0x0831f451 in JOIN::exec (this=0x9fb2d38) at sql_select.cc:2670
#18 0x0824b509 in subselect_single_select_engine::exec (this=0x9f634e0) at item_subselect.cc:2949
#19 0x08245333 in Item_subselect::exec (this=0x9f63418) at item_subselect.cc:572
#20 0x082465dc in Item_singlerow_subselect::val_int (this=0x9f63418) at item_subselect.cc:1073
#21 0x081e12c8 in Item::val_int_result (this=0x9f63418) at item.h:838
#22 0x083e8c1c in make_sortkey (param=0xa1e70f04,
to=0x9f83270 "\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217h4z\025\217\217\217\217Y",
ref_pos=0x9f823a8 "\310\306\376\t\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217PRIVILEGES") at filesort.cc:880
#23 0x083e833f in find_all_keys (param=0xa1e70f04, select=0x9fb7fc0, sort_keys=0x9f83240, buffpek_pointers=0xa1e70d3c, tempfile=0xa1e70e20, indexfile=0x0)
at filesort.cc:650
#24 0x083e6f4b in filesort (thd=0x9ef31e8, table=0x9f71918, sortorder=0x9fba438, s_length=1, select=0x9fb7fc0, max_rows=18446744073709551615,
sort_positions=false, examined_rows=0xa1e71070) at filesort.cc:240
#25 0x08342442 in create_sort_index (thd=0x9ef31e8, join=0x9f77d98, order=0x9f63b90, filesort_limit=18446744073709551615, select_limit=18446744073709551615,
is_order_by=true) at sql_select.cc:18042
#26 0x0831f19e in JOIN::exec (this=0x9f77d98) at sql_select.cc:2613
#27 0x0831fc7e in mysql_select (thd=0x9ef31e8, rref_pointer_array=0x9ef4d64, tables=0x9f76560, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0,
group=0x9f63b90, having=0x0, proc_param=0x0, select_options=2147764736, result=0x9f63bd0, unit=0x9ef494c, select_lex=0x9ef4c28) at sql_select.cc:2891
#28 0x08317b13 in handle_select (thd=0x9ef31e8, lex=0x9ef48f0, result=0x9f63bd0, setup_tables_done_option=0) at sql_select.cc:283
#29 0x082b2efa in execute_sqlcom_select (thd=0x9ef31e8, all_tables=0x9f76560) at sql_parse.cc:5087
#30 0x082a9cfe in mysql_execute_command (thd=0x9ef31e8) at sql_parse.cc:2231
#31 0x082b5536 in mysql_parse (thd=0x9ef31e8,
rawbuf=0x9f61bf0 "SELECT SUM( DISTINCT b ), (\nSELECT t2.a\nFROM t1 JOIN t2 ON t2.c != 0\nWHERE alias1.a != 0 AND t2.a != 0 \n)\nFROM ( SELECT * FROM t3 ) AS alias1\nGROUP BY 2", length=152, found_semicolon=0xa1e72228) at sql_parse.cc:6088
#32 0x082a797b in dispatch_command (command=COM_QUERY, thd=0x9ef31e8,
packet=0x9f8eae9 "SELECT SUM( DISTINCT b ), (\nSELECT t2.a\nFROM t1 JOIN t2 ON t2.c != 0\nWHERE alias1.a != 0 AND t2.a != 0 \n)\nFROM ( SELECT * FROM t3 ) AS alias1\nGROUP BY 2", packet_length=152) at sql_parse.cc:1208
#33 0x082a6e01 in do_command (thd=0x9ef31e8) at sql_parse.cc:906
#34 0x082a3e68 in handle_one_connection (arg=0x9ef31e8) at sql_connect.cc:1178
#35 0x00821919 in start_thread () from /lib/libpthread.so.0
#36 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 Using filesort
3 DERIVED t3 ALL NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)

revision-id: <email address hidden>
date: 2011-08-12 14:31:40 +0300
build-date: 2011-08-14 16:18:30 +0300
revno: 3155
branch-nick: maria-5.3

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

test case:

--source include/have_innodb.inc

CREATE TABLE t1 (a int) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0),(0);

CREATE TABLE t2 (a int, b int, c int) ENGINE=InnoDB;
INSERT INTO t2 VALUES (10,7,0);

CREATE TABLE t3 (a int, b int) ENGINE=InnoDB;
INSERT INTO t3 VALUES (10,7);

SELECT SUM( DISTINCT b ), (
SELECT t2.a
FROM t1 JOIN t2 ON t2.c != 0
WHERE alias1.a != 0 AND t2.a != 0
)
FROM ( SELECT * FROM t3 ) AS alias1
GROUP BY 2;

Seems to require InnoDB. Repeatable in maria-5.3. Not repeatable in maria-5.2,mysql-5.5 or maria-5.3 before WL#106. Does not involve NULLs or constant tables.