Bug #33182 Partitions: crash wih DIV in a function
Submitted: 12 Dec 2007 19:43 Modified: 28 Mar 2008 9:39
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.23-rc-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Mikael Ronström CPU Architecture:Any
Triage: D1 (Critical)

[12 Dec 2007 19:43] Peter Gulutzan
Description:
I create a partitioned table with DIV and / in partition function.
I insert one row. Then I change @@div_precision_increment.
I try to create an index, and fail (which itself may be a bug).
I try to update.
Crash.

How to repeat:
This is the same test as one of the test cases for
Bug#30188 Partitions: I can use DIV in a function,
I only had to add an UPDATE statement.

mysql> create table t (s1 int) partition by list( (s1/3)*10 div 1)
    ->   (partition p0 values in (0), partition p1 values in (1));
Query OK, 0 rows affected (0.03 sec)

mysql> set @@div_precision_increment=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> set @@div_precision_increment=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create index i on t (s1);
ERROR 1526 (HY000): Table has no partition for value 3
mysql> update t set s1 = 3;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[13 Dec 2007 0:00] Miguel Solorzano
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-rc-debug Source distribution

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

mysql> create table t (s1 int) partition by list( (s1/3)*10 div 1)
    -> (partition p0 values in (0), partition p1 values in (1));
Query OK, 0 rows affected (0.01 sec)

mysql> set @@div_precision_increment=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.03 sec)

mysql> set @@div_precision_increment=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create index i on t (s1);
ERROR 1526 (HY000): Table has no partition for value 3
mysql> update t set s1 = 3;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

Back Trace on Linux 32-bit:

071212 21:56:46 [Note] /home/miguel/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.23-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1303938160 (LWP 3845)]
mysqld: sql_partition.cc:314: int get_parts_for_update(const uchar*, uchar*, const uchar*, partition_info*, uint32*, uint32*, longlong*): Assertion `0' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread -1303938160 (LWP 3845)]
0x00275402 in __kernel_vsyscall ()
(gdb) bt full
#0  0x00275402 in __kernel_vsyscall ()
No symbol table info available.
#1  0x00ab4ba0 in raise () from /lib/libc.so.6
No symbol table info available.
#2  0x00ab64b1 in abort () from /lib/libc.so.6
No symbol table info available.
#3  0x00aae1db in __assert_fail () from /lib/libc.so.6
No symbol table info available.
#4  0x081bccc3 in get_parts_for_update (old_data=0x9c1c2f0 "ý\001", new_data=0x9c1c2e8 "ý\003", rec0=0x9c1c2e8 "ý\003", part_info=0x9c1c568, 
    old_part_id=0xb2476138, new_part_id=0xb247613c, new_func_value=0xb2476130) at sql_partition.cc:314
        part_field_array = (class Field **) 0x9c1cc98
        error = 160
        old_func_value = 3
        _db_func_ = 0x87d67cc "ha_partition::update_row"
        _db_file_ = 0x87d5b19 "ha_partition.cc"
        _db_level_ = 6
        _db_framep_ = (char **) 0xb247610c
        __PRETTY_FUNCTION__ = "int get_parts_for_update(const uchar*, uchar*, const uchar*, partition_info*, uint32*, uint32*, longlong*)"
#5  0x083aaafe in ha_partition::update_row (this=0x9c1bff0, old_data=0x9c1c2f0 "ý\001", new_data=0x9c1c2e8 "ý\003") at ha_partition.cc:2821
        new_part_id = 163709600
        old_part_id = 0
        error = 0
        func_value = 703128030876270592
        orig_timestamp_type = TIMESTAMP_NO_AUTO_SET
        _db_func_ = 0x87c634b "mysql_update"
        _db_file_ = 0x87c6067 "sql_update.cc"
        _db_level_ = 5
        _db_framep_ = (char **) 0x0
#6  0x0839d30c in handler::ha_update_row (this=0x9c1bff0, old_data=0x9c1c2f0 "ý\001", new_data=0x9c1c2e8 "ý\003") at handler.cc:3739
        error = 163693301
        __PRETTY_FUNCTION__ = "int handler::ha_update_row(const uchar*, uchar*)"
[13 Dec 2007 13:30] Mattias Jonsson
crash on DBUG_ASSERT if compiles with debug, but will return HA_ERR_NO_PARTITION_FOUND on production build.

The bug is that / operator changes behavior due to the div_precision_increment.
Solution will be to disable that operator as partition function operator.

Use the "DIV" function instead.
[13 Dec 2007 15:58] Jon Stephens
Noted in 5.1/6.0 Partitioning Limitations that "/" operator is no longer permitted in partitioning expressions per developer notes above and in Bug #30188.
[13 Dec 2007 23:33] Peter Gulutzan
Another test case.

mysql> create table mmm (s1 date) partition by list(s1 div 1) (partition p1 values in (1), partition p2 values in (0), partition p3 values in (100));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into mmm values ('0000-00-00'),('0000-01-00'),('0000-00-01');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> set sql_mode=no_zero_in_date;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mmm;
+------------+
| s1         |
+------------+
| 0000-00-01 |
| 0000-00-00 |
| 0000-01-00 |
+------------+
3 rows in set (0.00 sec)

mysql> select * from mmm where s1 < '9999-01-01';
+------------+
| s1         |
+------------+
| 0000-00-01 |
| 0000-00-00 |
| 0000-01-00 |
+------------+
3 rows in set (0.01 sec)

mysql> select * from mmm where s1 between '0000-00-01' and '0001-01-01';
+------------+
| s1         |
+------------+
| 0000-00-01 |
| 0000-00-00 |
| 0000-01-00 |
+------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '0000-00-01' for column 's1' at row 0 |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create index i on mmm (s1);
Query OK, 3 rows affected, 2 warnings (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> select * from mmm where s1 between '0000-00-01' and '0001-01-01';
+------------+
| s1         |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
3 rows in set, 1 warning (0.02 sec)

... '0000-00-00' is not between '0000-00-01' and '0001-01-01',
... and anyway I didn't insert three rows with '0000-00-00'.
[28 Jan 2008 21:04] Mikael Ronström
Patch

Attachment: bug33182.patch (text/x-patch), 2.58 KiB.

[28 Jan 2008 21:20] Mikael Ronström
The last bug reported with regards to creating index on table with
3 rows with erroneus dates. This bug is not related to the first
bug reported in this bug report and also isn't related to
partitioning and thus I'll ignore it here
[29 Jan 2008 0:45] Peter Gulutzan
Mikael's last comment is correct and I reported a new bug:
Bug#34119 CREATE INDEX changes data if no_zero_in_date
[1 Feb 2008 23:37] Mattias Jonsson
Why not only disallow the "/" operator, directly?
by:
on item_func.h ~ row 432 (What I had in mind...)
+  bool check_partition_func_processor(uchar *int_arg) {return TRUE;}

instead of disable it for the base-class (as default) and enable it for ('+','-'), '*' and '%' ? (is it a performance thing?)

But it does the same thing, so OK to push!
[27 Mar 2008 11:18] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 17:49] Bugs System
Pushed into 6.0.5-alpha
[28 Mar 2008 9:39] Jon Stephens
It's already been noted that we don't allow the use of "/" in partitioning expressions so there's nothing new here to document. Closed without further action.