Unions with value lists

[richardTowers]

It's possible (at least in postgres) to UNION together VALUES expressions. For example:

values(1) union values(2);
-- column1
-- ---------
--        1
--        2
-- (2 rows)

In Sequel, this doesn't seem to be possible:

DB.values([[1]]).union(DB.values([[2]])).sql
# SELECT * FROM (VALUES (1)) AS "t1"

I'm not sure whether this is user error on my part, an intentional limitation, or a bug.

I don't have a practical use case where this would be useful, but I was trying to recreate the recursive CTE example from the postgres docs:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

I think this should be:

DB[:t]
  .with_recursive(
    Sequel.lit("t(n)"),
    DB.values([[1]]),
    DB[:t].select(Sequel[:n] + 1).where(Sequel[:n] < 100),
  ).sum(:n)

... but this returns 1 instead of 5050. Looking at the SQL, it's because it's missed the UNION, so the CTE just has VALUES(1) as the entire expression.

[jeremyevans]

Thanks for the report. This is a bug with a fairly simple fix: ec34d09

[richardTowers]

Amazing - thanks for such a quick fix. I didn't think to check sqlite before, but I can confirm that already worked.