DataAccessException when implementing WITH RECURSIVE query using SQLITE and jOOQ

Viewed 132

I am currently using jOOQ 3.13.4 and SQLite in order to perform a recursive query. I am also restricted to only using Java 8 compatible versions of jOOQ. My general table structure is as follows:

CREATE TABLE group (
    ID                  GUID(32)        UNIQUE NOT NULL PRIMARY KEY,
    ParentID            GUID(32)        group (ID) ON DELETE CASCADE 
);

CREATE TABLE map (
    GroupID          GUID(32)    NOT NULL REFERENCES group(ID) ON DELETE CASCADE,
    Path             TEXT        NOT NULL
);

My goal is to select one group by its group.ID and retrieve a list of all map.Path values that are associated with the nested groups.

I am currently using the WITH RECURSIVE tutorial provided by jOOQ and have code very similar to the one described in the tutorial. The {some_value} is an id I can pass in as a parameter to determine the group that I want all nested paths for.

Table<?> table = table(select(GROUP.ID.as(field(name("id"), UUID)),
                              GROUP.PARENTID.as(field(name("parentId"), UUID)),
                              MAP.PATH.as(field(name("path"), VARCHAR)))
                        .from(GROUP)
                        .join(MAP)
                        .on(MAP.GROUPID.eq(GROUP.ID))).as("table");                                                                          

Field<UUID> id = table.field(name("id"), UUID);
Field<UUID> parentId = table.field(name("parentId"), UUID);
Field<String> path = table.field(name("path"), VARCHAR);

CommonTableExpression<?> cte = name("tree").fields("id", "path")
                                                   .as(select(id,
                                                              path).from(table)
                                                                   .where(id.eq({some_value}))
                                                                   .union(select(id,
                                                                                 path).from(table)
                                                                                      .join(table(name("tree")))
                                                                                      .on(parentId.eq(field(name("tree",
                                                                                                                 "id"),
                                                                                                            UUID)))));

return ctx.withRecursive(cte)
          .selectFrom(cte)
          .fetch(field(name("tree", "path"), VARCHAR), String.class)

However when running the method that contains the above code I receive the following exception:

org.jooq.exception.DataAccessException: SQL [with recursive tree(id, path) as (select * from (select "table".id, "table".path from (select group.ID as id, group.ParentID as parentId, map.Path as path from group join map on map.GroupID = group.ID) as "table" where "table".id = ?) x union select * from (select "table".id, "table".path from (select group.ID as id, group.ParentID as parentId, map.Path as path from group join map on map.GroupID = group.ID) as "table" join tree on "table".parentId = tree.id) x) select tree.id, tree.path from tree]; [SQLITE_ERROR] SQL error or missing database (recursive reference in a subquery: tree)

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (recursive reference in a subquery: tree)

This exception makes me think that you cannot reference table "tree" within the declaration of "tree" for SQLite, but it seems to be able to do so in the tutorial. Is there a caveat with jOOQ and SQLite for WITH RECURSIVE? Or is my aliased table a source of the problem?

0 Answers
Related