Runtime error with `putMany` on Sqlite

[parsonsmatt]

Bug Reports

Reporting a bug? If relevant, we recommend including:

• Your OS name and version: Ubuntu 18.04

• The versions of tools you're using:

   λ ~/ stack --version
  Version 1.9.3, Git revision 40cf7b37526b86d1676da82167ea8758a854953b (6211 commits) x86_64 
  hpack-0.31.1
  

• The versions of dependencies you're using: resolver: lts-13.3

• Finally, if possible, please reproduce the error in a small script; we have created a script for each backend with a minimal setup to use for this purpose. These are made with Stack's scripting support and can be run as ./file-name.hs. Even better, writing a failing test case is tremendously helpful.

Repro script available as a gist here

Output is:

$ ./repro.hs
Migrating: CREATE TABLE "person"("id" INTEGER PRIMARY KEY,"name" VARCHAR NOT NULL)
repro.hs: SQLite3 returned ErrorError while attempting to perform prepare "INSERT INTO \"person\"(\"name\") VALUES (?) ON CONFLICT () DO UPDATE SET \"name\"=EXCLUDED.\"name\"": near ")": syntax error

[psibi]

@naushadh I assume this would have arisen because of https://github.com/yesodweb/persistent/pull/833/files which seemed to modify putMany sql query. Can you look into this ?

[parsonsmatt]

For what it's worth, putMany seems to work fine on a table that has a Unique key.

[parsonsmatt]

The grammar for Sqlite is surprising. It seems to suggest that you should be able to write ON CONFLICT DO UPDATE .... However, this consistently fails with a syntax error. If I do ON CONFLICT DO NOTHING it's fine. But it seems to consistently fail without a key provided ON CONFLICT (id) DO UPDATE ...

[parsonsmatt]

I've made a mailing list post for this issue: https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg113513.html

[parsonsmatt]

The grammar doesn't tell the whole story:

In the docs there is this line: https://www.sqlite.org/lang_UPSERT.html
"The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING. A DO NOTHING upsert without a conflict target works the same as an INSERT OR IGNORE."

The charts are pretty, and useful, but they can show illegal paths due to various rules like this.

(source)

[psibi]

@parsonsmatt A new persistent version has been released with a fix. @naushadh Thanks for the quick fix!

[parsonsmatt]

Thanks 😄