Oracle - Grouping Sets and Pipelined Table Functions (expected NUMBER got ROW)

Question

I'm working on a report with summary logic using GROUPING SETS, but I'm getting this error:

SELECT c1, c2, c3, SUM(c4) AS MySum
FROM TABLE(get_data()) src
GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c1, c2, ());

-------------------------
ORA-00932: inconsistent datatypes: expected NUMBER got XXX.MYROW
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

It works fine when I only include c1 or c2 separately:

GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c1, ());  
GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c2, ());

It also works fine when I source the query directly from the t1 table:

SELECT c1, c2, c3, SUM(c4) AS MySum
FROM t1 src
GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c1, c2, ());

What am I missing? I feel like it's something simple. Here's a simplified example of my setup:

-- Base table
CREATE TABLE t1 (c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10), c4 INTEGER);

-- Row type
CREATE TYPE myrow AS OBJECT (c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10), c4 INTEGER);

-- Table type
CREATE OR REPLACE TYPE mytable AS TABLE OF myrow;

-- Get data function
CREATE OR REPLACE FUNCTION get_mydata
RETURN mytable PIPELINED AS
BEGIN
  FOR v_rec IN (
    SELECT c1, c2, c3, c4
    FROM t1
  ) LOOP
    PIPE ROW (myrow(v_Rec.c1, v_Rec.c2, v_Rec.c3, v_Rec.c4));
  END LOOP;
    
  RETURN;    
END;

DB version - 12.1.0

Update
Different error I get with my actual function (even with "materialize" hint):

ORA-22905: cannot access rows from a non-nested table item 22905. 
00000  -  "cannot access rows from a non-nested table item" 
*Cause: attempt to access rows of an item whose type is not known 
at parse time or that is not of a nested table type 
*Action: use CAST to cast the item to a nested table type

[db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f9a8b820d9494a881d4e873758995519) — MT0, Jan 04 '21 at 13:39

score 2 · Answer 1 · answered Jan 04 '21 at 13:51

2

I don't know why it won't work, but - see if this workaround helps (use a CTE and the materialize hint):

SQL> with test as
  2    (select /*+ materialize */
  3      c1, c2, c3, c4
  4     from table(get_mydata()) src
  5    )
  6  select c1, c2, c3, sum(c4) as mysum
  7  from test
  8  group by grouping sets ((c1, c2, c3), (c1, c2), c1, c2, ());

C1         C2         C3              MYSUM
---------- ---------- ---------- ----------
1          2          3                   4
1                                         4
           2                              4
                                          4
1          2                              4

SQL>

answered Jan 04 '21 at 13:51

Littlefoot

131,892
15
35
57

Thanks, this works great for the example I gave. I tried applying it to my actual function, though, and I get this error :`ORA-22905: cannot access rows from a non-nested table item 22905. 00000 - "cannot access rows from a non-nested table item" *Cause: attempt to access rows of an item whose type is not known at parse time or that is not of a nested table type *Action: use CAST to cast the item to a nested table type`. This is actually the error I initially had, but which didn't show up in my simplified setup. Same use case. I'll keep fiddling around. – ravioli Jan 04 '21 at 16:13
You're welcome; too bad it doesn't work in all cases ... Good luck! – Littlefoot Jan 04 '21 at 18:14

score 2 · Answer 2 · answered Jan 05 '21 at 06:14

You can workaround the bug by using a non-pipelined function and BULK COLLECT:

CREATE OR REPLACE FUNCTION get_mydata2
RETURN mytable AS
  v_data mytable;
BEGIN
  SELECT myrow(c1, c2, c3, c4)
  BULK COLLECT
  INTO v_data
  FROM t1;

  RETURN v_data;    
END;
/

I was able to reproduce your error in version 19c so it looks like you found a relatively big Oracle bug. In my experience, pipelined functions are generally buggier than regular functions and tend to be over-used. Pipelined functions can return data faster, but since your query is aggregating all the rows, you won't see that performance boost anyway.

The main problem with the non-pipelined function approach is that your session will need enough memory to store all of the results from the BULK COLLECT at once. That may not be feasible if you have to process millions of wide rows.

Oracle - Grouping Sets and Pipelined Table Functions (expected NUMBER got ROW)

2 Answers2