Home » SQL & PL/SQL » SQL & PL/SQL » CONNECT BY NOCYCLE gives different results (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi; Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod)
CONNECT BY NOCYCLE gives different results [message #359274] Fri, 14 November 2008 08:35 Go to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Run the following code with the same client (10.2.0.3.0) on different Database Versions:
SELECT banner FROM v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
WITH a AS
(
SELECT 1001 id,1  a,  1 e, 'M' typ from dual
  UNION ALL
SELECT 1002   ,1   ,  10,   'L'     from dual
  UNION ALL
SELECT 1003   ,1   ,  11,   'L'     from dual
  UNION ALL
SELECT 1004   ,1   ,  12,   'L'     from dual
  UNION ALL
SELECT 1004   ,10  ,  10,   'M'     from dual
  UNION ALL 
SELECT 1005   ,10  ,  21,   'L'     from dual
  UNION ALL 
SELECT 1006   ,22  ,  10,   'L'     from dual
)
SELECT LEVEL lv, a, e, prior a pa, prior e pe, typ, prior typ, SYS_CONNECT_BY_PATH(id, '/') weg
  FROM a
  START WITH id=1001
  CONNECT BY NOCYCLE
  (  
	(typ='M' and a in (PRIOR a, PRIOR e)  and PRIOR typ = 'L')
   or	
    (typ='L' and PRIOR a in (a,e)         and PRIOR typ = 'M')
  );


LV	A	E	PA	PE	TYP	PRIORTYP	WEG
1	1	1			M		/1001
2	1	10	1	1	L	M	/1001/1002
3	10	10	1	10	M	L	/1001/1002/1004
4	10	21	10	10	L	M	/1001/1002/1004/1005
4	22	10	10	10	L	M	/1001/1002/1004/1006
2	1	11	1	1	L	M	/1001/1003
2	1	12	1	1	L	M	/1001/1004


Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod


LV	A	E	PA	PE	TYP	PRIORTYP	WEG
1	1	1			M		/1001


What's wrong ?
Re: CONNECT BY NOCYCLE gives different results [message #359276 is a reply to message #359274] Fri, 14 November 2008 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10.2.0.3 introduces many bugs resulting to wrong results.
You have to upgrade to 10.2.0.4.

Regards
Michel
Re: CONNECT BY NOCYCLE gives different results [message #359284 is a reply to message #359276] Fri, 14 November 2008 09:16 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Thanks Michel.

Could You please test, if the bug is fixed in 10.2.0.4, so the script gives the correct output ?
Re: CONNECT BY NOCYCLE gives different results [message #359286 is a reply to message #359284] Fri, 14 November 2008 09:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looks fixed:
        LV          A          E         PA         PE T P WEG
---------- ---------- ---------- ---------- ---------- - - ------------------------
         1          1          1                       M   /1001
         2          1         10          1          1 L M /1001/1002
         3         10         10          1         10 M L /1001/1002/1004
         4         10         21         10         10 L M /1001/1002/1004/1005
         4         22         10         10         10 L M /1001/1002/1004/1006
         2          1         11          1          1 L M /1001/1003
         2          1         12          1          1 L M /1001/1004
Re: CONNECT BY NOCYCLE gives different results [message #359287 is a reply to message #359286] Fri, 14 November 2008 09:28 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
Thanks - have a good weekend.
Previous Topic: ORA-00600: internal error code
Next Topic: How to get top X record
Goto Forum:
  


Current Time: Sun Dec 04 12:59:05 CST 2016

Total time taken to generate the page: 0.10513 seconds