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)
)
;