You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
recreate table zzz_tbl (
id integer,
company_id integer,
hire_date date
);
insert into zzz_tbl (id, company_id, hire_date)
values (60108712, 60121225, '01.10.2004');
commit;
set term^;
create or alter procedure zzz_ppp returns (val integer)
as begin
val=1;
suspend;
end^
create or alter procedure ZZZ_DBG (
COMPANY_ID integer,
A_MONTH_BEG date)
returns (
PERSON_ID integer)
AS
begin
for
with
inp as(
select
company_id
,dateadd(1 month to p1)-1 p2
from(
select
:company_id company_id,:a_month_beg p1
--60121225 company_id,cast('01.09.2010' as date) p1
from
--rdb$database
zzz_ppp
)t
)
,person_nfo as
(
select http://n.id person_id,i.p2
--from inp i join zzz_tbl n on n.company_id = i.company_id
from zzz_tbl n left join inp i on n.company_id = i.company_id
group by http://n.id,p2
)
select f.person_id
from person_nfo f
join zzz_tbl nt on nt.hire_date <= f.p2
into :person_id
do suspend;
end^
set term;^
--------------------------------------------
The following call of SP zzz_dbg will bring EMPTY result: select * from zzz_dbg(60121225, '01.09.2010') - that's wrong because result must have 1 row.
If we change the CTE named 'inp' in zzz_dbg like this:
inp as(
select
company_id
,dateadd(1 month to p1)-1 p2
from(
select
--:company_id company_id,:a_month_beg p1
60121225 company_id,cast('01.09.2010' as date) p1
from
--rdb$database
zzz_ppp
)t
)
-- then result will be right.
If we change CTE 'inp' in such matter:
inp as(
select
company_id
,dateadd(1 month to p1)-1 p2
from(
select
:company_id company_id,:a_month_beg p1
--60121225 company_id,cast('01.09.2010' as date) p1
from
rdb$database
--zzz_ppp
)t
)
-- result Ok.
If we do not change CTE 'inp' and modify CTE 'person_nfo' (comment GROUP BY clause):
,person_nfo as
(
select http://n.id person_id,i.p2
from zzz_tbl n left join inp i on n.company_id = i.company_id
--group by http://n.id,p2
)
-- result Ok.
If we do not change CTEs and comment out only last join:
select f.person_id
from person_nfo f
--join zzz_tbl nt on nt.hire_date <= f.p2
into :person_id
-- result also be Ok.
Also result will be Ok if this last join will be replaced from INNER to LEFT:
select f.person_id
from person_nfo f
LEFT join zzz_tbl nt on nt.hire_date <= f.p2
into :person_id
This error was encountered in: WI-V6.3.0.26074, WI-V6.3.0.26082, LI-V6.3.0.26079.
There is no such error in FB 2.1.4.
Submitted by: @pavel-zotov
Is related to CORE3090
Consider the following DDL:
recreate table zzz_tbl (
id integer,
company_id integer,
hire_date date
);
insert into zzz_tbl (id, company_id, hire_date)
values (60108712, 60121225, '01.10.2004');
commit;
set term^;
create or alter procedure zzz_ppp returns (val integer)
as begin
val=1;
suspend;
end^
create or alter procedure ZZZ_DBG (
COMPANY_ID integer,
A_MONTH_BEG date)
returns (
PERSON_ID integer)
AS
begin
for
with
inp as(
select
company_id
,dateadd(1 month to p1)-1 p2
from(
select
:company_id company_id,:a_month_beg p1
--60121225 company_id,cast('01.09.2010' as date) p1
from
--rdb$database
zzz_ppp
)t
)
,person_nfo as
(
select http://n.id person_id,i.p2
--from inp i join zzz_tbl n on n.company_id = i.company_id
from zzz_tbl n left join inp i on n.company_id = i.company_id
group by http://n.id,p2
)
select f.person_id
from person_nfo f
join zzz_tbl nt on nt.hire_date <= f.p2
into :person_id
do suspend;
end^
set term;^
--------------------------------------------
The following call of SP zzz_dbg will bring EMPTY result: select * from zzz_dbg(60121225, '01.09.2010') - that's wrong because result must have 1 row.
If we change the CTE named 'inp' in zzz_dbg like this:
inp as(
select
company_id
,dateadd(1 month to p1)-1 p2
from(
select
--:company_id company_id,:a_month_beg p1
60121225 company_id,cast('01.09.2010' as date) p1
from
--rdb$database
zzz_ppp
)t
)
-- then result will be right.
If we change CTE 'inp' in such matter:
inp as(
select
company_id
,dateadd(1 month to p1)-1 p2
from(
select
:company_id company_id,:a_month_beg p1
--60121225 company_id,cast('01.09.2010' as date) p1
from
rdb$database
--zzz_ppp
)t
)
-- result Ok.
If we do not change CTE 'inp' and modify CTE 'person_nfo' (comment GROUP BY clause):
,person_nfo as
(
select http://n.id person_id,i.p2
from zzz_tbl n left join inp i on n.company_id = i.company_id
--group by http://n.id,p2
)
-- result Ok.
If we do not change CTEs and comment out only last join:
select f.person_id
from person_nfo f
--join zzz_tbl nt on nt.hire_date <= f.p2
into :person_id
-- result also be Ok.
Also result will be Ok if this last join will be replaced from INNER to LEFT:
select f.person_id
from person_nfo f
LEFT join zzz_tbl nt on nt.hire_date <= f.p2
into :person_id
This error was encountered in: WI-V6.3.0.26074, WI-V6.3.0.26082, LI-V6.3.0.26079.
There is no such error in FB 2.1.4.
Commits: 516b6f0 6d4a937
The text was updated successfully, but these errors were encountered: