MySQL Bugs: #42857: Got error 4541 'IndexBound has no bound information' from NDBCLUSTER

Bug #42857	Got error 4541 'IndexBound has no bound information' from NDBCLUSTER
Submitted:	14 Feb 2009 10:22	Modified:	17 Mar 2009 19:34
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3,5.1.30-ndb-6.3.20-cluster-gpl, 5.1.31-ndb-6.3.22	OS:	Linux
Assigned to:	Frazer Clement	CPU Architecture:	Any
Tags:	regression

Description:
mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.75 sec)

mysql> create table `t1`(`c1` varchar(20) primary key,`c2` char(20))engine=ndbcluster;
Query OK, 0 rows affected (1.06 sec)

mysql> insert into `t1`(`c1`,`c2`) values ('ddd','jg');
Query OK, 1 row affected (0.02 sec)

mysql> select * from `t1` where  (c2 < 'b' AND c1 <> 'g')  OR  (c2 <> 'a' AND c1 <> 'd') ;
ERROR 1296 (HY000): Got error 4541 'IndexBound has no bound information' from NDBCLUSTER
mysql>

ignoring the index makes the problem go away

How to repeat:
drop table if exists `t1`;
create table `t1`(`c1` varchar(20) primary key,`c2` char(20))engine=ndbcluster;
insert into `t1`(`c1`,`c2`) values ('ddd','jg');
select * from `t1` where  (c2 < 'b' AND c1 <> 'g')  OR  (c2 <> 'a' AND c1 <> 'd') ;

Thank you for the report.

Verified as described. Version 6.2.15 is not affected.

Bug can be reproduced in 5.1.32-ndb-6.2.17.

Problem is that :
1) ha_ndbcluster attempts to create MRR index scan with at least one range having no bounds
2) NdbApi (and kernel) does not currently support MRR scan with some ranges having 'no bounds' (i.e. all rows in index should be returned).

Analysis :
1) It can be argued that MRR is only useful for reducing data transfer while the set of all rows returned is smaller than the set of all rows. If any of the ranges in an MRR scan includes all rows then there is no point to using MRR, unless the scan will be stopped before completion in some case. This suggests that in cases where one of the ranges has no bounds, a different strategy should be taken where a single scan is performed, and the data for all of the ranges is obtained from this single scan. Not sure how much work in the handler this entails, and whether it would also require changes at a higher layer.

2) a) NdbApi currently checks that any call to setBounds() supplies some bounding information.
b) LQH currently splits MRR ranges according to the MRR length information, passing individual ranges to TUX. Currently it assumes that all ranges have at least 1 word of range type information (EQ, LT, LE, GT, GE).
c) TUX currently assumes that all bounds (per column) have at least 2 words - the bound type and the AttrHeader word.
d) When no bounds are given for a non MRR scan, the scan is simply started without them.
e) NULL is regarded as less than any other value in a column and equal to NULL.

If we choose to change 2) so that we can have ranges in an MRR scan with no real bounds then there are at least 2 approaches :
i) Modify NdbApi, Lqh, Tux to handle MRR with 'empty' ranges
NdbApi allows it, Lqh passes through 1 word bound, TUX treats it as 'no bounds' or 'GE NULL' on first column.
ii) Modify NdbApi to map 'empty' ranges onto ranges with a lower bound GE NULL for the first column in the index.
iii) Modify ha_ndbcluster to map 'empty' ranges onto ranges with a lower bound GE NULL for the first column in the index.

How it works in 6.2.15 :
  - 6.2.15 had the original NdbRecord mechanism to pass all bounds in one go via a callback + data ptr.
  - In this case, only 1 bound is passed
  - ha_ndbcluster code determines that both keys have length 0 (i.e. it's an 'empty' bound
  - NdbScanOperation code handles this differently and does not insert any KeyInfo for the bound
  - Kernel treats this like a scan with no bounds.
  - Expected data is returned to the caller

Comment
  - In later releases, the Scan code refuses the 'empty' bound.
  - Scan code can accept an 'empty bound' in this way only when the scan has only one range.  If it had more ranges, any empty bound scans would not occur as no bounds would exist to describe the scan to the LQH MRR code.
  - If it's possible to generate an MRR scan with an empty bound and another bound in 6.2.15, a bug would be exposed there too.

So it seems that the old code was more permissive, but not correct.
I think the options to fixing remain the same.

Proposed patch

Attachment: bug42857.patch (text/x-patch), 7.39 KiB.

Proposed patch takes approach 2 ii) to fix the bug
  ii) Modify NdbApi to map 'empty' ranges onto ranges with a lower bound GE NULL for the first column in the index.

This is done in preference to 2 iii) (Modify ha_ndbcluster) as NDBAPI users cannot otherwise express GE NULL for a first-indexed-column that is not nullable.

The original testcase is added to ndb_index_ordered.test to verify that the expected row is returned.

Note patch is against telco-6.2

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/69337

2877 Frazer Clement	2009-03-16
      Bug#42857 Got error 4541 Indexbound has no bound information from NDBCLUSTER
      
      MySQLD calls NdbIndexScanOperation::setBound() with an empty ('open range')
      IndexBound with the query given.
      
      setBound() is modified to handle this by inserting an 'open range' bound
      which is actually a lower bound of LE NULL on the first column in the index.
      
      The example testcase is added to ndb_index_ordered.
      modified:
        mysql-test/suite/ndb/r/ndb_index_ordered.result
        mysql-test/suite/ndb/t/ndb_index_ordered.test
        storage/ndb/include/ndbapi/NdbIndexScanOperation.hpp
        storage/ndb/src/ndbapi/NdbScanOperation.cpp
        storage/ndb/src/ndbapi/ndberror.c

Pushed into 5.1.32-ndb-7.0.4 (revid:frazer@mysql.com-20090316193858-okb08evkn96s4ygz) (version source revid:frazer@mysql.com-20090316193858-okb08evkn96s4ygz) (merge vers: 5.1.32-ndb-7.0.4) (pib:6)

Pushed into 5.1.32-ndb-6.3.24 (revid:frazer@mysql.com-20090316182601-8tlkznoeh1u2d6yv) (version source revid:frazer@mysql.com-20090316182601-8tlkznoeh1u2d6yv) (merge vers: 5.1.32-ndb-6.3.24) (pib:6)

Pushed into 5.1.32-ndb-6.2.18 (revid:frazer@mysql.com-20090316180848-jndxy1uxs2zjayf9) (version source revid:frazer@mysql.com-20090316180848-jndxy1uxs2zjayf9) (merge vers: 5.1.32-ndb-6.2.18) (pib:6)

Documented bugfix in the NDB-6.2.18, 6.3.24, and 7.0.4 changelogs as follows:

        Some queries using combinations of logical and comparison
        operators on an indexed column in the WHERE clause could fail
        with the error Got error 4541 'IndexBound has no bound
        information' from NDBCLUSTER.