MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Martin Skold Date:December 15 2009 9:46pm
Subject:bzr commit into mysql-5.1-telco-7.1 branch (Martin.Skold:3195) Bug#47054
Bug#49459
View as plain text  
#At file:///home/marty/MySQL/mysql-5.1-telco-7.1/

 3195 Martin Skold	2009-12-15 [merge]
      Merge (Bug#47054 Cluster only deletes first matched row in delete with left join: Resetting status, Bug#49459 Incorrect handling of too long string in condition pushdown)
      modified:
        mysql-test/suite/ndb/r/ndb_basic.result
        mysql-test/suite/ndb/r/ndb_condition_pushdown.result
        mysql-test/suite/ndb/t/ndb_basic.test
        mysql-test/suite/ndb/t/ndb_condition_pushdown.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster_cond.cc
        sql/ha_ndbcluster_cond.h

=== modified file 'mysql-test/suite/ndb/r/ndb_basic.result'
--- a/mysql-test/suite/ndb/r/ndb_basic.result	2009-11-09 12:36:00 +0000
+++ b/mysql-test/suite/ndb/r/ndb_basic.result	2009-12-15 21:46:35 +0000
@@ -965,4 +965,40 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ref	id,obj_id	obj_id	5	const	1	Using where with pushed condition
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.id	1	
 drop table t1, t2;
+#
+# Bug #47054 Cluster only deletes first matched row in delete with left join
+#
+CREATE TABLE child
+( id INT AUTO_INCREMENT PRIMARY KEY
+, parent_id INT
+) ENGINE=ndb;
+CREATE TABLE parent 
+( id INT AUTO_INCREMENT PRIMARY KEY
+) ENGINE=ndb;
+INSERT INTO parent VALUES (NULL),(NULL),(NULL);
+INSERT INTO child VALUES (NULL, 1),(NULL,2),(NULL,3),(NULL,4),(NULL,5);
+SELECT * FROM child ORDER BY id;
+id	parent_id
+1	1
+2	2
+3	3
+4	4
+5	5
+SELECT * 
+FROM child AS c 
+LEFT JOIN parent AS p ON c.parent_id = p.id 
+WHERE p.id IS NULL AND c.id < 100  ORDER BY c.id;
+id	parent_id	id
+4	4	NULL
+5	5	NULL
+DELETE c.* 
+FROM child AS c 
+LEFT JOIN parent AS p ON c.parent_id = p.id 
+WHERE p.id IS NULL AND c.id < 100;
+SELECT * FROM child ORDER BY id;
+id	parent_id
+1	1
+2	2
+3	3
+DROP TABLE child, parent;
 End of 5.1 tests

=== modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result	2008-08-26 14:05:01 +0000
+++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result	2009-12-15 14:44:50 +0000
@@ -2081,5 +2081,73 @@ a
 show warnings;
 Level	Code	Message
 drop table balerno;
+create table t (pk int primary key, x varchar(1)) engine = ndb;
+insert into t values (0,"a");
+set engine_condition_pushdown = off;
+select * from t where x <> "aa";
+pk	x
+0	a
+select * from t where "aa" <> x;
+pk	x
+0	a
+select * from t where x between "" and "bb";
+pk	x
+0	a
+select * from t where x not between "" and "bb";
+pk	x
+select * from t where x in ("","aa","b");
+pk	x
+select * from t where x not in ("","aa","b");
+pk	x
+0	a
+select * from t where x like "aa?";
+pk	x
+set engine_condition_pushdown = on;
+explain select * from t where x <> "aa";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using where
+select * from t where x <> "aa";
+pk	x
+0	a
+explain select * from t where "aa" <> x;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using where
+select * from t where "aa" <> x;
+pk	x
+0	a
+explain select * from t where x between "" and "bb";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using where
+select * from t where x between "" and "bb";
+pk	x
+0	a
+explain select * from t where x not between "" and "bb";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using where
+select * from t where x not between "" and "bb";
+pk	x
+explain select * from t where x in ("","aa","b");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using where
+select * from t where x in ("","aa","b");
+pk	x
+explain select * from t where x not in ("","aa","b");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using where
+select * from t where x not in ("","aa","b");
+pk	x
+0	a
+explain select * from t where x like "aa?";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using where with pushed condition
+select * from t where x like "aa?";
+pk	x
+explain select * from t where x not like "aa?";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using where with pushed condition
+select * from t where x not like "aa?";
+pk	x
+0	a
+drop table t;
 set engine_condition_pushdown = @old_ecpd;
 DROP TABLE t1,t2,t3,t4,t5;

=== modified file 'mysql-test/suite/ndb/t/ndb_basic.test'
--- a/mysql-test/suite/ndb/t/ndb_basic.test	2009-11-09 12:36:00 +0000
+++ b/mysql-test/suite/ndb/t/ndb_basic.test	2009-12-15 21:46:35 +0000
@@ -876,4 +876,37 @@ CREATE TABLE `t2` (
 explain SELECT t1.id FROM t1 INNER JOIN t2 ON t1.id = t2.id WHERE t2.obj_id=1;
 drop table t1, t2;
 
+--echo #
+--echo # Bug #47054 Cluster only deletes first matched row in delete with left join
+--echo #
+
+CREATE TABLE child
+( id INT AUTO_INCREMENT PRIMARY KEY
+, parent_id INT
+) ENGINE=ndb;
+
+CREATE TABLE parent 
+( id INT AUTO_INCREMENT PRIMARY KEY
+) ENGINE=ndb;
+
+INSERT INTO parent VALUES (NULL),(NULL),(NULL);
+
+INSERT INTO child VALUES (NULL, 1),(NULL,2),(NULL,3),(NULL,4),(NULL,5);
+
+SELECT * FROM child ORDER BY id;
+
+SELECT * 
+FROM child AS c 
+  LEFT JOIN parent AS p ON c.parent_id = p.id 
+WHERE p.id IS NULL AND c.id < 100  ORDER BY c.id;
+
+DELETE c.* 
+FROM child AS c 
+  LEFT JOIN parent AS p ON c.parent_id = p.id 
+WHERE p.id IS NULL AND c.id < 100;
+
+SELECT * FROM child ORDER BY id;
+
+DROP TABLE child, parent;
+
 --echo End of 5.1 tests

=== modified file 'mysql-test/suite/ndb/t/ndb_condition_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_condition_pushdown.test	2008-08-26 14:05:01 +0000
+++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test	2009-12-15 14:44:50 +0000
@@ -2229,7 +2229,35 @@ show warnings;
 
 drop table balerno;
 
-
+# bug#49459 Incorrect handling of too long string in condition pushdown
+create table t (pk int primary key, x varchar(1)) engine = ndb;
+insert into t values (0,"a");
+set engine_condition_pushdown = off;
+select * from t where x <> "aa";
+select * from t where "aa" <> x;
+select * from t where x between "" and "bb";
+select * from t where x not between "" and "bb";
+select * from t where x in ("","aa","b");
+select * from t where x not in ("","aa","b");
+select * from t where x like "aa?";
+set engine_condition_pushdown = on;
+explain select * from t where x <> "aa";
+select * from t where x <> "aa";
+explain select * from t where "aa" <> x;
+select * from t where "aa" <> x;
+explain select * from t where x between "" and "bb";
+select * from t where x between "" and "bb";
+explain select * from t where x not between "" and "bb";
+select * from t where x not between "" and "bb";
+explain select * from t where x in ("","aa","b");
+select * from t where x in ("","aa","b");
+explain select * from t where x not in ("","aa","b");
+select * from t where x not in ("","aa","b");
+explain select * from t where x like "aa?";
+select * from t where x like "aa?";
+explain select * from t where x not like "aa?";
+select * from t where x not like "aa?";
+drop table t;
 
 set engine_condition_pushdown = @old_ecpd;
 DROP TABLE t1,t2,t3,t4,t5;

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2009-11-18 13:12:53 +0000
+++ b/sql/ha_ndbcluster.cc	2009-12-15 21:46:35 +0000
@@ -11845,6 +11845,7 @@ ha_ndbcluster::read_multi_range_next(KEY
           *multi_range_found_p= m_multi_ranges + current_range_no;
           /* Copy out data from the new row. */
           unpack_record(table->record[0], m_next_row);
+          table->status= 0;
           /*
             Mark that we have used this row, so we need to fetch a new
             one on the next call.

=== modified file 'sql/ha_ndbcluster_cond.cc'
--- a/sql/ha_ndbcluster_cond.cc	2009-10-02 14:55:18 +0000
+++ b/sql/ha_ndbcluster_cond.cc	2009-12-15 14:44:50 +0000
@@ -246,6 +246,7 @@ void ndb_serialize_cond(const Item *item
       {
         // End marker for condition group
         DBUG_PRINT("info", ("End of condition group"));
+        context->expect_no_length();
         curr_cond->ndb_item= new Ndb_item(NDB_END_COND);
       }
       else
@@ -268,13 +269,15 @@ void ndb_serialize_cond(const Item *item
             DBUG_PRINT("info", ("FIELD_ITEM"));
             DBUG_PRINT("info", ("table %s", tab->getName()));
             DBUG_PRINT("info", ("column %s", field->field_name));
+            DBUG_PRINT("info", ("column length %u", field->field_length));
             DBUG_PRINT("info", ("type %d", field->type()));
             DBUG_PRINT("info", ("result type %d", field->result_type()));
-            
+
             // Check that we are expecting a field and with the correct
-            // result type
+            // result type and of length that can store the item value
             if (context->expecting(Item::FIELD_ITEM) &&
                 context->expecting_field_type(field->type()) &&
+                context->expecting_max_length(field->field_length) &&
                 (context->expecting_field_result(field->result_type()) ||
                  // Date and year can be written as string or int
                  ((type == MYSQL_TYPE_TIME ||
@@ -315,6 +318,7 @@ void ndb_serialize_cond(const Item *item
                     context->expect_only(Item::STRING_ITEM);
                     context->expect(Item::VARBIN_ITEM);
                     context->expect_collation(field_item->collation.collation);
+                    context->expect_max_length(field->field_length);
                     break;
                   case REAL_RESULT:
                     context->expect_only(Item::REAL_ITEM);
@@ -384,7 +388,7 @@ void ndb_serialize_cond(const Item *item
             context->supported= FALSE;
             break;
           }
-          
+          context->expect_no_length();
           switch (func_item->functype()) {
           case Item_func::EQ_FUNC:
           {
@@ -728,7 +732,9 @@ void ndb_serialize_cond(const Item *item
         }
         case Item::STRING_ITEM:
           DBUG_PRINT("info", ("STRING_ITEM")); 
-          if (context->expecting(Item::STRING_ITEM)) 
+          // Check that we do support pushing the item value length
+          if (context->expecting(Item::STRING_ITEM) &&
+              context->expecting_length(item->max_length)) 
           {
 #ifndef DBUG_OFF
             char buff[256];
@@ -747,12 +753,14 @@ void ndb_serialize_cond(const Item *item
               context->expect_only(Item::FIELD_ITEM);
               context->expect_only_field_result(STRING_RESULT);
               context->expect_collation(item->collation.collation);
+              context->expect_length(item->max_length);
             }
             else 
             {
               // Expect another logical expression
               context->expect_only(Item::FUNC_ITEM);
               context->expect(Item::COND_ITEM);
+              context->expect_no_length();
               // Check that we are comparing with a field with same collation
               if (!context->expecting_collation(item->collation.collation))
               {
@@ -916,6 +924,7 @@ void ndb_serialize_cond(const Item *item
           curr_cond= context->cond_ptr= new Ndb_cond();
           curr_cond->prev= prev_cond;
           prev_cond->next= curr_cond;
+          context->expect_no_length();
           curr_cond->ndb_item= new Ndb_item(NDB_END_COND);
           // Pop rewrite stack
           context->rewrite_stack=  rewrite_context->next;

=== modified file 'sql/ha_ndbcluster_cond.h'
--- a/sql/ha_ndbcluster_cond.h	2009-05-27 12:11:46 +0000
+++ b/sql/ha_ndbcluster_cond.h	2009-12-15 14:21:01 +0000
@@ -213,16 +213,24 @@ public:
 
   void save_in_field(Ndb_item *field_item)
   {
+    DBUG_ENTER("save_in_field");
     Field *field = field_item->value.field_value->field;
     const Item *item= value.item;
-
     if (item && field)
     {
+      DBUG_PRINT("info", ("item length %u, field length %u",
+                          item->max_length, field->field_length));
+      if (item->max_length > field->field_length)
+      {
+        DBUG_PRINT("info", ("Comparing field with longer value"));
+        DBUG_PRINT("info", ("Field can store %u", field->field_length));
+      }
       my_bitmap_map *old_map=
         dbug_tmp_use_all_columns(field->table, field->table->write_set);
       ((Item *)item)->save_in_field(field, FALSE);
       dbug_tmp_restore_column_map(field->table->write_set, old_map);
     }
+    DBUG_VOID_RETURN;
   };
 
   static NDB_FUNC_TYPE item_func_to_ndb_func(Item_func::Functype fun)
@@ -326,7 +334,7 @@ class Ndb_cond_stack : public Sql_alloc
 class Ndb_expect_stack : public Sql_alloc
 {
  public:
-  Ndb_expect_stack(): collation(NULL), next(NULL) 
+Ndb_expect_stack(): collation(NULL), length(0), max_length(0), next(NULL) 
   {
     // Allocate type checking bitmaps   
     bitmap_init(&expect_mask,
@@ -348,7 +356,7 @@ class Ndb_expect_stack : public Sql_allo
   void push(Ndb_expect_stack* expect_next)
   {
     next= expect_next;
-  };
+  }
   void pop()
   {
     if (next)
@@ -364,25 +372,25 @@ class Ndb_expect_stack : public Sql_allo
       next= next->next;
       delete expect_next;
     }
-  };
+  }
   void expect(Item::Type type)
   {
     bitmap_set_bit(&expect_mask, (uint) type);
     if (type == Item::FIELD_ITEM)
       expect_all_field_types();
-  };
+  }
   void dont_expect(Item::Type type)
   {
     bitmap_clear_bit(&expect_mask, (uint) type);
-  };
+  }
   bool expecting(Item::Type type)
   {
     return bitmap_is_set(&expect_mask, (uint) type);
-  };
+  }
   void expect_nothing()
   {
     bitmap_clear_all(&expect_mask);
-  };
+  }
   bool expecting_nothing()
   {
     return bitmap_is_clear_all(&expect_mask);
@@ -391,24 +399,24 @@ class Ndb_expect_stack : public Sql_allo
   {
     expect_nothing();
     expect(type);
-  };
+  }
 
   void expect_field_type(enum_field_types type)
   {
     bitmap_set_bit(&expect_field_type_mask, (uint) type);
-  };
+  }
   void expect_all_field_types()
   {
     bitmap_set_all(&expect_field_type_mask);
-  };
+  }
   bool expecting_field_type(enum_field_types type)
   {
     return bitmap_is_set(&expect_field_type_mask, (uint) type);
-  };
+  }
   void expect_no_field_type()
   {
     bitmap_clear_all(&expect_field_type_mask);
-  };
+  }
   bool expecting_no_field_type()
   {
     return bitmap_is_clear_all(&expect_field_type_mask);
@@ -417,21 +425,21 @@ class Ndb_expect_stack : public Sql_allo
   {
     expect_no_field_type();
     expect_field_type(result);
-  };
+  }
 
   void expect_field_result(Item_result result)
   {
     bitmap_set_bit(&expect_field_result_mask, (uint) result);
-  };
+  }
   bool expecting_field_result(Item_result result)
   {
     return bitmap_is_set(&expect_field_result_mask,
                          (uint) result);
-  };
+  }
   void expect_no_field_result()
   {
     bitmap_clear_all(&expect_field_result_mask);
-  };
+  }
   bool expecting_no_field_result()
   {
     return bitmap_is_clear_all(&expect_field_result_mask);
@@ -440,11 +448,11 @@ class Ndb_expect_stack : public Sql_allo
   {
     expect_no_field_result();
     expect_field_result(result);
-  };
+  }
   void expect_collation(CHARSET_INFO* col)
   {
     collation= col;
-  };
+  }
   bool expecting_collation(CHARSET_INFO* col)
   {
     bool matching= (!collation)
@@ -453,13 +461,35 @@ class Ndb_expect_stack : public Sql_allo
     collation= NULL;
 
     return matching;
-  };
+  }
+  void expect_length(Uint32 len)
+  {
+    length= len;
+  }
+  void expect_max_length(Uint32 max)
+  {
+    max_length= max;
+  }
+  bool expecting_length(Uint32 len)
+  {
+    return max_length == 0 || len <= max_length;
+  }
+  bool expecting_max_length(Uint32 max)
+  {
+    return max >= length;
+  }
+  void expect_no_length()
+  {
+    length= max_length= 0;
+  }
 
 private:
   MY_BITMAP expect_mask;
   MY_BITMAP expect_field_type_mask;
   MY_BITMAP expect_field_result_mask;
   CHARSET_INFO* collation;
+  Uint32 length;
+  Uint32 max_length;
   Ndb_expect_stack* next;
 };
 
@@ -496,7 +526,7 @@ class Ndb_cond_traverse_context : public
   { 
    if (stack)
       cond_ptr= stack->ndb_cond;
-  };
+  }
   ~Ndb_cond_traverse_context()
   {
     if (rewrite_stack) delete rewrite_stack;
@@ -504,19 +534,19 @@ class Ndb_cond_traverse_context : public
   inline void expect(Item::Type type)
   {
     expect_stack.expect(type);
-  };
+  }
   inline void dont_expect(Item::Type type)
   {
     expect_stack.dont_expect(type);
-  };
+  }
   inline bool expecting(Item::Type type)
   {
     return expect_stack.expecting(type);
-  };
+  }
   inline void expect_nothing()
   {
     expect_stack.expect_nothing();
-  };
+  }
   inline bool expecting_nothing()
   {
     return expect_stack.expecting_nothing();
@@ -524,61 +554,82 @@ class Ndb_cond_traverse_context : public
   inline void expect_only(Item::Type type)
   {
     expect_stack.expect_only(type);
-  };
+  }
 
   inline void expect_field_type(enum_field_types type)
   {
     expect_stack.expect_field_type(type);
-  };
+  }
   inline void expect_all_field_types()
   {
     expect_stack.expect_all_field_types();
-  };
+  }
   inline bool expecting_field_type(enum_field_types type)
   {
     return expect_stack.expecting_field_type(type);
-  };
+  }
   inline void expect_no_field_type()
   {
     expect_stack.expect_no_field_type();
-  };
+  }
   inline bool expecting_no_field_type()
   {
     return expect_stack.expecting_no_field_type();
-  };
+  }
   inline void expect_only_field_type(enum_field_types result)
   {
     expect_stack.expect_only_field_type(result);
-  };
+  }
 
   inline void expect_field_result(Item_result result)
   {
     expect_stack.expect_field_result(result);
-  };
+  }
   inline bool expecting_field_result(Item_result result)
   {
     return expect_stack.expecting_field_result(result);
-  };
+  }
   inline void expect_no_field_result()
   {
     expect_stack.expect_no_field_result();
-  };
+  }
   inline bool expecting_no_field_result()
   {
     return expect_stack.expecting_no_field_result();
-  };
+  }
   inline void expect_only_field_result(Item_result result)
   {
     expect_stack.expect_only_field_result(result);
-  };
+  }
   inline void expect_collation(CHARSET_INFO* col)
   {
     expect_stack.expect_collation(col);
-  };
+  }
   inline bool expecting_collation(CHARSET_INFO* col)
   {
     return expect_stack.expecting_collation(col);
-  };
+  }
+  inline void expect_length(Uint32 length)
+  {
+    expect_stack.expect_length(length);
+  }
+  inline void expect_max_length(Uint32 max)
+  {
+    expect_stack.expect_max_length(max);
+  }
+  inline bool expecting_length(Uint32 length)
+  {
+    return expect_stack.expecting_length(length);
+  }
+  inline bool expecting_max_length(Uint32 max)
+  {
+    return expect_stack.expecting_max_length(max);
+  }
+  inline void expect_no_length()
+  {
+    expect_stack.expect_no_length();
+  }
+  
 
   TABLE* table;
   const NdbDictionary::Table *ndb_table;

Thread
bzr commit into mysql-5.1-telco-7.1 branch (Martin.Skold:3195) Bug#47054Bug#49459Martin Skold15 Dec