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

waseigo

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:

https://github.com/elixir-sqlite/ecto_sqlite3/issues/160

18 comments

#ecto #troubleshooting #ecto_sqlite3

0 418 18

2025-02-23 15:16:13 UTC

First 10 of 18 Posts

dimitarvp

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

Post #1

waseigo

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?

Post #2

dimitarvp

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

Post #3

waseigo

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

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

Post #4

Johnny5

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 · GitHub

Post #5

Johnny5

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.

Post #6

waseigo

sqlite3 --version
3.43.2 2023-10-10

Post #7

waseigo

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

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.

Post #8

dimitarvp

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.

Post #9

waseigo

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

Post #10

Load more posts (8 remaining)