Query works with Oracle 10g but not with 11g?

Question

DECLARE
  trn  VARCHAR2(2) := 'DD';
  cur  SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT
      TRUNC(some_date, trn),
      NULL AS dummy_2,
      COUNT( DISTINCT dummy_1 )
    FROM
      (SELECT SYSDATE AS some_date, ROWNUM AS dummy_1 FROM dual)
    GROUP BY
      TRUNC(some_date, trn);
END;

This works with Oracle 10, but with Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production it results in:

ORA-00979: not a GROUP BY expression
ORA-06512: at line 5

Can anyone reproduce/explain this? Thanks!

I'm using this for statistics where I want to decide if the records should be grouped by day or by hour. — Peter Lang, Dec 06 '10 at 08:25
Works ok on my 11gR2 (64-bit). Just in case, full banner: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options — Alexander Malakhov, Dec 06 '10 at 10:47

score 6 · Accepted Answer · answered Dec 06 '10 at 09:52

6

if you have access to support, it looks like Bug 9478304: LOOP FAILING WITH ORA-00979: NOT A GROUP BY EXPRESSION. This seems to affect 11.2.0.1 only.

answered Dec 06 '10 at 09:52

Vincent Malgrat

66,725
9
119
171

That's it, thanks a lot! The suggested workaround of the parent bug (`_optimizer_distinct_agg_transform = false`) solves the problem. – Peter Lang Dec 06 '10 at 15:02

score 2 · Answer 2 · answered Dec 06 '10 at 08:35

2

I suspect your problem is that the NULL in your SELECT needs to be in the GROUP BY even though it is a constant. I can't imagine why it would work in Oracle 10 but not 11, though.

Does it work if you remove NULL AS dummy_2?

answered Dec 06 '10 at 08:35

Gabe

84,912
12
139
238

Yes, it works if I remove any part of the query, even the `DISTINCT`-keyword makes a difference. Grouping by `NULL` doesn't help... – Peter Lang Dec 06 '10 at 08:41
1

The definitely sounds like a bug in Oracle. – Gabe Dec 06 '10 at 08:50

score 1 · Answer 3 · answered Dec 06 '10 at 09:25

This works without errors:

DECLARE
  trn  VARCHAR2(2) := 'DD';
  cur  SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT
      TRUNC(some_date, dtrn),
      NULL AS dummy_2,
      COUNT( DISTINCT dummy_1 )
    FROM
      (SELECT SYSDATE AS some_date, ROWNUM AS dummy_1 FROM dual) data1,
      (SELECT trn AS dtrn FROM dual) data2
    GROUP BY TRUNC(some_date, dtrn);
END;

Problem is with trn variable and variable using in TRUNC function. Maybe it is bug.

score 1 · Answer 4 · answered Dec 06 '10 at 10:58

Truncating the sysdate in the inner select appears to work fine:

DECLARE
  trn  VARCHAR2(2) := 'DD';
  cur  SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT
      some_date,
      NULL AS dummy_2,
      COUNT( DISTINCT dummy_1 )
    FROM
      (SELECT trunc(SYSDATE, trn) AS some_date, ROWNUM AS dummy_1 FROM dual)
    GROUP BY
      some_date;
END;

Query works with Oracle 10g but not with 11g?

4 Answers4