PL/SQL bug ???

From: Jarek Palka <triss_at_zeus.polsl.gliwice.pl>
Date: Tue, 20 Oct 1998 14:44:27 +0200
Message-ID: <70i4ab$i0n@router.kamsoft.com.pl>

I wrote a PL/SQL function, similar to this one :

CREATE OR REPLACE FUNCTION Unitest (pndmg number) RETURN NUMBER IS
  ile NUMBER ;
BEGIN
   select sum(a)
   into ile
   from(

         select a from tst
         union all
         select 0 from dual

   );

   RETURN ile;
END; Table TST is declared : TABLE TST (A NUMBER(5)); Table is empty. In my opinion function should return sum of values of column A from table TST, but if I issue statement :

SELECT unitest(1111) from dual;

---

1111

SELECT unitest(100) from dual;

---

100

After inserting a row to TST :

SELECT unitest(1111) from dual;

---

2222

It means that function returns value of input parameter multiplied by number of rows in SELECT statement in function body !!!!!!??????

I tested this function on Oracle 8 EE for NT, Personal Oracle 8 for W'95 and Oracle 8 for Linux.
Results are the same.
On Oracle 7.3 for NT this function cannot be compiled.

Is it serious PL/SQL bug ???

Jarek Palka,
P.I. Kamsoft
Katowice, POLAND Received on Tue Oct 20 1998 - 07:44:27 CDT