Documentation Source Text

Check-in [c3bcaaf1e5]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Mention the ON parsing ambiguity of UPSERT on the UPSERT documentation page.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c3bcaaf1e540a8fd728afbd9a82483f01ac3565ec759f18017692449b027f2ad
User & Date: drh 2019-03-29 16:40:07
Context
2019-03-29
16:41
Fix spelling error in the previous check-in. check-in: cfd3eb70b4 user: drh tags: trunk
16:40
Mention the ON parsing ambiguity of UPSERT on the UPSERT documentation page. check-in: c3bcaaf1e5 user: drh tags: trunk
2019-03-28
20:22
Remove the "expr FOLLOWING" case from frame-specs that only describe the starting boundary. check-in: 5eff6feebc user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

3547
3548
3549
3550
3551
3552
3553

























3554
3555
3556
3557
3558
3559
3560
</pre></blockquote>

<p>In this last example, the phonebook2 entry is only
updated if the validDate for the newly inserted value is
newer than the entry already in the table.  If the table already
contains an entry with the same name and a current validDate,
then the WHERE clause causes the DO UPDATE to become a no-op.


























<h3>Limitations</h3>

<p>UPSERT does not currently work for [virtual tables].


<tcl>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
</pre></blockquote>

<p>In this last example, the phonebook2 entry is only
updated if the validDate for the newly inserted value is
newer than the entry already in the table.  If the table already
contains an entry with the same name and a current validDate,
then the WHERE clause causes the DO UPDATE to become a no-op.

<tcl>hd_fragment parseambig {UPSERT parsing ambiguity}</tcl>
<h3>Parsing Ambiguity</h3>

<p>When the [INSERT] statement to which the UPSERT is attached
takes its values from a [SELECT] statement, there is a potential
parsing ambiguity.  The parser might not be able to tell if the
"ON" keyword is introducing the UPSERT or if it is the ON clause
of a join.  To work around this, the SELECT statement should always
include a WHERE clause, even if that WHERE clause is just
"WHERE true".

<p>Ambiguious use of ON:

<blockquote><pre>
INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
</pre></blockquote>

<p>Ambiguity resolved using a WHERE clause:

<blockquote><pre>
INSERT INTO t1 SELECT * FROM t2 <font color="blue">WHERE true</font>
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
</pre></blockquote>

<h3>Limitations</h3>

<p>UPSERT does not currently work for [virtual tables].


<tcl>

Changes to pages/windowfunctions.in.

26
27
28
29
30
31
32
33


34
35


36
37

38
39
40
41
42
43
44
45
<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.
Also, Window functions may only appear in the result set and in the
ORDER BY clause of a SELECT statement.

<p>Window functions come in two varieties: 
[aggregate window functions] and
[built-in window functions].  Any


[aggfunc|aggregate function], including application-defined aggregate
functions, can often be used as an aggregate window function, simply


by adding an appropriate OVER clause.  The built-in window functions,
however, special-case handling in the query planner and hence cannot

be extended by the application.

<p>Here is an example using the built-in row_number()
window function:

<codeblock>
  CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
  INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');







|
>
>
|
|
>
>
|
|
>
|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.
Also, Window functions may only appear in the result set and in the
ORDER BY clause of a SELECT statement.

<p>Window functions come in two varieties: 
[aggregate window functions] and
[built-in window functions].  Every aggregate window function
can also work as a ordinary aggregate function, simply by omitting
the OVER and FILTER clauses.  Furthermore, all of the built-in
[aggfunc|aggregate functions] of SQLite can be used as an
aggregate window function by adding an appropriate OVER clause.
Applications can register new aggregate window functions using
the [sqlite3_create_window_function()] interface.
The built-in window functions, however, require special-case
handling in the query planner and hence new window functions
that exhibit the exceptional properties found in the built-in
window functions cannot be added by the application.

<p>Here is an example using the built-in row_number()
window function:

<codeblock>
  CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
  INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');