recursive query sqlite3.OperationalError: near "SELECT": syntax error

Question

So I have the following recursive

WITH RECURSIVE length(len, id, ord) AS (
    SELECT 0, t.id, 1
    FROM temp t
    UNION
    SELECT len + compute(

        SELECT w2.nodeid
        FROM waypoint w2
        WHERE w2.ordinal = ord AND w2.wayid =t.id,

        SELECT w2.nodeid
        FROM waypoint w2
        WHERE w2.ordinal = ord + 1 AND w2.wayid =t.id

    ), t.id, w1.ordinal
    FROM waypoint w1, length, temp t
    WHERE w1.wayid = t.id AND w1.ordinal = ord + 1
),
SELECT *
FROM length

The issue is that I keep getting this error

sqlite3.OperationalError: near "SELECT": syntax error

compute is just a user defined sqlite function that takes two node ids to compute the distance between them.

Answer 1

You have a syntax error in FROM waypoint w1, length, , temp t two commas apeaer near each other.

Try this:

WITH RECURSIVE length(len, id, ord) AS (
    SELECT 0 as len, t.id, 1 as ord
    FROM temp t
    UNION
    SELECT (len + compute(

        SELECT w2.nodeid
        FROM waypoint w2
        WHERE w2.ordinal = ord AND w2.wayid =t.id,

        SELECT w2.nodeid
        FROM waypoint w2
        WHERE w2.ordinal = ord + 1 AND w2.wayid =t.id

    ) )as len, t.id, w1.ordinal as ord
    FROM waypoint w1, length, temp t
    WHERE w1.wayid = t.id AND w1.ordinal = ord + 1
),
SELECT *
FROM length