Bug #50036 Inconsistent errors when using TIMESTAMP columns/expressions

• Submitted: 3 Jan 2010 13:49
• Modified: 16 Nov 2010 13:40
• Reporter: Jon Stephens
• Status: Closed
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.5.1-m2-bzr
• OS: Linux (OpenSUSE11.1/64)
• Assigned to: Mattias Jonsson
• CPU Architecture: Any
• Tags: errors, partitioning, timestamp

[3 Jan 2010 13:49] Jon Stephens

Description:
Different CREATE TABLE statements trying to use TIMESTAMP columns or expressions based on these give inconsistent and sometimes unhelpful errors.

Note that the failure of the statements is correct and not in question here, only the errors.

How to repeat:
1. These errors are IMO correct and appropriate in light of the fix for BUG#42849:

mysql> CREATE TABLE tsr (c TIMESTAMP)
    -> PARTITION BY RANGE (TO_DAYS(c)) (
    -> PARTITION p0 VALUES LESS THAN (10000),
    -> PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

mysql> CREATE TABLE ts (c TIMESTAMP);
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE ts PARTITION BY RANGE COLUMNS(c)  (
    ->     PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    ->     );
ERROR 1659 (HY000): Field 'c' is of a not allowed type for this type of partitioning

2. These errors are questionable:

mysql> CREATE TABLE tsr (c TIMESTAMP) PARTITION BY RANGE (c) ( PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'), PARTITION p1 VALUES LESS THAN (MAXVALUE) );
ERROR 1494 (HY000): VALUES value must be of same type as partition function

mysql> CREATE TABLE ts (c TIMESTAMP)
    -> PARTITION BY HASH (c) PARTITIONS 4;
ERROR 1564 (HY000): This partition function is not allowed

Suggested fix:
For both cases in (2) under 'How to repeat', ERROR 1659 would provide a much more appropriate response.

It might also be good to have a separate error for TIMESTAMP-related expressions highlighting the fact that in 5.5.1+ they can be used only with UNIX_TIMESTAMP().

[3 Jan 2010 23:02] MySQL Verification Team

Thank you for the bug report. Verified as described;

miguel@tikal-vm:~$ dbs/5.5/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.2-m2-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE tsr (c TIMESTAMP)
    ->  PARTITION BY RANGE (TO_DAYS(c)) (
    -> PARTITION p0 VALUES LESS THAN (10000),
    -> PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql> CREATE TABLE ts (c TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE ts PARTITION BY RANGE COLUMNS(c)  (
    ->  PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'),
    ->  PARTITION p1 VALUES LESS THAN (MAXVALUE)
    ->  );
ERROR 1659 (HY000): Field 'c' is of a not allowed type for this type of partitioning
mysql> CREATE TABLE tsr (c TIMESTAMP) PARTITION BY RANGE (c) ( PARTITION p0 VALUES LESS
    -> THAN ('2000-01-01 00:00:00'), PARTITION p1 VALUES LESS THAN (MAXVALUE) );
ERROR 1494 (HY000): VALUES value must be of same type as partition function
mysql> CREATE TABLE ts (c TIMESTAMP)
    -> PARTITION BY HASH (c) PARTITIONS 4;
ERROR 1050 (42S01): Table 'ts' already exists
mysql> drop table ts;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE ts (c TIMESTAMP) PARTITION BY HASH (c) PARTITIONS 4;
ERROR 1564 (HY000): This partition function is not allowed
mysql>

[30 Aug 2010 15:34] 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/117150

[1 Oct 2010 19:52] Mattias Jonsson

Pushed into mysql-5.5-bugteam and mysql-trunk-merge.

[4 Oct 2010 12:57] Jon Stephens

Waiting for merge into mainline tree(s).

[9 Nov 2010 19:47] Bugs System

Pushed into mysql-5.5 5.5.7-rc (revid:[e-mail]-20101109182959-otkxq8vo2dcd13la) (version source revid:[e-mail]-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

[13 Nov 2010 16:14] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

[13 Nov 2010 16:38] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20101113160336-atmtmfb3mzm4pz4i) (version source revid:[e-mail]-20100629074804-359l9m9gniauxr94) (pib:21)

[16 Nov 2010 13:40] Jon Stephens

No new changelog entries required. Returning to Closed state.