Why does Oracle pseudo column CONNECT_BY_ISLEAF seems broken?

Question

First a short description of my Issue

I have a table containing data which may be represented in this pseudo directed graph: I say it is a pseudo-graph because I have some «edges» which are only connected on 1 node.

Each «edges» are labelled, and will be refereed to as an event.

Each nodes contain only one ID.

The Oracle (12c) table look like this: http://sqlfiddle.com/#!4/79cdb5/4/0

on the Table I ran this query and one of the row I expected to have a 1 in its pseudo column CONNECT_BY_ISLEAF has instead a 0.

This is the guilty row: http://sqlfiddle.com/#!4/79cdb5/3/2

I am completely unable to understand why oracle does not consider this row to be a leaf when it obviously is one.

Description of the table data

In the table I am using each row represent an event (or graph edge) plus the node(s) to which it is connected.

the VUID column is the “previous” node, the AUID is «step», the EVENT is the event label, the NEW_VUID is the “next” node.

Exception is for event D and U which only have 1 node connected to it and this node will always be in the VUID column (even if for D event the node is the “next” node).

Description and aim of the faulty request

Here I will just give some context about the request I am doing

My final aim is to recreate this graph based on the data there is in the Table. For that I proceed in steps:

1. Build the «forward» trees, each root must be node with a D «edge»
2. Build the «reverse» tree, each root would be node with no child
3. «merge» all the relevant trees together to end up with the wanted graph. Final graph should be close to the one shown at beginning of this question.

During step 1. I should end up with forward trees looking like that:

To create the trees with oracle I think the easiest would be to make the right hierarchical query and then use the pseudo column SYS_CONNECT_BY_PATH and filter on CONNECT_BY_ISLEAF = 1, this is because using only leaf plus the path each leaf had it is easy to re-create the tree.

However I am stuck because for some reason I do not understand Oracle is not considering all leaf the same way I do. The leaf containing the node 88888 is not considering

Answer 1

I didn't take the time to fully understand your data model and would suggest that you might have difficulty accomplishing your goal in an understandable manner without some sort of primary key in your table. CONNECT BY is one of the more advanced query forms in Oracle and having a traditional PRIOR t.id = t.parent_id relationship makes it easier.

Anyway, the reason for the results that you find confusing is because you have this row in your data:

into TEST_HISTORY values (88888, 3, 'U', null)

It is a "child" of the row you think is a leaf, making that row actually not a leaf at all.

Run your query without the WHERE clause and you should see it. The CONNECT BY happens before the WHERE clause. Filtering out leaves in the WHERE clause does not make their now-childless parents into leaves.