A devilish
quiz by Scott Noyes has made me thinking about a side effect of partitioning.
The quiz
Given a table
trickytable with a INT field, explain how these statements and results are possible:
INSERT IGNORE INTO trickyTable (id) VALUES (1);
Query OK, 0 rows affected (0.00 sec)
SELECT * FROM trickyTable;
Empty set (0.00 sec)
There are no blackhole, federated, triggers, proxy.
I initially tried with the event scheduler, with an event that empties the table every second, but the insert results in 1 row to be inserted, while the quiz asked for 0 rows.
After a few minutes, I got it. The right answer was to use partitioning.
Let me show off a bit. :)
Table
trickytable was created with partitions by range or list , with a partition that does not include 1.
Either of the following will do the trick:
create table trickytable (id int)
partition by list (id) (partition p1 values in (2));
#or
create table trickytable (id int)
partition by range (id) (partition p1 values less than (1));
If you insert "1" in this table, without the IGNORE keyword, you get an error:
INSERT INTO trickytable (id) VALUES (1);
ERROR 1526 (HY000): Table has no partition for value 1
Using IGNORE means "don't react to errors". This is used, for example, when inserting duplicated values, to have them discarded without triggering errors.
Thus, adding the IGNORE keyword will discard the error, leaving no rows to be inserted. Thus the "0 rows affected" message and the resulting empty table.
Well spot, Scott!
Practical use
Using the same concept, we can use partitioning to enforce a constraint.
For example, let's assume that we have a table with part_number and category_id, and we want only products belonging to categories 10, 25, 40, and 53 to be in this table.
Using a partition by list does the trick nicely:
create table special_products (
part_no int not null,
category_id int not null,
primary key (part_no, category_id)
) engine = InnoDB
partition by list (category_id)
(
partition p1 values in (10, 25, 40, 53)
);
insert into special_products values (1, 53), (2, 25);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
insert into special_products values (1, 23);
ERROR 1526 (HY000): Table has no partition for value 23
If we want to include category 23 into the list, we can use
REORGANIZE PARTITIONalter table special_products
reorganize partition p1 into
(
partition p1 values in (10, 23, 25, 40, 53)
);
Or we can actually add more partitions
alter table special_products
reorganize partition p1 into
(
partition p1 values in (10, 23, 25),
partition p2 values in (40, 53)
);
This is an efficient way of adding a constraint (within the limits of partitioning, which accept only integer values and a fixed set of functions).
