On 7/7/18, Paul van Helden <p...@planetgis.co.za> 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
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to