Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() with index_merge+index_merge_sort_union+loosescan+semijoin, IN subquery, InnoDB
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Sergey Petrunia |
Bug Description
Query:
SELECT * FROM t1 WHERE b IN (
SELECT d FROM t2, t1
WHERE a = d AND ( pk < 2 OR d = 'z' )
);
mysqld: opt_range.h:584: virtual void QUICK_INDEX_
120323 3:32:15 [ERROR] mysqld got signal 6 ;
#8 0xb748e014 in __assert_fail () from /lib/libc.so.6
#9 0x0841bb69 in QUICK_INDEX_
#10 0x084261f4 in setup_semijoin_
at opt_subselect.
#11 0x0836856a in make_join_readinfo (join=0x9eebfc8, options=0, no_jbuf_after=3)
at sql_select.cc:9494
#12 0x0835487a in JOIN::optimize (this=0x9eebfc8) at sql_select.cc:1536
#13 0x083591c8 in mysql_select (thd=0x9e687f0, rref_pointer_
wild_num=1, fields=..., conds=0x9ed8380, og_num=0, order=0x0, group=0x0, having=0x0,
proc_param=0x0, select_
select_
#14 0x08350ecb in handle_select (thd=0x9e687f0, lex=0x9e69f14, result=0x9ed8528,
setup_
#15 0x082ebf4d in execute_
#16 0x082e3329 in mysql_execute_
#17 0x082ee588 in mysql_parse (thd=0x9e687f0,
rawbuf=
#18 0x082e0f69 in dispatch_command (command=COM_QUERY, thd=0x9e687f0,
packet=
#19 0x082e0413 in do_command (thd=0x9e687f0) at sql_parse.cc:923
#20 0x082dd399 in handle_
#21 0xb771cb25 in start_thread () from /lib/libpthread
bzr version-info
revision-id: <email address hidden>
date: 2012-03-22 13:23:55 +0100
build-date: 2012-03-23 03:35:16 +0400
revno: 3466
This particular test case passes on MariaDB 5.5 (revno 3345), but I have a bigger one which fails with the same assertion.
Minimal optimizer_switch: index_merge=
Full optimizer_switch (default): index_merge=
EXPLAIN also fails.
Test case:
--source include/
SET optimizer_switch = 'index_
CREATE TABLE t1 (
a INT, b VARCHAR(1), c INT,
KEY(a), KEY(b)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES
(1,'v',
(6,'i',
(11,'z'
(16,'n'
CREATE TABLE t2 (
pk INT, d VARCHAR(1), e INT,
PRIMARY KEY(pk), KEY(d,e)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES
(1,'x',
(6,'u',
(11,'m'
(15,'g'
SELECT * FROM t1 WHERE b IN (
SELECT d FROM t2, t1
WHERE a = d AND ( pk < 2 OR d = 'z' )
);
# End of test case
Changed in maria: | |
status: | In Progress → Fix Committed |
tags: | added: assertion optimizer |
Changed in maria: | |
status: | Fix Committed → Fix Released |
The failure scenario is as follows:
> best_access_ path(idx= 0, table=t2)
> considers ref access
- start considering ref access and LooseScan
- ref access is not possible (cost=DBL_MAX)
- LooseScan (as full index scan) is possible (cost=1.025)
> then consider full scan/range accesses
- find that quick access is possible
- however it's not applicable for loose scan.
- still, quick select is chosen.
< best_access_path
...
> advance_sj_state
(choses the above loose scan over full index scan)
<
The problem is that subsequent code assumes that tab->type==JT_ALL &&
tab->quick!=NULL => use quick select for retrieval.
And eventually we crash with an assert because QUICK_SORT_ INTERSECT_ SELECT is
unable to produce records in order (that's true).