recursive query with GROUP BY in root part: wrong results if no index exists
for fields that are grouped
--------------------------------------------------------------------------------------------------------
Key: CORE-3683
URL: http://tracker.firebirdsql.org/browse/CORE-3683
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
