#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.
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';
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.
Timour Katchaounov (Inactive)
added a comment - 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.
A simpler test case:
SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT_ROWS_EXAMINED 2;