Bug #18025 Partitions: Alter col. used for part. function, SELECT with wrong result set
Submitted: 7 Mar 2006 12:35 Modified: 2 Apr 2006 6:51
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1 OS:
Assigned to: Sergey Petrunya CPU Architecture:Any

[7 Mar 2006 12:35] Matthias Leich
Description:
CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER)
PARTITION BY LIST(MOD(f_int1,4))
(PARTITION part_3 VALUES IN (-3),
PARTITION part_2 VALUES IN (-2),
PARTITION part_1 VALUES IN (-1),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2),
PARTITION part3 VALUES IN (3));
INSERT INTO t1 SET f_int1 = 9, f_int2 = 9;
INSERT INTO t1 SET f_int1 = 8, f_int2 = 8;
INSERT INTO t1 SET f_int1 = 7, f_int2 = 7;
INSERT INTO t1 SET f_int1 = 6, f_int2 = 6;
INSERT INTO t1 SET f_int1 = 5, f_int2 = 5;
INSERT INTO t1 SET f_int1 = 4, f_int2 = 4;
INSERT INTO t1 SET f_int1 = 3, f_int2 = 3;
INSERT INTO t1 SET f_int1 = 2, f_int2 = 2;
INSERT INTO t1 SET f_int1 = 1, f_int2 = 1;
# This result set is correct.
SELECT * FROM t1 WHERE f_int1 BETWEEN 5 AND 15 ORDER by f_int1;
f_int1  f_int2
5       5
6       6
7       7
8       8
9       9
# This statement leads to the corruption.
ALTER TABLE t1 MODIFY f_int1 MEDIUMINT;
# SELECT with qualification for column used in part. function
# --> wrong number of rows
SELECT * FROM t1 WHERE f_int1 BETWEEN 5 AND 15 ORDER by f_int1;
f_int1  f_int2
5       5
9       9
# SELECT with qualification for column not used in part. function
# --> correct result set
SELECT * FROM t1 WHERE f_int2 BETWEEN 5 AND 15 ORDER by f_int1;
f_int1  f_int2
5       5
6       6
7       7
8       8
9       9

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

How to repeat:
Please use my attached testscript ml1114.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml1114.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml1114
[7 Mar 2006 12:37] Matthias Leich
testscript

Attachment: ml1114.test (application/test, text), 1.29 KiB.

[30 Mar 2006 23:15] Sergey Petrunya
Matthias, I won't say that that is "corruption" - it is reproduceable without ALTER TABLE too, when one creates table with MEDIUMINT column.
[30 Mar 2006 23:16] Sergey Petrunya
The bug seems to be two separate problems in partition pruning code:
  * Inconsistent handling of MEDIUMINT columns wrt. TINYINT/INT
  * A bug in partition pruning algorithm
[30 Mar 2006 23:39] 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/4349
[31 Mar 2006 11:11] 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/4362
[31 Mar 2006 20:16] Sergey Petrunya
The fix has been pushed to 5.1.9.
The fix makes the following changes:
 * Now partition pruning works for MEDIUMINT columns in the same way as for other *INT columns.
 * Fixed a bug in partition pruning code that could cause some partitions that should be used not to be used for certain kinds of WHERE clauses
[2 Apr 2006 6:51] Jon Stephens
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 fixes in 5.1.9 changelog. Closed.