List:Commits« Previous MessageNext Message »
From:Jonas Oreland Date:November 24 2010 4:38pm
Subject:bzr push into mysql-5.1-telco-6.3 branch (jonas:3344 to 3347) Bug#58280
View as plain text  
 3347 Jonas Oreland	2010-11-24
      ndb - bug#58280 - implement ::cmp_ref which among other things is needed for "sort_union"

    modified:
      mysql-test/suite/ndb/r/ndb_index.result
      mysql-test/suite/ndb/t/ndb_index.test
      sql/ha_ndbcluster.cc
      sql/ha_ndbcluster.h
 3346 Jonas Oreland	2010-11-24
      ndb - bug#57481 - make sure to close previous mrr scans, before starting new

    modified:
      mysql-test/suite/ndb/r/ndb_read_multi_range.result
      mysql-test/suite/ndb/t/ndb_read_multi_range.test
      sql/ha_ndbcluster.cc
 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

    modified:
      mysql-test/suite/ndb/r/ndb_subquery.result
      mysql-test/suite/ndb/t/ndb_subquery.test
      sql/ha_ndbcluster.cc
 3344 Jonas Oreland	2010-11-24
      ndb - bug#58453 fix execCOMMIT/execCOMPLETE wrt execLQH_TRANSREQ

    modified:
      storage/ndb/src/kernel/blocks/ERROR_codes.txt
      storage/ndb/src/kernel/blocks/dblqh/DblqhMain.cpp
      storage/ndb/test/ndbapi/testNodeRestart.cpp
      storage/ndb/test/run-test/daily-basic-tests.txt
=== modified file 'mysql-test/suite/ndb/r/ndb_index.result'
--- a/mysql-test/suite/ndb/r/ndb_index.result	2010-03-01 19:26:45 +0000
+++ b/mysql-test/suite/ndb/r/ndb_index.result	2010-11-24 16:37:58 +0000
@@ -269,3 +269,93 @@ b
 5
 6
 drop table t1;
+create table t1(vc varchar(16), i int, vc2 varchar(1024)
+, PRIMARY KEY(vc,vc2) USING HASH
+, KEY i1(i)
+, KEY i2(vc)
+)  ENGINE=ndbcluster;
+insert into t1 values
+('1',1,'1'), ('2',2,'2'), ('3',3,'3'), ('4',1,'4'), ('5',2,'5'),
+('6',3,'6'), ('7',1,'7'), ('8',2,'8'), ('9',3,'9'), ('10',1,'10'), 
+('11',2,'11'), ('12',3,'12'), ('13',1,'13'), ('14',2,'14'), ('15',3,'15'),
+('16',1,'16'), ('17',2,'17'), ('x',3,'x'), ('y',1,'y'), ('z',2,'z'),
+('1000',3,'1000'), ('2000',3,'2000'), ('10000',3,'10000');
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+explain
+select i,vc from t1
+where i>=1 or vc > '0';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	PRIMARY,i1,i2	i1,i2	5,18	NULL	20	Using sort_union(i1,i2); Using where with pushed condition
+select i,vc from t1
+where i>=1 or vc > '0';
+i	vc
+1	1
+1	10
+1	13
+1	16
+1	4
+1	7
+1	y
+2	11
+2	14
+2	17
+2	2
+2	5
+2	8
+2	z
+3	1000
+3	10000
+3	12
+3	15
+3	2000
+3	3
+3	6
+3	9
+3	x
+create table t2(vc varchar(16), i int, vc2 varchar(1024)
+, KEY i1(i)
+, KEY i2(vc)
+)  ENGINE=ndbcluster;
+insert into t2 values
+('1',1,'1'), ('2',2,'2'), ('3',3,'3'), ('4',1,'4'), ('5',2,'5'),
+('6',3,'6'), ('7',1,'7'), ('8',2,'8'), ('9',3,'9'), ('10',1,'10'), 
+('11',2,'11'), ('12',3,'12'), ('13',1,'13'), ('14',2,'14'), ('15',3,'15'),
+('16',1,'16'), ('17',2,'17'), ('x',3,'x'), ('y',1,'y'), ('z',2,'z'),
+('1000',3,'1000'), ('2000',3,'2000'), ('10000',3,'10000');
+analyze table t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+explain
+select i,vc from t2
+where i>=1 or vc > '0';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index_merge	i1,i2	i1,i2	5,19	NULL	20	Using sort_union(i1,i2); Using where with pushed condition
+select i,vc from t2
+where i>=1 or vc > '0';
+i	vc
+1	1
+1	10
+1	13
+1	16
+1	4
+1	7
+1	y
+2	11
+2	14
+2	17
+2	2
+2	5
+2	8
+2	z
+3	1000
+3	10000
+3	12
+3	15
+3	2000
+3	3
+3	6
+3	9
+3	x
+drop table t1, t2;

=== modified file 'mysql-test/suite/ndb/r/ndb_read_multi_range.result'
--- a/mysql-test/suite/ndb/r/ndb_read_multi_range.result	2009-02-03 13:35:56 +0000
+++ b/mysql-test/suite/ndb/r/ndb_read_multi_range.result	2010-11-24 13:16:09 +0000
@@ -588,3 +588,24 @@ i	i	9
 m	m	13
 v	v	22
 drop table t1, t2;
+create table t1 (pk int primary key, a int) engine=ndb;
+create table t2 (pk int primary key, a int) engine=ndb;
+insert into t2 values
+(0,0), (1,1), (2,2), (3,3), (4,4),
+(5,5), (6,6), (7,7), (8,8), (9,9);
+insert into t1
+select
+t1.a + t2.a*10 + t3.a*100 + t4.a*1000, 
+(t1.a + t2.a*10 + t3.a*100 + t4.a*1000) / 1000
+from
+t2 as t1, t2 as t2, t2 as t3, t2 as t4
+where (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) < 3000;
+explain
+SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM 
+t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3000	Using temporary
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Distinct
+SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM 
+t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
+drop table t1, t2;

=== modified file 'mysql-test/suite/ndb/r/ndb_subquery.result'
--- a/mysql-test/suite/ndb/r/ndb_subquery.result	2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/ndb/r/ndb_subquery.result	2010-11-24 13:05:04 +0000
@@ -89,4 +89,10 @@ a	b
 3	30
 4	30
 drop table if exists t1, t2, t3, t4;
+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);
+k	uq
+drop table if exists t;
 End of 5.1 tests

=== modified file 'mysql-test/suite/ndb/t/ndb_index.test'
--- a/mysql-test/suite/ndb/t/ndb_index.test	2010-03-01 19:26:45 +0000
+++ b/mysql-test/suite/ndb/t/ndb_index.test	2010-11-24 16:37:58 +0000
@@ -170,3 +170,50 @@ select distinct b from t1;
 select distinct b from t1 group by b;
 
 drop table t1;
+
+# bug#58280
+create table t1(vc varchar(16), i int, vc2 varchar(1024)
+  , PRIMARY KEY(vc,vc2) USING HASH
+  , KEY i1(i)
+  , KEY i2(vc)
+ )  ENGINE=ndbcluster;
+
+insert into t1 values
+('1',1,'1'), ('2',2,'2'), ('3',3,'3'), ('4',1,'4'), ('5',2,'5'),
+('6',3,'6'), ('7',1,'7'), ('8',2,'8'), ('9',3,'9'), ('10',1,'10'), 
+('11',2,'11'), ('12',3,'12'), ('13',1,'13'), ('14',2,'14'), ('15',3,'15'),
+('16',1,'16'), ('17',2,'17'), ('x',3,'x'), ('y',1,'y'), ('z',2,'z'),
+('1000',3,'1000'), ('2000',3,'2000'), ('10000',3,'10000');
+
+analyze table t1;
+explain
+select i,vc from t1
+  where i>=1 or vc > '0';
+
+--sorted_result
+select i,vc from t1
+  where i>=1 or vc > '0';
+
+# also test with hidden primary key
+create table t2(vc varchar(16), i int, vc2 varchar(1024)
+  , KEY i1(i)
+  , KEY i2(vc)
+ )  ENGINE=ndbcluster;
+
+insert into t2 values
+('1',1,'1'), ('2',2,'2'), ('3',3,'3'), ('4',1,'4'), ('5',2,'5'),
+('6',3,'6'), ('7',1,'7'), ('8',2,'8'), ('9',3,'9'), ('10',1,'10'), 
+('11',2,'11'), ('12',3,'12'), ('13',1,'13'), ('14',2,'14'), ('15',3,'15'),
+('16',1,'16'), ('17',2,'17'), ('x',3,'x'), ('y',1,'y'), ('z',2,'z'),
+('1000',3,'1000'), ('2000',3,'2000'), ('10000',3,'10000');
+
+analyze table t2;
+explain
+select i,vc from t2
+  where i>=1 or vc > '0';
+
+--sorted_result
+select i,vc from t2
+  where i>=1 or vc > '0';
+
+drop table t1, t2;

=== modified file 'mysql-test/suite/ndb/t/ndb_read_multi_range.test'
--- a/mysql-test/suite/ndb/t/ndb_read_multi_range.test	2009-02-03 13:35:56 +0000
+++ b/mysql-test/suite/ndb/t/ndb_read_multi_range.test	2010-11-24 13:16:09 +0000
@@ -428,3 +428,53 @@ select * from t1
     or (a = 'v')
     order by a asc, b asc;
 drop table t1, t2;
+
+########################
+# Check propper reinit of a mrr executed multiple time as part of a join.
+# The mrr access is driven by a scan and executed as for every tuple 
+# in the scaned table.
+#
+# In certain queries the optimizer don't read the entire mrr result set
+# before it fetch the next tuple from the scanned table. 
+# The next mrr operation would then still have an open scan which wasn't
+# cleaned up as expected. This may cause all available NdbOperation,
+# NdbTransaction or lock objects to be consumed before the operation 
+# could finish.
+#####################
+
+create table t1 (pk int primary key, a int) engine=ndb;
+create table t2 (pk int primary key, a int) engine=ndb;
+
+insert into t2 values
+   (0,0), (1,1), (2,2), (3,3), (4,4),
+   (5,5), (6,6), (7,7), (8,8), (9,9);
+
+##
+# 10^4 cross product on t2 creates 10.000 rows:
+# Insert volume has been tunes to insert only 3.000
+# of these as this is sufficient to produce an 'out of connection objects'
+##
+insert into t1
+ select
+   t1.a + t2.a*10 + t3.a*100 + t4.a*1000, 
+   (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) / 1000
+from
+  t2 as t1, t2 as t2, t2 as t3, t2 as t4
+where (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) < 3000;
+
+
+# Execute a 'scan(t1) join mrr(t2)'
+#  - 'DISTINCT t1.pk' will cause optimizer to stop fetching mrr(t2) 
+#     when the first matching 't2.a = t1.a' is found.
+#  - 'LEFT JOIN' is to ensure that 'Using join buffer' is *not* used
+#
+explain
+SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM 
+   t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
+
+--disable_result_log
+SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM 
+   t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
+--enable_result_log
+
+drop table t1, t2;

=== modified file 'mysql-test/suite/ndb/t/ndb_subquery.test'
--- a/mysql-test/suite/ndb/t/ndb_subquery.test	2007-11-29 10:29:35 +0000
+++ b/mysql-test/suite/ndb/t/ndb_subquery.test	2010-11-24 13:05:04 +0000
@@ -75,4 +75,15 @@ select * from t3 where exists (select * 
 
 drop table if exists t1, t2, t3, t4;
 
+##########
+# bug#58163
+
+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);
+
+drop table if exists t;
+
 --echo End of 5.1 tests

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2010-11-14 14:16:10 +0000
+++ b/sql/ha_ndbcluster.cc	2010-11-24 16:37:58 +0000
@@ -4786,8 +4786,10 @@ int ha_ndbcluster::index_read(uchar *buf
   default:
     break;
   }
-  DBUG_RETURN(read_range_first_to_buf(&start_key, 0, descending,
-                                      m_sorted, buf));
+  const int error= read_range_first_to_buf(&start_key, 0, descending,
+                                           m_sorted, buf);
+  table->status=error ? STATUS_NOT_FOUND: 0;
+  DBUG_RETURN(error);
 }
 
 
@@ -4795,7 +4797,9 @@ int ha_ndbcluster::index_next(uchar *buf
 {
   DBUG_ENTER("ha_ndbcluster::index_next");
   ha_statistic_increment(&SSV::ha_read_next_count);
-  DBUG_RETURN(next_result(buf));
+  const int error= next_result(buf);
+  table->status=error ? STATUS_NOT_FOUND: 0;
+  DBUG_RETURN(error);
 }
 
 
@@ -4803,7 +4807,9 @@ int ha_ndbcluster::index_prev(uchar *buf
 {
   DBUG_ENTER("ha_ndbcluster::index_prev");
   ha_statistic_increment(&SSV::ha_read_prev_count);
-  DBUG_RETURN(next_result(buf));
+  const int error= next_result(buf);
+  table->status=error ? STATUS_NOT_FOUND: 0;
+  DBUG_RETURN(error);
 }
 
 
@@ -4814,7 +4820,9 @@ int ha_ndbcluster::index_first(uchar *bu
   // Start the ordered index scan and fetch the first row
 
   // Only HA_READ_ORDER indexes get called by index_first
-  DBUG_RETURN(ordered_index_scan(0, 0, TRUE, FALSE, buf, NULL));
+  const int error= ordered_index_scan(0, 0, TRUE, FALSE, buf, NULL);
+  table->status=error ? STATUS_NOT_FOUND: 0;
+  DBUG_RETURN(error);
 }
 
 
@@ -4822,7 +4830,9 @@ int ha_ndbcluster::index_last(uchar *buf
 {
   DBUG_ENTER("ha_ndbcluster::index_last");
   ha_statistic_increment(&SSV::ha_read_last_count);
-  DBUG_RETURN(ordered_index_scan(0, 0, TRUE, TRUE, buf, NULL));
+  const int error= ordered_index_scan(0, 0, TRUE, TRUE, buf, NULL);
+  table->status=error ? STATUS_NOT_FOUND: 0;
+  DBUG_RETURN(error);
 }
 
 int ha_ndbcluster::index_read_last(uchar * buf, const uchar * key, uint key_len)
@@ -5009,9 +5019,14 @@ int ha_ndbcluster::rnd_next(uchar *buf)
   DBUG_ENTER("rnd_next");
   ha_statistic_increment(&SSV::ha_read_rnd_next_count);
 
-  if (!m_active_cursor)
-    DBUG_RETURN(full_table_scan(NULL, NULL, NULL, buf));
-  DBUG_RETURN(next_result(buf));
+  int error;
+  if (m_active_cursor)
+    error= next_result(buf);
+  else
+    error= full_table_scan(NULL, NULL, NULL, buf);
+  
+  table->status= error ? STATUS_NOT_FOUND: 0;
+  DBUG_RETURN(error);
 }
 
 
@@ -5073,6 +5088,7 @@ int ha_ndbcluster::rnd_pos(uchar *buf, u
        */
       res= HA_ERR_RECORD_DELETED;
     }
+    table->status= res ? STATUS_NOT_FOUND: 0;
     DBUG_RETURN(res);
   }
 }
@@ -5172,6 +5188,44 @@ void ha_ndbcluster::position(const uchar
   DBUG_VOID_RETURN;
 }
 
+int
+ha_ndbcluster::cmp_ref(const uchar * ref1, const uchar * ref2)
+{
+  DBUG_ENTER("cmp_ref");
+
+  if (table_share->primary_key != MAX_KEY) 
+  {
+    KEY *key_info = table->key_info + table_share->primary_key;
+    KEY_PART_INFO *key_part = key_info->key_part;
+    KEY_PART_INFO *end = key_part + key_info->key_parts;
+    
+    for (; key_part != end; key_part++) 
+    {
+      // NOTE: No need to check for null since PK is not-null
+
+      Field *field = key_part->field;
+      int result= field->key_cmp(ref1, ref2);
+      if (result)
+      {
+        DBUG_RETURN(result);
+      }
+
+      if (field->type() ==  MYSQL_TYPE_VARCHAR)
+      {
+        ref1 += 2;
+        ref2 += 2;
+      }
+      
+      ref1 += key_part->length;
+      ref2 += key_part->length;
+    }
+    DBUG_RETURN(0);
+  } 
+  else
+  {
+    DBUG_RETURN(memcmp(ref1, ref2, ref_length));
+  }
+}
 
 int ha_ndbcluster::info(uint flag)
 {
@@ -10929,6 +10983,7 @@ ha_ndbcluster::read_multi_range_first(KE
   const NdbOperation* op;
   Thd_ndb *thd_ndb= m_thd_ndb;
   NdbTransaction *trans= m_thd_ndb->trans;
+  int error;
 
   DBUG_ENTER("ha_ndbcluster::read_multi_range_first");
   DBUG_PRINT("info", ("blob fields=%d read_set=0x%x", table_share->blob_fields, table->read_set->bitmap[0]));
@@ -10950,6 +11005,14 @@ ha_ndbcluster::read_multi_range_first(KE
                                                 sorted, 
                                                 buffer));
   }
+
+  /**
+   * There may still be an open m_multi_cursor from the previous mrr access on this handler.
+   * Close it now to free up resources for this NdbScanOperation.
+   */ 
+  if (unlikely(error= close_scan()))
+    DBUG_RETURN(error);
+
   thd_ndb->query_state|= NDB_QUERY_MULTI_READ_RANGE;
   m_disable_multi_read= FALSE;
 
@@ -10985,8 +11048,8 @@ ha_ndbcluster::read_multi_range_first(KE
   */
 
   DBUG_ASSERT(cur_index_type != UNDEFINED_INDEX);
+  DBUG_ASSERT(m_multi_cursor==NULL);
 
-  m_multi_cursor= 0;
   const NdbOperation* lastOp= trans ? trans->getLastDefinedOperation() : 0;
   NdbOperation::LockMode lm= 
     (NdbOperation::LockMode)get_ndb_lock_type(m_lock.type, table->read_set);
@@ -10994,7 +11057,6 @@ ha_ndbcluster::read_multi_range_first(KE
   const uchar *end_of_buffer= buffer->buffer_end;
   uint num_scan_ranges= 0;
   uint i;
-  int error;
   bool any_real_read= FALSE;
 
   if (m_read_before_write_removal_possible)

=== modified file 'sql/ha_ndbcluster.h'
--- a/sql/ha_ndbcluster.h	2010-11-14 14:16:10 +0000
+++ b/sql/ha_ndbcluster.h	2010-11-24 16:37:58 +0000
@@ -407,6 +407,7 @@ class ha_ndbcluster: public handler
   int rnd_next(uchar *buf);
   int rnd_pos(uchar *buf, uchar *pos);
   void position(const uchar *record);
+  virtual int cmp_ref(const uchar * ref1, const uchar * ref2);
   int read_range_first(const key_range *start_key,
                        const key_range *end_key,
                        bool eq_range, bool sorted);

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-5.1-telco-6.3 branch (jonas:3344 to 3347) Bug#58280Jonas Oreland24 Nov