Very simple Table Function in a View failing when queried ? ....

[Paul Reddin]

Can anybody tell me why the select of the following fails ??

--(run'able SQL)
create function f(p_i integer)
returns table (i integer)
begin atomic
declare v_i integer default 1;
declare v_v varchar(10) default 'N';

return select v_i from sysibm.sysdummy1;
--return values (v_i);
end;

create view y as select i from table(f(1)) as f;

select * from y;
[IBM][CLI Driver][DB2/LINUX] SQL0344N The recursive common table
expression "DB2JBSI2.Y" has mismatched data types, lengths or code
pages for column "I". SQLSTATE=42825

There seems to be a fundamental problem returning SQL variables from
Table
functions referenced in views ??

Thanks.

Paul.

[Serge Rielau]

I can repro, will get back to you. Looks like a bug at first glance.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Guilty as charged, I bow my head in shame.

The DEFAULT 1 from the variable declaration propagated all the way to view.
Since the table function was not expanded when the view was created the
select gets confused because of the sudden appearance.
Fix will be in FP7. Open an APAR and tell support to to route straight
to me if you need a fix before then.

The workaround is to not use the default and have a separate SET
statement. Or to use an expression in teh RETURN insetad of the variable
straight (like var + 0)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab