Bug #17432 Partitions: wrong result, SELECT ... where <column> is null
Submitted: 15 Feb 2006 19:15 Modified: 27 Feb 2006 20:46
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1 OS:
Assigned to: Reggie Burnett CPU Architecture:Any

[15 Feb 2006 19:15] Matthias Leich
Description:
SET AUTOCOMMIT= 1;
set @@session.sql_mode= '';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
f_int1 INTEGER, f_int2 INTEGER,
f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000)
)
PARTITION BY RANGE(f_int1 DIV 2) 
SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2 (
PARTITION parta VALUES LESS THAN (0),
PARTITION partb VALUES LESS THAN (5),
PARTITION parte VALUES LESS THAN (10),
PARTITION partf VALUES LESS THAN (2147483647));
INSERT INTO t1 SET f_int1 = NULL , f_int2 = -20, f_char1 = CAST(-20 AS CHAR),
f_char2 = CAST(-20 AS CHAR), f_charbig = '#NULL#';
SELECT * FROM t1 WHERE f_int1 IS NULL;
f_int1	f_int2	f_char1	f_char2	f_charbig
   <---- Where is the just inserted record ?
SELECT * FROM t1;
f_int1	f_int2	f_char1	f_char2	f_charbig
NULL	-20	-20	-20	#NULL#

I set Severity to 1, because there is no sensible workaround for
SELECT ... WHERE <column> IS NULL .
There are several other open bugs, which fiddle with NULL values.
I am unsure if this bug is maybe a duplucate of them,
because they show crashes and/or use other partitioning methods.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
         Version 5.1 last ChangeSet@1.2108, 2006-02-15
   Please note, that this source contains the fixes for the
   crashing bugs #16901 and #16907, which improved much 
   but did not help here.

How to repeat:
Please execute the statements above or use my attached 
testscript ml101.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml101.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml101
[15 Feb 2006 19:16] Matthias Leich
testcase

Attachment: ml101.test (application/test, text), 743 bytes.

[24 Feb 2006 17:30] 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/3119
[24 Feb 2006 17:30] Reggie Burnett
Fixed in 5.1.7
[27 Feb 2006 20:46] Mike Hillyer
Documented in 5.1.7 changelog:

  <listitem>
        <para>
          Partitioning with certain <literal>SUBPARTITION BY
            HASH</literal> clauses caused error when querying for
          partitioned column with <literal>IS NULL</literal> check. (Bug
          #17430, Bug #17432)
        </para>
      </listitem>