search

crystal-lang / crystal-sqlite3

SQLite3 bindings for Crystal
https://crystaldoc.info/github/crystal-lang/crystal-sqlite3
MIT License
139 stars 30 forks source link

Upsert with new syntax from SQLIte 3.24.0 fails #72

Closed yb66 closed 3 years ago

yb66 commented 3 years ago

Hi,

This is the error being returned:

Exception occurred: UNIQUE constraint failed: my_join_table.left_id, my_join_table.right_id

Query : INSERT INTO my_join_table(left_id,right_id) VALUES(?,?)
  ON CONFLICT(left_id) DO UPDATE SET right_id = excluded.right_id;
args : [239, 1]

That's run using the DB::QueryMethods#exec method.

If I plug in the values into that statement and run it using the sqlite client (v3.36.0) it works fine. The syntax is from this Sqlite doc.

If there's any more info you need from me, just ask. I'll use one of the older workarounds I've found until (if) this is fixed, so no pressure (but it would be nice if it's looked at 😄 )

Edit: forgive me, I forgot the library info

sqlite3:
    git: https://github.com/crystal-lang/crystal-sqlite3.git
    version: 0.18.0
  db:
    git: https://github.com/crystal-lang/crystal-db.git
    version: 0.10.1

$ crystal --version
Crystal 1.1.0 [af095d72d] (2021-07-14)

LLVM: 10.0.0
Default target: x86_64-apple-macosx

Regards, iain

bcardiff commented 3 years ago

The shard does not interpret the SQL statement. It's directly passed to libsqlite.

Are you sure there is no issue with the existing data in your db? It would be great if you can share code and schema that reproduce the issue.

yb66 commented 3 years ago

@bcardiff I'll try and extract something to look at today. If I find I've made some mistake I'll close this and disappear, sheepishly, into the night 😬

yb66 commented 3 years ago

It was indeed a problem with the code 😬 , not the library. I'm sorry for the wasted time, thanks for taking the time with this.

Regards, iain