Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Empty result when select from SP that contains two CTE (second of them with GROUP BY clause) and INNER join [CORE3173] #3547

Closed
firebird-automations opened this issue Oct 9, 2010 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE3090 [ CORE3090 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.1 [ 10333 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment