Issue Details (XML | Word | Printable)

Key: CORE-3173
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 09/Oct/10 09:09 AM   Updated: 23/Feb/11 07:42 AM
Component/s: Engine
Affects Version/s: 3.0 Initial, 2.5.0
Fix Version/s: 2.5.1, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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 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 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 n.id person_id,i.p2
from zzz_tbl n left join inp i on n.company_id = i.company_id
--group by 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.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.