Re: another bug with user-defined functions in sql

In article <l.carl.pedersen-2404951202470001_at_kip-1-sn-423.dartmouth.edu>

           l.carl.pedersen_at_dartmouth.edu "L Carl Pedersen" writes:

: this one is not a real impediment, but it's subtle and could be confusing.
:
: ADM> create or replace function bug2(flag in number) return number is
: 2
: 2 junk number := 0;
: 3
: 3 begin
: 4
: 4 select 1 into junk from dual where flag < 4;
: 5
: 5 return junk;
: 6
: 6 end bug2;
: 7 /
 

: X BUG2(X)
: ---------- ----------
: 1 1
: 2 1
: 3 1
:

Carl,
You've probably discovered by now that it's a lot worse than your first example. I tried the same thing, but put the function in a package so with typos and errors I ended up with table getting the numbers from 1 to 8 half a dozen times:

when I did the:

        select n1, bug1.bug1(n1) from t1;
I got:

	        N1 BUG1.BUG1(N1)
        ---------- -------------



       3 rows selected

As you've probably guessed, there were no null rows in the table !

BTW: select bug1.bug1(2) from dual; gave the right answer, so the function was working.

-- 
Jonathan Lewis