*** DRAFT ***
About Documentation Download License Support Purchase

SQLite Requirement Matrix Details
lang_returning.html

Index Summary Markup Original

R-45416-45177-17165-44236-63392-58604-16012-62044 tcl slt th3 src

The effect of the RETURNING clause is to cause the statement to return one result row for each database row that is deleted, inserted, or updated.

th3/cov1/returning01.test:11 

/* IMP: R-45416-45177 */
# EVIDENCE-OF: R-45416-45177 The effect of the RETURNING clause is to
# cause the statement to return one result row for each database row
# that is deleted, inserted, or updated.

R-02066-39846-49596-48067-57975-02968-06614-12287 tcl slt th3 src 

CREATE TABLE t0(
  a INTEGER PRIMARY KEY,
  b DATE DEFAULT CURRENT_TIMESTAMP,
  c INTEGER
);
INSERT INTO t0(c) VALUES(random()) RETURNING *;

In the INSERT statement above, SQLite computes the values for all three columns.

th3/cov1/returning01.test:146 

/* IMP: R-02066-39846 */
# EVIDENCE-OF: R-02066-39846 CREATE TABLE t0( a INTEGER PRIMARY KEY, b
# DATE DEFAULT CURRENT_TIMESTAMP, c INTEGER ); INSERT INTO t0(c)
# VALUES(random()) RETURNING *; In the INSERT statement above, SQLite
# computes the values for all three columns.

R-41357-61664-43525-02491-20290-06673-35745-04652 tcl slt th3 src

The RETURNING clause is followed by a comma-separated list of expressions.

th3/cov1/returning01.test:15 

/* IMP: R-41357-61664 */
# EVIDENCE-OF: R-41357-61664 The RETURNING clause is followed by a
# comma-separated list of expressions.

R-20205-54808-33054-53571-46292-49413-32449-15224 tcl slt th3 src

These expressions are similar to the expressions following the SELECT keyword in a SELECT statement in that they define the values of the columns in the result set.

th3/cov1/returning01.test:163 

/* IMP: R-20205-54808 */
# EVIDENCE-OF: R-20205-54808 These expressions are similar to the
# expressions following the SELECT keyword in a SELECT statement in that
# they define the values of the columns in the result set.

R-25727-24709-18641-35581-28019-63513-51229-00924 tcl slt th3 src

Each expression defines the value for a single column.

th3/cov1/returning01.test:167 

/* IMP: R-25727-24709 */
# EVIDENCE-OF: R-25727-24709 Each expression defines the value for a
# single column.

R-02910-05480-32972-52841-06216-21480-55591-52930 tcl slt th3 src

Each expression may be optionally followed by an AS clause that determines the name of the result column.

th3/cov1/returning01.test:175 

/* IMP: R-02910-05480 */
# EVIDENCE-OF: R-02910-05480 Each expression may be optionally followed
# by an AS clause that determines the name of the result column.

R-46605-62546-04930-07815-45981-57271-29937-28077 tcl slt th3 src

The special "*" expression expands into a list of all non-hidden columns of the table being deleted, inserted, or updated.

th3/cov1/returning01.test:184 

/* IMP: R-46605-62546 */
# EVIDENCE-OF: R-46605-62546 The special "*" expression expands into a
# list of all non-hidden columns of the table being deleted, inserted,
# or updated.

R-13116-43655-17420-11723-30506-15977-20565-12123 tcl slt th3 src

The RETURNING clause only returns rows that are directly modified by the DELETE, INSERT, or UPDATE statement.

th3/cov1/returning04.test:10 

/* IMP: R-13116-43655 */
# EVIDENCE-OF: R-13116-43655 The RETURNING clause only returns rows that
# are directly modified by the DELETE, INSERT, or UPDATE statement.

R-40069-12485-64831-34937-24315-11591-54668-02692 tcl slt th3 src

The RETURNING clause does not report any additional database changes caused by foreign key constraints or triggers.

th3/cov1/returning04.test:13 

/* IMP: R-40069-12485 */
# EVIDENCE-OF: R-40069-12485 The RETURNING clause does not report any
# additional database changes caused by foreign key constraints or
# triggers.

R-15195-28467-04090-15133-31201-08580-44070-48072 tcl slt th3 src

A RETURNING clause for an UPSERT reports both inserted and updated rows.

th3/cov1/returning01.test:120 

/* IMP: R-15195-28467 */
# EVIDENCE-OF: R-15195-28467 A RETURNING clause for an UPSERT reports
# both inserted and updated rows.

R-01364-08748-27865-47497-61554-10289-43301-18311 tcl slt th3 src

The RETURNING clause is not available on DELETE and UPDATE statements against virtual tables.

th3/cov1/returning06.test:30 

/* IMP: R-01364-08748 */
# EVIDENCE-OF: R-01364-08748 The RETURNING clause is not available on
# DELETE and UPDATE statements against virtual tables.

R-43323-52188-61316-01603-51862-46847-20290-63862 tcl slt th3 src

The RETURNING clause cannot be used by statements within triggers.

th3/cov1/returning01.test:215 

/* IMP: R-43323-52188 */
# EVIDENCE-OF: R-43323-52188 The RETURNING clause cannot be used by
# statements within triggers.

R-63299-18573-32017-29403-14917-35510-40700-49799 tcl slt th3 src

The values emitted by the RETURNING clause are the values as seen by the top-level DELETE, INSERT, or UPDATE statement and do not reflect any subsequent value changes made by triggers.

th3/cov1/returning01.test:230 

/* IMP: R-63299-18573 */
# EVIDENCE-OF: R-63299-18573 The values emitted by the RETURNING clause
# are the values as seen by the top-level DELETE, INSERT, or UPDATE
# statement and do not reflect any subsequent value changes made by
# triggers.

R-62434-33746-54785-17605-60783-47230-32024-49540 tcl slt th3 src

Thus, if the database includes AFTER triggers that modifies some of the values of each row inserted or updated, the RETURNING clause emits the original values that are computed before those triggers run.

th3/cov1/returning01.test:235 

/* IMP: R-62434-33746 */
# EVIDENCE-OF: R-62434-33746 Thus, if the database includes AFTER
# triggers that modifies some of the values of each row inserted or
# updated, the RETURNING clause emits the original values that are
# computed before those triggers run.

R-34076-18298-19777-37411-10938-20306-38293-17518 tcl slt th3 src

The RETURNING clause may not contain top-level aggregate functions or window functions.

th3/cov1/returning01.test:36 

/* IMP: R-34076-18298 */
# EVIDENCE-OF: R-34076-18298 The RETURNING clause may not contain
# top-level aggregate functions or window functions.

R-06711-65327-14932-40385-35570-36295-04830-18729 tcl slt th3 src

If there are subqueries in the RETURNING clause, those subqueries may contain aggregates and window functions, but aggregates cannot occur at the top level.

th3/cov1/returning01.test:250 

/* IMP: R-06711-65327 */
# EVIDENCE-OF: R-06711-65327 If there are subqueries in the RETURNING
# clause, those subqueries may contain aggregates and window functions,
# but aggregates cannot occur at the top level.

R-25149-16900-02037-62067-08314-01684-52893-04562 tcl slt th3 src

The RETURNING clause may only reference the table being modified.

/* IMP: R-25149-16900 */
# EVIDENCE-OF: R-25149-16900 The RETURNING clause may only reference the
# table being modified.

R-62902-41226-55424-31114-14414-20606-40238-02650 tcl slt th3 src

In an UPDATE FROM statement, the auxiliary tables named in the FROM clause may not participate in the RETURNING clause.

/* IMP: R-62902-41226 */
# EVIDENCE-OF: R-62902-41226 In an UPDATE FROM statement, the auxiliary
# tables named in the FROM clause may not participate in the RETURNING
# clause.