bk commit into 5.0 tree (mskold:1.2282) BUG#21507

[Martin Skold]

Below is the list of changes that have just been committed into a local
5.0 repository of marty. When marty does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-11-03 10:53:44+01:00, mskold@stripped +4 -0
  bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash
only indexes with NULL valued attributes, but any NULL valued access will become full
table scan with pushed condition on index attribute values

  mysql-test/r/ndb_index_unique.result@stripped, 2006-11-03 10:53:18+01:00, mskold@stripped
+33 -1
    bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash
only indexes with NULL valued attributes, but any NULL valued access will become full
table scan with pushed condition on index attribute values

  mysql-test/t/ndb_index_unique.test@stripped, 2006-11-03 10:53:18+01:00, mskold@stripped +14
-1
    bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash
only indexes with NULL valued attributes, but any NULL valued access will become full
table scan with pushed condition on index attribute values

  sql/ha_ndbcluster.cc@stripped, 2006-11-03 10:53:18+01:00, mskold@stripped +192 -51
    bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash
only indexes with NULL valued attributes, but any NULL valued access will become full
table scan with pushed condition on index attribute values

  sql/ha_ndbcluster.h@stripped, 2006-11-03 10:53:18+01:00, mskold@stripped +13 -1
    bug#21507 I can't create a unique hash index in NDB: Added possibillity to create hash
only indexes with NULL valued attributes, but any NULL valued access will become full
table scan with pushed condition on index attribute values

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	mskold
# Host:	linux.site
# Root:	/windows/Linux_space/MySQL/mysql-5.0-ndb

--- 1.19/mysql-test/r/ndb_index_unique.result	2006-11-03 10:53:55 +01:00
+++ 1.20/mysql-test/r/ndb_index_unique.result	2006-11-03 10:53:55 +01:00
@@ -140,7 +140,39 @@ b int unsigned not null,
 c int unsigned,
 UNIQUE USING HASH (b, c)	
 ) engine=ndbcluster;
-ERROR 42000: Column 'c' is used with UNIQUE or INDEX but is not defined as NOT NULL
+Warnings:
+Warning	1121	Ndb does not support unique index on NULL valued attribute c, index access
with NULL value will become full table scan
+insert t2
values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL);
+select * from t2 where c IS NULL order by a;
+a	b	c
+1	1	NULL
+3	3	NULL
+5	5	NULL
+7	7	NULL
+8	3	NULL
+9	3	NULL
+select * from t2 where b = 3 AND c IS NULL order by a;
+a	b	c
+3	3	NULL
+8	3	NULL
+9	3	NULL
+select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a;
+a	b	c
+3	3	NULL
+8	3	NULL
+9	3	NULL
+set @old_ecpd = @@session.engine_condition_pushdown;
+set engine_condition_pushdown = true;
+explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY,b	PRIMARY	4	NULL	1	Using where with pushed condition
+select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
+a	b	c
+3	3	NULL
+5	5	NULL
+8	3	NULL
+set engine_condition_pushdown = @old_ecpd;
+drop table t2;
 CREATE TABLE t3 (
 a int unsigned NOT NULL,
 b int unsigned not null,

--- 1.17/mysql-test/t/ndb_index_unique.test	2006-11-03 10:53:55 +01:00
+++ 1.18/mysql-test/t/ndb_index_unique.test	2006-11-03 10:53:55 +01:00
@@ -85,13 +85,26 @@ select * from t2 order by a;
 
 drop table t2;
 
--- error 1121
 CREATE TABLE t2 (
   a int unsigned NOT NULL PRIMARY KEY,
   b int unsigned not null,
   c int unsigned,
   UNIQUE USING HASH (b, c)	
 ) engine=ndbcluster;
+
+
+insert t2
values(1,1,NULL),(2,2,2),(3,3,NULL),(4,4,4),(5,5,NULL),(6,6,6),(7,7,NULL),(8,3,NULL),(9,3,NULL);
+
+select * from t2 where c IS NULL order by a;
+select * from t2 where b = 3 AND c IS NULL order by a;
+select * from t2 where (b = 3 OR b = 5) AND c IS NULL order by a;
+set @old_ecpd = @@session.engine_condition_pushdown;
+set engine_condition_pushdown = true;
+explain select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
+select * from t2 where (b = 3 OR b = 5) AND c IS NULL AND a < 9 order by a;
+set engine_condition_pushdown = @old_ecpd;
+
+drop table t2;
 
 #
 # Show use of PRIMARY KEY USING HASH indexes 

--- 1.285/sql/ha_ndbcluster.cc	2006-11-03 10:53:55 +01:00
+++ 1.286/sql/ha_ndbcluster.cc	2006-11-03 10:53:55 +01:00
@@ -1096,8 +1096,8 @@ int ha_ndbcluster::build_index_list(Ndb 
           error= create_unique_index(unique_index_name, key_info);
         break;
       case UNIQUE_INDEX:
-        if (!(error= check_index_fields_not_null(i)))
-          error= create_unique_index(unique_index_name, key_info);
+	check_index_fields_not_null(i);
+	error= create_unique_index(unique_index_name, key_info);
         break;
       case ORDERED_INDEX:
         error= create_ordered_index(index_name, key_info);
@@ -1150,7 +1150,7 @@ NDB_INDEX_TYPE ha_ndbcluster::get_index_
           ORDERED_INDEX);
 } 
 
-int ha_ndbcluster::check_index_fields_not_null(uint inx)
+void ha_ndbcluster::check_index_fields_not_null(uint inx)
 {
   KEY* key_info= table->key_info + inx;
   KEY_PART_INFO* key_part= key_info->key_part;
@@ -1162,13 +1162,15 @@ int ha_ndbcluster::check_index_fields_no
       Field* field= key_part->field;
       if (field->maybe_null())
       {
-        my_printf_error(ER_NULL_COLUMN_IN_INDEX,ER(ER_NULL_COLUMN_IN_INDEX),
-                        MYF(0),field->field_name);
-        DBUG_RETURN(ER_NULL_COLUMN_IN_INDEX);
+	push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                            ER_NULL_COLUMN_IN_INDEX,
+                            "Ndb does not support unique index on NULL valued attribute
%s, index access with NULL value will become full table scan",
+                            field->field_name);
+        DBUG_VOID_RETURN;
       }
     }
   
-  DBUG_RETURN(0);
+  DBUG_VOID_RETURN;
 }
 
 void ha_ndbcluster::release_metadata()
@@ -2090,6 +2092,42 @@ int ha_ndbcluster::ordered_index_scan(co
 }
 
 /*
+  Unique index scan in NDB (full table scan with scan filter)
+ */
+
+int ha_ndbcluster::unique_index_scan(const KEY* key_info, 
+				     const byte *key, 
+				     uint key_len,
+				     byte *buf)
+{
+  int res;
+  NdbScanOperation *op;
+  NdbTransaction *trans= m_active_trans;
+
+  DBUG_ENTER("unique_index_scan");  
+  DBUG_PRINT("enter", ("Starting new scan on %s", m_tabname));
+
+  NdbOperation::LockMode lm=
+    (NdbOperation::LockMode)get_ndb_lock_type(m_lock.type);
+  bool need_pk = (lm == NdbOperation::LM_Read);
+  if (!(op=trans->getNdbScanOperation((const NDBTAB *) m_table)) ||
+      op->readTuples(lm, 
+		     (need_pk)?NdbScanOperation::SF_KeyInfo:0, 
+		     parallelism))
+    ERR_RETURN(trans->getNdbError());
+  m_active_cursor= op;
+  if (generate_scan_filter_from_key(op, key_info, key, key_len, buf))
+    DBUG_RETURN(ndb_err(trans));
+  if ((res= define_read_attrs(buf, op)))
+    DBUG_RETURN(res);
+
+  if (execute_no_commit(this,trans,false) != 0)
+    DBUG_RETURN(ndb_err(trans));
+  DBUG_PRINT("exit", ("Scan started successfully"));
+  DBUG_RETURN(next_result(buf));
+}
+
+/*
   Start full table scan in NDB
  */
 
@@ -2763,7 +2801,7 @@ int ha_ndbcluster::index_read(byte *buf,
     }
     else if (type == UNIQUE_INDEX)
     {
-      DBUG_RETURN(1);
+      DBUG_RETURN(unique_index_scan(key_info, key, key_len, buf));
     }
     break;
   case ORDERED_INDEX:
@@ -6181,7 +6219,8 @@ ha_ndbcluster::read_multi_range_first(KE
   const NDBINDEX *unique_idx= (NDBINDEX *) m_index[active_index].unique_index;
   const NDBINDEX *idx= (NDBINDEX *) m_index[active_index].index; 
   const NdbOperation* lastOp= m_active_trans->getLastDefinedOperation();
-  NdbIndexScanOperation* scanOp= 0;
+  NdbScanOperation* tableScanOp= 0;
+  NdbIndexScanOperation* indexScanOp= 0;
   for (; multi_range_curr<multi_range_end && curr+reclength <=
end_of_buffer; 
        multi_range_curr++)
   {
@@ -6214,53 +6253,92 @@ ha_ndbcluster::read_multi_range_first(KE
       /* fall through */
     case UNIQUE_INDEX:
     {
-      multi_range_curr->range_flag |= UNIQUE_RANGE;
-      if ((op= m_active_trans->getNdbIndexOperation(unique_idx, tab)) && 
-          !op->readTuple(lm) && 
-          !set_index_key(op, key_info, multi_range_curr->start_key.key) &&
-          !define_read_attrs(curr, op) &&
-          (op->setAbortOption(AO_IgnoreError), TRUE))
-        curr += reclength;
+      const byte *key= multi_range_curr->start_key.key;
+      uint key_len= multi_range_curr->start_key.length;
+
+      if (index_type == UNIQUE_INDEX &&
+	  check_null_in_key(key_info, key, key_len))
+      {
+	DBUG_PRINT("info", ("Found NULL in unique key"));
+	multi_range_curr->range_flag &= ~(uint)UNIQUE_RANGE;
+	if (tableScanOp == 0)
+	{
+	  if (m_multi_cursor)
+	  {
+	    tableScanOp= m_multi_cursor;
+	    DBUG_ASSERT(tableScanOp->getLockMode() == 
+			(NdbOperation::LockMode)get_ndb_lock_type(m_lock.type));
+	    end_of_buffer -= reclength;
+	  }
+	  else if ((tableScanOp= m_active_trans->getNdbScanOperation(tab)) 
+		   &&!tableScanOp->readTuples(lm, need_pk, parallelism)
+		   &&!generate_scan_filter_from_key(tableScanOp,
+						    key_info,
+						    key,
+						    key_len,
+						    curr)
+		   &&!define_read_attrs(end_of_buffer-reclength, tableScanOp))
+	  {
+	    m_multi_cursor= (NdbIndexScanOperation *) tableScanOp;
+	    m_multi_range_cursor_result_ptr= end_of_buffer-reclength;
+	  }
+	  else
+	  {
+	    ERR_RETURN(tableScanOp ? tableScanOp->getNdbError() : 
+		       m_active_trans->getNdbError());
+	  }
+	}
+      }
       else
-        ERR_RETURN(op ? op->getNdbError() : m_active_trans->getNdbError());
+      {
+	multi_range_curr->range_flag |= UNIQUE_RANGE;
+	if ((op= m_active_trans->getNdbIndexOperation(unique_idx, tab)) && 
+	    !op->readTuple(lm) && 
+	    !set_index_key(op, key_info, key) &&
+	    !define_read_attrs(curr, op) &&
+	    (op->setAbortOption(AO_IgnoreError), TRUE))
+	  curr += reclength;
+	else
+	  ERR_RETURN(op ? op->getNdbError() : m_active_trans->getNdbError());
+      }
       break;
     }
     case ORDERED_INDEX:
     {
   range:
       multi_range_curr->range_flag &= ~(uint)UNIQUE_RANGE;
-      if (scanOp == 0)
+      if (indexScanOp == 0)
       {
         if (m_multi_cursor)
         {
-          scanOp= m_multi_cursor;
-          DBUG_ASSERT(scanOp->getSorted() == sorted);
-          DBUG_ASSERT(scanOp->getLockMode() == 
+          indexScanOp= m_multi_cursor;
+          DBUG_ASSERT(indexScanOp->getSorted() == sorted);
+          DBUG_ASSERT(indexScanOp->getLockMode() == 
                       (NdbOperation::LockMode)get_ndb_lock_type(m_lock.type));
-          if (scanOp->reset_bounds(m_force_send))
+          if (indexScanOp->reset_bounds(m_force_send))
             DBUG_RETURN(ndb_err(m_active_trans));
           
           end_of_buffer -= reclength;
         }
-        else if ((scanOp= m_active_trans->getNdbIndexScanOperation(idx, tab)) 
-                 &&!scanOp->readTuples(lm, 0, parallelism, sorted, 
+        else if ((indexScanOp= m_active_trans->getNdbIndexScanOperation(idx, tab)) 
+                 &&!indexScanOp->readTuples(lm, 0, parallelism, sorted, 
 				       FALSE, TRUE, need_pk)
-                 &&!generate_scan_filter(m_cond_stack, scanOp)
-                 &&!define_read_attrs(end_of_buffer-reclength, scanOp))
+                 &&!generate_scan_filter(m_cond_stack, indexScanOp)
+                 &&!define_read_attrs(end_of_buffer-reclength, indexScanOp))
         {
-          m_multi_cursor= scanOp;
+          m_multi_cursor= indexScanOp;
           m_multi_range_cursor_result_ptr= end_of_buffer-reclength;
         }
         else
         {
-          ERR_RETURN(scanOp ? scanOp->getNdbError() : 
+          ERR_RETURN(indexScanOp ? indexScanOp->getNdbError() : 
                      m_active_trans->getNdbError());
         }
       }
 
       const key_range *keys[2]= { &multi_range_curr->start_key, 
                                   &multi_range_curr->end_key };
-      if ((res= set_bounds(scanOp, keys, multi_range_curr-ranges)))
+      if ((res= set_bounds(indexScanOp, keys, multi_range_curr-ranges)))
         DBUG_RETURN(res);
       break;
     }
@@ -7967,36 +8045,99 @@ ha_ndbcluster::generate_scan_filter(Ndb_
                                     NdbScanOperation *op)
 {
   DBUG_ENTER("generate_scan_filter");
+
   if (ndb_cond_stack)
   {
-    DBUG_PRINT("info", ("Generating scan filter"));
     NdbScanFilter filter(op);
-    bool multiple_cond= FALSE;
-    // Wrap an AND group around multiple conditions
-    if (ndb_cond_stack->next) {
-      multiple_cond= TRUE;
-      if (filter.begin() == -1)
-        DBUG_RETURN(1); 
-    }
-    for (Ndb_cond_stack *stack= ndb_cond_stack; 
-         (stack); 
-         stack= stack->next)
-      {
-        Ndb_cond *cond= stack->ndb_cond;
-
-        if (build_scan_filter(cond, &filter))
-        {
-          DBUG_PRINT("info", ("build_scan_filter failed"));
-          DBUG_RETURN(1);
-        }
-      }
-    if (multiple_cond && filter.end() == -1)
-      DBUG_RETURN(1);
+    
+    DBUG_RETURN(generate_scan_filter_from_cond(ndb_cond_stack, filter));
   }
   else
   {  
     DBUG_PRINT("info", ("Empty stack"));
   }
+
+  DBUG_RETURN(0);
+}
+
+int
+ha_ndbcluster::generate_scan_filter_from_cond(Ndb_cond_stack *ndb_cond_stack,
+					      NdbScanFilter& filter)
+{
+  DBUG_ENTER("generate_scan_filter_from_cond");
+  bool multiple_cond= FALSE;
+  
+  DBUG_PRINT("info", ("Generating scan filter"));
+  // Wrap an AND group around multiple conditions
+  if (ndb_cond_stack->next) 
+  {
+    multiple_cond= TRUE;
+    if (filter.begin() == -1)
+      DBUG_RETURN(1); 
+  }
+  for (Ndb_cond_stack *stack= ndb_cond_stack; 
+       (stack); 
+       stack= stack->next)
+  {
+    Ndb_cond *cond= stack->ndb_cond;
+    
+    if (build_scan_filter(cond, &filter))
+    {
+      DBUG_PRINT("info", ("build_scan_filter failed"));
+      DBUG_RETURN(1);
+    }
+  }
+  if (multiple_cond && filter.end() == -1)
+    DBUG_RETURN(1);
+
+  DBUG_RETURN(0);
+}
+
+int ha_ndbcluster::generate_scan_filter_from_key(NdbScanOperation *op,
+						 const KEY* key_info, 
+						 const byte *key, 
+						 uint key_len,
+						 byte *buf)
+{
+  KEY_PART_INFO* key_part= key_info->key_part;
+  KEY_PART_INFO* end= key_part+key_info->key_parts;
+  NdbScanFilter filter(op);
+  int res;
+
+  DBUG_ENTER("generate_scan_filter_from_key");
+  filter.begin(NdbScanFilter::AND);
+  for (; key_part != end; key_part++) 
+  {
+    Field* field= key_part->field;
+    uint32 pack_len= field->pack_length();
+    const byte* ptr= key;
+    char buf[256];
+    DBUG_PRINT("info", ("Filtering value for %s", field->field_name));
+    DBUG_DUMP("key", (char*)ptr, pack_len);
+    if (key_part->null_bit)
+    {
+      DBUG_PRINT("info", ("Generating ISNULL filter"));
+      if (filter.isnull(key_part->fieldnr-1) == -1)
+	DBUG_RETURN(1);
+    }
+    else
+    {
+      DBUG_PRINT("info", ("Generating EQ filter"));
+      if (filter.cmp(NdbScanFilter::COND_EQ, 
+		     key_part->fieldnr-1,
+		     ptr,
+		     pack_len) == -1)
+	DBUG_RETURN(1);
+    }
+    key += key_part->store_length;
+  }      
+  // Add any pushed condition
+  if (m_cond_stack &&
+      (res= generate_scan_filter_from_cond(m_cond_stack, filter)))
+    DBUG_RETURN(res);
+    
+  if (filter.end() == -1)
+    DBUG_RETURN(1);
 
   DBUG_RETURN(0);
 }

--- 1.105/sql/ha_ndbcluster.h	2006-11-03 10:53:55 +01:00
+++ 1.106/sql/ha_ndbcluster.h	2006-11-03 10:53:55 +01:00
@@ -649,7 +649,7 @@ private:
   void release_metadata();
   NDB_INDEX_TYPE get_index_type(uint idx_no) const;
   NDB_INDEX_TYPE get_index_type_from_table(uint index_no) const;
-  int check_index_fields_not_null(uint index_no);
+  void check_index_fields_not_null(uint index_no);
 
   int pk_read(const byte *key, uint key_len, byte *buf);
   int complemented_pk_read(const byte *old_data, byte *new_data);
@@ -663,6 +663,11 @@ private:
   int ordered_index_scan(const key_range *start_key,
                          const key_range *end_key,
                          bool sorted, bool descending, byte* buf);
+  int unique_index_scan(const KEY* key_info, 
+			const byte *key, 
+			uint key_len,
+			byte *buf);
+
   int full_table_scan(byte * buf);
   int fetch_next(NdbScanOperation* op);
   int next_result(byte *buf); 
@@ -725,6 +730,13 @@ bool uses_blob_value(bool all_fields);
   int build_scan_filter(Ndb_cond* &cond, NdbScanFilter* filter);
   int generate_scan_filter(Ndb_cond_stack* cond_stack, 
                            NdbScanOperation* op);
+  int generate_scan_filter_from_cond(Ndb_cond_stack* cond_stack, 
+				     NdbScanFilter& filter);
+  int generate_scan_filter_from_key(NdbScanOperation* op,
+                                   const KEY* key_info, 
+                                   const byte *key, 
+                                   uint key_len,
+                                   byte *buf);
 
   friend int execute_commit(ha_ndbcluster*, NdbTransaction*);
   friend int execute_no_commit(ha_ndbcluster*, NdbTransaction*, bool);