Bug #47838 TABLE partitioned by LIST COLUMN_LIST not processing <=, >= properly
Submitted: 5 Oct 2009 17:42 Modified: 12 Nov 2009 12:05
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5 WL#3352 OS:Any
Assigned to: Mikael Ronström CPU Architecture:Any
Tags: LIST COLUMN_LIST, missing data, partitioning
Triage: Triaged: D2 (Serious)

[5 Oct 2009 17:42] Patrick Crews
Description:
A table using LIST COLUMN LIST partitioning is not recognizing data:

This table:
CREATE TABLE `PP_E` (
`date_key` date,
`varchar_10_key` varchar(10),
`datetime_key` datetime,
`varchar_5` varchar(5),
`int_signed` int signed,
`date` date,
`datetime` datetime,
`varchar_5_key` varchar(5),
`int_signed_key` int signed,
`varchar_10` varchar(10),
key (`date_key` ),key (`varchar_10_key` ),key (`datetime_key` ),key (`varchar_5_key` ),key (`int_signed_key` )) ENGINE=myisam /*!50100 PARTITION BY list column_list(int_signed)subpartition by linear hash( to_seconds(`date`))subpartitions 4 (
partition p0 values in (column_list(1),column_list(3),
column_list(5),column_list(7),
column_list(9),column_list(null)),
partition p1 values in (column_list(2),column_list(4),
column_list(6),column_list(8),
column_list(0))) */;

Is failing on this query:
SELECT COUNT(*) FROM PP_E WHERE int_signed <= 1;
COUNT(*)
1

Despite having this data:
SELECT int_signed FROM PP_E ORDER BY int_signed;
int_signed
NULL
0
1
2
2
4
4
4
8
8

It appears to not recognize the '0' row as being <= 1....
SELECT int_signed FROM PP_E WHERE int_signed <= 1;
int_signed
1

But the table *does* not it is in there...
SELECT COUNT(*) FROM PP_E WHERE int_signed = 0;
COUNT(*)
1

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS PP_E;
--enable_warnings 

CREATE TABLE `PP_E` (
`date_key` date,
`varchar_10_key` varchar(10),
`datetime_key` datetime,
`varchar_5` varchar(5),
`int_signed` int signed,
`date` date,
`datetime` datetime,
`varchar_5_key` varchar(5),
`int_signed_key` int signed,
`varchar_10` varchar(10),
        key (`date_key` ),
key (`varchar_10_key` ),
key (`datetime_key` ),
key (`varchar_5_key` ),
key (`int_signed_key` )) ENGINE=myisam /*!50100 PARTITION BY list column_list(int_signed)
                           subpartition by linear hash( to_seconds(`date`))
                           subpartitions 4 (
                           partition p0 values in (column_list(1),column_list(3),
                                                  column_list(5),column_list(7),
                                                  column_list(9),column_list(null)),
                           partition p1 values in (column_list(2),column_list(4),
                                                  column_list(6),column_list(8),
                                                  column_list(0))) */;

--disable_warnings
INSERT IGNORE INTO PP_E VALUES  (NULL, 'v', '2002-09-15 04:02:20', 'x', '1', '2007-02-21 22:14:13', '2002', 'g', '0', 'b') ,  ('20050714182248', 'g', '23:17:13', 'l', '4', '2002', '2008', NULL, '4', NULL) ,  ('14:04:40', 's', '2006', 'z', '0', NULL, '2009-01-28 19:31:57', NULL, '8', 'w') ,  ('2000-02-22', 'k', '2004', 'n', '2', '2005-09-16', '2000-08-07', NULL, NULL, 'm') ,  ('2006', 'd', '17:13:33', 'g', '2', '2005-01-21', '23:10:36', 'z', '4', 'b') ,  ('20010215094012', NULL, '2001-12-04 21:07:12', 't', '4', '2009', NULL, 'f', '2', NULL) ,  ('20:11:44', NULL, '21:07:20', 's', '4', '20001202111601', '20080716233638', NULL, '2', 'b') ,  ('2008-11-26 03:23:12', 'a', '2000-03-04', 'u', '8', '2007-12-17 02:42:44', '18:40:59', NULL, NULL, 'o') ,  ('2000-08-08 21:53:44', 'u', '06:14:50', 'g', '8', NULL, '20000314224023', 'y', '4', 'j') ,  ('23:02:39', 'q', '09:16:06', 'p', NULL, NULL, '2002-05-14', 'h', '3', 'b');
--enable_warnings

SELECT COUNT(*) FROM PP_E WHERE int_signed <= 1;
SELECT int_signed FROM PP_E ORDER BY int_signed;
SELECT int_signed FROM PP_E WHERE int_signed <= 1;
SELECT COUNT(*) FROM PP_E WHERE int_signed = 0;

# This failing UPDATE is what initial alerted me to the bug
--echo pre-UPDATE-check
SELECT COUNT(*) FROM PP_E WHERE int_signed <= 1;
SELECT COUNT(*) FROM PP_E WHERE int_signed = 8;
UPDATE `PP_E` SET `int_signed` = 8 WHERE ( `PP_E` . `int_signed` <= 1 ) ;

--echo post-UPDATE-check
SELECT COUNT(*) FROM PP_E WHERE int_signed <= 1;
SELECT COUNT(*) FROM PP_E WHERE int_signed = 8;

DROP TABLE PP_E;

Suggested fix:
Ensure that partitioned tables correctly process queries / that they process range predicates correctly.
[5 Oct 2009 17:59] Patrick Crews
NOTE / UPDATE:
It appears that the server is not processing ranges defined as >= <value> or <= <value> properly.

If we change <= 1 into >-1 AND <2, the query will process properly.
[6 Oct 2009 14:24] 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/85923

2886 Mikael Ronstrom	2009-10-06
      BUG#47838, NULL values in ranges was dropped due to missing else part in store_tuple_to_record, added more tests
      modified:
        mysql-test/r/partition_column.result
[6 Oct 2009 14:25] 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/85924

2885 Mikael Ronstrom	2009-10-06
      BUG#47838, NULL values in ranges was dropped due to missing else part in store_tuple_to_record
      modified:
        mysql-test/r/partition_column.result
        mysql-test/t/partition_column.test
        sql/sql_partition.cc
[6 Oct 2009 14:25] Mikael Ronström
Missing else part in assign set_null and set_notnull made all
ranges use set_notnull, problem was also causing issues for range
partitioned tables using column list partitioning.
[5 Nov 2009 6:51] Bugs System
Pushed into 6.0.14-alpha (revid:mikael@mysql.com-20091104090210-om5lq1v39ppduu0e) (version source revid:mikael@mysql.com-20091030163450-387z4yevx0lrj3fb) (merge vers: 6.0.14-alpha) (pib:13)
[5 Nov 2009 17:38] Jon Stephens
Discussed with Mikael; he confirmed that this fix was pushed together with main push for WL#3352; closed without further action.
[12 Nov 2009 8:17] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091028172236-0v5j962mh2opxpkj) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 12:05] Jon Stephens
Re-closed; see previous comments.