MIN / MAX against partitioned table bug

If you’ve read my previous post on the aggregate functions basics you’ll see the problem with the following script (run against version 11.2.0.3):

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 11.2.0.3 (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 11.2.0.4.

More Table Partition Queries and DML

In the last post I noted how it’s possible to run SQL queries and DML operations against specific table partitions using syntax shown below:

SELECT *
FROM   part_test PARTITION (part_test_p5)

The above query references the partition by name, i.e. PART_TEST_P5. Oracle support an alternate syntax that permits the partition to be referenced by a data value, i.e.:

SELECT *
FROM   part_test PARTITION FOR (45)

The value of 45 in the above query is simply a value that Oracle can use to resolve to a specific partition. Any value will do. The value used can involve an expression but the use of a bind variables will throw an exception:

SQL> VARIABLE part_val NUMBER

SQL> EXEC :part_val := 45

PL/SQL procedure successfully completed.

SQL> SELECT *
  2  FROM   part_test PARTITION FOR (:part_val)
  3  /
FROM   part_test PARTITION FOR (:part_val)
       *
ERROR at line 2:
ORA-14763: Unable to resolve FOR VALUES clause to a partition number

The PARTITION FOR syntax works for SELECT, INSERT, UPDATE, DELETE and even TRUNCATE.