(Possibly) platform-specific issue with ecto_sqlite3 and group_by/2

Questions / Help
, ,
1

Hello everyone,

A reader (@brownerd) of Northwind Elixir Traders has stumbled upon a weird issue that I am unable to replicate and it seems that it’s platform-specific.

The code in question is this very basic query:

  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

When I’m running this function, I get the results just fine:

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}
]

When @brownerd runs the same code, he gets this:

** (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: []
 }
...

We checked our respective dependencies and there is nothing peculiar about them. We both use:

ecto 3.12.5
ecto_sql 3.12.1
ecto_sqlite3 0.15.1
exqlite 0.29.0

There are two differences in environment:

  • @brownerd uses Elixir 1.17, I use 1.18.2 – I doubt that this what causes the issue.
  • @brownerd is on a Mac, I’m on Debian – this smells more like the root cause of the issue.

Has anyone here encountered such an issue before? I have also opened an issue here:

2

And what of the underlying SQLite versions? I’d think that’s the most likely culprit.

3

How do I check this? Is it the version of sqlite3_nif.so within ~/.cache/elixir_make/exqlite-nif-2.17-*.tar.gz?

Or does it relate to anything installed on system-level?

4

Both of you should run sqlite3 --version in your terminals and compare.

5 
$ sqlite3 --version
3.40.1 2022-12-28 14:03:47 ...

Does using SQLite from Elixir depend on the version installed system-wide?

6

Unless you went out of your way to use system resources [1] then your system resources should have little to do with the failures (maybe could be differences in tooling to build the NIF though).

[1] GitHub - elixir-sqlite/exqlite: An SQLite3 driver for Elixir

7

I’m not sure if your test suite is executed using GitHub Actions. However, it would be beneficial to test your code on both Linux and macOS. If you’re not already testing on macOS, it should be added to your testing environment.

8

sqlite3 --version
3.43.2 2023-10-10

9

No tests here, I still don’t know anything about testing :confused:

Also, now I regret installing Debian 12 on my 2012 MacBook Air–though it doesn’t really make a difference, if the issue is related with a precompiled binary for aarch64.

10

As a first step I’d suggest both of you upgrade to the latest SQLite, make sure your versions are identical, and test again.

Eliminate the differences in the environments one by one. The SQLite version should be the first thing that’s synchronized.

11

But how does the SQLite version installed system-wide impact what ex_sqlite uses? I thought it downloaded a precompiled version as a NIF, so since we both use the same package versions, both of our Elixir installations use the same (but most likely compiled for a different CPU arch, as most Macs right now use Apple Silicon)–or not?

tisaak@monster:~$ cd .cache/elixir_make/
tisaak@monster:~/.cache/elixir_make$ ls
exqlite-nif-2.17-x86_64-linux-gnu-0.29.0.tar.gz
tisaak@monster:~/.cache/elixir_make$ tar tf exqlite-nif-2.17-x86_64-linux-gnu-0.29.0.tar.gz 
./sqlite3_nif.so
12

It doesn’t. Sqlite version directly depends on the c source file used by exqlite, namely sqlite3.c.

The easiest method to track down the version is to look at commit history, there you should be able to correlate to what exqlite version it belongs.

13

No idea, I didn’t inspect exqlite. If it bundles SQLite as @D4no0 is saying then you’ll have to dig in there and check the version inside.

14

OK, so we’re both on v0.29.0, so I assume that both of us get the same v2.17 or perhaps @brownerd gets v2.16. Still doesn’t explain why this only impacts :group_by/2. Super weird.

15 
iex(895)> import Ecto.Query
iex(896)> q = (Customer
...(896)>     |> join(:inner, [c], o in assoc(c, :orders))
...(896)>     |> group_by([c, o], c.id)
...(896)>     |> select([c, o], %{id: c.id, name: c.name, num_orders: count(o.id)})
...(896)>     |> order_by([c, o], desc: count(o.id))
...(896)>     |> limit(5))
#Ecto.Query<from c0 in NorthwindElixirTraders.Customer,
 join: o1 in assoc(c0, :orders), group_by: [c0.id],
 order_by: [desc: count(o1.id)], limit: 5,
 select: %{id: c0.id, name: c0.name, num_orders: count(o1.id)}>
iex(898)> Ecto.Adapters.SQL.to_sql(:all, Repo, q) |> elem(0) |> IO.puts
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
:ok

$ sqlite3 northwind_elixir_traders_repo.db 
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> 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;
20|Ernst Handel|10
63|QUICK-Stop|7
65|Rattlesnake Canyon Grocery|7
87|Wartian Herkku|7
37|Hungry Owl All-Night Grocers|6

The query is pretty mundane, no?

16

@brownerd ran the SQL query in sqlite3, and it works. This means that the issue lies with the NIF shipped with ex_sqlite on his platform.

17 
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”

18

updating to {:ecto_sqlite3, “~> 0.18.1”} fixed the issue for me

2 Likes
19

Many thanks to @ruslandoga for figuring it out: No function clause matching in anonymous fn/1 in Ecto.Adapters.SQLite3.Connection.group_by/2 · Issue #160 · elixir-sqlite/ecto_sqlite3 · GitHub

2 Likes