Query on Function-based index not returning correct results in 11g
731105Oct 30 2009 — edited Nov 10 2009I am trying to run a query in 11g (11.1.0.7.0) similar to one below where I have a function-based index on "TRUNC(DATE_COL)". Since this query returns a sum, I expect it to return one row instead this query returns me five rows with all null values (same as rows returned in the inner query). This has been working well for me in 10g (10.2.0.1).
SELECT SUM(DATE_COUNT) FROM
(
SELECT TRUNC(DATE_COL) TRUNC_DATE_COL, COUNT(*) DATE_COUNT
FROM TEST_FUNCTION_INDEX
GROUP BY TRUNC(DATE_COL)
);
Here is a script to re-create my problem:
DROP TABLE TEST_FUNCTION_INDEX;
CREATE TABLE TEST_FUNCTION_INDEX
(
ID NUMBER NOT NULL,
DATE_COL DATE
);
CREATE INDEX IDX_1 ON TEST_FUNCTION_INDEX
(TRUNC("DATE_COL"));
INSERT INTO TEST_FUNCTION_INDEX VALUES (1, sysdate - 1);
INSERT INTO TEST_FUNCTION_INDEX VALUES (2, sysdate - 2);
INSERT INTO TEST_FUNCTION_INDEX VALUES (3, sysdate - 3);
INSERT INTO TEST_FUNCTION_INDEX VALUES (4, sysdate - 4);
INSERT INTO TEST_FUNCTION_INDEX VALUES (5, sysdate - 5);