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

[12 Nov 2010 13:33] Ole John Aske
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
[12 Nov 2010 13:34] Ole John Aske
I have a fix for this which I may commit after the weekend - Need a reviewer to be assigned though.
[15 Nov 2010 9:10] Bugs System
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 -> ...
[15 Nov 2010 9:21] Bugs System
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
[24 Nov 2010 16:39] Bugs System
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)
[24 Nov 2010 17:16] Bugs System
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
[24 Nov 2010 17:50] Bugs System
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)
[24 Nov 2010 17:54] Jonas Oreland
pushed to 6.3.40, 7.0.21 and 7.1.10
[25 Nov 2010 22:13] Jon Stephens
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.