Is this an SQL parsing / ambiguity bug ?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Is this an SQL parsing / ambiguity bug ?

Vladimir Vysotsky
I found a case where an "on conflict" clause generates an unexpected error,
could this be an SQL parsing bug or grammar ambiguity?

trivee@work:~/test$ sqlite3 --version
3.29.0 2019-07-10 17:32:03
fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88alt1
trivee@work:~/test$ sqlite3 test.db
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
sqlite> create table src(id integer primary key);
sqlite> create table dst(id integer primary key);
sqlite> insert into dst(id) select id from src on conflict do nothing;
Error: near "do": syntax error
sqlite> insert into dst(id) select id from src where 1 on conflict do
nothing;
sqlite>

-=- Vlad
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this an SQL parsing / ambiguity bug ?

Simon Slavin-3
On 9 Mar 2020, at 8:40pm, Vladimir Vysotsky <[hidden email]> wrote:

> sqlite> insert into dst(id) select id from src on conflict do nothing;
> Error: near "do": syntax error

SQLite does not understand "DO NOTHING".  You probably want "ON CONFLICT IGNORE".

<https://sqlite.org/lang_conflict.html>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is this an SQL parsing / ambiguity bug ?

Richard Hipp-3
On 3/10/20, Simon Slavin <[hidden email]> wrote:
> On 9 Mar 2020, at 8:40pm, Vladimir Vysotsky <[hidden email]> wrote:
>
>> sqlite> insert into dst(id) select id from src on conflict do nothing;
>> Error: near "do": syntax error
>
> SQLite does not understand "DO NOTHING".  You probably want "ON CONFLICT
> IGNORE".

I think Vlad is trying to do an upsert, which does have a DO NOTHING
syntax that I copied from postgres.

The parsing ambiguity that Vlad is complaining about (if I understand
him correctly) is documented at the bottom of the upsert page:
https://www.sqlite.org/lang_UPSERT.html#parseambig

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users