This one’s really got me. Maybe it’s a bug. Ideas anyone? See below…
We have a table with the following partition scheme (example names and values):
PARTITION BY LIST COLUMNS(`owner`) SUBPARTITION BY HASH(CASE WHEN (`foo`+`bar`) < 1 THEN 0 ELSE 1 END) SUBPARTITIONS 2 ( PARTITION p0 VALUES IN('A','B','C','D','E','F','G'), PARTITION p1 VALUES IN('H'), PARTITION p2 VALUES IN('I','J'), PARTITION p3 VALUES IN('K') );
N.B. the A,B,C, etc. values above are only examples. The real table has strings that represent the owner of the row. There are over 3m rows.
We should end up with a partition that has all the foo+bar = 0 values owned by A through G as the first subpartition of partition p0.
We get the following output from :
+----------+----------+ | owner | COUNT(*) | +----------+----------+ | H | 2077675 | | B | 138243 | | I | 13333 | | C | 26507 | | A | 806889 | | K | 134 | | E | 38165 | | D | 14118 | +----------+----------+
There are no values for F, G or J.
Now when I run , I get zero.
If I change the list to
I’ve tested this on 5.6 on another server with a subset of the data, and it is fine, with the additional feature of selecting directly from p0, etc.
Anyone ever seen anything like this? Am I missing something? Let me know if you want to know any variables.