Cannot create LIST partition on data type other than INT

[iShiBin]

Hi @michaljuhas

I am now doing 'S04-P01-Practice'. However, I got error when I want to create a LIST partition. Here is my code and the error message. Could you please help?

CREATE TABLE `sample_staff`.`invoice_partitioned` AS SELECT * FROM `sample_staff`.`invoice`;
ALTER TABLE invoice_partitioned ADD COLUMN department_code VARCHAR(35); 
ALTER TABLE `sample_staff`.`invoice_partitioned`  REMOVE PARTITIONING ;

UPDATE invoice_partitioned,
    department_employee_rel,
    department 
SET 
    invoice_partitioned.department_code = department.code
WHERE
    invoice_partitioned.employee_id = department_employee_rel.employee_id
        AND department_employee_rel.department_id = department.id
        AND invoice_partitioned.invoiced_date BETWEEN department_employee_rel.from_date AND department_employee_rel.to_date
;

ALTER TABLE invoice_partitioned
partition BY LIST(department_code) (
partition CS	values in ("CS"),
partition	DEV	values in ("DEV"),
partition	FIN	values in ("FIN"),
partition	HR	values in ("HR"),
partition	MKT	values in ("MKT"),
partition	PROD	values in ("PROD"),
partition	QA	values in ("QA"),
partition	RES	values in ("RES"),
partition	SAL	values in ("SAL")
)
;

Error message: Error Code: 1697. VALUES value for partition 'CS' must have type INT

[iShiBin]

I even add the partition for the null value, but it does not work.

ALTER TABLE invoice_partitioned
partition BY LIST(department_code) (
partition CS	values in ('CS'),
partition DEV	values in ('DEV'),
partition	FIN	values in ('FIN'),
partition	HR	values in ('HR'),
partition	MKT	values in ('MKT'),
partition	PROD	values in ('PROD'),
partition	QA	values in ('QA'),
partition	RES	values in ('RES'),
partition	SAL	values in ('SAL'),
PARTITION NUL values in (null)
)
;

[iShiBin]

I think it should work but unfortunately it has not worked. So instead, I add another INT column to make it work.

ALTER TABLE invoice_partitioned ADD COLUMN department_id INT;

UPDATE invoice_partitioned,
    department_employee_rel 
SET 
    invoice_partitioned.department_id = department_employee_rel.department_id
WHERE
    invoice_partitioned.employee_id = department_employee_rel.employee_id
        AND invoice_partitioned.invoiced_date BETWEEN department_employee_rel.from_date AND department_employee_rel.to_date
;

ALTER TABLE invoice_partitioned
partition BY LIST(department_id) (
partition	CS	values in (	1	),
partition	DEV	values in (	2	),
partition	FIN	values in (	3	),
partition	HR	values in (	4	),
partition	MKT	values in (	5	),
partition	PROD	values in (	6	),
partition	QA	values in (	7	),
partition	RES	values in (	8	),
partition	SAL	values in (	9	),
partition NUL values in (null)
)
;