Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
mysqld: protocol.cc:509: void net_end_statement(THD*): Assertion `0' failed.
120216 1:46:27 [ERROR] mysqld got signal 6 ;
#8 0xb74a3014 in __assert_fail () from /lib/libc.so.6
#9 0x082c50ed in net_end_statement (thd=0x93efc00) at protocol.cc:509
#10 0x082e25cc in dispatch_command (command=COM_QUERY, thd=0x93efc00,
packet=0x9449561 "SELECT DISTINCT a AS field1 FROM t1, t2 \nWHERE EXISTS ( \nSELECT c FROM t3 RIGHT JOIN t2 ON b = d \n) \nHAVING field1 > 8 \nLIMIT_ROWS_EXAMINED 20", packet_length=142)
at sql_parse.cc:1620
#11 0x082e0a27 in do_command (thd=0x93efc00) at sql_parse.cc:923
#12 0x082dd9ad in handle_one_connection (arg=0x93efc00) at sql_connect.cc:1193
Query (0x945f7c8): SELECT DISTINCT a AS field1 FROM t1, t2 WHERE EXISTS ( SELECT c FROM t3 LEFT JOIN t2 ON b = d ) HAVING field1 > 8 LIMIT_ROWS_EXAMINED 20
Connection ID (thread ID): 1
Status: NOT_KILLED
Could not reproduce on the same query without LIMIT_ROWS_EXAMINED.
bzr version-info
revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r
date: 2012-02-13 16:52:48 +0200
build-date: 2012-02-16 01:48:07 +0400
revno: 3417
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 Using temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Distinct 2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select distinct 'USA' AS `field1` from `test`.`t1` join `test`.`t2` where exists(select `test`.`t3`.`c` from `test`.`t3` left join `test`.`t2` on((`test`.`t2`.`b` = `test`.`t3`.`d`)) where 1) having (`field1` > 8)
Minimal optimizer_switch: in_to_exists=on,outer_join_with_cache=on
Full optimizer_switch (default): 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=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
Test case:
SET optimizer_switch='in_to_exists=on,outer_join_with_cache=on';
CREATE TABLE t1 ( a VARCHAR(3) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('USA');
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES
(3899),(3914),(3888);
CREATE TABLE t3 ( c VARCHAR(33), d INT );
INSERT INTO t3 VALUES
('USASpanish',8),('USATagalog',0),('USAVietnamese',0);
SELECT DISTINCT a AS field1 FROM t1, t2
WHERE EXISTS (
SELECT c FROM t3 LEFT JOIN t2 ON b = d
)
HAVING field1 > 8
LIMIT_ROWS_EXAMINED 20;
# End of test case
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-28 LIMIT ROWS EXAMINED clause to limit the number of rows examined during SELECT processing
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The cause for the assert is that the query plan for the query above calls JOIN::exec->remove_duplicates.
When remove_duplicates calls handler methods it may exceed LIMIT_ROWS_EXAMINED, however in
the case of THD::killed, this function returns an error. This results in returning from JOIN::exec immediately
without setting the status of THD::main_da to Diagnostics_area::DA_EOF. This causes the assert.
The solution is to disable LIMIT_ROWS_EXAMINED within remove_duplicates. The reasoning is that
this function is reasonably cheap, and it is called after most of the query execution was done. Therefore
it makes sense to complete this final step, and produce a possibly incomplete query result.
The test case is added to MDEV-28.
A simpler test case:
SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT_ROWS_EXAMINED 2;