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: 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:
- build «forward» trees, each root must node d «edge»
- build «reverse» tree, each root node no child
- «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:
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
Post a Comment