MySQL Bugs: #18659: Partitions: wrong result on WHERE <col. used in part. function> IS NULL

Bug #18659	Partitions: wrong result on WHERE <col. used in part. function> IS NULL
Submitted:	30 Mar 2006 17:09	Modified:	4 Apr 2006 16:50
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.19-BK, 5.1	OS:	Linux (Linux)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
# Extreme simplified testcase
CREATE TABLE t1 (f_int1 INTEGER)
PARTITION BY LIST(ABS(MOD(f_int1,2))) 
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
(PARTITION part1 VALUES IN (0),
PARTITION part2 VALUES IN (1),
PARTITION part4 VALUES IN (NULL));
INSERT INTO t1 SET f_int1 = NULL;
# There is the record with f_int1 IS NULL
SELECT * FROM t1;
f_int1
NULL

# Where is the record with f_int1 IS NULL ??
SELECT * FROM t1
WHERE f_int1 IS NULL;
f_int1
     <--------------- empty result set

Other experiments reveal
- InnoDB and NDB show the same bug
- the bug disappears if the subpartitioning is removed

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
         Version 5.1 last ChangeSet@1.2263, 2006-03-30

How to repeat:
Please use my attached testscript ml0002.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml0002.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml0002

testscript

Attachment: ml002.test (application/test, text), 1.56 KiB.

Thank you for a bug report. With 5.1.9-debug (ChangeSet@1.2265, 2006-03-30 11:12:24+02:00) I got a crash:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.9-beta-debug

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

mysql> CREATE TABLE t1 (f_int1 INTEGER)
    -> PARTITION BY LIST(ABS(MOD(f_int1,2)))
    -> SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
    -> (PARTITION part1 VALUES IN (0),
    -> PARTITION part2 VALUES IN (1),
    -> PARTITION part4 VALUES IN (NULL));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t1 SET f_int1 = NULL;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+--------+
| f_int1 |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

mysql> select * from t1 where f_intl is NULL;
ERROR 1054 (42S22): Unknown column 'f_intl' in 'where clause'
mysql> select * from t1 where f_int1 is NULL;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
060330 17:29:53  mysqld restarted

mysql> exit
Bye
openxs@suse:~/dbs/5.1> tail -40 var/suse.err
Cannot determine thread, fp=0xbe5fe738, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80edc10
0x84a368d
0x84b8331
0x84a0f5b
0x84b8764
0x84b3be0
0x81c1694
0x81b17b6
0x81b1a51
0x81b0f02
0x814523c
0x8148fab
0x8144092
0x8106ae2
0x810eea4
0x8104d0d
0x81045f8
0x8103785
0x849e561
0x84d8e6a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8e686b8 = select * from t1 where f_int1 is NULL
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
060330 17:29:53  mysqld restarted
/home/openxs/dbs/5.1/libexec/mysqld: Table 'general_log' is marked as crashed and should be repaired
/home/openxs/dbs/5.1/libexec/mysqld: Table 'slow_log' is marked as crashed and should be repaired
060330 17:29:54  InnoDB: Started; log sequence number 0 43665
060330 17:29:54 [Note] /home/openxs/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.9-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
openxs@suse:~/dbs/5.1>

Resolved stack trace is:

openxs@suse:~/dbs/5.1> bin/resolve_stack_dump -s /tmp/mysqld.sym -n 18659.stack

0x80edc10 handle_segfault + 412
0x84a368d __pthread_sighandler + 173
0x84b8331 __kill + 17
0x84a0f5b __pthread_raise + 27
0x84b8764 abort + 340
0x84b3be0 __assert_fail + 224
0x81c1694 bitmap_set_bit + 38
0x81b17b6 _Z20find_used_partitionsP19st_part_prune_paramP7SEL_ARG + 790
0x81b1a51 _Z20find_used_partitionsP19st_part_prune_paramP7SEL_ARG + 1457
0x81b0f02 _Z16prune_partitionsP3THDP8st_tableP4Item + 800
0x814523c _ZN4JOIN8optimizeEv + 1442
0x8148fab _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orde
rSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 491
0x8144092 _Z13handle_selectP3THDP6st_lexP13select_resultm + 274
0x8106ae2 _Z21mysql_execute_commandP3THD + 1628
0x810eea4 _Z11mysql_parseP3THDPcj + 484
0x8104d0d _Z16dispatch_command19enum_server_commandP3THDPcj + 1797
0x81045f8 _Z10do_commandP3THD + 518
0x8103785 handle_one_connection + 803
0x849e561 pthread_start_thread + 225
0x84d8e6a __clone + 106

Needed to check for subpartitioning in part of optimizer code in
get_part_iter_for_interval_via_walking

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

The fix has been pushed into 5.1.9 tree.

Notes for the changelog: 
The bug affected queries where
 * the table had partitions and sub-partitions.
 * the partitioning function depended on a single integer (or smallint, or mediumint, etc) table field.
 * the partitioning function was not a monotonic growing function. 
For such tables, the query that had condition "partition_field IS NULL" produced server crash in debug mode due to assertion failure.

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.9 changelog - closed.