"error: no such column" on complex "conflict target"

[ewildgoose]

Hi, running this the following in the console is accepted:

INSERT INTO "counter_interfaces" ("device_id","device_type") VALUES ("123","3g") ON CONFLICT ("device_id", COALESCE("sim_id", "")) DO UPDATE SET "device_id" = EXCLUDED."device_id","device_type" = EXCLUDED."device_type","sim_id" = EXCLUDED."sim_id";

However, when run through exqlite I get an error:

iex(7)> {:ok, statement} = Exqlite.Sqlite3.prepare(conn, ~s<INSERT INTO "counter_interfaces" ("device_id","device_type") VALUES (?,?) ON CONFLICT ("device_id",coalesce("sim_id", "")) DO UPDATE SET "device_id" = EXCLUDED."device_id","device_type" = EXCLUDED."device_type","sim_id" = EXCLUDED."sim_id">)                               ** 

(MatchError) no match of right hand side value: {:error, "no such column: "}

The database definition is as follows:

    create table("counter_interfaces") do
      add :device_id,   :string, null: false
      add :device_type, :string,  null: false
      add :sim_id,      :string, null: true
    end
    create unique_index("counter_interfaces", [:device_id, "coalesce(sim_id, '')"], name: :unique_counter_interfaces_on_any_sim_id)

What I'm trying to achieve is a situation where I have a unique constraint on multiple device_id/sim_id pairs, including the case that sim_id is nil (the default case for sqlite is that uniqueness is not enforced for columns which are null)

I'm not clear if this problem is coming from the sqlite parser or something in exqlite? That it works ok on the sqlite command line suggests exqlite? Any suggestions?

[warmwaffles]

Quick question, does that same table definition work for postgres as well?

What I think the issue is is that the unique index contains [:device_id, "coalesce(sim_id, '')"], the unique index has to be a column and not a computed value I think https://sqlite.org/syntax/upsert-clause.html

EDIT: https://sqlite.org/syntax/indexed-column.html

[ewildgoose]

Answer is "I think so". It's quite widely discussed in the issues which lead to the introduction of this field, also it's mentioned specifically in the documentation (see :conflict_target here: https://hexdocs.pm/ecto/Ecto.Repo.html#c:insert/2-options )

The approach is suggested as valid here:

• https://stackoverflow.com/questions/22699409/sqlite-null-and-unique/22699498

I'm totally open to other ideas on how to achieve the required end result though!

I want to use this via Ecto and things I've ruled out:

• I don't think I can use multiple ON CONFLICT clauses (not even sure if the DB allows this?). So I don't think I can use two ON CONFLICT clauses, one for the null case, another for the non null, then use unique index with a WHERE clause
• I can't leave the clause blank on sqlite if I want an "DO REPLACE" (can only leave blank on DO NOTHING)
• Using only column names doesn't enforce the unique constraint because in most DBs you are allowed multiple NULL rows within the unique constraint

Just to be clear, the INSERT statement functions as expected if run through the sqlite cli ! It's only giving me this error if I call exsqlite.prepare. Having looked at your code this is almost a direct passthrough to sqlite, so I assume this is some bug in their parsing of prepared statements?

If you agree, could you please help me describe a bug report to pass upstream? (Challenge of creating reproducible bug reports as you get further from your home turf...)

[warmwaffles]

@ewildgoose elixir-sqlite/ecto_sqlite3#59 that PR is a fix for this ya?

[ewildgoose]

@ewildgoose elixir-sqlite/ecto_sqlite3#59 that PR is a fix for this ya?

Actually, that's the PR which reveals the problem... Prior to that I couldn't send ON CONFLICT with an :unsafe_fragment down the path to your library. So I guess that PR, while not incorrect, isn't so useful until we can figure out why sqlite doesn't accept the contents of the ON CONFLICT (to repeat, it accepts it fine via the cli)

Any thoughts?

[warmwaffles]

Exqlite is just a driver library. It doesn't do the SQL generation. That is done by ecto_sqlite so we need to resolve the issue there. I'll check that PR out later this evening and do some poking.

[ewildgoose]

Hi, I think I'm explaining myself badly. I generate a bunch of SQL "by hand". This SQL functions when run directly in the sqlite cli. However, near identical SQL returns an error when passed into Exqlite.Sqlite3.prepare(conn, "hand generated SQL")

The ecto_sqlite change simply allows me to get to the point of finding myself blocked here. However, checking your library code suggests that "Exqlite.Sqlite3.prepare" is a pretty thin wrapper around the C api, and so that implies the problem is deeper in sqlite I think?

So my question is something like: Why this SQL is accepted in the sqlite cli, but not accepted through the "Exqlite.Sqlite3.prepare" call?

[warmwaffles]

That's a good question. I didn't fully read the part about running it in the prepare part.

Can you copy paste the sql schema for the table? So I can poke around in iex.

[greg-rychlewski]

fwiw I can get this to work if the empty string in coalesce is replaced with single quotes

this works:

{:ok, conn} = Exqlite.Sqlite3.open(":memory:")
:ok = Exqlite.Sqlite3.execute(conn, "create table counter_interfaces (device_id string, device_type string, sim_id string)")
:ok = Exqlite.Sqlite3.execute(conn, ~s<create unique index idx on counter_interfaces (device_id, coalesce(sim_id, ''))>)
{:ok, statement} = Exqlite.Sqlite3.prepare(conn, ~s<INSERT INTO "counter_interfaces" ("device_id","device_type") VALUES (?,?) ON CONFLICT ("device_id",coalesce("sim_id", '')) DO UPDATE SET "device_id" = EXCLUDED."device_id","device_type" = EXCLUDED."device_type","sim_id" = EXCLUDED."sim_id">)  

this doesn't:

{:ok, conn} = Exqlite.Sqlite3.open(":memory:")
:ok = Exqlite.Sqlite3.execute(conn, "create table counter_interfaces (device_id string, device_type string, sim_id string)")
:ok = Exqlite.Sqlite3.execute(conn, ~s<create unique index idx on counter_interfaces (device_id, coalesce(sim_id, ""))>)
{:ok, statement} = Exqlite.Sqlite3.prepare(conn, ~s<INSERT INTO "counter_interfaces" ("device_id","device_type") VALUES (?,?) ON CONFLICT ("device_id",coalesce("sim_id", "")) DO UPDATE SET "device_id" = EXCLUDED."device_id","device_type" = EXCLUDED."device_type","sim_id" = EXCLUDED."sim_id">)  

so it seems like when the string is using double quotes it's being seen as a column identifier, based on the error: {:error, "no such column: "}. you can see if you change empty string to something else, like "s", it will give the error {:error, "no such column: s"}

[ewildgoose]

Ooh! Interesting! Nice spot!

[greg-rychlewski]

It seems like double quoted strings were disabled on purpose: 88b4316

So I believe everything is working as intended

[ruslandoga]

I think this issue can be closed.