Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed with subquery and derived table
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
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:
#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_
#12 0x081d8f70 in Item_direct_
#13 0x0820c238 in Arg_comparator:
#14 0x081f16a2 in Arg_comparator:
#15 0x0820dc82 in Item_func_
#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_
#19 0x08245333 in Item_subselect:
#20 0x082465dc in Item_singlerow_
#21 0x081e12c8 in Item::val_
#22 0x083e8c1c in make_sortkey (param=0xa1e70f04,
to=0x9f83270 "\217\217\
ref_
#23 0x083e833f in find_all_keys (param=0xa1e70f04, select=0x9fb7fc0, sort_keys=
at filesort.cc:650
#24 0x083e6f4b in filesort (thd=0x9ef31e8, table=0x9f71918, sortorder=
sort_
#25 0x08342442 in create_sort_index (thd=0x9ef31e8, join=0x9f77d98, order=0x9f63b90, filesort_
is_
#26 0x0831f19e in JOIN::exec (this=0x9f77d98) at sql_select.cc:2613
#27 0x0831fc7e in mysql_select (thd=0x9ef31e8, rref_pointer_
group=
#28 0x08317b13 in handle_select (thd=0x9ef31e8, lex=0x9ef48f0, result=0x9f63bd0, setup_tables_
#29 0x082b2efa in execute_
#30 0x082a9cfe in mysql_execute_
#31 0x082b5536 in mysql_parse (thd=0x9ef31e8,
rawbuf=
#32 0x082a797b in dispatch_command (command=COM_QUERY, thd=0x9ef31e8,
packet=
#33 0x082a6e01 in do_command (thd=0x9ef31e8) at sql_parse.cc:906
#34 0x082a3e68 in handle_
#35 0x00821919 in start_thread () from /lib/libpthread
#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=
test case:
--source include/
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.
Changed in maria: | |
milestone: | none → 5.3 |
Changed in maria: | |
status: | New → Confirmed |
Changed in maria: | |
assignee: | nobody → Igor Babaev (igorb-seattle) |
status: | Confirmed → In Progress |
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
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;