Using LAG in SQL returns 'unqualified name' error
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Describe the bug**
Using LAG to compare results with previous time periods returns the following error:
```
Query 1 ERROR at Line 1: : ERROR: Error during rewrite: Error during planning: No field with unqualified name 'LAG(SUM(source.importeVenta),Int64(1)) PARTITION BY [#source.cliente] ORDER BY [#source.mes ASC NULLS LAST]'. Valid fields are 'LAG(SUM(source.importeVenta),Int64(1)) PARTITION BY [#source.cliente AS cliente] ORDER BY [#source.mes AS mes ASC NULLS LAST]', 'source.mes', 'source.cliente', 'SUM(source.importeVenta)'.. Please check logs for additional information.
QUERY: SELECT "source"."mes" AS "mes", "source"."cliente" AS "cliente", SUM("source"."importeVenta") AS "sum", LAG(SUM("source"."importeVenta"), 1) OVER (PARTITION BY "source"."cliente" ORDER BY "source"."mes" ASC) AS "Sum of ImporteVenta (anterior año)", (CAST(SUM("source"."importeVenta") AS FLOAT) / NULLIF(LAG(SUM("source"."importeVenta"), 1) OVER (PARTITION BY "source"."cliente" ORDER BY "source"."mes" ASC), 0)) - 1 AS "Sum of ImporteVenta (% vs anterior año)" FROM (SELECT DATE_TRUNC('year', "Venta"."mesFactura") AS "mes", "Cliente"."nombre" AS "cliente", "Venta"."importeVenta" AS "importeVenta" FROM "Venta" LEFT JOIN "Cliente" ON "Venta"."__cubeJoinField" = "Cliente"."__cubeJoinField") AS "source" GROUP BY "source"."mes", "source"."cliente" ORDER BY "source"."mes" ASC, "source"."cliente" ASC
```
**To Reproduce**
SQL Query used:
```
SELECT
"source"."mes" AS "mes",
"source"."cliente" AS "cliente",
SUM("source"."importeVenta") AS "sum",
LAG(SUM("source"."importeVenta"), 1) OVER (
PARTITION BY "source"."cliente"
ORDER BY
"source"."mes" ASC
) AS "Sum of ImporteVenta (anterior año)",
(
CAST(SUM("source"."importeVenta") AS float) / NULLIF(
LAG(SUM("source"."importeVenta"), 1) OVER (
PARTITION BY "source"."cliente"
ORDER BY
"source"."mes" ASC
),
0
)
) - 1 AS "Sum of ImporteVenta (% vs anterior año)"
FROM
(
SELECT
DATE_TRUNC('year', "Venta"."mesFactura") AS "mes",
"Cliente"."nombre" AS "cliente",
"Venta"."importeVenta" AS "importeVenta"
FROM
"Venta"
LEFT JOIN "Cliente" ON "Venta"."__cubeJoinField" = "Cliente"."__cubeJoinField"
) AS "source"
GROUP BY
"source"."mes",
"source"."cliente"
ORDER BY
"source"."mes" ASC,
"source"."cliente" ASC
```
**Expected behavior**
I expected to see the total sales for each client in a given month, their total sales last month and the percentage change
importeVenta is a simple number-type property and the results do show up when I don't include the lag part of the query
**Version:**
0.36.6
Contributor guide
Assessment
This issue has not been assessed yet.