If you’ve read my previous post on the aggregate functions basics you’ll see the problem with the following script (run against version 220.127.116.11):
SQL> CREATE TABLE part_test 2 (id NUMBER(10)) 3 PARTITION BY RANGE (id) INTERVAL (10) 4 (PARTITION p1 VALUES LESS THAN (11)) 5 / Table created. SQL> SET NULL <<null>> SQL> SELECT MAX(id) 2 FROM part_test 3 WHERE id > 1 4 / MAX(ID) ---------- <<null>> SQL> SELECT MAX(id) 2 FROM part_test 3 WHERE id > 11 4 / no rows selected
Here I have created a simple one column partitioned table. Just one partition has been created for values less than 11 and no data has been loaded. The first query against the table seeks to obtain the maximum column value where the value is greater than 1. Since there is no data in the table the result comes back as NULL. The next query is a repeat of the first but is changed to seek the maximum value greater than 11. Instead of reporting a NULL result, Oracle reports no rows selected. This is wrong.
Consider the result we get if the table is not partitioned:
SQL> CREATE TABLE non_part_test 2 (id NUMBER(10)) 3 / Table created. SQL> SET NULL <<null>> SQL> SELECT MAX(id) 2 FROM non_part_test 3 WHERE id > 1 4 / MAX(ID) ---------- <<null>> SQL> SELECT MAX(id) 2 FROM non_part_test 3 WHERE id > 11 4 / MAX(ID) ---------- <<null>>
Both queries in this case return NULL.
If we add another partition to our partitioned table by inserting a value of 12 (and rolling it back so the table remains empty) and repeat the no rows query we get:
SQL> INSERT INTO part_test 2 VALUES (12) 3 / 1 row created. SQL> ROLLBACK 2 / Rollback complete. SQL> SELECT MAX(id) 2 FROM part_test 3 WHERE id > 11 4 / MAX(ID) ---------- <<null>>
Now we get the correct result for our selection involving 11. Obviously we would still get the wrong result for values greater than 21 as no partition is available for those values.
The problem only seems to occur for MIN and MAX aggregate functions. Other functions, such as AVG and SUM, do indeed return NULL. COUNT returns the correct result of 0. Due to the problem only impacting MIN and MAX functions it makes me wonder if this is some side-effect of the MIN/MAX query optimisation that lets Oracle short circuit partition access.
I raised an SR with Oracle regarding this as it is wrong and found out that it’s a known bug with 18.104.22.168 (Bug 16883319 Wrong results with query involving range partitioned empty table ). A fix should be provided in the next patch-set.
Update July 2013: Oracle 12c Release 1 was released a few weeks back. I checked this bug and it’s still present in that release. The notes for the bug in Oracle Support now say that it will be addressed in future patch sets.
Update Feb 2014: This bug appears to have been fixed in Oracle 22.214.171.124.