Why does the Oracle CONNECT_BY_ISLEAF pseudo-column look like?

Question

First a short description of my problem

I have a table containing data that can be represented in this pseudo-oriented graph: I say this is a pseudograph, because I have some "edges" that are connected only on 1 node.

Each "ribs" are marked and will be treated as an event.

Each node contains only one identifier.

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

in the table, I ran this query and one of the rows that I expected should have 1 in its pseudo column, CONNECT_BY_ISLEAF has 0 instead.

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

I absolutely do not understand why the oracle does not consider this line as a sheet when it is obviously one.

Description of table data

In the table, I use each row representing the event (or edge of the graph) plus the node (s) to which it is connected.

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

An exception is for events D and U, which are associated with only 1 node, and this node will always be in the VUID column (even if for the event D node is the "next" node).

Description and purpose of the failed request

Here I just talk about what I do

My ultimate goal is to recreate this graph based on the data that is in the table. To do this, I take the steps:

• Build the trees "forward", each root must be a node with D "edge"
• Create a "reverse" tree, each root will be a node without a child
• "merge" all the corresponding trees together to get the desired graph. The final schedule should be close to that shown at the beginning of this question.

During step 1. In the end, I should look like this:

To create trees using oracle, I think that the simplest would be to make the correct hierarchical query, and then use the pseudo-column SYS_CONNECT_BY_PATH and filter by CONNECT_BY_ISLEAF = 1, because using only the sheet plus the path on which each sheet was easy for recreation tree.

However, I am stuck because for some reason I do not understand that Oracle does not view the entire sheet in the same way as I do. A sheet containing node 88888 does not consider

Answer 1

I did not take the time to fully understand your data model, and would suggest that it may be difficult for you to achieve your goal in an understandable way without any primary key in your table. CONNECT BY is one of the most complex forms of queries in Oracle and the traditional PRIOR t.id = t.parent_id relationship PRIOR t.id = t.parent_id makes it easier to execute.

In any case, the reason for the results that you find confusing is that you have this line in your data:

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

This is the "child" of the line that you think is a sheet, which makes this line actually not a sheet.

Run the query without the WHERE and you will see it. CONNECT BY is executed before the WHERE . Filtering the leaves in the WHERE does not make their childless parents into leaves.