Problem with aliasing expression for "a depth-first" query using Esqueleto's "withRecursive"

[ciukstar]

I'm trying to run the following query with a recursive common table expressions using Esqueleto's withRecursive but received an unexpected error at runtime.
Is there a way to fix this?

 do   
    tags <- runDB $ select $ do
        cte <- withRecursive
            (do
                  x <- from $ table @SignTag
                  where_ $ isNothing_ $ x ^. SignTagGroup
                  let level = val (0 :: Int)
                  return (level,x)
            )
            unionAll_
            (\parent -> do
                  (l,_) :& x <- from $ parent
                      `innerJoin` table @SignTag `on` (\((_, p) :& x) -> just (p ^. SignTagId) ==. x ^. SignTagGroup)
                  let level = l +. val 1
                  orderBy [desc level] -- ^ Here is the issue, I think
                  return (level,x)
            )
        from cte

Runtime error:

[Error#yesod-core] SQLite3 returned ErrorError while attempting to perform prepare "WITH RECURSIVE \"cte\" AS (SELECT ? AS \"v\", \"sign_tag\".\"id\" AS \"v2_id\", \"sign_tag\".\"name\" AS \"v2_name\", \"sign_tag\".\"descr\" AS \"v2_descr\", \"sign_tag\".\"group\" AS \"v2_group\"\nFROM \"sign_tag\"\nWHERE \"sign_tag\".\"group\" IS NULL\nUNION ALL\nSELECT \"cte\".\"v\", \"sign_tag2\".\"id\", \"sign_tag2\".\"name\", \"sign_tag2\".\"descr\", \"sign_tag2\".\"group\"\nFROM \"cte\" INNER JOIN \"sign_tag\" AS \"sign_tag2\" ON \"cte\".\"v2_id\" = \"sign_tag2\".\"group\"\nORDER BY \"cte\".\"v\" + ? DESC\n)\nSELECT \"cte\".\"v\", \"cte\".\"v2_id\", \"cte\".\"v2_name\", \"cte\".\"v2_descr\", \"cte\".\"v2_group\"\nFROM \"cte\"\n": 1st ORDER BY term does not match any column in the result set

The generated query is:

WITH RECURSIVE "cte" AS (
SELECT ? AS "v"
  , "sign_tag"."id" AS "v2_id"
  , "sign_tag"."name" AS "v2_name"
  , "sign_tag"."descr" AS "v2_descr"
  , "sign_tag"."group" AS "v2_group"
FROM "sign_tag"
WHERE "sign_tag"."group" IS NULL
UNION ALL
SELECT ("cte"."v" + ?)
     , "sign_tag2"."id"
     , "sign_tag2"."name"
     , "sign_tag2"."descr"
     , "sign_tag2"."group"
FROM "cte" INNER JOIN "sign_tag" AS "sign_tag2" ON "cte"."v2_id" = "sign_tag2"."group"
ORDER BY "cte"."v" + ? DESC
)
SELECT "cte"."v", "cte"."v2_id", "cte"."v2_name", "cte"."v2_descr", "cte"."v2_group"
FROM "cte"

with 0 and 1 as arguments.

[belevy]

Is order by v + 1 legal? If you just order by v does it work?

[ciukstar]

Is order by v + 1 legal? If you just order by v does it work?

ORDER BY "cte"."v" + ? DESC this is what is generated.

I tried this too with the same RUNTIME error (compiles without problems):

...
            unionAll_
            (\parent -> do
                  (l,_) :& x <- from $ parent
                      `innerJoin` table @SignTag `on` (\((_, p) :& x) -> just (p ^. SignTagId) ==. x ^. SignTagGroup)
                  let level = l +. val 1
                  orderBy [desc l]
                  return (level,x)
            )
...

I'm trying to do something like what is described in 3.4. Controlling Depth-First Versus Breadth-First Search Of a Tree Using ORDER BY:

But if we change the ORDER BY clause to add the "DESC" modifier, that will cause lower levels in the organization (with larger "level" values) to be processed first by the recursive-select, resulting in a depth-first search:

WITH RECURSIVE
  under_alice(name,level) AS (
    VALUES('Alice',0)
    UNION ALL
    SELECT org.name, under_alice.level+1
      FROM org JOIN under_alice ON org.boss=under_alice.name
     ORDER BY 2 DESC
  )
SELECT substr('..........',1,level*3) || name FROM under_alice;
The output of this revised query is:

Alice
...Bob
......Dave
......Emma
...Cindy
......Fred
......Gail
When the ORDER BY clause is omitted from the recursive-select, the queue behaves as a FIFO, which results in a breadth-first search.

Maybe there is a way to order by column position? I didn't find anything about this.

[belevy]

Right so it looks like in order to use an expression like v+1 in an order by you need to wrap that in parentheses. That seems to be not specific to how recursive CTEs work. That said sorting by a value v and a value v + 1 should behave identically and should function to work around the error.

[ciukstar]

to wrap that in parentheses

If I may, how to wrap the expression in parentheses?

This clearly doesn't work:)

                  let level = ( l +. val 1 )
                  orderBy [desc level]
                  return (level,x)

[belevy]

Please read my entire reply.

The error you are experiencing is more general than just recursive CTEs and would require a more general fix.

However, you don't actually need to sort on your level variable since sorting on what you have named as l should function identically.

[ciukstar]

Please read my entire reply.

The error you are experiencing is more general than just recursive CTEs and would require a more general fix.

However, you don't actually need to sort on your level variable since sorting on what you have named as l should function identically.

It does not work for:

                  let level = l +. val 1
                  orderBy [desc l]
                  return (level,x)

Error:

SQLite3 returned ErrorError while attempting to perform prepare "WITH RECURSIVE \"cte\" AS (SELECT ? AS \"v\", \"sign_tag\".\"id\" AS \"v2_id\", \"sign_tag\".\"name\" AS \"v2_name\", \"sign_tag\".\"descr\" AS \"v2_descr\", \"sign_tag\".\"group\" AS \"v2_group\"\nFROM \"sign_tag\"\nWHERE \"sign_tag\".\"group\" IS NULL\nUNION ALL\nSELECT (\"cte\".\"v\" + ?), \"sign_tag2\".\"id\", \"sign_tag2\".\"name\", \"sign_tag2\".\"descr\", \"sign_tag2\".\"group\"\nFROM \"cte\" INNER JOIN \"sign_tag\" AS \"sign_tag2\" ON \"cte\".\"v2_id\" = \"sign_tag2\".\"group\"\nORDER BY \"cte\".\"v\" DESC\n)\nSELECT \"cte\".\"v\", \"cte\".\"v2_id\", \"cte\".\"v2_name\", \"cte\".\"v2_descr\", \"cte\".\"v2_group\"\nFROM \"cte\"\n": 1st ORDER BY term does not match any column in the result set

Generated SQL:

WITH RECURSIVE "cte" AS (
SELECT ? AS "v"
     , "sign_tag"."id" AS "v2_id"
     , "sign_tag"."name" AS "v2_name"
     , "sign_tag"."descr" AS "v2_descr"
     , "sign_tag"."group" AS "v2_group"
FROM "sign_tag"
WHERE "sign_tag"."group" IS NULL
UNION ALL
SELECT ("cte"."v" + ?)
       , "sign_tag2"."id"
       , "sign_tag2"."name"
       , "sign_tag2"."descr"
       , "sign_tag2"."group"
FROM "cte" INNER JOIN "sign_tag" AS "sign_tag2" ON "cte"."v2_id" = "sign_tag2"."group"
ORDER BY "cte"."v" DESC
)
SELECT "cte"."v", "cte"."v2_id", "cte"."v2_name", "cte"."v2_descr", "cte"."v2_group"
FROM "cte"

[belevy]

You could probably just orderBy [desc (val 1)] but that seems to be sqlite specific syntax.

Ultimately the bug that needs fixing is to properly parenthesize expressions in order by clauses.

As an aside, the whole order of the rows popping off the work queue thing is super low level and not all that intuitive to me(but I don't really use recursive CTEs all that much). I would personally prefer to keep IDs to reference who my parent is rather than relying on the order of the results to build a tree like that.

[ciukstar]

You could probably just orderBy [desc (val 1)] but that seems to be sqlite specific syntax.

Unfortunately this doesn't work either:

                  let level = l +. val 1
                  orderBy [desc (val (1 :: Int))]
                  return (level,x)

Error:

[Error#yesod-core] SQLite3 returned ErrorError while attempting to perform prepare "WITH RECURSIVE \"cte\" AS (SELECT ? AS \"v\", \"sign_tag\".\"id\" AS \"v2_id\", \"sign_tag\".\"name\" AS \"v2_name\", \"sign_tag\".\"descr\" AS \"v2_descr\", \"sign_tag\".\"group\" AS \"v2_group\"\nFROM \"sign_tag\"\nWHERE \"sign_tag\".\"group\" IS NULL\nUNION ALL\nSELECT (\"cte\".\"v\" + ?), \"sign_tag2\".\"id\", \"sign_tag2\".\"name\", \"sign_tag2\".\"descr\", \"sign_tag2\".\"group\"\nFROM \"cte\" INNER JOIN \"sign_tag\" AS \"sign_tag2\" ON \"cte\".\"v2_id\" = \"sign_tag2\".\"group\"\nORDER BY ? DESC\n)\nSELECT \"cte\".\"v\", \"cte\".\"v2_id\", \"cte\".\"v2_name\", \"cte\".\"v2_descr\", \"cte\".\"v2_group\"\nFROM \"cte\"\n": 1st ORDER BY term does not match any column in the result set @(yesod-core-1.6.25.1

Generated query:

[Debug#SQL] 

WITH RECURSIVE "cte" AS (SELECT ? AS "v", "sign_tag"."id" AS "v2_id", "sign_tag"."name" AS "v2_name", "sign_tag"."descr" AS "v2_descr", "sign_tag"."group" AS "v2_group"
FROM "sign_tag"
WHERE "sign_tag"."group" IS NULL
UNION ALL
SELECT ("cte"."v" + ?), "sign_tag2"."id", "sign_tag2"."name", "sign_tag2"."descr", "sign_tag2"."group"
FROM "cte" INNER JOIN "sign_tag" AS "sign_tag2" ON "cte"."v2_id" = "sign_tag2"."group"
ORDER BY ? DESC
)
SELECT "cte"."v", "cte"."v2_id", "cte"."v2_name", "cte"."v2_descr", "cte"."v2_group"
FROM "cte"
;

 [PersistInt64 0,PersistInt64 1,PersistInt64 1]