Cannot use `values`: "SQLite3 does not support table prefixes"

[dlindenkreuz]

I am trying to use the values/2 macro to filter results by matching compound keys (group_name, name).

vals =
  Enum.map(rows, fn row ->
    %{group_name: row.group_name, name: row.name}
  end)

Repo.all(
  from s in Symbol,
    join: v in values(vals, %{group_name: :string, name: :string}),
    on: [group_name: s.group_name, name: s.name],
)

With SQLite, this error is raised:

** (ArgumentError) SQLite3 does not support table prefixes
    (ecto_sqlite3 0.13.0) lib/ecto/adapters/sqlite3/connection.ex:1894: Ecto.Adapters.SQLite3.Connection.quote_table/2
    (ecto_sqlite3 0.13.0) lib/ecto/adapters/sqlite3/connection.ex:1573: Ecto.Adapters.SQLite3.Connection.create_name/3
    (ecto_sqlite3 0.13.0) lib/ecto/adapters/sqlite3/connection.ex:1553: Ecto.Adapters.SQLite3.Connection.create_names/4
    (ecto_sqlite3 0.13.0) lib/ecto/adapters/sqlite3/connection.ex:1554: Ecto.Adapters.SQLite3.Connection.create_names/4
    (ecto_sqlite3 0.13.0) lib/ecto/adapters/sqlite3/connection.ex:1548: Ecto.Adapters.SQLite3.Connection.create_names/2
    (ecto_sqlite3 0.13.0) lib/ecto/adapters/sqlite3/connection.ex:174: Ecto.Adapters.SQLite3.Connection.all/2
    (ecto_sqlite3 0.13.0) lib/ecto/adapters/sqlite3.ex:184: Ecto.Adapters.SQLite3.prepare/2
    (ecto 3.11.1) lib/ecto/query/planner.ex:182: Ecto.Query.Planner.query_without_cache/4
    (ecto 3.11.1) lib/ecto/query/planner.ex:152: Ecto.Query.Planner.query_prepare/6
    (ecto 3.11.1) lib/ecto/query/planner.ex:127: Ecto.Query.Planner.query_with_cache/8
    (ecto 3.11.1) lib/ecto/repo/queryable.ex:214: Ecto.Repo.Queryable.execute/4
    (ecto 3.11.1) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    iex:6: (file)

I added a debug statement to ecto_sqlite3 to print the arguments of quote_table/2: [(ecto_sqlite3 0.13.0) lib/ecto/adapters/sqlite3/connection.ex:1893: Ecto.Adapters.SQLite3.Connection.quote_table/2] {prefix, name} #=> {[[group_name: :string, name: :string], 2], :values}

The prefix var contains the types keyword list from values invocation and 2 indicating the number of rows. Looks like this doesn't belong there?

[warmwaffles]

hmm that does seem wrong. I'll look into this.

[warmwaffles]

Can you provide an abbreviated schema for Symbol? I'm going to pull together a test to demonstrate the bug.

[dlindenkreuz]

Sure!

defmodule MyApp.Symbols.Symbol do
  use Ecto.Schema

  @primary_key false
  
  schema "symbol" do
    field :group_name, :string, primary_key: true
    field :name, :string, primary_key: true
  end
end

[dlindenkreuz]

@warmwaffles Did you get around to this? I didn't find a workaround on my end 😿

[warmwaffles]

I have not yet. I'll try to get to it soon.

[spicychickensauce]

I'm also running into this issue.

My code:

    types = %{id: :integer, album_id: :integer, loaded_at: :naive_datetime}

    from(a in Album,
      right_join: params in values(params, types),
      on: params.album_id == a.id,
      left_join: t in Track,
      on: t.album_id == a.id and params.loaded_at < t.inserted_at,
      group_by: params.id,
      select: {params.id, count(t.id)}
    )

The error:

** (Ecto.QueryError) SQLite3 adapter does not support values lists in query:

from a0 in Album,
  right_join: v1 in values (id, album_id, loaded_at),
  on: v1.album_id == a0.id,
  left_join: t2 in Track,
  on: is_nil(t2.deleted_at) and (t2.album_id == a0.id and v1.loaded_at < t2.inserted_at),
  where: is_nil(a0.deleted_at),
  group_by: [v1.id],
  select: {v1.id, count(t2.id)}

stacktrace:
  (ecto_sqlite3 0.19.0) lib/ecto/adapters/sqlite3/connection.ex:1041: Ecto.Adapters.SQLite3.Connection.assert_valid_join/2
  (ecto_sqlite3 0.19.0) lib/ecto/adapters/sqlite3/connection.ex:1020: anonymous fn/3 in Ecto.Adapters.SQLite3.Connection.join/2
  (elixir 1.18.4) lib/enum.ex:1714: Enum."-map/2-lists^map/1-1-"/2
  (ecto_sqlite3 0.19.0) lib/ecto/adapters/sqlite3/connection.ex:180: Ecto.Adapters.SQLite3.Connection.all/2
  (ecto_sqlite3 0.19.0) lib/ecto/adapters/sqlite3.ex:251: Ecto.Adapters.SQLite3.prepare/2
  (ecto 3.13.2) lib/ecto/query/planner.ex:202: Ecto.Query.Planner.query_without_cache/4
  (ecto 3.13.2) lib/ecto/query/planner.ex:143: Ecto.Query.Planner.query_with_cache/8
  (ecto 3.13.2) lib/ecto/repo/queryable.ex:223: Ecto.Repo.Queryable.execute/4
  (ecto 3.13.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3

---

The code works with postgres, but unfortunately I need to support both postgres and sqlite. Is there maybe a workaround to make it work? There seems to be ways to get named columns for values expressions in sqlite: https://stackoverflow.com/questions/43913457/how-do-i-name-columns-in-a-values-clause But I haven't been able to convert that into a fragment

[warmwaffles]

You'll have to use a CTE in that case. SQLite does not support values like Postgres does. Generally you are going to find little inconsistencies between other SQL implementations and SQLite like this more and more.

I don't know of a work around. In my personal projects I have not run into this need yet so I don't have a good idea of the need.

[spicychickensauce]

I'd happily use a CTE if knew how 😅 The main problem I have is that even with a fragment, I don't know how I can pass a table like object as a parameter. My input is in the form of [%{id: 1, n: 42}, %{id: 1, n: 13}, %{id: 2, n: 123}]. The only other way besides values I can think of to pass that in a query is via json, but then I don't know how to join to such a json value.

Is values/2 not supported in ecto_sqlite3 because of fundamental issues/differences with sqlite or could it theoretically be supported, e.g. by translating it into this?

with tmp(id, n) as (values (1, 42), (1, 13), (2, 123))

[warmwaffles]

CTE's are actually quite fun to use to make queries more legible in regular raw SQL. If you used https://hexdocs.pm/ecto/Ecto.Query.html#with_cte/3 in your library you could side step this issue.