ERROR 1492 (HY000): For RANGE partitions each partition must be defined

Question

I'm trying to create a partitioned table but I'm getting the error message

ERROR 1492 (HY000): For RANGE partitions each partition must be defined

Am I missing something here. The query looks like this:

CREATE TABLE IF NOT EXISTS logdata (
    `timestamp` datetime NOT NULL,
    `device_id` int(11) NOT NULL,
    `row_id` int(11) NOT NULL,
    `value_0` DECIMAL(27,9) DEFAULT NULL, 
    `value_1` DECIMAL(27,9) DEFAULT NULL, 
    `value_2` DECIMAL(27,9) DEFAULT NULL, 
    `value_3` DECIMAL(27,9) DEFAULT NULL, 
    `value_4` DECIMAL(27,9) DEFAULT NULL, 
    `value_5` DECIMAL(27,9) DEFAULT NULL, 
    `value_6` DECIMAL(27,9) DEFAULT NULL, 
    `value_7` DECIMAL(27,9) DEFAULT NULL, 
    `value_8` DECIMAL(27,9) DEFAULT NULL, 
    `value_9` DECIMAL(27,9) DEFAULT NULL,
    PRIMARY KEY (`timestamp`,`device_id`,`row_id`),
    KEY `device_id_1_202004` (`device_id`),
    KEY `row_id_1_202004` (`row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED 
PARTITION BY RANGE(TO_DAYS(`timestamp`)) 
SUBPARTITION BY HASH (`device_id`) PARTITIONS 2 
(
    PARTITION p2001xx VALUES LESS THAN (TO_DAYS('2001-01-01 00:00:00')) (
        SUBPARTITION s2001xxa, 
        SUBPARTITION s2001xxb
    ),
    PARTITION p202004 VALUES LESS THAN (TO_DAYS('2020-04-01 00:00:00')) (
        SUBPARTITION s202004a, 
        SUBPARTITION s202004b
    ),
    PARTITION pMax VALUES LESS THAN MAXVALUE (
        SUBPARTITION smaxa, 
        SUBPARTITION smaxb
    )
);

Answer 1

If you specify the number of subpartitions, then use SUBPARTITIONS 2, not PARTITIONS 2:

...
PARTITION BY RANGE(TO_DAYS(`timestamp`)) 
SUBPARTITION BY HASH (`device_id`) SUBPARTITIONS 2 
(
...

If you name the subpartitions explicitly, then you don't strictly need the SUBPARTITIONS 2:

...
PARTITION BY RANGE(TO_DAYS(`timestamp`)) 
SUBPARTITION BY HASH (`device_id`)
(
    PARTITION p2001xx VALUES LESS THAN (TO_DAYS('2001-01-01 00:00:00')) (
        SUBPARTITION s2001xxa, 
        SUBPARTITION s2001xxb
    ),
    PARTITION p202004 VALUES LESS THAN (TO_DAYS('2020-04-01 00:00:00')) (
        SUBPARTITION s202004a, 
        SUBPARTITION s202004b
    ),
    PARTITION pMax VALUES LESS THAN MAXVALUE (
        SUBPARTITION smaxa, 
        SUBPARTITION smaxb
    )
);