#1639 Condition does not work for CTE WITH RECURSIVE .. WHERE ... IN (SELECT * FROM unnest(ARRAY[UUID('...')])...

[Sergey Matvienko]

Hi guys!

I have built the graph query with a loop prevent condition.
The problem is 'in array' condition doesn't work during CTE WITH RECURSIVE.
But works fine outside CTE or on stand-alone tests;
Also query works fine on PostgreSQL with correct output.
Arrays contains uuid type values.
The problem is located on that line:

r.from_id NOT IN (SELECT * FROM unnest(re.path)) and --this does not work

versions affected: 2.5.0, 2.6.0

Schema:

        DROP TABLE IF EXISTS relation_test;
        CREATE TABLE IF NOT EXISTS relation_test
        (
            from_id             uuid,
            from_type           varchar(255),
            to_id               uuid,
            to_type             varchar(255),
            relation_type_group varchar(255),
            relation_type       varchar(255),
            additional_info     varchar,
            CONSTRAINT relation_test_pkey PRIMARY KEY (from_id, from_type, relation_type_group, relation_type, to_id, to_type)
        );

Data:

INSERT INTO relation_test (from_id, from_type, to_id, to_type, relation_type_group, relation_type, additional_info) 
 VALUES 
 ('11111111-0f19-11ec-ba23-e981fc95500d', 'TENANT', '22222222-0f19-11ec-ba23-e981fc95500d', 'ASSET', 'COMMON', 'Contains', null), --head
 ('22222222-0f19-11ec-ba23-e981fc95500d', 'ASSET',  '33333333-0f19-11ec-ba23-e981fc95500d', 'DEVICE', 'COMMON', 'Contains', null),
 ('33333333-0f19-11ec-ba23-e981fc95500d', 'DEVICE', '11111111-0f19-11ec-ba23-e981fc95500d', 'TENANT', 'COMMON', 'Contains', null); --loop 

Query:

WITH RECURSIVE related_entities(from_id, from_type, to_id, to_type, lvl, path)
                   AS (SELECT from_id,
                              from_type,
                              to_id,
                              to_type,
                              1               as lvl,
                              ARRAY [from_id] as path
                       FROM relation_test r
                       WHERE from_id = '11111111-0f19-11ec-ba23-e981fc95500d'
                         and from_type = 'TENANT'
                         and relation_type_group = 'COMMON'
                       GROUP BY r.from_id, r.from_type, r.to_id, r.to_type, 1, ARRAY [from_id]
                       UNION ALL
                       SELECT r.from_id,
                              r.from_type,
                              r.to_id,
                              r.to_type,
                              (re.lvl + 1)                   as lvl,
                              (re.path || ARRAY [r.from_id]) as path
                       FROM relation_test r
                                INNER JOIN related_entities re
                                           ON r.from_id = re.to_id and
                                              r.from_type = re.to_type and
                                              relation_type_group = 'COMMON' and
                                              r.from_id NOT IN (SELECT * FROM unnest(re.path)) and --this does not work
                                              re.lvl <= 7
                       GROUP BY r.from_id, r.from_type, r.to_id, r.to_type,
                                (re.lvl + 1), (re.path || ARRAY [r.from_id]))
SELECT lvl, from_id, from_type, to_id, to_type, path
from related_entities r_int  

Output (wrong, only 3 lines expected):

1 [LVL, FROM_ID, FROM_TYPE, TO_ID, TO_TYPE, PATH]
2 [1, 11111111-0f19-11ec-ba23-e981fc95500d, TENANT, 22222222-0f19-11ec-ba23-e981fc95500d, ASSET, ARRAY[11111111-0f19-11ec-ba23-e981fc95500d]]
3 [2, 22222222-0f19-11ec-ba23-e981fc95500d, ASSET, 33333333-0f19-11ec-ba23-e981fc95500d, DEVICE, ARRAY[11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d]]
4 [3, 33333333-0f19-11ec-ba23-e981fc95500d, DEVICE, 11111111-0f19-11ec-ba23-e981fc95500d, TENANT, ARRAY[11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d]]
5 [4, 11111111-0f19-11ec-ba23-e981fc95500d, TENANT, 22222222-0f19-11ec-ba23-e981fc95500d, ASSET, ARRAY[11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d,11111111-0f19-11ec-ba23-e981fc95500d]]
6 [5, 22222222-0f19-11ec-ba23-e981fc95500d, ASSET, 33333333-0f19-11ec-ba23-e981fc95500d, DEVICE, ARRAY[11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d,11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d]]
7 [6, 33333333-0f19-11ec-ba23-e981fc95500d, DEVICE, 11111111-0f19-11ec-ba23-e981fc95500d, TENANT, ARRAY[11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d,11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d]]
8 [7, 11111111-0f19-11ec-ba23-e981fc95500d, TENANT, 22222222-0f19-11ec-ba23-e981fc95500d, ASSET, ARRAY[11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d,11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d,11111111-0f19-11ec-ba23-e981fc95500d]]
9 [8, 22222222-0f19-11ec-ba23-e981fc95500d, ASSET, 33333333-0f19-11ec-ba23-e981fc95500d, DEVICE, ARRAY[11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d,11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d,33333333-0f19-11ec-ba23-e981fc95500d,11111111-0f19-11ec-ba23-e981fc95500d,22222222-0f19-11ec-ba23-e981fc95500d]]

Note: without lvl control (re.lvl <= 7) we got the org.hsqldb.HsqlException: query execution reached limit of recursion

Outside of CTE the 'in array' condition works fine (stand-alone test)

SELECT UUID('463e5c80-0f38-11ec-8153-55a9f38b54f3') IN (SELECT * FROM unnest(ARRAY[UUID('463e5c80-0f38-11ec-8153-55a9f38b54f3')] || ARRAY[UUID('46957d30-0f38-11ec-8153-55a9f38b54f3')] )) 

properties:

spring.datasource.username=sa
spring.datasource.password=
spring.datasource.url=jdbc:hsqldb:file:target/tmp/testDb;sql.enforce_size=false
spring.datasource.driverClassName=org.hsqldb.jdbc.JDBCDriver

pom.xml

<properties>
          <hsqldb.version>2.6.0</hsqldb.version>
 </properties>
...
 <dependency>
                <groupId>org.hsqldb</groupId>
                <artifactId>hsqldb</artifactId>
                <version>${hsqldb.version}</version>
</dependency>

I hope it will help to perpoduce and fix the problem

[Fred Toussi]

Please use version 2.6.0 or later for this solution:

WITH RECURSIVE related_entities(from_id, from_type, to_id, to_type, lvl, path)
                   AS (SELECT from_id,
                              from_type,
                              to_id,
                              to_type,
                              1               as lvl,
                              ARRAY [from_id] as path
                       FROM relation_test r
                       WHERE from_id = '11111111-0f19-11ec-ba23-e981fc95500d'
                         and from_type = 'TENANT'
                         and relation_type_group = 'COMMON'
                       GROUP BY r.from_id, r.from_type, r.to_id, r.to_type, 1, ARRAY [from_id]
                       UNION ALL
                       SELECT r.from_id,
                              r.from_type,
                              r.to_id,
                              r.to_type,
                              (re.lvl + 1)                   as lvl,
                              (re.path || ARRAY [r.from_id]) as path
                       FROM relation_test r
                                INNER JOIN related_entities re
                                           ON r.from_id = re.to_id and
                                              r.from_type = re.to_type and
                                              relation_type_group = 'COMMON' and
                                              position_array(r.from_id in re.path) = 0 and --this does not work
                                              re.lvl <= 7
                       GROUP BY r.from_id, r.from_type, r.to_id, r.to_type,
                                (re.lvl + 1), (re.path || ARRAY [r.from_id]))
SELECT lvl, from_id, from_type, to_id, to_type, path
from related_entities r_int 

[Sergey Matvienko]

Hi Fred!
Thank you for the solution!
Unfortunatelly, it doesn't suite my task.

I have to write code that compatible with PostgresSQL and it seems the syntax for arrays is different. In compatibility mode too sql.syntax_pgs=true
The desired approach is

AND (g.c1 <> ALL(sg.path)) -- prevent from cycling
A clear explanation of that approach I found on https://www.alibabacloud.com/blog/postgresql-graph-search-practices---10-billion-scale-graph-with-millisecond-response_595039

May be you can add another compatibility for PostgreSQL?
Please :)
Here an examples:

--hsqldb
select position_array('mon' in ARRAY['sun','mon','tue']); --result 2
--psql
select array_position(ARRAY['sun','mon','tue'], 'mon'); --object not found: ARRAY_POSITION

spring.datasource.url=jdbc:hsqldb:file:target/tmp/testDb;sql.enforce_size=false;sql.syntax_pgs=true;

version 2.6.0

Thank you for your effort!

[Fred Toussi]

You can use a simple UNNEST:

r.from_id NOT IN (unnest(re.path)) and --this works

Alternatively, you can define the PostgreSQL style function:

CREATE FUNCTION ARRAY_POSITION(A UUID ARRAY, B UUID) RETURNS INT
BEGIN ATOMIC
RETURN POSITION_ARRAY(B IN A);
END

[Fred Toussi]

Fixed and committed to SVN /base/trunk