using outer apply with a table macro function fails when the macro has 'fetch first row only'

User_2OQ7J

Hi,

i've compiled a short demo; it works fine, but if you add in the 'fetch first row only' the result is dead wrong.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0

CREATE OR REPLACE FUNCTION testMacroFunction(inputnumber integer) return varchar2 sql_macro
AS 
BEGIN
 RETURN q'{
  SELECT
   field2, field1
  FROM ( SELECT 1 AS field1, 'a' AS field2 FROM dual UNION ALL
     SELECT 2, 'b1' FROM dual UNION ALL
     SELECT 2, 'b2' FROM dual UNION ALL
     SELECT 3, 'c' FROM dual) atable 
  WHERE field1 = testmacrofunction.inputnumber
  fetch first row only
 }';
END;
/

--vb outer apply
WITH table1 AS
(
  SELECT 1 AS col FROM dual UNION ALL
  SELECT 2 FROM dual    UNION ALL
  SELECT 4 FROM dual
)
SELECT table1.col, h.field2, h.field1
FROM table1
  outer apply testmacrofunction(table1.col) h

When the ' fetch first row only' is left out, the result is correct:

with ' fetch first row only' left in, the result is this:

while it should be

1 a 1
2 b1 2
4

kind regards,

Nico

Alex Keh-Oracle

I would recommend posting this issue on the general DB forum as you will find more Oracle SQL experts there that can verify if this is a bug or not.

This is the ODP.NET forum. Our focus is on the .NET data access to Oracle DB.

User_2OQ7J

ok thank you for the heads up, i've posted it to the general db forum.