| 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: | |
| Category: | MySQL Server: Partitions | Severity: | S1 (Critical) |
| Version: | 5.1 | OS: | |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[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>
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