Issue Details (XML | Word | Printable)

Key: CORE-3683
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Zotov
Votes: 0
Watchers: 5
Operations

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

Wrong results if the recursive query contains an embedded GROUP BY clause

Created: 02/Dec/11 08:34 AM   Updated: 09/Apr/14 02:51 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.1.2, 2.1.3, 3.0 Initial, 2.5.0, 2.1.4, 2.5.1
Fix Version/s: 2.5.2, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Duplicate
 
Relate
 

Planning Status: Unspecified


 Description  « Hide
DDL:
-- table of some routes between cities and additional info (names from cenral region of Russia):
recreate table rdeps(parent varchar(32),child varchar(32), parent_type int, child_type int, f01 int);
commit;

insert into rdeps values( 'MOSCOW', 'TULA', 0, 5, 21);
insert into rdeps values( 'MOSCOW', 'TULA', 0, 5, 22);

insert into rdeps values( 'TULA', 'OREL', 5, 5, 51);
insert into rdeps values( 'TULA', 'OREL', 5, 5, 52);

insert into rdeps values( 'TULA', 'LIPETSK', 5, 2, 71);
insert into rdeps values( 'TULA', 'LIPETSK', 5, 2, 72);

insert into rdeps values( 'TULA', 'RYAZAN', 5, 5, 61);
insert into rdeps values( 'TULA', 'RYAZAN', 5, 5, 62);

insert into rdeps values( 'OREL', 'KURSK', 5, 5, 81);
insert into rdeps values( 'OREL', 'KURSK', 5, 5, 82);

insert into rdeps values( 'LIPETSK','VORONEZH', 5, 2, 71);
insert into rdeps values( 'LIPETSK','VORONEZH', 5, 2, 72);

insert into rdeps values( 'RYAZAN','MUROM', 5, 5, 61);
insert into rdeps values( 'RYAZAN','MUROM', 5, 5, 62);

commit;

Task: get all possible routes starting from MOSCOW

Query:
with recursive
rd as(
  select
     d.parent parent
    ,d.child
  from rdeps d
  group by d.parent,d.child -- <<< we need this grouping to eliminate duplicates
)
,cr as(
  select 0 step,parent,child,cast(parent as varchar(32000))||'->'||child routes
  from rd
  where rd.parent='MOSCOW'

  UNION ALL

  select x.step+1,rd.parent,rd.child,x.routes||'->'||rd.child
  from cr x
  join rd on x.child=rd.parent
)
select step,routes from cr order by step,routes

Result (WRONG):
==============
STEP ROUTES
0 MOSCOW->TULA
1 MOSCOW->TULA->LIPETSK
1 MOSCOW->TULA->RYAZAN
1 MOSCOW->TULA->RYAZAN
2 MOSCOW->TULA->LIPETSK->VORONEZH
2 MOSCOW->TULA->RYAZAN->MUROM
2 MOSCOW->TULA->RYAZAN->MUROM

(dupliates MOSCOW->TULA->RYAZAN, MOSCOW->TULA->RYAZAN->MUROM; missing MOSCOW->TULA->OREL)


Add [unique] index:
===============
--create unique index rdeps_unq on rdeps(parent, child, f01);
create index rdeps_unq on rdeps(parent, child, f01);
 Repeat the query - result will be correct:
STEP ROUTES
0 MOSCOW->TULA
1 MOSCOW->TULA->LIPETSK
1 MOSCOW->TULA->OREL
1 MOSCOW->TULA->RYAZAN
2 MOSCOW->TULA->LIPETSK->VORONEZH
2 MOSCOW->TULA->OREL->KURSK
2 MOSCOW->TULA->RYAZAN->MUROM

PS. The result will be also correct if we replace GROUP BY in root member with DISTINCT - but these operations are not equal.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 04/Dec/11 07:03 PM
Adriano, can you also fix this for 2.5.x ?

Adriano dos Santos Fernandes added a comment - 04/Dec/11 07:57 PM
Would like to Vlad review it first.

Vlad Khorsun added a comment - 09/Dec/11 08:47 AM
Adriano,

i reviewed your first commit and it looks OK for me.
But i could miss something as i know opt\rse parts not very well.

Adriano dos Santos Fernandes added a comment - 11/Dec/11 01:10 AM
The related code is completely different and ugly in 2.5. I'm leaving it.

Dmitry Yemanov added a comment - 19/Dec/11 09:59 AM
Backported into v2.5.2.