Fwd: ON CONFLICT parser bug?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Fwd: ON CONFLICT parser bug?

Paul van Helden
Hi,

I've used the new UPSERT with success with a complex SELECT, then got to
scratch my head for a while on a much simpler query, so assuming this might
be a bug:

CREATE TABLE T2 (A INTEGER PRIMARY KEY);
INSERT INTO T2 VALUES (1);
CREATE TABLE T1 (A INTEGER PRIMARY KEY);
INSERT INTO T1 VALUES (1);
INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING;

An SQL error has occurred: near "DO": syntax error

sqlite3_prepare_v2 actually stops the statement after DO, so that pzTail
points to " NOTHING".

More complex SELECT statements (seems anything after the table name, e.g.
WHERE or LIMIT will suffice) works:

INSERT INTO T1 (A) SELECT A FROM T2 WHERE 1=1
ON CONFLICT(A) DO NOTHING

I'm sure others have found this already?

Regards,

Paul.
_______________________________________________
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: Fwd: ON CONFLICT parser bug?

Simon Slavin-3
On 7 Jul 2018, at 3:07pm, Paul van Helden <[hidden email]> wrote:

> INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING;

It's possible that many people miss this construction because SQLite has

    INSERT OR IGNORE INTO T1 (A) SELECT A FROM T2;

Simon.
_______________________________________________
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: Fwd: ON CONFLICT parser bug?

Richard Hipp-3
In reply to this post by Paul van Helden
On 7/7/18, Paul van Helden <[hidden email]> wrote:

> Hi,
>
> I've used the new UPSERT with success with a complex SELECT, then got to
> scratch my head for a while on a much simpler query, so assuming this might
> be a bug:
>
> CREATE TABLE T2 (A INTEGER PRIMARY KEY);
> INSERT INTO T2 VALUES (1);
> CREATE TABLE T1 (A INTEGER PRIMARY KEY);
> INSERT INTO T1 VALUES (1);
> INSERT INTO T1 (A) SELECT A FROM T2 ON CONFLICT(A) DO NOTHING;
>

A SELECT in an UPSERT should always contain a WHERE clause.  Like this:

   INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING;

Without the WHERE clause, the ON keyword tricks the parser into
thinking it is processing an "ON" join constraint on the FROM clause.
The WHERE clause resolves the ambiguity.  The extra no-op WHERE clause
is omitted from the prepared statement by the query optimizer and
hence does not slow down the execution of the statement.

This is a known limitation of the UPSERT syntax.  I had intended to
document it, but I apparently forgot to do so, or at least I cannot
find where I documented it right this second.  It is a messy situation
that comes about due to our use of an LALR(1) parser (parsers with
more lookahead also run slower) and by the need to provide full
backwards compatibility with older versions of SQLite.
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Fwd: ON CONFLICT parser bug?

Paul van Helden
>
>
>
> A SELECT in an UPSERT should always contain a WHERE clause.  Like this:
>
>    INSERT INTO t1(a) SELECT a FROM t2 WHERE true ON CONFLICT(A) DO NOTHING;
>
> Without the WHERE clause, the ON keyword tricks the parser into
> thinking it is processing an "ON" join constraint on the FROM clause.
> The WHERE clause resolves the ambiguity.  The extra no-op WHERE clause
> is omitted from the prepared statement by the query optimizer and
> hence does not slow down the execution of the statement.
>
> This is a known limitation of the UPSERT syntax.  I had intended to
> document it, but I apparently forgot to do so, or at least I cannot
> find where I documented it right this second.  It is a messy situation
> that comes about due to our use of an LALR(1) parser (parsers with
> more lookahead also run slower) and by the need to provide full
> backwards compatibility with older versions of SQLite.
>
>
Thanks. Easy to live with! The first time I used UPSERT with a SELECT it
was a JOIN ending with USING(). Worked like a charm!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users