difference between call and select

[Baron]

I have problem with the following code:

create procedure MyDummy()

begin

select 'mydummy' from dummy;

end;

create procedure MyDummyWrapper()

begin

select * from MyDummy();

end;

---

call MyDummy(); -->mydummy (As Expected)

call MyDummyWrapper();-->mydummy (As Expected)

---

alter procedure MyDummy()

begin

select 'mydummy' from dummy where 1 = 0;

end;

---

call MyDummy()-->empty (As Expected)

call MyDummyWrapper()-->mydummy (NOT As Expected!!!)

select * from MyDummyWrapper()-->empty (As Expected)

I have here 2 Questions:

1. what is the difference between select and call? Why I have the problem only with call?
2. how can I overcome the problem with call? is there any alternative for sp_recompile (as with Adaptive Server)?

[VolkerBarth]

FWIW, it would be helpful to state more than just OK/NOK, I had expected the "NOK" would mean an error message and not a wrong result set. In my tests with 16.0.0.2798 and 17.0.10.6175 "call MyDummyWrapper()" returns the result "mydummy" whereas the other procedure invoocations return an empty result set as expected.

---

In my humble opinion this looks like a bug.

The behaviour can be fixed via

alter procedure MyDummyWrapper recompile;

Note, that in my tests both procedures (lacking a result set clause) are described via sysprocparm as having one result set column with type CHAR(7), which is appropriate.

select SP.proc_name, SPP.* from SYSPROCEDURE SP key join SYSPROCPARM SPP
where proc_name in ('MyDummy', 'MyDummyWrapper')

---

BTW, there's more to this:

If you drop the first procedure via "drop procedure MyDummy" and do not afterwards recomplie the second procedure, the second procedure still can be called successfully without an error, which is totally unexpected in my book.

So I do have to assume the wrapper procedure somehow interally caches the result set, also the attempt to disable plan caching via "set temporary option max_plans_cached = 0;" does not make a difference in my tests...

[Baron]

Yes, your fix works, I have tried it before! Moreover It also works if I restart the database (even without the mentioned fix).

The problem with the mentioned fix is, how can I know (not in this specific case, but generally) how many wrappers has the procedure MyDummy, and how many other procedures are affected of the last alter procedure MyDummy()

[VolkerBarth]

As to "procedure dependencies": I do not know how to recognize those automatically, in contrast to the "View dependency feature".

[Baron]

Yes, you have right, even after dropping the first procedure, then the wrapper is still functioning correct!

I don't think that this is related to caching the last result set, because after calling sa_flush_cache everything is still working as if nothing is happened!

[VolkerBarth]

Well, there are several types of caching, and I was relating to plan caching which has nothing to do with the database server's page caching that would be influenced by sa_flush_cache. But disabling plan caching did not matter here, either, as stated.

Furthermore, the noticed effect might be due to "inlining simple procedures", but again, that sould only happen for using procedures in the FROM clause, not for call statements, and this does not explain the effect, either.