MySQL Bugs: #107214: ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR is misreported while creating a table

Bug #107214	ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR is misreported while creating a table
Submitted:	5 May 2022 7:12	Modified:	5 May 2022 12:01
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any (x86-64)

Description:
Hello,
  Recently I find a case that a common field of datetime type with `default NOW()` attribute will cause the create table SQL get an error "ERROR 1064 (42000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near 'month(TXN_DT) )( partition p0 values less than (202201) )' at line 1".

  But actually the field has no business with partitioning function:

mysql> create table if not exists channel_journal_test ( `txn_dt` date primary key, `rcrd_rgtm` datetime(3) default (now()) ) partition by range (month(TXN_DT) )( partition p0 values less than (202201) );
ERROR 1064 (42000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near 'month(TXN_DT) )( partition p0 values less than (202201) )' at line 1

  After `default NOW()` attribute is removed, the SQL is executed successfully:

mysql> create table if not exists channel_journal_test ( `txn_dt` date primary key, `rcrd_rgtm` datetime(3)  ) partition by range (month(TXN_DT) )( partition p0 values less than (202201) );
Query OK, 0 rows affected (0.05 sec)

  Now that field `rcrd_rgtm` has no business with partitioning function, I think it's a misreport.

How to repeat:
mysql> create database if not exists test;
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> use test
Database changed
mysql>
mysql> create table if not exists channel_journal_test ( `txn_dt` date primary key, `rcrd_rgtm` datetime(3) default (now()) ) partition by range (month(TXN_DT) )( partition p0 values less than (202201) );
ERROR 1064 (42000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near 'month(TXN_DT) )( partition p0 values less than (202201) )' at line 1
mysql>
mysql>
mysql> create table if not exists channel_journal_test ( `txn_dt` date primary key, `rcrd_rgtm` datetime(3)  ) partition by range (month(TXN_DT) )( partition p0 values less than (202201) );
Query OK, 0 rows affected (0.05 sec)

Hello Brian Yue,

Thank you for the report and test case.
This issue seems to be no longer exists, looking at the change log this issue was fixed in 8.0.28. More details here under "Partitioning". Please see https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html

-
- 8.0.28

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table if not exists channel_journal_test ( `txn_dt` date primary key, `rcrd_rgtm` datetime(3) default (now()) ) partition by range (month(TXN_DT) )( partition p0 values less than (202201) );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table channel_journal_test\G
*************************** 1. row ***************************
       Table: channel_journal_test
Create Table: CREATE TABLE `channel_journal_test` (
  `txn_dt` date NOT NULL,
  `rcrd_rgtm` datetime(3) DEFAULT (now()),
  PRIMARY KEY (`txn_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (month(`txn_dt`))
(PARTITION p0 VALUES LESS THAN (202201) ENGINE = InnoDB) */
1 row in set (0.00 sec)

- 8.0.29

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table if not exists channel_journal_test ( `txn_dt` date primary key, `rcrd_rgtm` datetime(3) default (now()) ) partition by range (month(TXN_DT) )( partition p0 values less than (202201) );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table channel_journal_test\G
*************************** 1. row ***************************
       Table: channel_journal_test
Create Table: CREATE TABLE `channel_journal_test` (
  `txn_dt` date NOT NULL,
  `rcrd_rgtm` datetime(3) DEFAULT (now()),
  PRIMARY KEY (`txn_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (month(`txn_dt`))
(PARTITION p0 VALUES LESS THAN (202201) ENGINE = InnoDB) */
1 row in set (0.00 sec)

regards,
Umesh