sql - Why does Oracle pseudo column CONNECT_BY_ISLEAF seems broken? -


first short description of issue

i have table containing data may represented in pseudo directed graph: pseudo-graph image i say pseudo-graph because i have «edges» connected on 1 node.

each «edges» labelled, , refereed event.

each nodes contain 1 id.

the oracle (12c) table this: http://sqlfiddle.com/#!4/79cdb5/4/0

on table i ran this query , 1 of row i expected have 1 in pseudo column connect_by_isleaf has instead 0.

this guilty row: http://sqlfiddle.com/#!4/79cdb5/3/2

i am unable understand why oracle not consider row leaf when one.

description of table data

in table i am using each row represent event (or graph edge) plus node(s) connected.

the vuid column “previous” node, auid «step», event event label, new_vuid “next” node.

exception event d , u have 1 node connected , node in vuid column (even if d event node “next” node).

description , aim of faulty request

here i will give context request i am doing

my final aim recreate graph based on data there in table. i proceed in steps:

  1. build «forward» trees, each root must node d «edge»
  2. build «reverse» tree, each root node no child
  3. «merge» relevant trees end wanted graph. final graph should close 1 shown @ beginning of question.

during step 1. i should end forward trees looking that: first computed forward tree second computed forward tree

to create trees oracle i think easiest make right hierarchical query , use pseudo column sys_connect_by_path , filter on connect_by_isleaf = 1, because using leaf plus path each leaf had easy re-create tree.

however stuck because reason i do not understand oracle not considering leaf same way do. leaf containing node 88888 not considering

i didn't take time understand data model , suggest might have difficulty accomplishing goal in understandable manner without sort of primary key in table. connect by 1 of more advanced query forms in oracle , having traditional prior t.id = t.parent_id relationship makes easier.

anyway, reason results find confusing because have row in data:

into test_history values (88888, 3, 'u', null) 

it "child" of row think leaf, making row not leaf @ all.

run query without where clause , should see it. connect by happens before where clause. filtering out leaves in where clause not make now-childless parents leaves.


Comments

Popular posts from this blog

ubuntu - PHP script to find files of certain extensions in a directory, returns populated array when run in browser, but empty array when run from terminal -

php - How can i create a user dashboard -

javascript - How to detect toggling of the fullscreen-toolbar in jQuery Mobile? -