Why my hierarchy query is showing duplicate records?

Question

My requirement is to find all lapsed days in a month. Below are my sample queries.

CREATE TABLE custom.date_full (
    sno         NUMBER,
    curr_date   DATE);

INSERT INTO custom.date_full VALUES ( 1,'27-sep-2018' );
INSERT INTO custom.date_full VALUES ( 2,'27-sep-2018' );

--Query:1 - RETURNS 4 RECORDS AS EXPECTED
 SELECT  curr_date, 
        TRUNC (curr_date, 'MM') + LEVEL - 1 AS DAY,
        LEVEL 
   FROM (SELECT * FROM custom.date_full WHERE sno=1)
CONNECT BY level<=4
  ORDER BY DAY;

--Query 2: RETURNS 15 RECORDS WITH DUPLICATES
SELECT  curr_date, 
        TRUNC (curr_date, 'MM') + LEVEL - 1 AS DAY,
        LEVEL 
   FROM custom.date_full 
  WHERE sno=1
CONNECT BY level<=4
  ORDER BY DAY;

My Query 1 works good but Query 2 is showing duplicate records. Why?

user_5174436 · Answer

You are not understanding how CONNECT BY works. Here is a walkthrough of how Oracle is evaluating your 2nd query.

Without a START WITH clause, every row in your table with be used as a starting point, or "root" in your hierarchy.

Since you have no CONNECT BY conditions (like "columnA = PRIOR columnB"), every row in your table will be considered a child of every other row. This will happen forever, until your LEVEL <=4 condition is reached.

So,

LEVEL 1
--------
SNO 1
SNO 2

Explanation: Each row in your table is a starting point of its own hierarchy (because you have no START WITH conditions).

LEVEL 2
--------
SNO 1 -> SNO 1
SNO 1 -> SNO 2 
SNO 2 -> SNO 1
SNO 2 -> SNO 2

Explanation of those 4 rows -- both SNO 1 and SNO 2 are roots, and for each root, SNO 1 and SNO 2 are children. So, 2x2 rows = 4 rows.

LEVEL 3 
-------
SNO 1 -> SNO 1 -> SNO 1
SNO 1 -> SNO 1 -> SNO 2
SNO 1 -> SNO 2 -> SNO 1 
SNO 1 -> SNO 2 -> SNO 2 
SNO 2 -> SNO 1 -> SNO 1
SNO 2 -> SNO 1 -> SNO 2
SNO 2 -> SNO 2 -> SNO 1
SNO 2 -> SNO 2 -> SNO 2

Explanation of those 8 rows. Starting with the 4 rows from level 2, both SNO 1 and SNO 2 are children of each, giving 4x2 = 8 rows at level 3.

Level 4, which I won't draw out, will similarly give 8x2 = 16 rows.

So, in total, you have 2 + 4 + 8 + 16 = 30 rows. (That's level 1 + level 2 + level 3 + level 4).

Then, after your CONNECT BY processing (shown above), the WHERE clause is applied, limiting your final results to rows where the value (at the lowest level of the hierarchy) is SNO = 1. That is exactly half of the 30 rows, or 15 rows, which is what you are getting.

user_2657515 · Answer

Using CONNECT BY creates a hierarchical query. Typically, this also includes a START WITH that specifies rows that are the parent/root of the hierarchical query. When there is no START WITH, all rows in the table are used as parent/root rows.

In Query 1, the parent/root is a table with 1 row:

FROM (SELECT * FROM custom.date_full WHERE sno=1)

In Query 2, the parent/root is a table with 2 rows:

FROM custom.date_full

This START WITH and CONNECT BY relationship determines how rows in the hierarchy will be connected in the results.

There are no duplicates in Query because there is only a single parent row with relationships to multiple child rows.

There are duplicate results in Query 2 because there are multiple parent rows with relationships to multiple child rows.

More general information is available in the following resources:

Why my hierarchy query is showing duplicate records?

2 Answers