MariaDB

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

Bug #826279 reported by Philip Stoev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
High
Igor Babaev
MariaDB 5.3

Bug 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.

Philip Stoev (pstoev-askmonty)
Changed in maria:
milestone: none → 5.3
Igor Babaev (igorb-seattle)
Changed in maria:
status: New → Confirmed
Igor Babaev (igorb-seattle)
Changed in maria:
assignee: nobody → Igor Babaev (igorb-seattle)
status: Confirmed → In Progress
Igor Babaev (igorb-seattle)
Changed in maria:
importance: Undecided → High
Revision history for this message
Igor Babaev (igorb-seattle) wrote :

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;

Revision history for this message
Igor Babaev (igorb-seattle) wrote :
Download full text (3.7 KiB)

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 LI...

Read more...

Revision history for this message
Igor Babaev (igorb-seattle) wrote :

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.

Igor Babaev (igorb-seattle)
Changed in maria:
status: In Progress → Fix Committed
Daniel Bartholomew (dbart)
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.