[MDEV-174] LIMIT ROWS EXAMINED: Assertion `0' failed in net_end_statement(THD*) with subquery in SELECT, constant table, aggregate function Created: 2012-03-08  Updated: 2012-03-09  Resolved: 2012-03-09

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 5.5.21

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-28 LIMIT ROWS EXAMINED clause to limit t... Closed

 Description   

mysqld: protocol.cc:509: void net_end_statement(THD*): Assertion `0' failed
 
#8  0xb7479014 in __assert_fail () from /lib/libc.so.6
#9  0x082c5479 in net_end_statement (thd=0x93f0c18) at protocol.cc:509
#10 0x082e2464 in dispatch_command (command=COM_QUERY, thd=0x93f0c18, 
    packet=0x944a589 "SELECT    \n( SELECT MAX(c) FROM t1, t2 ) \nFROM t2 \nWHERE c = ( SELECT MAX(b) FROM t2 ) \nLIMIT ROWS_EXAMINED 3", packet_length=109) at sql_parse.cc:1620
#11 0x082e08bf in do_command (thd=0x93f0c18) at sql_parse.cc:923
#12 0x082dd845 in handle_one_connection (arg=0x93f0c18) at sql_connect.cc:1193
#13 0xb7707b25 in start_thread () from /lib/libpthread.so.0
 
Query (94607a8): SELECT     ( SELECT MAX(c) FROM t1, t2 )  FROM t2  WHERE c = ( SELECT MAX(b) FROM t2 )  LIMIT ROWS_EXAMINED 3
Connection ID (thread ID): 1
Status: NOT_KILLED

bzr version-info
revision-id: timour@askmonty.org-20120305200233-lyj8vveiuwkm1nq2
date: 2012-03-05 22:02:33 +0200
build-date: 2012-03-08 04:21:36 +0400
revno: 3453

EXPLAIN:

id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered Extra
1       PRIMARY t2      ref     c       c       5       const   0       0.00    Using where; Using index
3       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    6       100.00
2       SUBQUERY        t1      system  NULL    NULL    NULL    NULL    0       0.00    const row not found
2       SUBQUERY        t2      index   NULL    c       5       NULL    6       100.00  Using index
Warnings:
Note    1003    select (select max(`test`.`t2`.`c`) from `test`.`t1` join `test`.`t2`) AS `( SELECT MAX(c) FROM t1, t2 )` from `test`.`t2` where (`test`.`t2`.`c` = (select max(`test`.`t2`.`b`) from `test`.`t2`))

Minimal optimizer_switch:

in_to_exists=on

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=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';
 
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT, c INT, KEY(c) );
INSERT INTO t2 VALUES 
  (5, 0),(3, 4),(6, 1),
  (5, 8),(4, 9),(8, 1);
 
SELECT
  ( SELECT MAX(c) FROM t1, t2 )
FROM t2 
WHERE c = ( SELECT MAX(b) FROM t2 )
LIMIT ROWS_EXAMINED 3;



 Comments   
Comment by Timour Katchaounov (Inactive) [ 2012-03-09 ]

Problem:
The failed assert was a result of subquery execution during optimization of the outer query.
In such cases, query processing can be interrupted before we even get to the execution phase.
EOF is not sent, and hence the failed assert.

Solution:
Make sure that LIMIT ROWS is enabled only when the outer-most query begins execution.

Generated at Thu Feb 08 06:26:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.