MySQL Bugs: #58163: IN-subquery cause MySQL Cluster to enter infinite loop

Bug #58163	IN-subquery cause MySQL Cluster to enter infinite loop
Submitted:	12 Nov 2010 13:33	Modified:	25 Nov 2010 22:13
Reporter:	Ole John Aske	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	mysql-5.1-telco-7.1	OS:	Any
Assigned to:	Ole John Aske	CPU Architecture:	Any
Tags:	5.1.51-ndb-7.1.9

Description:
Some subqueries may cause mysqld to enter an infinite loop when the storage engine is NDB.

Error returns from ::index:next(), ::index_prev(), ::index_first(), ::Index_last() fails to set 'table->status= STATUS_NOT_FOUND' in case of HA_ERR_END_OF_FILE is returned from the underlying utility methods.

This leads to that subselect_indexsubquery_engine::exec() fails to terminate its ::index_next_same() loop where it search for IN-matches as the termination criteria is 'table->status!=0'

BTW: Its questionable if the mysqld behaviour of requiring 'table->status!=0' to terminate the loop is correct. One could argue that returning 'error= HA_ERR_END_OF_FILE' should be sufficient to conclude that there was no more rows available from the table.

However, this is how 'end of file' seems to be returned from the other MySQL handler (Has checked MyIsam, heap and memory) and its is probably simplest to only adjust Cluster to have the same rerurn values.

How to repeat:
create table t (k int, uq int, unique key ix1 (uq)) engine = ndb;
insert into t values (1,3), (3,6), (6,9), (9,1);

select * from t where 
   k in (select uq from t as subq where subq.k>10);

-> mysqld peek at 100% CPU

I have a fix for this which I may commit after the weekend - Need a reviewer to be assigned though.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123842

3982 Ole John Aske	2010-11-15
      Fix for bug#58163 - for review.
      
      All 'first', 'last', 'next' 'prev' navigators should set 'table->status = STATUS_NOT_FOUND' if there is
      returned an errorcode.
      
      This pattern is more or less a verbatime copy of how the same situation is handled in most
      (all?) of the other storage engines (Checked MyISAM, heap and memory...)
      
      I think the same fix should be applied for 6.3 -> ...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123845

3363 Ole John Aske	2010-11-15
      spj-scan-scan: cherry picked proposed fix for bug#58163 into SPJ branch.
      
      Required as this was a showstopper for further RQG subquery testing

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.40 (revid:jonas@mysql.com-20101124163758-ylwcnqce1piv1rut) (version source revid:jonas@mysql.com-20101124130504-a1bv2y7oxrbm3c6o) (merge vers: 5.1.51-ndb-6.3.40) (pib:23)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124866

3345 Jonas Oreland	2010-11-24
      ndb - bug#58163 - failure(?) to set table->status to STATUS_NOT_FOUND leads to infinite loop with certain subqueries

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:jonas@mysql.com-20101124174527-2n60gu2e0wx7an60) (version source revid:jonas@mysql.com-20101124174527-2n60gu2e0wx7an60) (merge vers: 5.1.51-ndb-7.0.21) (pib:23)

pushed to 6.3.40, 7.0.21 and 7.1.10

Documented bugfix in the NDB-6.3.40, 7.0.21, and 7.1.10 changelogs as follows:

        Some queries of the form SELECT ... WHERE column IN (subquery)
        against an NDB table could cause mysqld to hang in an endless
        loop.

Closed.