MySQL Bugs: #17173: Partitions: less-than search fails

Bug #17173	Partitions: less-than search fails
Submitted:	7 Feb 2006 1:04	Modified:	24 Mar 2006 13:45
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.7-beta-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I create a partitioned table.
I try to find "where partitioned-column < 3".
I get nothing.
To prove something's there,
I try to find "where partitioned-column = 2".
I get something.

How to repeat:
mysql> create table etbd (s1 int) partition by list (s1) (partition p1 values in (1),partition p2 values in (2),partition p3 values in (3),partition p4 values in (4),partition p5 values in (5));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into etbd values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select count(*) from etbd where s1 < 3;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from etbd where s1 = 2;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

/* I now establish that with an ordinary table, '<' still works. */

mysql> create table tmmm (s1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tmmm values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select count(*) from tmmm where s1 < 3;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Thank you fro the bug report.

miguel@hegel:~/dbs/5.1> bin/mysqladmin -uroot create db3
miguel@hegel:~/dbs/5.1> bin/mysql -uroot db3
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.7-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table etbd (s1 int) partition by list (s1) (partition p1 values in
    -> (1),partition p2 values in (2),partition p3 values in (3),partition p4 values in
    -> (4),partition p5 values in (5));
Query OK, 0 rows affected (0.07 sec)

mysql> 
mysql> insert into etbd values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select count(*) from etbd where s1 < 3;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from etbd where s1 = 2;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> create table tmmm (s1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmmm values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select count(*) from tmmm where s1 < 3;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql>

With a larger record count, less-than searches cause crashes.
How to repeat:

delimiter //
create table tp1 (s1 int, s2 int) partition by list (s1) (partition p0 values in (0), partition p1 values in (1), partition p2 values in (2), partition p3 values in (3), partition p4 values in (4), partition p5 values in (5), partition p6 values in (6), partition p7 values in (7), partition p8 values in (8), partition p9 values in (9))//
create procedure pp1 () begin declare v int default 0; while v < 1000000 do insert into tp1 values (v mod 10,v); set v = v + 1; end while; end//
call pp1()//
create table tp2 (s1 int, s2 int)//
create procedure pp2 () begin declare v int default 0; while v < 1000000 do insert into tp2 values (v mod 10,v); set v = v + 1; end while; end//
call pp2()//
select count(*) from tp1 where s1 = 5;//
select count(*) from tp2 where s1 = 5;//
select count(*) from tp1 where s1 > 5;//
select count(*) from tp2 where s1 > 5;//
select count(*) from tp1 where s1 < 5//         [ crash ]

Bug: http://bugs.mysql.com/bug.php?id=17980 marked as duplicate
of this,

The problem is in handling of NULLs in "interval mapping".
It can be demonstrated as follows:

Apply the following patch (it adds printout at the end of get_part_iter_for_interval_via_mapping):

===== sql_partition.cc 1.40 vs edited =====
--- 1.40/sql/sql_partition.cc   2006-02-22 02:39:57 +03:00
+++ edited/sql_partition.cc     2006-03-10 01:09:41 +03:00
@@ -5563,6 +5563,9 @@
     if (part_iter->part_nums.start== part_iter->part_nums.end)
       return 0; /* No partitions */
   }
+  fprintf(stderr, "min_idx=%d, max_idx=%d\n", 
+          (int)part_iter->part_nums.start,
+          (int)part_iter->part_nums.end);
   return 1; /* Ok, iterator initialized */
 }

Then run the last query from the previous example, and see this printout:
  min_idx=1, max_idx=0

which is wrong. "min_idx<=max_idx" should hold for the returned (min_idx, max_idx) pair.
The value of min_idx is wrong - NULL should to 0-th partition, not to 1st partition.

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

This patch will appear in 5.1.8

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.8 changelog. Closed.