Wrong results for CONNECT BY NOCYCLE

lukaseder
SQL-Fighter

I'm using Exasol 7.0.7 on Windows with Docker:

 

docker run --name EXASOL -p 127.0.0.1:9563:8563 --detach --privileged --stop-timeout 120 exasol/docker-db:latest

 

Now, run this query:

 

SELECT LEVEL 
FROM dual
CONNECT BY NOCYCLE LEVEL < 10

 

It produces

 

|LEVEL|
|-----|
|1    |
|2    |

 

But I don't see why NOCYCLE should have any effect in this particular query. It should produce the same thing as Oracle:

 

|LEVEL|
|-----|
|1    |
|2    |
|3    |
|4    |
|5    |
|6    |
|7    |
|8    |
|9    |

 

In fact, without any usage of PRIOR, I think that NOCYCLE can be safely ignored?

 

4 REPLIES 4

exa-SebastianN
Team Exasol
Team Exasol

Hi Lukas,

thanks for your input 😀

As per Oracles definition a loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendant) of another row. This is independent of the PRIOR condition but just depends on the connect by condition: in your case of LEVEL < 10. The same single row of DUAL is joined repeatedly while executing the connect by. Since there is only one row this leads to a cycle because the row with level 2 has the same row as parent and as child.

I am not sure why Oracle implemented this differently 🤔 That said we are not compatible to Oracle when it comes to cycle detection (see https://community.exasol.com/t5/database-features/connect-by-cycle-detection/ta-p/1666).

Cheers,

Sebastian

lukaseder
SQL-Fighter

Thanks for the link Sebastian.

Informix also behaves like Oracle:

SELECT LEVEL 
from (
  select 1 as dual
  from systables
  where (tabid = 1)
) t (a)
CONNECT BY NOCYCLE LEVEL < 10

Snowflake doesn't support NOCYCLE yet, so can't be compared. The rationale can be seen in the Oracle docs (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Query-Pseudocolumns...😞

NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

So, it wouldn't be possible to "loop" over the dual table otherwise. I'll do some more research to understand why Oracle is doing what it's doing. Of course, this is a rather esoteric edge case. Given that one does not expect cycles in such purely iterative CONNECT BY usages, one wouldn't use the NOCYCLE syntax in the first place.

lukaseder
SQL-Fighter

Doing some further research, I still think this has to do with the presence or absence of PRIOR. To me, at least intuitively, only PRIOR can establish a hierarchy, and thus produce cycles. Without PRIOR, no true hierarchy, and thus no cycle. Oracle seems to require PRIOR and the query I've shown might be working because of undocumented syntax.

Informix doesn't require PRIOR but also doesn't clearly explain what the semantics of NOCYCLE is in the absence of PRIOR: https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_2036.htm#ids_sqs_...

Up to you. Again, I don't think this particular usage is going to happen too often. I ran into this by randomly combining syntax in jOOQ integration tests for the upcoming jOOQ/Exasol integration. I doubt that users will run into this as easily.

exa-SebastianN
Team Exasol
Team Exasol

Thanks for the further input and links, this is highly appreciated 😊

We tried to stay as close to the documented behavior as possible when implementing CONNECT BY. Of course Oracle is the de facto standard for this feature, since it is not standardized, so deviating from their behavior might be a problem for (Oracle) users. On the other hand it is very hard to make sense of Oracles behavior, because their behavior contradicts their documentation and not in a consistent way ... at least I couldn't make sense of it.