| 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: | |
| 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 | ||
[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.
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.