bk commit into 5.1 tree (sergefp:1.2259) BUG#20257

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
5.1 repository of psergey. When psergey 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-07-22 23:23:04+04:00, sergefp@stripped +3 -0
  BUG#20257: Fix partition pruning for BIGINT UNSIGNED:
   - Fix problems in the "Interval walking" partition interval analyzer.

  mysql-test/r/partition_pruning.result@stripped, 2006-07-22 23:22:59+04:00, sergefp@stripped
+79 -0
    BUG#20257: Add more testcases for partition pruning

  mysql-test/t/partition_pruning.test@stripped, 2006-07-22 23:22:59+04:00, sergefp@stripped
+66 -0
    BUG#20257: Add more testcases for partition pruning

  sql/sql_partition.cc@stripped, 2006-07-22 23:22:59+04:00, sergefp@stripped +12 -4
    BUG#20257: Fix partition pruning for BIGINT UNSIGNED, interval walking:
     - provide special handling for an edge case of interval of size 4G-1.
     - Store interval size in ulonglong, not uint (it was possible to miss partitions this
way)
     - In get_next_partition_via_walking(), interpret the value of walked-over field as
having the 
       same "signedness" as field (this is so because this value was obtained by calling
field->val_int())
     - Remove out of date todo comment.

# 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:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-5.1-bug20257

--- 1.80/sql/sql_partition.cc	2006-07-22 23:23:09 +04:00
+++ 1.81/sql/sql_partition.cc	2006-07-22 23:23:09 +04:00
@@ -6566,10 +6566,19 @@
   
   store_key_image_to_rec(field, max_value, len);
   b= field->val_int();
+  
+  /* 
+    Handle a special case where the distance between interval bounds is 
+    exactly 4G-1. This interval is too big for range walking, and if it is an
+    (x,y]-type interval then the following "b +=..." code will convert it to 
+    an empty interval by "wrapping around" a + 4G-1 + 1 = a. 
+  */
+  if ((ulonglong)b - (ulonglong)a == ~0ULL)
+    return -1;
 
   a += test(flags & NEAR_MIN);
   b += test(!(flags & NEAR_MAX));
-  uint n_values= b - a;
+  ulonglong n_values= b - a;
   
   if (n_values > total_parts || n_values > MAX_RANGE_TO_WALK)
     return -1;
@@ -6673,7 +6682,8 @@
   while (part_iter->field_vals.cur != part_iter->field_vals.end)
   {
     longlong dummy;
-    field->store(part_iter->field_vals.cur++, FALSE);
+    field->store(part_iter->field_vals.cur++,
+                 ((Field_num*)field)->unsigned_flag);
     if (part_iter->part_info->is_sub_partitioned() &&
         !part_iter->part_info->get_part_partition_id(part_iter->part_info,
                                                      &part_id, &dummy) ||
@@ -6681,8 +6691,6 @@
                                                 &part_id, &dummy))
       return part_id;
   }
-  //psergey-todo: return partition(part_func(NULL)) here...
-  
   part_iter->field_vals.cur= part_iter->field_vals.start;
   return NOT_A_PARTITION_ID;
 }

--- 1.17/mysql-test/r/partition_pruning.result	2006-07-22 23:23:09 +04:00
+++ 1.18/mysql-test/r/partition_pruning.result	2006-07-22 23:23:09 +04:00
@@ -697,3 +697,82 @@
 rows	2
 Extra	
 drop table t2;
+create table t1 (a bigint unsigned not null) partition by range(a) (
+partition p0 values less than (10),
+partition p1 values less than (100),
+partition p2 values less than (1000),
+partition p3 values less than (18446744073709551000),
+partition p4 values less than (18446744073709551614)
+);
+insert into t1 values (5),(15),(105),(1005);
+insert into t1 values (18446744073709551000+1);
+insert into t1 values (18446744073709551614-1);
+explain partitions select * from t1 where a < 10;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 
+where a >= 18446744073709551000-1 and a <= 18446744073709551000+1;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3,p4	ALL	NULL	NULL	NULL	NULL	3	Using where
+explain partitions select * from t1 
+where a between 18446744073709551001 and 18446744073709551002;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a = 18446744073709551000;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a = 18446744073709551613;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p4	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a = 18446744073709551614;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading
const tables
+drop table t1;
+create table t1 (a int) 
+partition by range((a & 0xFF) << 56) (
+partition p0 values less than (0x40 << 56),
+partition p1 values less than (0x80 << 56),
+partition p2 values less than (0xFF << 56)
+);
+insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
+explain partitions select * from t1 where a=0;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a=0xFE;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	ALL	NULL	NULL	NULL	NULL	2	Using where
+explain partitions select * from t1 where a>0xFE and a<= 0xFF;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading
const tables
+drop table t1;
+create table t1(a bigint unsigned not null) partition by range(a+0) (
+partition p1 values less than (10),
+partition p2 values less than (20),
+partition p3 values less than (2305561538531885056),
+partition p4 values less than (2305561538531950591)
+);
+insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
+insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
+explain partitions select * from t1 where 
+a >= 2305561538531885056-10 and a <= 2305561538531885056-8;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3	ALL	NULL	NULL	NULL	NULL	4	Using where
+explain partitions select * from t1 where 
+a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading
const tables
+explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1,p2,p3,p4	ALL	NULL	NULL	NULL	NULL	8	Using where
+drop table t1;
+create table t1 (a bigint) partition by range(a+0) (
+partition p1 values less than (-1000),
+partition p2 values less than (-10),
+partition p3 values less than (10),
+partition p4 values less than (1000)
+);
+insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15);
+explain partitions select * from t1 where a>-2 and a <=0;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3	ALL	NULL	NULL	NULL	NULL	4	Using where
+drop table t1;

--- 1.14/mysql-test/t/partition_pruning.test	2006-07-22 23:23:09 +04:00
+++ 1.15/mysql-test/t/partition_pruning.test	2006-07-22 23:23:09 +04:00
@@ -590,3 +590,69 @@
 --horizontal_results
 drop table t2;
 
+# BUG#20257: partition pruning test coverage for BIGINT UNSIGNED
+create table t1 (a bigint unsigned not null) partition by range(a) (
+  partition p0 values less than (10),
+  partition p1 values less than (100),
+  partition p2 values less than (1000),
+  partition p3 values less than (18446744073709551000),
+  partition p4 values less than (18446744073709551614)
+);
+insert into t1 values (5),(15),(105),(1005);
+insert into t1 values (18446744073709551000+1);
+insert into t1 values (18446744073709551614-1);
+
+explain partitions select * from t1 where a < 10;
+explain partitions select * from t1 
+  where a >= 18446744073709551000-1 and a <= 18446744073709551000+1;
+
+explain partitions select * from t1 
+  where a between 18446744073709551001 and 18446744073709551002;
+
+explain partitions select * from t1 where a = 18446744073709551000;
+explain partitions select * from t1 where a = 18446744073709551613;
+explain partitions select * from t1 where a = 18446744073709551614;
+drop table t1;
+
+create table t1 (a int) 
+  partition by range((a & 0xFF) << 56) (
+  partition p0 values less than (0x40 << 56),
+  partition p1 values less than (0x80 << 56),
+  partition p2 values less than (0xFF << 56)
+);
+
+insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE); 
+explain partitions select * from t1 where a=0;
+explain partitions select * from t1 where a=0xFE;
+explain partitions select * from t1 where a>0xFE and a<= 0xFF;
+drop table t1;
+
+create table t1(a bigint unsigned not null) partition by range(a+0) (
+  partition p1 values less than (10),
+  partition p2 values less than (20),
+  partition p3 values less than (2305561538531885056),
+  partition p4 values less than (2305561538531950591)
+);
+
+insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
+insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
+
+explain partitions select * from t1 where 
+  a >= 2305561538531885056-10 and a <= 2305561538531885056-8;
+
+explain partitions select * from t1 where 
+  a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
+
+explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF;
+drop table t1;
+
+create table t1 (a bigint) partition by range(a+0) (
+  partition p1 values less than (-1000),
+  partition p2 values less than (-10),
+  partition p3 values less than (10),
+  partition p4 values less than (1000)
+);
+insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15);
+explain partitions select * from t1 where a>-2 and a <=0;
+drop table t1;
+