On 7/7/18, Paul van Helden <[email protected]> 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 [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
