| Bug #57071 | EXTRACT(WEEK from date_col) cannot be allowed as partitioning function | ||
|---|---|---|---|
| Submitted: | 28 Sep 2010 14:46 | Modified: | 10 Jan 2011 17:33 |
| Reporter: | Mattias Jonsson | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S1 (Critical) |
| Version: | 5.1+, 5.5.7-bzr | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[28 Sep 2010 16:24]
Valeriy Kravchuk
Verified also with current mysql-5.5 from bzr on Mac OS X:
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.7-rc-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE t1
-> (`date` date,
-> `extracted_week` int,
-> `yearweek` int,
-> `week` int,
-> `default_week_format` int)
-> PARTITION BY LIST (EXTRACT(WEEK FROM date) % 3)
-> (PARTITION p0 VALUES IN (0),
-> PARTITION p1 VALUES IN (1),
-> PARTITION p2 VALUES IN (2));
Query OK, 0 rows affected (0.15 sec)
mysql> SET @old_default_week_format := @@default_week_format;
Query OK, 0 rows affected (0.00 sec)
mysql> SET default_week_format = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'),
-> YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
Query OK, 1 row affected (0.00 sec)
mysql> SET default_week_format = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'),
-> YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
Query OK, 1 row affected (0.00 sec)
mysql> SET default_week_format = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'),
-> YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
Query OK, 1 row affected (0.55 sec)
mysql> SET default_week_format = 3;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'),
-> YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
Query OK, 1 row affected (0.00 sec)
mysql> SET default_week_format = 4;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'),
-> YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
Query OK, 1 row affected (0.00 sec)
mysql> SET default_week_format = 5;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'),
-> YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
Query OK, 1 row affected (0.02 sec)
mysql> SET default_week_format = 6;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'),
-> YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
Query OK, 1 row affected (0.00 sec)
mysql> SET default_week_format = 7;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'),
-> YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
Query OK, 1 row affected (0.00 sec)
mysql> SET default_week_format = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t1;
+------------+----------------+----------+------+---------------------+
| date | extracted_week | yearweek | week | default_week_format |
+------------+----------------+----------+------+---------------------+
| 2000-01-01 | 0 | 199952 | 0 | 0 |
| 2000-01-01 | 0 | 199952 | 0 | 1 |
| 2000-01-01 | 0 | 199952 | 0 | 4 |
| 2000-01-01 | 0 | 199952 | 0 | 5 |
| 2000-01-01 | 52 | 199952 | 52 | 2 |
| 2000-01-01 | 52 | 199952 | 52 | 3 |
| 2000-01-01 | 52 | 199952 | 52 | 6 |
| 2000-01-01 | 52 | 199952 | 52 | 7 |
+------------+----------------+----------+------+---------------------+
8 rows in set (0.01 sec)
mysql> SELECT * FROM t1 WHERE date = '2000-01-01';
+------------+----------------+----------+------+---------------------+
| date | extracted_week | yearweek | week | default_week_format |
+------------+----------------+----------+------+---------------------+
| 2000-01-01 | 0 | 199952 | 0 | 0 |
| 2000-01-01 | 0 | 199952 | 0 | 1 |
| 2000-01-01 | 0 | 199952 | 0 | 4 |
| 2000-01-01 | 0 | 199952 | 0 | 5 |
+------------+----------------+----------+------+---------------------+
4 rows in set (0.00 sec)
mysql> SET default_week_format = 2;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM t1 WHERE date = '2000-01-01';
+------------+----------------+----------+------+---------------------+
| date | extracted_week | yearweek | week | default_week_format |
+------------+----------------+----------+------+---------------------+
| 2000-01-01 | 52 | 199952 | 52 | 2 |
| 2000-01-01 | 52 | 199952 | 52 | 3 |
| 2000-01-01 | 52 | 199952 | 52 | 6 |
| 2000-01-01 | 52 | 199952 | 52 | 7 |
+------------+----------------+----------+------+---------------------+
4 rows in set (0.01 sec)
...
[20 Dec 2010 23:21]
Mattias Jonsson
related to bug#54483. Time/date functions needs to have correct arguments to be allowed as partitioning functions.
[22 Dec 2010 9:51]
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/127488 3530 Mattias Jonsson 2010-12-22 Bug#54483: valgrind errors when making warnings for multiline inserts into partition Bug#57071: EXTRACT(WEEK from date_col) cannot be allowed as partitioning function There were functions allowed as partitioning functions that implicit allowed cast. That could result in unacceptable behaviour. Solution was to check that the arguments of date and time functions have allowed types (field and date/datetime/time depending on function). @ mysql-test/r/partition.result Updated result @ mysql-test/r/partition_error.result Updated result @ mysql-test/suite/parts/inc/part_supported_sql_funcs_main.inc disabled test with not allowed arguments. @ mysql-test/suite/parts/r/part_supported_sql_func_innodb.result Updated result @ mysql-test/suite/parts/r/part_supported_sql_func_myisam.result Updated result @ mysql-test/t/partition.test Fixed typo in bug number and removed non allowed function (bad argument) @ mysql-test/t/partition_error.test Added tests to verify correct type of argument. @ sql/item.h Renamed processor since it is no longer only for timezone @ sql/item_func.h Added help functions for checking date/time/datetime arguments. @ sql/item_timefunc.h Added processors for argument correctness @ sql/sql_partition.cc renamed the processor for checking arguments.
[22 Dec 2010 14:46]
Mattias Jonsson
Changed to patch approved, since it is the same patch as bug#54483.
[22 Dec 2010 14:46]
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/127510 3531 Mattias Jonsson 2010-12-22 Bug#54483: valgrind errors when making warnings for multiline inserts into partition Bug#57071: EXTRACT(WEEK from date_col) cannot be allowed as partitioning function Renamed function according to reviewers comments. @ sql/item.h better name of processor function @ sql/item_func.h better name of processor function @ sql/item_timefunc.h better name of processor function @ sql/sql_partition.cc better name of processor function Updated comment.
[10 Jan 2011 16:49]
Bugs System
Pushed into mysql-trunk 5.6.2 (revid:mattias.jonsson@oracle.com-20110110163747-ssccpoh0wesjl68q) (version source revid:mattias.jonsson@oracle.com-20110110163747-ssccpoh0wesjl68q) (merge vers: 5.6.2) (pib:24)
[10 Jan 2011 16:49]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:mattias.jonsson@oracle.com-20110110160254-zodzbuutmr53468f) (version source revid:mattias.jonsson@oracle.com-20110110160254-zodzbuutmr53468f) (merge vers: 5.5.9) (pib:24)
[10 Jan 2011 16:50]
Bugs System
Pushed into mysql-5.1 5.1.55 (revid:mattias.jonsson@oracle.com-20110110115627-2bj4218kve40go1r) (version source revid:mattias.jonsson@oracle.com-20110110115627-2bj4218kve40go1r) (merge vers: 5.1.55) (pib:24)
[10 Jan 2011 17:33]
Mattias Jonsson
Closing as duplicate of bug#54483.
Description: EXTRACT(WEEK from date_col) is currently an allowed partitioning function, but it depends on default_week_format session variable, which breaks partitioning. How to repeat: CREATE TABLE t1 (`date` date, `extracted_week` int, `yearweek` int, `week` int, `default_week_format` int) PARTITION BY LIST (EXTRACT(WEEK FROM date) % 3) (PARTITION p0 VALUES IN (0), PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); SET @old_default_week_format := @@default_week_format; SET default_week_format = 0; INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format); SET default_week_format = 1; INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format); SET default_week_format = 2; INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format); SET default_week_format = 3; INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format); SET default_week_format = 4; INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format); SET default_week_format = 5; INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format); SET default_week_format = 6; INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format); SET default_week_format = 7; INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format); SET default_week_format = 1; SELECT * FROM t1; date extracted_week yearweek week default_week_format 2000-01-01 0 199952 0 0 2000-01-01 0 199952 0 1 2000-01-01 0 199952 0 4 2000-01-01 0 199952 0 5 2000-01-01 52 199952 52 2 2000-01-01 52 199952 52 3 2000-01-01 52 199952 52 6 2000-01-01 52 199952 52 7 # This does not include all rows! SELECT * FROM t1 WHERE date = '2000-01-01'; date extracted_week yearweek week default_week_format 2000-01-01 0 199952 0 0 2000-01-01 0 199952 0 1 2000-01-01 0 199952 0 4 2000-01-01 0 199952 0 5 SET default_week_format = 2; # This does not include all rows! SELECT * FROM t1 WHERE date = '2000-01-01'; date extracted_week yearweek week default_week_format 2000-01-01 52 199952 52 2 2000-01-01 52 199952 52 3 2000-01-01 52 199952 52 6 2000-01-01 52 199952 52 7 SELECT count(*) FROM t1; count(*) 8 # This should be the same SELECT count(*) FROM t1 WHERE date = '2000-01-01'; count(*) 4 SET default_week_format = @old_default_week_format; DROP TABLE t1; Suggested fix: Disable EXTRACT(WEEK from date_col) if possible, otherwise disable EXTRACT completely. Note that YEARWEEK is not dependent of default_week_format and can still be allowed as a partitioning function! (related to bug#30454).