Right outer join of fragment with date cast gives no case clause matching error

Questions / Help
1

Hello everyone,

I’m trying to get the following expression to give me a selection of a date range generated via a fragment and join it with a table on its date column where my right outer join is the generated date range, but I can’t seem to get it to work through Ecto even though running the generated SQL directly in postgres seems to work just fine

The query:

  from(q in query,
    right_join: day in fragment("select generate_series(cast(? as date), cast(? as date), '1 day')::date AS d", ^start_date, ^end_date),
    on: fragment("date(?) = d", field(q, :inserted_at)),
    group_by: fragment("day"),
    group_by: q.operation,
    select: %{date: fragment("date(d) as day"), operation: q.operation, no_of_ops: count(q.operation)})

The generated SQL query:

SELECT date(d) as day, i0."operation", count(i0."operation") FROM "inventory_orders" AS i0 RIGHT OUTER JOIN (select generate_series(cast($1 as date), cast($2 as date), '1 day')::date AS d) AS f1 ON i0."inserted_at" = d WHERE ((i0."operation" = 'deposit') OR i0."operation" IS NULL) GROUP BY day, i0."operation" ["2022-03-21", "2022-04-20"]

The error I get:

** (CaseClauseError) no case clause matching: {"2022-03-21"}
    (ecto 2.2.12) lib/ecto/adapters/postgres/datetime.ex:715: Ecto.Adapters.Postgres.TypeModule.encode_params/3
    (postgrex 0.13.5) lib/postgrex/query.ex:45: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection 1.1.3) lib/db_connection.ex:1079: DBConnection.describe_run/5
    (db_connection 1.1.3) lib/db_connection.ex:1150: anonymous fn/4 in DBConnection.run_meter/5
    (db_connection 1.1.3) lib/db_connection.ex:1207: DBConnection.run_begin/3
    (db_connection 1.1.3) lib/db_connection.ex:592: DBConnection.prepare_execute/4
    (ecto 2.2.12) lib/ecto/adapters/postgres/connection.ex:73: Ecto.Adapters.Postgres.Connection.prepare_execute/5
    (ecto 2.2.12) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
    (ecto 2.2.12) lib/ecto/adapters/sql.ex:426: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto 2.2.12) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto 2.2.12) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
    (work 0.1.1-alpha.2) web/controllers/inventory_order_context.ex:33: Work.InventoryOrderContext._get_inventory_orders_grouped_by/2
    (work 0.1.1-alpha.2) web/controllers/inventory_order_context.ex:7: Work.InventoryOrderContext.get_inventory_orders_grouped_by/2

The only thing I changed when attempting this query directly in postgres was to wrap the date variables with single quotes but otherwise, it works as is.
Obviously, I’m running an old version of Ecto but would have hoped this didn’t matter.

Any ideas as to what I might be doing wrong here, please? Appreciate the help!

2

Your start_date and end_date values are strings and the query is expecting %Date{} types.

Newer versions of Postgrex improved the error message:

Postgrex expected %Date{}, got “2022-03-21”. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

3

@03juan yes indeed, that seems to be the issue and thank you very much for your response!

1 Like