Hi folks, I am very new to Elixir and Ecto and struggling to express an SQL query in Ecto where I have to select computed values from a CTE into virtual fields.

Schema and query look like this (simplified for readability):

schema "readings" do
  field :read_at, :naive_datetime
  field :value, :integer
  field :previous_value, :integer, virtual: true
  field :current_value, :integer, virtual: true
end

current_values_query =
  Reading
  |> select_merge([r], %{
    current_value:
      coalesce(
        r.value,
        lag(r.value)
        |> over(order_by: r.read_at)
      ),
  })

query = { "current_values", Reading }
  |> with_cte("current_values", as: ^current_values_query)
  |> select_merge([r], %{
    previous_value:
      lag(r.value)
      |> over(order_by: r.read_at),

  })
  |> order_by([r], [asc: r.read_at])

In a nutshell, I am querying a timeseries of current and previous values, and there might be gaps in values, in which which case the effective current value (current_velue) is the value from the previous row.

Because PostgreSQL does not allow values from window functions within window functions, I have to resort to a CTE.

The above query compiles and executs fine, but current_value is always nil.

The generated SQL looks like this, which seems correct, except missing c0.current_value in the second SELECT:

WITH "current_values"
         AS (SELECT sr0."id"                                                 AS "id",
                    sr0."read_at"                                            AS "read_at",
                    sr0."value"                                              AS "value",
                    sr0."gas"                                                AS "gas",
                    sr0."water"                                              AS "water",
                    sr0."inserted_at"                                        AS "inserted_at",
                    sr0."updated_at"                                         AS "updated_at",
                    COALESCE(sr0."value",
                             LAG(sr0."value") OVER (ORDER BY sr0."read_at")) AS "current_value"
             FROM "readings" AS sr0)
SELECT c0."id",
       c0."read_at",
       c0."value",
       c0."gas",
       c0."water",
       c0."inserted_at",
       c0."updated_at",
       LAG(c0."value") OVER (ORDER BY c0."read_at")
FROM "current_values" AS c0
ORDER BY c0."read_at";

Am I doing it wrong? Is this a limitation? Is this a bug?