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

[7 Feb 2006 1:04] Peter Gulutzan
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)
[7 Feb 2006 1:51] Miguel Solorzano
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>
[7 Feb 2006 19:02] Peter Gulutzan
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 ]
[6 Mar 2006 20:15] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=17980 marked as duplicate
of this,
[9 Mar 2006 17:18] Sergey Petrunya
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.
[13 Mar 2006 13:51] Bugs System
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
[20 Mar 2006 22:59] Mikael Ronström
This patch will appear in 5.1.8
[24 Mar 2006 13:45] Jon Stephens
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.