Doing a SELECT WHERE including the primary key of a partitioned table causes the following SELECTs to behave incorrectly.
Essentially the following behaviour:

INSERT INTO foo VALUES (1, '1'),(2, '2'),(3, '3'),(4, '4');
– ok, check directly on backend nodes and values are split between them
SELECT * FROM foo;
– ok, returns all entries
SELECT * FROM foo WHERE id=1;
– ok, returns previously inserted row
SELECT * FROM foo;
– broken! returns only entries from the partition containing id=1!
SELECT * FROM foo WHERE id=2;
– ok, returns previously inserted row
SELECT * FROM foo;
– broken! returns only entries from the partition containing id=2!

Note that this happens even when the SELECT's are done from different connections in spider.

The setup is very simple, running locally, with:

• 1 spider node
• 2 datanodes (node1 and node2)

No changes to spider variables, everything is default.

I'm using the following table, partitioned BY KEY(id) in spider:

CREATE TABLE foo
(
id int(10) NOT NULL,
val varchar(50) NOT NULL,
PRIMARY KEY(id)
)
ENGINE=spider
COMMENT='wrapper "mysql", table "foo"'
PARTITION BY KEY (id)
(
PARTITION p1 COMMENT = 'srv "node1"',
PARTITION p2 COMMENT = 'srv "node2"'
);

This problem also happens with RANGE/HASH partitioned tables.
Also, if the primary key is composite, say (id, val), then the select must include both columns for the problematic behavior to happen.