No function clause matching in anonymous fn/1 in Ecto.Adapters.SQLite3.Connection.group_by/2

[waseigo]

Versions:

• Elixir 1.17
• ecto 3.12.5
• ecto_sql 3.12.1
• ecto_sqlite3 0.15.1
• exqlite 0.29.0

Relevant code

  def list_top_n_customers_by_order_count(n \\ 5) when is_integer(n) do
    Customer
    |> join(:inner, [c], o in assoc(c, :orders))
    |> group_by([c, o], c.id)
    |> select([c, o], %{id: c.id, name: c.name, num_orders: count(o.id)})
    |> order_by([c, o], desc: count(o.id))
    |> limit(^n)
    |> Repo.all()
  end

Issue

The above query function runs fine on Debian 12, but not on a Mac, as verified by @brownerd.

On Debian 12:

iex(892)> Insights.list_top_n_customers_by_order_count
[
  %{id: 20, name: "Ernst Handel", num_orders: 10},
  %{id: 63, name: "QUICK-Stop", num_orders: 7},
  %{id: 65, name: "Rattlesnake Canyon Grocery", num_orders: 7},
  %{id: 87, name: "Wartian Herkku", num_orders: 7},
  %{id: 37, name: "Hungry Owl All-Night Grocers", num_orders: 6}
]

On Mac:

iex> Insights.list_top_n_customers_by_order_count
** (FunctionClauseError) no function clause matching in anonymous fn/1 in Ecto.Adapters.SQLite3.Connection.group_by/2

The following arguments were given to anonymous fn/1 in Ecto.Adapters.SQLite3.Connection.group_by/2:

 # 1
 %Ecto.Query.ByExpr{
 expr: [{{:., [], [{:&, [], [0]}, :id]}, [], []}],
 file: ".../Northwind/northwind_elixir_traders/lib/northwind_elixir_traders/insights.ex",
 line: 43,
 params: nil,
 subqueries: []
 }
...

Any reason why group_by/2 would not work on a Mac? It's the only thing that seems to be pertinently different between our respective setups--the other one being that I'm on Elixir 1.18.2, but the code was working fine even as far back as 1.14, so this can't be the root cause.

Paging @brownerd in case he can provide more information about the platform.

[ruslandoga]

👋 @waseigo

Would you or @brownerd be able to prepare a minimal example that reproduces the issue?

I ran the following on a Mac and it seems to work.

iex(1)> Mix.install [:ecto_sqlite3]

iex(2)> defmodule Customer do
...(2)>   use Ecto.Schema
...(2)>
...(2)>   schema "customers" do
...(2)>     field :name, :string
...(2)>   end
...(2)> end

iex(3)> defmodule Order do
...(3)>   use Ecto.Schema
...(3)>
...(3)>   schema "orders" do
...(3)>     belongs_to :customer, Customer
...(3)>   end
...(3)> end

iex(4)> defmodule Repo do
...(4)>   use Ecto.Repo, adapter: Ecto.Adapters.SQLite3, otp_app: :demo
...(4)> end

iex(5)> Application.put_env(:demo, Repo, database: "demo.db")

iex(6)> Repo.start_link

iex(7)> Repo.query("create table customers (id integer primary key, name text)")

iex(8)> Repo.query("create table orders (id integer primary key, customer_id integer references customers(id))")

iex(9)> Repo.insert_all("customers", [[name: "Ernst Handel"], [name: "QUICK-Stop"], [name: "Rattlesnake Canyon Grocery"]])

iex(10)> Repo.insert_all("orders", [[customer_id: 1], [customer_id: 1], [customer_id: 2]])

iex(11)> defmodule Customer do
...(11)>   use Ecto.Schema
...(11)>   schema "customers" do
...(11)>     field :name, :string
...(11)>     has_many :orders, Order
...(11)>   end
...(11)> end

iex(12) import Ecto.Query

iex(13)> Customer |> join(:inner, [c], o in assoc(c, :orders)) |> group_by([c, o], c.id) |> select([c, o], %{id: c.id, name: c.name, num_orders: count(o.id)}) |> order_by([c, o], desc: count(o.id)) |> limit(^5) |> Repo.all

# [debug] QUERY OK source="customers" db=0.2ms queue=0.2ms idle=1634.4ms
# SELECT c0."id", c0."name", count(o1."id") FROM "customers" AS c0 INNER JOIN "orders" AS o1 ON o1."customer_id" = c0."id" GROUP BY c0."id" ORDER BY count(o1."id") DESC LIMIT ? [5]
[
  %{id: 1, name: "Ernst Handel", num_orders: 2},
  %{id: 2, name: "QUICK-Stop", num_orders: 1}
]

[ruslandoga]

The error itself is weird, it seems to happen at

ecto_sqlite3/lib/ecto/adapters/sqlite3/connection.ex

Line 1078 in bbe7ebe

| Enum.map_intersperse(group_bys, ", ", fn %ByExpr{expr: expression} ->

but I don't see what isn't matching.

[waseigo]

Hello @ruslandoga, thanks for helping out.

What we found out since yesterday: @brownerd can run the query just fine from within the sqlite3 binary. However, this is irrelevant, as (if I am correct) the underlying exqlite uses the precompiled NIF, and since we are both on the same version of exqlite, we both get the same precompiled shared object sqlite3_nif.so.

I have uploaded the latest version of the SQLite3 db here: https://www.dropbox.com/scl/fi/6dnznvup0acm7rmz6o76f/northwind_elixir_traders_repo.db?rlkey=z3k7aif88cxd37m0kbyrk06h4&st=noqsepx6&dl=0

Do you think I should report this issue to the exqlite repo instead?

[ruslandoga]

I don't think the binary matters, at least with the information provided so far. The error happens during SQL "building" stage.

[waseigo]

@brownerd killed all the deps and re-installed. Upon running iex, he saw this warning during the build:

Generated cc_precompiler app
warning: key "exqlite-nif-2.16-aarch64-linux-gnu-0.29.0.tar.gz" will be overridden in map
└─ nofile:1

[ruslandoga]

https://github.com/brownerd killed all the deps and re-installed.

Could you please try removing _build and running mix compile --force as well? I have a feeling this is more about some outdated .beam artifact and Elixir structs.

[ruslandoga]

Or even better, upload the whole project together with _build and deps somewhere :)

[waseigo]

mix deps.clean --all
rm -rf _build .elixir_ls
mix deps.get
mix compile

"issue still exists. it's just this group_by function that blows up"

[ruslandoga]

I was almost right :) The issue is renamed structs in Ecto.

In the SQLite adapter you are using (ecto_sqlite3 0.15.1) the expected struct is Ecto.Query.QueryExpr

https://github.com/elixir-sqlite/ecto_sqlite3/blob/v0.15.1/lib/ecto/adapters/sqlite3/connection.ex#L1068

But in Ecto 3.12+ it's been renamed to Ecto.Query.ByExpr

The quickfix is to update the adapter to the latest version.

And the real fix is for this adapter to be more strict with the allowed Ecto versions :)

Related:

• Support subqueries in order_by, group_by, distinct, and windows elixir-ecto/ecto#4417 (comment)
• Support for subqueries in order_bys and group_bys elixir-ecto/ecto_sql#607
• FunctionClauseError after Ecto v3.12 update #146

[gandhiShepard]

I tried the example above and I got this error

Customer |> join(:inner, [c], o in assoc(c, :orders)) |> group_by([c, o], c.id) |> select([c, o], %{id: c.id, name: c.name, num_orders: count(o.id)}) |> order_by([c, o], desc: count(o.id)) |> limit(^5) |> Repo.all

error: undefined function limit/2 (there is no such import)
└─ iex:14

(╯°□°）╯** (CompileError) cannot compile code (errors have been logged)

[ruslandoga]

👋 @gandhiShepard

You need to run import Ecto.Query first. Sorry, I missed it while copy-pasting.

It doesn't matter much though, since I think it's clear what the issue is now: #160 (comment)

[waseigo]

Also I don't think that the pin operator is needed in front of "5", but doesn't hurt.

[gandhiShepard]

Gotcha. The example ran fine

09:03:40.015 [debug] QUERY OK source="customers" db=0.2ms queue=0.2ms idle=1534.8ms
SELECT c0."id", c0."name", count(o1."id") FROM "customers" AS c0 INNER JOIN "orders" AS o1 ON o1."customer_id" = c0."id" GROUP BY c0."id" ORDER BY count(o1."id") DESC LIMIT ? [5]

[
%{id: 1, name: "Ernst Handel", num_orders: 2},
%{id: 2, name: "QUICK-Stop", num_orders: 1}
]

[ruslandoga]

Also I don't think that the pin operator is needed in front of "5", but doesn't hurt.

The query is built differently with it. And the original example included it.

[waseigo]

The query is built differently with it. And the original example included it.

Yep, you're right -- it passes 5 as a parameter for the ? placeholder.