MySQL Bugs: #40535: NDBAPI : ScanFilter does not support BIT columns

Bug #40535	NDBAPI : ScanFilter does not support BIT columns
Submitted:	5 Nov 2008 18:57	Modified:	12 Dec 2008 17:43
Reporter:	Frazer Clement	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: NDB API	Severity:	S4 (Feature request)
Version:	5.1-telco-6.2+	OS:	Any
Assigned to:	Frazer Clement	CPU Architecture:	Any

Description:
The NDBAPI ScanFilter allows the normal conditions to be specified against a BIT column, but does not return the correct rows when executed.

How to repeat:
1) Create a table with a multi-bit BIT column
2) Populate with data
3) Define a scan + ScanFilter against the column, retrieving only specific values
4) Execute
5) Check that returned rows match the specified condition.

Suggested fix:
Properly support Scanfilters with Bit columns

Proposed patch

Attachment: bug40535.patch (text/x-patch), 56.69 KiB.

Patch against 6.3 proposed

This patch fixes bug#40535, and adds some new filter conditions so that ScanFilters can check bitfields for set membership etc.

e.g. 
COND_AND_EQ_MASK(bitmask) - true if all bits set in mask are set in bit column
COND_AND_EQ_ZERO(bitmask) - true if no bits set in mask are set in bit column

These types can be used to return rows where none, some, or all of the bits in a bitmask are set in a bit column.  This is useful where a bitfield encodes a set of flags etc.

The other conditions (EQ, GE, GT, LE, LT, NE) also work with bitfield columns. 

As with all ScanFilter conditions, they can be arbitrarily combined with AND, OR, NOT etc.

1) make sure to monitor that tests actually work in autotest.
2) check the code (i didnt) so that using a new ndbapi generating instructions
  not supported by ndbd does not cause crash, but generates relevant error

approved to 6.3 with conditions above

ok, let skip it...
but isnt there any "illegal intruction"-error ?
we should add one

but maybe not for this

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

2786 Frazer Clement	2008-12-09
      Bug# 40535 : NDBAPI : ScanFilter does not support BIT columns
      
      This bug is fixed to provide ScanFilter support for EQ, NE LT, LE, GT, GE 
      on bit columns.
      
      Additionally, 4 new comparison operations are defined for bit types :
       - COL_AND_MASK_EQ_MASK
       - COL_AND_MASK_NE_MASK
       - COL_AND_MASK_EQ_ZERO
       - COL_AND_MASK_NE_ZERO
      
      These can be used to test bits within a bitfield as part of a ScanFiler.
      
      A test program for all of the conditions and bit columns is added

Pushed into 5.1.30-ndb-6.4.0  (revid:frazer@mysql.com-20081209154714-6cq5p9pig50yife1) (version source revid:frazer@mysql.com-20081209170223-20vduecg5ygpq2cq) (pib:5)

Pushed into 5.1.30-ndb-6.3.20  (revid:frazer@mysql.com-20081209154714-6cq5p9pig50yife1) (version source revid:frazer@mysql.com-20081209154714-6cq5p9pig50yife1) (pib:5)

Documented bugfix in the ndb-6.3.20 changelog as follows:

        When creating a scan using an NdbScanFilter
        object, it was possible to specify conditions against a
        BIT column, but the correct rows were not
        returned when the scan was executed.
        
        As part of this fix, 4 new comparison operators have been
        implemented for use with scans on BIT columns:
          
            COL_AND_MASK_EQ_MASK
            
            COL_AND_MASK_NE_MASK
            
            COL_AND_MASK_EQ_ZERO
            
            COL_AND_MASK_NE_ZERO
                  
        For more information about these operators, see
        "The NdbScanFilter::BinaryCondition Type".

Also documented the new operators in the section indicated by the changelog entry.