[MDEV-29270] ALTER TABLE PARTITION for RANGE UNIX_TIMESTAMP(COLUMN_NAME) doesn't work Created: 2022-08-08  Updated: 2022-08-23  Resolved: 2022-08-08

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.3, 10.4, 10.5, 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Pramod Mahto Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 1
Labels: None


 Description   

Currently MariaDB don't support ALTER TABLE PARTITION for RANGE UNIX_TIMESTAMP(COLUMN_NAME)

Reference :- https://mariadb.com/kb/en/unix_timestamp/

For Example :- 

 
 
CREATE TABLE `test_partitioned` (
 
  `id` bigint NOT NULL AUTO_INCREMENT,
 
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 
  `local_time` timestamp NULL DEFAULT NULL,
 
  `resident_id` int NOT NULL,
 
  KEY `Index 1` (`id`),
 
  KEY `Index 2` (`resident_id`,`local_time`),
 
  KEY `Index 3` (`created_at`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
/*!50100 PARTITION BY RANGE (unix_timestamp(`created_at`))
 
(PARTITION p20220806 VALUES LESS THAN (1659744000) ENGINE = InnoDB,
 
 PARTITION p20220807 VALUES LESS THAN (1659830400) ENGINE = InnoDB,
 
 PARTITION p20220808 VALUES LESS THAN (1659916800) ENGINE = InnoDB,
 
 PARTITION p20220809 VALUES LESS THAN (1660003200) ENGINE = InnoDB,
 
 PARTITION p20220810 VALUES LESS THAN (1660089600) ENGINE = InnoDB,
 
 PARTITION p20220811 VALUES LESS THAN (1660176000) ENGINE = InnoDB,
 
 PARTITION p20220812 VALUES LESS THAN (1660262400) ENGINE = InnoDB,
 
 PARTITION p20220813 VALUES LESS THAN (1660348800) ENGINE = InnoDB,
 
 PARTITION p20220814 VALUES LESS THAN (1660435200) ENGINE = InnoDB,
 
 PARTITION p20220815 VALUES LESS THAN (1660521600) ENGINE = InnoDB,
 
 PARTITION p20220816 VALUES LESS THAN (1660608000) ENGINE = InnoDB,
 
 PARTITION p20220817 VALUES LESS THAN (1660694400) ENGINE = InnoDB,
 
 PARTITION p20220818 VALUES LESS THAN (1660780800) ENGINE = InnoDB,
 
 PARTITION p20220819 VALUES LESS THAN (1660867200) ENGINE = InnoDB,
 
 PARTITION p20220820 VALUES LESS THAN (1660953600) ENGINE = InnoDB,
 
 PARTITION p20220821 VALUES LESS THAN (1661040000) ENGINE = InnoDB,
 
 PARTITION p20220822 VALUES LESS THAN (1661126400) ENGINE = InnoDB,
 
 PARTITION p20220823 VALUES LESS THAN (1661212800) ENGINE = InnoDB,
 
 PARTITION p20220824 VALUES LESS THAN (1661299200) ENGINE = InnoDB,
 
 PARTITION p20220825 VALUES LESS THAN (1661385600) ENGINE = InnoDB,
 
 PARTITION p20220826 VALUES LESS THAN (1661472000) ENGINE = InnoDB,
 
 PARTITION p20220827 VALUES LESS THAN (1661558400) ENGINE = InnoDB,
 
 PARTITION p20220828 VALUES LESS THAN (1661644800) ENGINE = InnoDB,
 
 PARTITION p20220829 VALUES LESS THAN (1661731200) ENGINE = InnoDB,
 
 PARTITION p20220830 VALUES LESS THAN (1661817600) ENGINE = InnoDB,
 
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
 



 
 
ALTER TABLE test_partitioned REORGANIZE PARTITION future INTO (   PARTITION p20220901 VALUES
 
LESS THAN (UNIX_TIMESTAMP('2022–09–02 00:00:00')),    PARTITION future VALUES LESS THAN MAXVALUE );
 



 
 
ERROR 1697 (HY000): VALUES value for partition 'p20220901' must have type INT

During create table UNIX_TIMESTAMP('2022-09-02 00:00:00') will be converted into int, before the int check will be done. Maybe in the alter statement it is different.... so using the int in alter is a workaround.

SELECT UNIX_TIMESTAMP('2022-09-02 00:00:00');
 
 
 
+---------------------------------------+
 
| UNIX_TIMESTAMP('2022-09-02 00:00:00') |
 
+---------------------------------------+
 
|                            1662091200 |
 
+---------------------------------------+
 
1 row in set (0.000 sec)

Using the above value in below alter command ... 

 
 
ALTER TABLE test_partitioned REORGANIZE PARTITION future INTO ( PARTITION p20220901 VALUES
 
LESS THAN (1662091200), PARTITION future VALUES LESS THAN MAXVALUE );
 
Query OK, 0 rows affected (0.036 sec)
 
Records: 0  Duplicates: 0  Warnings: 0



 Comments   
Comment by Alice Sherepa [ 2022-08-08 ]

KB says: "As you can see in the examples above, UNIX_TIMESTAMP(constant-date-string) returns a timestamp with 6 decimals while MariaDB 5.2 and before returns it without decimals. This can cause a problem if you are using UNIX_TIMESTAMP() as a partitioning function. You can fix this by using FLOOR(UNIX_TIMESTAMP(..)) or changing the date string to a date number, like 20080101000000."
so in the example:

MariaDB [test]> ALTER TABLE test_partitioned REORGANIZE PARTITION future INTO ( PARTITION p20220902 VALUES LESS THAN (floor(UNIX_TIMESTAMP('2022-09-02 00:00:00'))),    PARTITION future VALUES LESS THAN MAXVALUE );
 
Query OK, 0 rows affected (0.166 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

Comment by Pramod Mahto [ 2022-08-08 ]

alice I don't see any difference using floor(UNIX_TIMESTAMP('2022-09-02 00:00:00')) and UNIX_TIMESTAMP('2022-09-02 00:00:00') , its value remain same so I believe both workaround are fine. But is this what we need to do that while using a UNIX_TIMESTAMP ? not a bug ? 

ALTER TABLE test_partitioned REORGANIZE PARTITION future INTO (   PARTITION p20220901 VALUES
 
LESS THAN (UNIX_TIMESTAMP('2022–09–02 00:00:00')),    PARTITION future VALUES LESS THAN MAXVALUE );
 
ERROR 1697 (HY000): VALUES value for partition 'p20220901' must have type INT


MariaDB [test]> select floor(UNIX_TIMESTAMP('2022-09-02 00:00:00'));
 
+----------------------------------------------+
 
| floor(UNIX_TIMESTAMP('2022-09-02 00:00:00')) |
 
+----------------------------------------------+
 
|                                   1662091200 |
 
+----------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> SELECT UNIX_TIMESTAMP('2022-09-02 00:00:00');
 
+---------------------------------------+
 
| UNIX_TIMESTAMP('2022-09-02 00:00:00') |
 
+---------------------------------------+
 
|                            1662091200 |
 
+---------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> ALTER TABLE test_partitioned REORGANIZE PARTITION future INTO ( PARTITION p20220902 VALUES LESS THAN (floor(UNIX_TIMESTAMP('2022-09-02 00:00:00'))),    PARTITION future VALUES LESS THAN MAXVALUE );
 
Query OK, 0 rows affected (0.166 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

Comment by Sergei Golubchik [ 2022-08-08 ]

Indeed, you can use

ALTER TABLE test_partitioned REORGANIZE PARTITION future INTO ( PARTITION p20220902 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-02 00:00:00')), PARTITION future VALUES LESS THAN MAXVALUE );

The server would see that the datetime literal has no fractional second part and will return an integer. Can be verified with

MariaDB [test]> create table t1 as select UNIX_TIMESTAMP('2022-09-02 00:00:00') as x;
 
Query OK, 1 row affected (0.040 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show create table t1;
 
+-------+------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                             |
 
+-------+------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `x` bigint(17) DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
 
+-------+------------------------------------------------------------------------------------------+

But it only works in case of a valid datetime literal. Your test case from the bug description uses not ascii dash 0x2d - character, but an EN DASH, Unicode U+2013 character:

MariaDB [test]> select unix_timestamp('2022–09–02 00:00:00');
 
+-------------------------------------------+
 
| unix_timestamp('2022–09–02 00:00:00')     |
 
+-------------------------------------------+
 
|                                      NULL |
 
+-------------------------------------------+
 
1 row in set, 1 warning (0.001 sec)
 
 
 
Warning (Code 1292): Incorrect datetime value: '2022\xE2\x80\x9309\xE2\x80\x9302 00:00:00'

Generated at Thu Feb 08 10:07:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.