I’ve used the dump() command in the second query to show you that ALL the rows in this table have exactly the same value for the status column.
But either (a) half of them aren’t being counted in the first query or (b) all of them are being counted twice in the second query.

The /*+ full(t1) */ hint is there to let you know that I’ve used the same execution plan in both cases, so I haven’t managed to engineer the problem by carefully corrupting an index.

What have I done to my database ? (12c, by the way).

Answer

Someone supplied the answer before I did, so I won’t repeat it.
The posting was prompted by an email I got about the previous posting, suggesting that the apparently redundant predicate might have been generated to avoid exactly this kind of reporting error, so I felt the need to run up a quick check to show that there are plenty of cases where such a predicate doesn’t appear and the answers can look bizarre.

Here’s the execution plan that Joel Garry asked for, by the way. It demonstrates a little feature that has been around for a very long time (possibly since the introduction of partitioning): partition-wise aggregation. Many people know about the benefits of partition-wise joins, but partition-wise aggregation is a detail that doesn’t get mentioned. and perhaps doesn’t get noticed; it’s just one of those clever little touches: the optimizer saw that it could produce the required result by collecting and aggregating the data from each partition in turn, rather than collecting all the data across every partition and then doing a single large aggregation.

(Of course in this case the fact that the same value appeared twice after aggregation gave us the clue that we have bad data in one of the partitions)

The moral of this story

If you’re going to say “we don’t need constraints, the code ensures that the data is correct”, or if you’re going to use the “trust me” (without validation) option make sure that the code that supposed to be checks your data is absolutely perfect.

I believe I could do this on any version of Oracle from 8.0 – so no virtual columns

This is a heap table, not clustered, not an IOT; it has no indexes, no object-type columns, no XML types, no LOBs or longs,no views or PL/SQL involved, no triggers, no row-level security, no contexts, no profiles.

Exchange partition without validation is very powerfull tool, but also very dangerous (as everything powerfull) :-).
And partition pruning make it very hard to find problem, if you have data in wrong partitions and don’t have clue what’s going on.