MySQL Bugs: #33182: Partitions: crash wih DIV in a function

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

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

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*)"

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.

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.

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'.

Patch

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

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

Mikael's last comment is correct and I reported a new bug:
Bug#34119 CREATE INDEX changes data if no_zero_in_date

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!

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.