[MDEV-3261] LP:826279 - Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed with subquery and derived table Created: 2011-08-14  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug826279.xml    

 Description   

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.

 Comments   
Comment by Igor Babaev [ 2011-08-20 ]

Re: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed with subquery and derived table
The problem can be demonstrated with MyISAM as well with the test case:

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

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

CREATE TABLE t3 (a int, b int);
INSERT INTO t3 VALUES (10,7), (0,7);

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

Comment by Igor Babaev [ 2011-08-20 ]

Re: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed with subquery and derived table
A slightly simpler query

SELECT SUM(DISTINCT b),
(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)
FROM (SELECT * FROM t3) AS t
GROUP BY 2;

also demonstrates this problem:

#4 0x00007fc0ffee99e5 in raise () from /lib64/libc.so.6
#5 0x00007fc0ffeeaee6 in abort () from /lib64/libc.so.6
#6 0x00007fc0ffee2235 in __assert_fail () from /lib64/libc.so.6
#7 0x00000000006932bf in Field_long::val_int (this=0x1d065c0) at field.cc:3635
#8 0x00000000005e47bb in Item_field::val_int (this=0x1d192e8) at item.cc:2280
#9 0x00000000005f0ccd in Item_direct_ref::val_int (this=0x1d0ff78) at item.cc:6851
#10 0x00000000005f0ccd in Item_direct_ref::val_int (this=0x1d0fd70) at item.cc:6851
#11 0x000000000062d37f in Arg_comparator::compare_int_signed (this=0x1c75908) at item_cmpfunc.cc:1161
#12 0x000000000060e825 in Arg_comparator::compare (this=0x1c75908) at item_cmpfunc.h:72
#13 0x000000000062f242 in Item_func_ne::val_int (this=0x1c75848) at item_cmpfunc.cc:1851
#14 0x000000000077fcfd in do_select (join=0x1d10530, fields=0x1c74610, table=0x0, procedure=0x0) at sql_select.cc:14722
#15 0x000000000076235a in JOIN::exec (this=0x1d10530) at sql_select.cc:2679
#16 0x0000000000676920 in subselect_single_select_engine::exec (this=0x1c75f38) at item_subselect.cc:2949
#17 0x000000000066f98d in Item_subselect::exec (this=0x1c75df8) at item_subselect.cc:572
#18 0x0000000000670ee1 in Item_singlerow_subselect::val_int (this=0x1c75df8) at item_subselect.cc:1073
#19 0x00000000005fa5a9 in Item::val_int_result (this=0x1c75df8) at item.h:838
#20 0x0000000000842ad7 in make_sortkey (param=0x7fc0f73fe370, to=0x1d1eaf0 "", ref_pos=0x1d06720 "\230>\325\001")
at filesort.cc:880
#21 0x00000000008420c8 in find_all_keys (param=0x7fc0f73fe370, select=0x1d17a08, sort_keys=0x1d1ea88,
buffpek_pointers=0x7fc0f73fe050, tempfile=0x7fc0f73fe1e0, indexfile=0x0) at filesort.cc:650
#22 0x0000000000840c1b in filesort (thd=0x1bef618, table=0x1d05570, sortorder=0x1d1a330, s_length=1, select=0x1d17a08,
max_rows=18446744073709551615, sort_positions=false, examined_rows=0x7fc0f73fe558) at filesort.cc:240
#23 0x00000000007883c7 in create_sort_index (thd=0x1bef618, join=0x1cff350, order=0x1cfee10,
filesort_limit=18446744073709551615, select_limit=18446744073709551615, is_order_by=true) at sql_select.cc:18063
#24 0x0000000000762038 in JOIN::exec (this=0x1cff350) at sql_select.cc:2628
#25 0x0000000000762bee in mysql_select (thd=0x1bef618, rref_pointer_array=0x1bf21f8, tables=0x1cfe990, wild_num=0, fields=...,
conds=0x0, og_num=1, order=0x0, group=0x1cfee10, having=0x0, proc_param=0x0, select_options=2147764736, result=0x1cfeef8,
unit=0x1bf1ac0, select_lex=0x1bf1fa8) at sql_select.cc:2900
#26 0x0000000000759b63 in handle_select (thd=0x1bef618, lex=0x1bf1a20, result=0x1cfeef8, setup_tables_done_option=0)
at sql_select.cc:283
#27 0x00000000006e9bde in execute_sqlcom_select (thd=0x1bef618, all_tables=0x1cfe990) at sql_parse.cc:5090
#28 0x00000000006e0cc5 in mysql_execute_command (thd=0x1bef618) at sql_parse.cc:2234
#29 0x00000000006ec5bc in mysql_parse (thd=0x1bef618,
rawbuf=0x1c74050 "SELECT SUM(DISTINCT b), \n(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)\nFROM (SELECT * FROM t3) AS t\nGROUP BY 2", length=119, found_semicolon=0x7fc0f73ffc98) at sql_parse.cc:6091
#30 0x00000000006de54b in dispatch_command (command=COM_QUERY, thd=0x1bef618,
packet=0x1c6abe9 "SELECT SUM(DISTINCT b), \n(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1)\nFROM (SELECT * FROM t3) AS t\nGROUP BY 2", packet_length=119) at sql_parse.cc:1211
#31 0x00000000006dd886 in do_command (thd=0x1bef618) at sql_parse.cc:906
#32 0x00000000006da88a in handle_one_connection (arg=0x1bef618) at sql_connect.cc:1186

Comment by Igor Babaev [ 2011-08-20 ]

Re: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed with subquery and derived table
This is a legacy bug. Its cause is the same as for mysql bug #58690 (still not fixed in mysql 5.1/5.5).
I've checked the fix for 5.2 and, as expected, the failure with the test case of the bug #5869O has gone.
It makes sense to apply the fix for mariadb-5.2 as well.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 826279

Generated at Thu Feb 08 06:47:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.