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:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1+, 5.5.7-bzr OS:Any
Assigned to: Assigned Account
Triage: Triaged: D2 (Serious)

[28 Sep 2010 14:46] Mattias Jonsson
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).
[28 Sep 2010 16:24] Valerii 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.