SQL error when using having: clause

[whscullin]

What happens?

This query when used in conjunction with flights.malloy generates a SQL error:

run: flights -> {
  having: flight_count > 10
  # line_chart
  nest: by_month is {
    group_by: dep_month is dep_time.month
    aggregate: flight_count
  }
  limit: 10
}

The error generated is:

Parser Error: syntax error at or near ")"

This is the generated SQL:

WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set=1 THEN
      DATE_TRUNC('month', base."dep_time")
      END as "dep_month__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "flight_count__1",
    CASE WHEN group_set=0 THEN CASE WHEN (CASE WHEN group_set=0 THEN
      COUNT(1)
      END)>10 THEN 0 ELSE 1 END END as __delete__0
  FROM '../data/flights.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2
)
, __stage1 AS (
  SELECT
    *,
    MAX(CASE WHEN group_set IN (0,1) THEN __delete__0 END) OVER(partition by ) as __shaving__0 
  FROM __stage0)
, __stage2 AS (
  SELECT *
  FROM __stage1
  WHERE NOT ((group_set IN (0,1) AND __shaving__0=1)
  ))
SELECT
  COALESCE(LIST({
    "dep_month": "dep_month__1", 
    "flight_count": "flight_count__1"}  ORDER BY  "dep_month__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_month"
FROM __stage2
LIMIT 10

The broken line is this, note the (partition by ):

 MAX(CASE WHEN group_set IN (0,1) THEN __delete__0 END) OVER(partition by ) as __shaving__0 

To Reproduce

Paste the provided query into flights.malloy, and hit "Run".

OS:

macOS

Malloy Client:

VS Code

Malloy Client Version:

0.0.201

Database Connection:

DuckDB