Description:
Hello,
Dear MySQL veification team, I find that MySQL validates timezone-dependent expressions incorrectly while creating a partitioned-table.
Please reference to `How to repeat` part for detail, which is tested with MySQL8.0.22.
Here are some problems about the testment:
(1) While creating table `t1`, column `insn_dt_tm` is used as partitioned-column, and it's timezone-dependent, so MySQL gives an error, right?
(2) While creating table `t2`, column `insn_dt_tm` is also used as partitioned-column, but default expression of column insn_dt_tm is designed as `default now()` instead of `default (now())` in `t1`, as a result I create `t2` successfully. It's very strange, right?
(3) While creating table `t3`, column `acg_dt` is used as partitioned-column, and partitioning has no business with column `acg_dt`, and as a result table `t3` is created successfully.
(4) While creating table `t4`, I change default expression of column `insn_dt_tm` from `default now()` to `default (now())` comparing with `t3`, as a result an error is given from MySQL. But partitioning has no business with column `acg_dt`, why an error is given?
So, I think problem (2) and problem (4) are needed to be consisdered.
How to repeat:
[yxxdb_8022@zxin21 ~]$
[yxxdb_8022@zxin21 ~]$ mysql --socket=/home/yxxdb_8022/bin/mysql1.sock -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.22 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database part;
Query OK, 1 row affected (0.01 sec)
mysql> use part;
Database changed
mysql>
mysql> create table t1
-> ( acg_dt char(8), insn_dt_tm datetime default (now()) )
-> partition by list columns (insn_dt_tm)
-> ( partition p1 values in ('20201111'), partition p2 values in ('20201112'));
ERROR 1064 (42000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ''20201111'), partition p2 values in ('20201112'))' at line 4
mysql>
mysql> create table t2
-> ( acg_dt char(8), insn_dt_tm datetime default now() )
-> partition by list columns (insn_dt_tm)
-> ( partition p1 values in ('20201111'), partition p2 values in ('20201112'));
Query OK, 0 rows affected (0.03 sec)
mysql> create table t3
-> ( acg_dt char(8), insn_dt_tm datetime default now() )
-> partition by list columns (acg_dt)
-> ( partition p1 values in ('20201111'), partition p2 values in ('20201112'));
Query OK, 0 rows affected (0.02 sec)
mysql> create table t4
-> ( acg_dt char(8), insn_dt_tm datetime default (now()) )
-> partition by list columns (acg_dt)
-> ( partition p1 values in ('20201111'), partition p2 values in ('20201112'));
ERROR 1064 (42000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ''20201111'), partition p2 values in ('20201112'))' at line 4
mysql>
Suggested fix:
nope
Description: Hello, Dear MySQL veification team, I find that MySQL validates timezone-dependent expressions incorrectly while creating a partitioned-table. Please reference to `How to repeat` part for detail, which is tested with MySQL8.0.22. Here are some problems about the testment: (1) While creating table `t1`, column `insn_dt_tm` is used as partitioned-column, and it's timezone-dependent, so MySQL gives an error, right? (2) While creating table `t2`, column `insn_dt_tm` is also used as partitioned-column, but default expression of column insn_dt_tm is designed as `default now()` instead of `default (now())` in `t1`, as a result I create `t2` successfully. It's very strange, right? (3) While creating table `t3`, column `acg_dt` is used as partitioned-column, and partitioning has no business with column `acg_dt`, and as a result table `t3` is created successfully. (4) While creating table `t4`, I change default expression of column `insn_dt_tm` from `default now()` to `default (now())` comparing with `t3`, as a result an error is given from MySQL. But partitioning has no business with column `acg_dt`, why an error is given? So, I think problem (2) and problem (4) are needed to be consisdered. How to repeat: [yxxdb_8022@zxin21 ~]$ [yxxdb_8022@zxin21 ~]$ mysql --socket=/home/yxxdb_8022/bin/mysql1.sock -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.22 Source distribution Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database part; Query OK, 1 row affected (0.01 sec) mysql> use part; Database changed mysql> mysql> create table t1 -> ( acg_dt char(8), insn_dt_tm datetime default (now()) ) -> partition by list columns (insn_dt_tm) -> ( partition p1 values in ('20201111'), partition p2 values in ('20201112')); ERROR 1064 (42000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ''20201111'), partition p2 values in ('20201112'))' at line 4 mysql> mysql> create table t2 -> ( acg_dt char(8), insn_dt_tm datetime default now() ) -> partition by list columns (insn_dt_tm) -> ( partition p1 values in ('20201111'), partition p2 values in ('20201112')); Query OK, 0 rows affected (0.03 sec) mysql> create table t3 -> ( acg_dt char(8), insn_dt_tm datetime default now() ) -> partition by list columns (acg_dt) -> ( partition p1 values in ('20201111'), partition p2 values in ('20201112')); Query OK, 0 rows affected (0.02 sec) mysql> create table t4 -> ( acg_dt char(8), insn_dt_tm datetime default (now()) ) -> partition by list columns (acg_dt) -> ( partition p1 values in ('20201111'), partition p2 values in ('20201112')); ERROR 1064 (42000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ''20201111'), partition p2 values in ('20201112'))' at line 4 mysql> Suggested fix: nope