Confirmed problem?

From: Adam Donahue <adonahue_at_opsware.com>
Date: Thu, 11 Nov 2004 17:58:24 -0800
Message-ID: <419418C0.3080707@opsware.com>

Take a look at this.

Examples:

  1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc
  2 WHERE cr.role_class_id = drc.role_class_id (+)   3 START WITH cr.role_class_id = 3
  4* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL>
ROLE_CLASS_ID SUBSTR(LPAD('',2*LEVEL)||SUBSTR(ROLE_CLA DVC_ID
------------- ---------------------------------------- -------------

            3   Hardware
      1350003     SERVER                                      440003
      1420003       IBM
      1430003         7028-6C1                                 20003
      1430003         7028-6C1                                780003
      1360003       SUN MICROSYSTEMS
      1370003         ENTERPRISE 420R                          10003
      1470003         NETRA X1                                150004
      1470003         NETRA X1                                170003
      1470003         NETRA X1                                180003
      1670003         NETRA T1
      8210003       COMPAQ
      8220003         PROLIANT DL360
      1630003       HEWLETT PACKARD
      1640003         HP NETSERVER LPR                        430003
      1420004       DELL COMPUTER CORPOR
      1430004         POWEREDGE 650                            20004
      1430004         POWEREDGE 650                            30004
      8130003         PRECISION WORKSTATIO                    100007
      1680003         XPST700                                 460003
      8130003         PRECISION WORKSTATIO                    620007
      8130003         PRECISION WORKSTATIO                    820003
      8620003         XPS-Z                                   840003
      8700003         OPTIPLEX GX110                          870003

24 rows selected.

  1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc
  2 WHERE cr.role_class_id = drc.role_class_id (+)   3 AND drc.dvc_id is not null
  4 START WITH cr.role_class_id = 3
  5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / no rows selected

  1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc
  2 WHERE cr.role_class_id = drc.role_class_id (+)   3 AND drc.dvc_id is null
  4 START WITH cr.role_class_id = 3
  5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / ROLE_CLASS_ID SUBSTR(LPAD('',2*LEVEL)||SUBSTR(ROLE_CLA DVC_ID
------------- ---------------------------------------- -------------

            3   Hardware
      1420003       IBM
      1360003       SUN MICROSYSTEMS
      1670003         NETRA T1
      8210003       COMPAQ
      8220003         PROLIANT DL360
      1630003       HEWLETT PACKARD
      1420004       DELL COMPUTER CORPOR

8 rows selected.

  1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc
  2 WHERE cr.role_class_id = drc.role_class_id (+)   3 AND drc.dvc_id = 430003
  4 START WITH cr.role_class_id = 3
  5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / no rows selected

  1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc
  2 WHERE cr.role_class_id = drc.role_class_id (+)   3 AND drc.dvc_id != 430003
  4 START WITH cr.role_class_id = 3
  5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / no rows selected

  1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc
  2 WHERE cr.role_class_id = drc.role_class_id (+)   3 AND drc.dvc_id in ( 430003 )
  4 START WITH cr.role_class_id = 3
  5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / no rows selected

  1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc
  2 WHERE cr.role_class_id = drc.role_class_id (+)   3 AND drc.dvc_id in ( 430003, 192018291 )   4 START WITH cr.role_class_id = 3
  5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / ROLE_CLASS_ID SUBSTR(LPAD('',2*LEVEL)||SUBSTR(ROLE_CLA DVC_ID
------------- ---------------------------------------- -------------

      1640003         HP NETSERVER LPR                        430003

(Note 192018291 above is just a random number -- it is /not/ a dvc_id.)

  1 SELECT cr.role_class_id, substr( lpad( ' ', 2 * level )||substr( role_class_short_name, 1, 20 ), 1, 40), dvc_id FROM role_classes cr, device_role_classes drc
  2 WHERE cr.role_class_id = drc.role_class_id (+)   3 AND nvl(drc.dvc_id,0) = 430003
  4 START WITH cr.role_class_id = 3
  5* CONNECT BY cr.parent_role_class_id = prior cr.role_class_id SQL> / ROLE_CLASS_ID SUBSTR(LPAD('',2*LEVEL)||SUBSTR(ROLE_CLA DVC_ID
------------- ---------------------------------------- -------------

      1640003         HP NETSERVER LPR                        430003

The interesting thing is that this same example using a pair of dummy tables (foo, bar) seems to work fine -- including the direct = clause above and the is not null! (In case one suspected this was related to nvl.)

  1 select f.id, lpad( ' ', 2 * level )||f.name name, b.id FROM foo f, bar b
  2 where f.id = b.foo_id (+)
  3 start with f.id = 1
  4* connect by f.parent_id = prior f.id SQL> /

           ID NAME                            ID


------------- -------------------- -------------

            1   1
            2     1.1                          1
            3     1.2
            8       1.2.1
            4     1.3
            7       1.3.1                      2
            7       1.3.1                      4

7 rows selected.

  1 select f.id, lpad( ' ', 2 * level )||f.name name, b.id FROM foo f, bar b
  2 where f.id = b.foo_id (+)
  3 and b.id = 2
  4 start with f.id = 1
  5* connect by f.parent_id = prior f.id SQL> /

           ID NAME                            ID


------------- -------------------- -------------

            7       1.3.1                      2

  1 select f.id, lpad( ' ', 2 * level )||f.name name, b.id FROM foo f, bar b
  2 where f.id = b.foo_id (+)
  3 and b.id in ( 1, 2 )
  4 start with f.id = 1
  5* connect by f.parent_id = prior f.id SQL> /

           ID NAME                            ID


------------- -------------------- -------------

            2     1.1                          1
            7       1.3.1                      2

  1 select f.id, lpad( ' ', 2 * level )||f.name name, b.id FROM foo f, bar b
  2 where f.id = b.foo_id (+)
  3 and b.id is not null
  4 start with f.id = 1
  5* connect by f.parent_id = prior f.id SQL> /

           ID NAME                            ID


------------- -------------------- -------------

            2     1.1                          1
            7       1.3.1                      2
            7       1.3.1                      4

The only difference between the two cases, so far as I can see, if that the device_role_classes PK in the first example is a composite key. But I tried that on foo/bar and it worked as well.

Anyhow, before I open a TAR, is there any obvious flaw in the above? It's been a long day.

Thanks!

Adam

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 11 2004 - 22:10:44 CST