Choose any three.
SQLite Requirement Matrix Details
lang_select.html
Index Summary Markup Original
R-20637-43463-07167-46903-44256-61988-15144-63319 tcl slt th3 src
A simple SELECT statement is an aggregate query if it contains either a GROUP BY clause or one or more aggregate functions in the result-set.
/* IMP: R-20637-43463 */
# EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate # query if it contains either a GROUP BY clause or one or more aggregate # functions in the result-set.
R-23155-55597-19897-49290-61770-15838-04803-37418 tcl slt th3 src
Otherwise, if a simple SELECT contains no aggregate functions or a GROUP BY clause, it is a non-aggregate query.
/* IMP: R-23155-55597 */
# EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no # aggregate functions or a GROUP BY clause, it is a non-aggregate query.
R-16074-54196-43824-25031-17914-10655-20694-13926 tcl slt th3 src
If the FROM clause is omitted from a simple SELECT statement, then the input data is implicitly a single row zero columns wide
/* IMP: R-16074-54196 */
# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple # SELECT statement, then the input data is implicitly a single row zero # columns wide
R-59237-46742-53933-54312-09951-05866-00248-19595 tcl slt th3 src
A subquery specified in the table-or-subquery following the FROM clause in a simple SELECT statement is handled as if it was a table containing the data returned by executing the subquery statement.
/* IMP: R-59237-46742 */
# EVIDENCE-OF: R-59237-46742 A subquery specified in the # table-or-subquery following the FROM clause in a simple SELECT # statement is handled as if it was a table containing the data returned # by executing the subquery statement.
R-27438-53558-39373-64011-10542-64778-48166-54900 tcl slt th3 src
Each column of the subquery has the collation sequence and affinity of the corresponding expression in the subquery statement.
/* IMP: R-27438-53558 */
# EVIDENCE-OF: R-27438-53558 Each column of the subquery has the # collation sequence and affinity of the corresponding expression in the # subquery statement.
R-45424-07352-14354-52803-50663-63365-62375-01898 tcl slt th3 src
If there is only a single table or subquery in the FROM clause, then the input data used by the SELECT statement is the contents of the named table.
/* IMP: R-45424-07352 */
# EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery # in the FROM clause, then the input data used by the SELECT statement # is the contents of the named table.
R-28355-09804-02024-51679-13656-23007-30178-16776 tcl slt th3 src
If there is more than one table or subquery in FROM clause then the contents of all tables and/or subqueries are joined into a single dataset for the simple SELECT statement to operate on.
/* IMP: R-28355-09804 */
# EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery # in FROM clause then the contents of all tables and/or subqueries are # joined into a single dataset for the simple SELECT statement to # operate on.
R-59089-25828-47807-24089-43105-44116-25711-61833 tcl slt th3 src
The columns of the cartesian product dataset are, in order, all the columns of the left-hand dataset followed by all the columns of the right-hand dataset.
/* IMP: R-59089-25828 */
# EVIDENCE-OF: R-59089-25828 The columns of the cartesian product # dataset are, in order, all the columns of the left-hand dataset # followed by all the columns of the right-hand dataset.
R-44414-54710-39878-46703-10464-52029-15490-53304 tcl slt th3 src
There is a row in the cartesian product dataset formed by combining each unique combination of a row from the left-hand and right-hand datasets.
/* IMP: R-44414-54710 */
# EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product # dataset formed by combining each unique combination of a row from the # left-hand and right-hand datasets.
R-18439-38548-20374-55698-53430-21042-59445-48532 tcl slt th3 src
In other words, if the left-hand dataset consists of Nleft rows of Mleft columns, and the right-hand dataset of Nright rows of Mright columns, then the cartesian product is a dataset of Nleft×Nright rows, each containing Mleft+Mright columns.
/* IMP: R-18439-38548 */
# EVIDENCE-OF: R-18439-38548 In other words, if the left-hand dataset # consists of Nleft rows of Mleft columns, and the right-hand dataset of # Nright rows of Mright columns, then the cartesian product is a dataset # of Nleft×Nright rows, each containing Mleft+Mright columns.
R-49872-03192-58704-53251-31114-19723-47133-57999 tcl slt th3 src
If the join-operator is "CROSS JOIN", "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, then the result of the join is simply the cartesian product of the left and right-hand datasets.
tcl/e_select.test:383 tcl/e_select2.test:347
/* IMP: R-49872-03192 */
# EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
# "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
# clause, then the result of the join is simply the cartesian product of
# the left and right-hand datasets.
R-38465-03616-05704-04458-16230-08535-12299-37595 tcl slt th3 src
If there is an ON clause then the ON expression is evaluated for each row of the cartesian product as a boolean expression. Only rows for which the expression evaluates to true are included from the dataset.
tcl/e_select.test:513 tcl/e_select2.test:371
/* IMP: R-38465-03616 */
# EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON # expression is evaluated for each row of the cartesian product as a # boolean expression. Only rows for which the expression evaluates to # true are included from the dataset.
R-49933-05137-11348-33836-49788-49818-47982-53945 tcl slt th3 src
If there is a USING clause then each of the column names specified must exist in the datasets to both the left and right of the join-operator.
/* IMP: R-49933-05137 */
# EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the # column names specified must exist in the datasets to both the left and # right of the join-operator.
R-22776-52830-07659-57324-38629-30744-28731-31224 tcl slt th3 src
For each pair of named columns, the expression "lhs.X = rhs.X" is evaluated for each row of the cartesian product as a boolean expression. Only rows for which all such expressions evaluates to true are included from the result set.
/* IMP: R-22776-52830 */
# EVIDENCE-OF: R-22776-52830 For each pair of named columns, the # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian # product as a boolean expression. Only rows for which all such # expressions evaluates to true are included from the result set.
R-54046-48600-47086-52711-24230-00638-25946-34111 tcl slt th3 src
When comparing values as a result of a USING clause, the normal rules for handling affinities, collation sequences and NULL values in comparisons apply.
/* IMP: R-54046-48600 */
# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a # USING clause, the normal rules for handling affinities, collation # sequences and NULL values in comparisons apply.
R-38422-04402-49671-03749-58225-12387-56102-01352 tcl slt th3 src
The column from the dataset on the left-hand side of the join-operator is considered to be on the left-hand side of the comparison operator (=) for the purposes of collation sequence and affinity precedence.
/* IMP: R-38422-04402 */
# EVIDENCE-OF: R-38422-04402 The column from the dataset on the # left-hand side of the join-operator is considered to be on the # left-hand side of the comparison operator (=) for the purposes of # collation sequence and affinity precedence.
R-57047-10461-09824-05395-44122-51121-43183-19652 tcl slt th3 src
For each pair of columns identified by a USING clause, the column from the right-hand dataset is omitted from the joined dataset.
/* IMP: R-57047-10461 */
# EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a # USING clause, the column from the right-hand dataset is omitted from # the joined dataset.
R-56132-15700-29603-38264-36933-60961-45364-58760 tcl slt th3 src
This is the only difference between a USING clause and its equivalent ON constraint.
/* IMP: R-56132-15700 */
# EVIDENCE-OF: R-56132-15700 This is the only difference between a USING # clause and its equivalent ON constraint.
R-04932-55942-18064-33744-52944-17414-47744-37595 tcl slt th3 src
If the NATURAL keyword is in the join-operator then an implicit USING clause is added to the join-constraints. The implicit USING clause contains each of the column names that appear in both the left and right-hand input datasets.
/* IMP: R-04932-55942 */
# EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the # join-operator then an implicit USING clause is added to the # join-constraints. The implicit USING clause contains each of the # column names that appear in both the left and right-hand input # datasets.
R-49566-01570-19269-51029-21052-63278-54120-39261 tcl slt th3 src
If the left and right-hand input datasets feature no common column names, then the NATURAL keyword has no effect on the results of the join.
/* IMP: R-49566-01570 */
# EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets # feature no common column names, then the NATURAL keyword has no effect # on the results of the join.
R-39625-59133-51160-16821-08966-29861-61919-63168 tcl slt th3 src
A USING or ON clause may not be added to a join that specifies the NATURAL keyword.
/* IMP: R-39625-59133 */
# EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a # join that specifies the NATURAL keyword.
R-24610-05866-31843-28121-00824-18930-59704-16191 tcl slt th3 src
If the join-operator is a "LEFT JOIN" or "LEFT OUTER JOIN", then after the ON or USING filtering clauses have been applied, an extra row is added to the output for each row in the original left-hand input dataset that does not match any row in the right-hand dataset.
/* IMP: R-24610-05866 */
# EVIDENCE-OF: R-24610-05866 If the join-operator is a "LEFT JOIN" or # "LEFT OUTER JOIN", then after the ON or USING filtering clauses have # been applied, an extra row is added to the output for each row in the # original left-hand input dataset that does not match any row in the # right-hand dataset.
R-15607-52988-55821-17631-36260-55856-06121-21546 tcl slt th3 src
The added rows contain NULL values in the columns that would normally contain values copied from the right-hand input dataset.
/* IMP: R-15607-52988 */
# EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the # columns that would normally contain values copied from the right-hand # input dataset.
R-15753-04279-34093-62735-52476-02019-63320-45902 tcl slt th3 src
If the join-operator is a "RIGHT JOIN" or "RIGHT OUTER JOIN", then after the ON or USING filtering clauses have been applied, an extra row is added to the output for each row in the original right-hand input dataset that does not match any row in the left-hand dataset.
/* IMP: R-15753-04279 */
# EVIDENCE-OF: R-15753-04279 If the join-operator is a "RIGHT JOIN" or # "RIGHT OUTER JOIN", then after the ON or USING filtering clauses have # been applied, an extra row is added to the output for each row in the # original right-hand input dataset that does not match any row in the # left-hand dataset.
R-02534-00960-24847-49634-15008-56172-25293-49709 tcl slt th3 src
The added rows contain NULL values in the columns that would normally contain values copied from the left-hand input dataset.
/* IMP: R-02534-00960 */
# EVIDENCE-OF: R-02534-00960 The added rows contain NULL values in the # columns that would normally contain values copied from the left-hand # input dataset.
R-18902-30764-50144-06285-62230-16633-33845-18068 tcl slt th3 src
A "FULL JOIN" or "FULL OUTER JOIN" is a combination of a "LEFT JOIN" and a "RIGHT JOIN".
/* IMP: R-18902-30764 */
# EVIDENCE-OF: R-18902-30764 A "FULL JOIN" or "FULL OUTER JOIN" is a # combination of a "LEFT JOIN" and a "RIGHT JOIN".
R-20569-30326-20759-60917-34243-38983-18543-24657 tcl slt th3 src
Extra rows of output are added for each row in left dataset that matches no rows in the right, and for each row in the right dataset that matches no rows in the left.
/* IMP: R-20569-30326 */
# EVIDENCE-OF: R-20569-30326 Extra rows of output are added for each row # in left dataset that matches no rows in the right, and for each row in # the right dataset that matches no rows in the left.
R-39317-26817-16448-42621-51632-09123-64957-11200 tcl slt th3 src
Unmatched columns are filled in with NULL.
/* IMP: R-39317-26817 */
# EVIDENCE-OF: R-39317-26817 Unmatched columns are filled in with NULL.
R-28760-53843-47957-08431-27887-29261-48374-11570 tcl slt th3 src
When more than two tables are joined together as part of a FROM clause, the join operations are processed in order from left to right. In other words, the FROM clause (A join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C).
/* IMP: R-28760-53843 */
# EVIDENCE-OF: R-28760-53843 When more than two tables are joined # together as part of a FROM clause, the join operations are processed # in order from left to right. In other words, the FROM clause (A # join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C).
R-46256-57243-24322-24814-28812-31567-52831-61261 tcl slt th3 src
There is no difference between the "INNER JOIN", "JOIN" and "," join operators.
tcl/e_select.test:392 tcl/e_select2.test:352
/* IMP: R-46256-57243 */
# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER # JOIN", "JOIN" and "," join operators.
R-25071-21202-61916-09187-06401-02826-31838-35119 tcl slt th3 src
The "CROSS JOIN" join operator produces the same result as the "INNER JOIN", "JOIN" and "," operators
tcl/e_select.test:395 tcl/e_select2.test:355
/* IMP: R-25071-21202 */
# EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the # same result as the "INNER JOIN", "JOIN" and "," operators
R-60775-64916-04549-62415-27709-55207-27671-06558 tcl slt th3 src
If a WHERE clause is specified, the WHERE expression is evaluated for each row in the input data as a boolean expression. Only rows for which the WHERE clause expression evaluates to true are included from the dataset before continuing.
/* IMP: R-60775-64916 */
# EVIDENCE-OF: R-60775-64916 If a WHERE clause is specified, the WHERE # expression is evaluated for each row in the input data as a boolean # expression. Only rows for which the WHERE clause expression evaluates # to true are included from the dataset before continuing.
R-36327-17224-30714-13238-62239-10610-34314-51642 tcl slt th3 src
If a result expression is the special expression "*" then all columns in the input data are substituted for that one expression.
/* IMP: R-36327-17224 */
# EVIDENCE-OF: R-36327-17224 If a result expression is the special # expression "*" then all columns in the input data are substituted for # that one expression.
R-43693-30522-63226-58235-18305-62021-52549-18888 tcl slt th3 src
If the expression is the alias of a table or subquery in the FROM clause followed by ".*" then all columns from the named table or subquery are substituted for the single expression.
/* IMP: R-43693-30522 */
# EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table # or subquery in the FROM clause followed by ".*" then all columns from # the named table or subquery are substituted for the single expression.
R-38023-18396-22713-13690-61527-29951-04231-55806 tcl slt th3 src
It is an error to use a "*" or "alias.*" expression in any context other than a result expression list.
/* IMP: R-38023-18396 */
# EVIDENCE-OF: R-38023-18396 It is an error to use a "*" or "alias.*" # expression in any context other than a result expression list.
R-44324-41166-42942-47705-27184-58566-20473-14339 tcl slt th3 src
It is also an error to use a "*" or "alias.*" expression in a simple SELECT query that does not have a FROM clause.
/* IMP: R-44324-41166 */
# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or # "alias.*" expression in a simple SELECT query that does not have a # FROM clause.
R-08669-22397-26856-32296-22372-63471-24126-56225 tcl slt th3 src
The number of columns in the rows returned by a simple SELECT statement is equal to the number of expressions in the result expression list after substitution of * and alias.* expressions.
/* IMP: R-08669-22397 */
# EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned # by a simple SELECT statement is equal to the number of expressions in # the result expression list after substitution of * and alias.* # expressions.
R-44050-47362-62664-60634-46008-39199-23714-03291 tcl slt th3 src
If the SELECT statement is a non-aggregate query, then each expression in the result expression list is evaluated for each row in the dataset filtered by the WHERE clause.
/* IMP: R-44050-47362 */
# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate # query, then each expression in the result expression list is evaluated # for each row in the dataset filtered by the WHERE clause.
R-57629-25253-01540-58314-47057-32967-17520-58138 tcl slt th3 src
If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset.
/* IMP: R-57629-25253 */
# EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate # query without a GROUP BY clause, then each aggregate expression in the # result-set is evaluated once across the entire dataset.
R-26684-40576-09126-53282-12439-40003-41887-60526 tcl slt th3 src
Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset.
/* IMP: R-26684-40576 */
# EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the # result-set is evaluated once for an arbitrarily selected row of the # dataset.
R-27994-60376-08852-47971-54191-53637-09431-08610 tcl slt th3 src
The same arbitrarily selected row is used for each non-aggregate expression.
/* IMP: R-27994-60376 */
# EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used # for each non-aggregate expression.
R-04486-07266-01902-48649-32493-31514-19208-51625 tcl slt th3 src
Or, if the dataset contains zero rows, then each non-aggregate expression is evaluated against a row consisting entirely of NULL values.
/* IMP: R-04486-07266 */
# EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then # each non-aggregate expression is evaluated against a row consisting # entirely of NULL values.
R-51988-01124-61073-07192-64442-28469-58291-20254 tcl slt th3 src
The single row of result-set data created by evaluating the aggregate and non-aggregate expressions in the result-set forms the result of an aggregate query without a GROUP BY clause.
/* IMP: R-51988-01124 */
# EVIDENCE-OF: R-51988-01124 The single row of result-set data created # by evaluating the aggregate and non-aggregate expressions in the # result-set forms the result of an aggregate query without a GROUP BY # clause.
R-64138-28774-62367-40797-31143-24203-38235-49085 tcl slt th3 src
An aggregate query without a GROUP BY clause always returns exactly one row of data, even if there are zero rows of input data.
/* IMP: R-64138-28774 */
# EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY # clause always returns exactly one row of data, even if there are zero # rows of input data.
R-40855-36147-31785-54623-27721-28379-48170-05800 tcl slt th3 src
If the SELECT statement is an aggregate query with a GROUP BY clause, then each of the expressions specified as part of the GROUP BY clause is evaluated for each row of the dataset according to the processing rules stated below for ORDER BY expressions. Each row is then assigned to a "group" based on the results; rows for which the results of evaluating the GROUP BY expressions are the same get assigned to the same group.
/* IMP: R-40855-36147 */
# EVIDENCE-OF: R-40855-36147 If the SELECT statement is an aggregate # query with a GROUP BY clause, then each of the expressions specified # as part of the GROUP BY clause is evaluated for each row of the # dataset according to the processing rules stated below for ORDER BY # expressions. Each row is then assigned to a "group" based on the # results; rows for which the results of evaluating the GROUP BY # expressions are the same get assigned to the same group.
R-14926-50129-46614-17358-58969-23562-31943-57141 tcl slt th3 src
For the purposes of grouping rows, NULL values are considered equal.
/* IMP: R-14926-50129 */
# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL # values are considered equal.
R-10470-30318-56877-28142-06718-49670-04430-59485 tcl slt th3 src
The usual rules for selecting a collation sequence with which to compare text values apply when evaluating expressions in a GROUP BY clause.
/* IMP: R-10470-30318 */
# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation # sequence with which to compare text values apply when evaluating # expressions in a GROUP BY clause.
R-25883-55063-43215-03328-35797-53686-57723-04298 tcl slt th3 src
The expressions in the GROUP BY clause do not have to be expressions that appear in the result.
/* IMP: R-25883-55063 */
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do # not have to be expressions that appear in the result.
R-63573-50730-47689-26625-21808-11120-05729-38640 tcl slt th3 src
The expressions in a GROUP BY clause may not be aggregate expressions.
/* IMP: R-63573-50730 */
# EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may # not be aggregate expressions.
R-31537-00101-65351-40298-33316-64699-39889-60990 tcl slt th3 src
If a HAVING clause is specified, it is evaluated once for each group of rows as a boolean expression. If the result of evaluating the HAVING clause is false, the group is discarded.
/* IMP: R-31537-00101 */
# EVIDENCE-OF: R-31537-00101 If a HAVING clause is specified, it is # evaluated once for each group of rows as a boolean expression. If the # result of evaluating the HAVING clause is false, the group is # discarded.
R-04132-09474-00805-46196-04402-30131-02139-26149 tcl slt th3 src
If the HAVING clause is an aggregate expression, it is evaluated across all rows in the group.
/* IMP: R-04132-09474 */
# EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate # expression, it is evaluated across all rows in the group.
R-28262-47447-02372-41671-47406-00862-29752-13937 tcl slt th3 src
If a HAVING clause is a non-aggregate expression, it is evaluated with respect to an arbitrarily selected row from the group.
/* IMP: R-28262-47447 */
# EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate # expression, it is evaluated with respect to an arbitrarily selected # row from the group.
R-55403-13450-43666-61222-09717-03349-14429-52085 tcl slt th3 src
The HAVING expression may refer to values, even aggregate functions, that are not in the result.
/* IMP: R-55403-13450 */
# EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values, # even aggregate functions, that are not in the result.
R-23927-54081-32743-59723-35169-61192-28393-34691 tcl slt th3 src
Each expression in the result-set is then evaluated once for each group of rows.
/* IMP: R-23927-54081 */
# EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then # evaluated once for each group of rows.
R-53735-47017-47286-05670-21598-63500-17525-58856 tcl slt th3 src
If the expression is an aggregate expression, it is evaluated across all rows in the group.
/* IMP: R-53735-47017 */
# EVIDENCE-OF: R-53735-47017 If the expression is an aggregate # expression, it is evaluated across all rows in the group.
R-62913-19830-16030-42655-41174-29116-40982-36331 tcl slt th3 src
Otherwise, it is evaluated against a single arbitrarily chosen row from within the group.
/* IMP: R-62913-19830 */
# EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single # arbitrarily chosen row from within the group.
R-53924-08809-36668-27423-20589-58335-57366-25050 tcl slt th3 src
If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row.
/* IMP: R-53924-08809 */
# EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate # expression in the result-set, then all such expressions are evaluated # for the same row.
R-19334-12811-52965-43642-11320-54818-13590-65507 tcl slt th3 src
Each group of input dataset rows contributes a single row to the set of result rows.
/* IMP: R-19334-12811 */
# EVIDENCE-OF: R-19334-12811 Each group of input dataset rows # contributes a single row to the set of result rows.
R-02223-49279-19512-29951-11875-18036-12006-07318 tcl slt th3 src
Subject to filtering associated with the DISTINCT keyword, the number of rows returned by an aggregate query with a GROUP BY clause is the same as the number of groups of rows produced by applying the GROUP BY and HAVING clauses to the filtered input dataset.
/* IMP: R-02223-49279 */
# EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the # DISTINCT keyword, the number of rows returned by an aggregate query # with a GROUP BY clause is the same as the number of groups of rows # produced by applying the GROUP BY and HAVING clauses to the filtered # input dataset.
R-60770-10612-35184-24244-53222-19878-52254-20031 tcl slt th3 src
One of the ALL or DISTINCT keywords may follow the SELECT keyword in a simple SELECT statement.
/* IMP: R-60770-10612 */
# EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may # follow the SELECT keyword in a simple SELECT statement.
R-08861-34280-62866-63452-63262-62491-16025-47018 tcl slt th3 src
If the simple SELECT is a SELECT ALL, then the entire set of result rows are returned by the SELECT.
/* IMP: R-08861-34280 */
# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then # the entire set of result rows are returned by the SELECT.
R-01256-01950-44319-11761-57126-19053-46711-18121 tcl slt th3 src
If neither ALL or DISTINCT are present, then the behavior is as if ALL were specified.
/* IMP: R-01256-01950 */
# EVIDENCE-OF: R-01256-01950 If neither ALL or DISTINCT are present, # then the behavior is as if ALL were specified.
R-14442-41305-19421-12764-11199-11923-26443-20423 tcl slt th3 src
If the simple SELECT is a SELECT DISTINCT, then duplicate rows are removed from the set of result rows before it is returned.
/* IMP: R-14442-41305 */
# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT, # then duplicate rows are removed from the set of result rows before it # is returned.
R-02054-15343-41665-64514-11380-36685-28202-41617 tcl slt th3 src
For the purposes of detecting duplicate rows, two NULL values are considered to be equal.
/* IMP: R-02054-15343 */
# EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate # rows, two NULL values are considered to be equal.
R-47709-27231-29719-54015-39691-57688-49858-37109 tcl slt th3 src
The usual rules apply for selecting a collation sequence to compare text values.
/* IMP: R-47709-27231 */
# EVIDENCE-OF: R-47709-27231 The usual rules apply for selecting a # collation sequence to compare text values.
R-39368-64333-53302-20010-22332-46980-48834-46750 tcl slt th3 src
In a compound SELECT, all the constituent SELECTs must return the same number of result columns.
/* IMP: R-39368-64333 */
# EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent # SELECTs must return the same number of result columns.
R-01450-11152-28406-58151-44531-37891-10618-14069 tcl slt th3 src
As the components of a compound SELECT must be simple SELECT statements, they may not contain ORDER BY or LIMIT clauses.
/* IMP: R-01450-11152 */
# EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must # be simple SELECT statements, they may not contain ORDER BY or LIMIT # clauses.
R-45440-25633-60928-38627-51174-16128-58812-25800 tcl slt th3 src
ORDER BY and LIMIT clauses may only occur at the end of the entire compound SELECT, and then only if the final element of the compound is not a VALUES clause.
/* IMP: R-45440-25633 */
# EVIDENCE-OF: R-45440-25633 ORDER BY and LIMIT clauses may only occur # at the end of the entire compound SELECT, and then only if the final # element of the compound is not a VALUES clause.
R-08531-36543-43368-50490-58607-10151-50307-46292 tcl slt th3 src
A compound SELECT created using UNION ALL operator returns all the rows from the SELECT to the left of the UNION ALL operator, and all the rows from the SELECT to the right of it.
/* IMP: R-08531-36543 */
# EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL # operator returns all the rows from the SELECT to the left of the UNION # ALL operator, and all the rows from the SELECT to the right of it.
R-20560-39162-53987-29912-13699-13892-53398-21192 tcl slt th3 src
The UNION operator works the same way as UNION ALL, except that duplicate rows are removed from the final result set.
/* IMP: R-20560-39162 */
# EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as # UNION ALL, except that duplicate rows are removed from the final # result set.
R-45764-31737-17057-32264-52283-10533-45215-02214 tcl slt th3 src
The INTERSECT operator returns the intersection of the results of the left and right SELECTs.
/* IMP: R-45764-31737 */
# EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the # intersection of the results of the left and right SELECTs.
R-25787-28949-24920-53948-06133-34727-56080-32939 tcl slt th3 src
The EXCEPT operator returns the subset of rows returned by the left SELECT that are not also returned by the right-hand SELECT.
/* IMP: R-25787-28949 */
# EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of # rows returned by the left SELECT that are not also returned by the # right-hand SELECT.
R-40729-56447-32753-40044-54162-25797-56212-55799 tcl slt th3 src
Duplicate rows are removed from the results of INTERSECT and EXCEPT operators before the result set is returned.
/* IMP: R-40729-56447 */
# EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results # of INTERSECT and EXCEPT operators before the result set is returned.
R-46765-43362-55332-05088-06369-35446-43155-35174 tcl slt th3 src
For the purposes of determining duplicate rows for the results of compound SELECT operators, NULL values are considered equal to other NULL values and distinct from all non-NULL values.
/* IMP: R-46765-43362 */
# EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate # rows for the results of compound SELECT operators, NULL values are # considered equal to other NULL values and distinct from all non-NULL # values.
R-51232-50224-58506-40920-61688-18757-44131-05457 tcl slt th3 src
The collation sequence used to compare two text values is determined as if the columns of the left and right-hand SELECT statements were the left and right-hand operands of the equals (=) operator, except that greater precedence is not assigned to a collation sequence specified with the postfix COLLATE operator.
/* IMP: R-51232-50224 */
# EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two # text values is determined as if the columns of the left and right-hand # SELECT statements were the left and right-hand operands of the equals # (=) operator, except that greater precedence is not assigned to a # collation sequence specified with the postfix COLLATE operator.
R-32706-07403-21974-02831-34123-06843-55643-09070 tcl slt th3 src
No affinity transformations are applied to any values when comparing rows as part of a compound SELECT.
/* IMP: R-32706-07403 */
# EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to # any values when comparing rows as part of a compound SELECT.
R-32562-20566-46417-07999-12317-06673-44579-53773 tcl slt th3 src
When three or more simple SELECTs are connected into a compound SELECT, they group from left to right. In other words, if "A", "B" and "C" are all simple SELECT statements, (A op B op C) is processed as ((A op B) op C).
/* IMP: R-32562-20566 */
# EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are # connected into a compound SELECT, they group from left to right. In # other words, if "A", "B" and "C" are all simple SELECT statements, (A # op B op C) is processed as ((A op B) op C).
R-02644-22131-14285-04703-01059-19073-41336-39895 tcl slt th3 src
In a compound SELECT statement, only the last or right-most simple SELECT may have an ORDER BY clause.
tcl/select4.test:108 th3/req1/select02.test:123
/* IMP: R-02644-22131 */
# EVIDENCE-OF: R-02644-22131 In a compound SELECT statement, only the # last or right-most simple SELECT may have an ORDER BY clause.
R-34494-34867-10904-51436-14050-30444-55567-08867 tcl slt th3 src
That ORDER BY clause will apply across all elements of the compound.
th3/req1/select02.test:112
/* IMP: R-34494-34867 */
# EVIDENCE-OF: R-34494-34867 That ORDER BY clause will apply across all # elements of the compound.
R-38644-64912-46499-33348-47960-41753-16072-56504 tcl slt th3 src
If the right-most element of a compound SELECT is a VALUES clause, then no ORDER BY clause is allowed on that statement.
th3/req1/select02.test:103
/* IMP: R-38644-64912 */
# EVIDENCE-OF: R-38644-64912 If the right-most element of a compound # SELECT is a VALUES clause, then no ORDER BY clause is allowed on that # statement.
R-44988-41064-02279-18587-19747-40125-16068-53673 tcl slt th3 src
Rows are first sorted based on the results of evaluating the left-most expression in the ORDER BY list, then ties are broken by evaluating the second left-most expression and so on.
/* IMP: R-44988-41064 */
# EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results # of evaluating the left-most expression in the ORDER BY list, then ties # are broken by evaluating the second left-most expression and so on.
R-06617-54588-52951-29775-47412-02155-55122-34673 tcl slt th3 src
Each ORDER BY expression may be optionally followed by one of the keywords ASC (smaller values are returned first) or DESC (larger values are returned first).
/* IMP: R-06617-54588 */
# EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally # followed by one of the keywords ASC (smaller values are returned # first) or DESC (larger values are returned first).
R-18705-33393-57819-41139-38249-59845-61429-28312 tcl slt th3 src
If neither ASC or DESC are specified, rows are sorted in ascending (smaller values first) order by default.
/* IMP: R-18705-33393 */
# EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows # are sorted in ascending (smaller values first) order by default.
R-29779-04281-26216-55813-25026-31775-44460-23073 tcl slt th3 src
If the ORDER BY expression is a constant integer K then the expression is considered an alias for the K-th column of the result set (columns are numbered from left to right starting with 1).
/* IMP: R-29779-04281 */
# EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant # integer K then the expression is considered an alias for the K-th # column of the result set (columns are numbered from left to right # starting with 1).
R-63286-51977-31491-11604-12483-48115-04561-38158 tcl slt th3 src
If the ORDER BY expression is an identifier that corresponds to the alias of one of the output columns, then the expression is considered an alias for that column.
/* IMP: R-63286-51977 */
# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier # that corresponds to the alias of one of the output columns, then the # expression is considered an alias for that column.
R-65068-27207-32171-32439-07967-23118-62780-22376 tcl slt th3 src
Otherwise, if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows.
/* IMP: R-65068-27207 */
# EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is # any other expression, it is evaluated and the returned value used to # order the output rows.
R-03421-57988-17823-20871-36963-31591-05991-31026 tcl slt th3 src
If the SELECT statement is a simple SELECT, then an ORDER BY may contain any arbitrary expressions.
/* IMP: R-03421-57988 */
# EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT, # then an ORDER BY may contain any arbitrary expressions.
R-28853-08147-20510-38197-22369-16459-32849-06937 tcl slt th3 src
However, if the SELECT is a compound SELECT, then ORDER BY expressions that are not aliases to output columns must be exactly the same as an expression used as an output column.
/* IMP: R-28853-08147 */
# EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound # SELECT, then ORDER BY expressions that are not aliases to output # columns must be exactly the same as an expression used as an output # column.
R-10883-17697-39247-15529-29929-13310-12082-24783 tcl slt th3 src
For the purposes of sorting rows, values are compared in the same way as for comparison expressions.
/* IMP: R-10883-17697 */
# EVIDENCE-OF: R-10883-17697 For the purposes of sorting rows, values # are compared in the same way as for comparison expressions.
R-64199-22471-28685-05688-45410-48917-35278-06379 tcl slt th3 src
If the ORDER BY expression is assigned a collation sequence using the postfix COLLATE operator, then the specified collation sequence is used.
/* IMP: R-64199-22471 */
# EVIDENCE-OF: R-64199-22471 If the ORDER BY expression is assigned a # collation sequence using the postfix COLLATE operator, then the # specified collation sequence is used.
R-09398-26102-26496-59828-02242-35200-16935-00376 tcl slt th3 src
Otherwise, if the ORDER BY expression is an alias to an expression that has been assigned a collation sequence using the postfix COLLATE operator, then the collation sequence assigned to the aliased expression is used.
/* IMP: R-09398-26102 */
# EVIDENCE-OF: R-09398-26102 Otherwise, if the ORDER BY expression is an # alias to an expression that has been assigned a collation sequence # using the postfix COLLATE operator, then the collation sequence # assigned to the aliased expression is used.
R-27301-09658-03082-11591-62753-46064-11323-29821 tcl slt th3 src
Otherwise, if the ORDER BY expression is a column or an alias of an expression that is a column, then the default collation sequence for the column is used.
/* IMP: R-27301-09658 */
# EVIDENCE-OF: R-27301-09658 Otherwise, if the ORDER BY expression is a # column or an alias of an expression that is a column, then the default # collation sequence for the column is used.
R-49925-55905-53634-34425-17507-41771-04127-21905 tcl slt th3 src
Otherwise, the BINARY collation sequence is used.
/* IMP: R-49925-55905 */
# EVIDENCE-OF: R-49925-55905 Otherwise, the BINARY collation sequence is # used.
R-44130-32593-50612-23115-00910-25537-42820-39348 tcl slt th3 src
If an ORDER BY expression is not an integer alias, then SQLite searches the left-most SELECT in the compound for a result column that matches either the second or third rules above. If a match is found, the search stops and the expression is handled as an alias for the result column that it has been matched against. Otherwise, the next SELECT to the right is tried, and so on.
/* IMP: R-44130-32593 */
# EVIDENCE-OF: R-44130-32593 If an ORDER BY expression is not an integer # alias, then SQLite searches the left-most SELECT in the compound for a # result column that matches either the second or third rules above. If # a match is found, the search stops and the expression is handled as an # alias for the result column that it has been matched against. # Otherwise, the next SELECT to the right is tried, and so on.
R-39265-04070-30004-05781-22531-14920-64985-48690 tcl slt th3 src
If no matching expression can be found in the result columns of any constituent SELECT, it is an error.
/* IMP: R-39265-04070 */
# EVIDENCE-OF: R-39265-04070 If no matching expression can be found in # the result columns of any constituent SELECT, it is an error.
R-03407-11483-10764-04340-16731-05555-54147-57723 tcl slt th3 src
Each term of the ORDER BY clause is processed separately and may be matched against result columns from different SELECT statements in the compound.
/* IMP: R-03407-11483 */
# EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is # processed separately and may be matched against result columns from # different SELECT statements in the compound.
R-37911-51642-34718-50738-49482-42498-05083-29076 tcl slt th3 src
The LIMIT clause is used to place an upper bound on the number of rows returned by the entire SELECT statement.
th3/req1/select02.test:63
/* IMP: R-37911-51642 */
# EVIDENCE-OF: R-37911-51642 The LIMIT clause is used to place an upper # bound on the number of rows returned by the entire SELECT statement.
R-13512-64012-34604-11605-36367-14798-29892-18682 tcl slt th3 src
In a compound SELECT, only the last or right-most simple SELECT may contain a LIMIT clause.
tcl/limit.test:254 th3/req1/select02.test:80
/* IMP: R-13512-64012 */
# EVIDENCE-OF: R-13512-64012 In a compound SELECT, only the last or # right-most simple SELECT may contain a LIMIT clause.
R-03782-50113-07889-61345-45931-21509-48774-40990 tcl slt th3 src
In a compound SELECT, the LIMIT clause applies to the entire compound, not just the final SELECT.
tcl/limit.test:257 th3/req1/select02.test:66
/* IMP: R-03782-50113 */
# EVIDENCE-OF: R-03782-50113 In a compound SELECT, the LIMIT clause # applies to the entire compound, not just the final SELECT.
R-65380-53022-62704-42379-02430-24457-15703-19233 tcl slt th3 src
If the right-most simple SELECT is a VALUES clause then no LIMIT clause is allowed.
th3/req1/select02.test:92
/* IMP: R-65380-53022 */
# EVIDENCE-OF: R-65380-53022 If the right-most simple SELECT is a VALUES # clause then no LIMIT clause is allowed.
R-30481-56627-22651-35996-10349-58660-54475-15242 tcl slt th3 src
Any scalar expression may be used in the LIMIT clause, so long as it evaluates to an integer or a value that can be losslessly converted to an integer.
/* IMP: R-30481-56627 */
# EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the # LIMIT clause, so long as it evaluates to an integer or a value that # can be losslessly converted to an integer.
R-46155-47219-10347-23082-03363-55795-00922-50884 tcl slt th3 src
If the expression evaluates to a NULL value or any other value that cannot be losslessly converted to an integer, an error is returned.
/* IMP: R-46155-47219 */
# EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value # or any other value that cannot be losslessly converted to an integer, # an error is returned.
R-03014-26414-56458-20281-20451-13984-34730-16215 tcl slt th3 src
If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned.
/* IMP: R-03014-26414 */
# EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a # negative value, then there is no upper bound on the number of rows # returned.
R-33750-29536-47450-45123-25964-11560-21995-55948 tcl slt th3 src
Otherwise, the SELECT returns the first N rows of its result set only, where N is the value that the LIMIT expression evaluates to.
/* IMP: R-33750-29536 */
# EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N # rows of its result set only, where N is the value that the LIMIT # expression evaluates to.
R-54935-19057-55411-14278-34768-16661-27190-25243 tcl slt th3 src
Or, if the SELECT statement would return less than N rows without a LIMIT clause, then the entire result set is returned.
/* IMP: R-54935-19057 */
# EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return # less than N rows without a LIMIT clause, then the entire result set is # returned.
R-24188-24349-51458-56364-12806-43304-14352-46380 tcl slt th3 src
The expression attached to the optional OFFSET clause that may follow a LIMIT clause must also evaluate to an integer, or a value that can be losslessly converted to an integer.
/* IMP: R-24188-24349 */
# EVIDENCE-OF: R-24188-24349 The expression attached to the optional # OFFSET clause that may follow a LIMIT clause must also evaluate to an # integer, or a value that can be losslessly converted to an integer.
R-20467-43422-30485-36826-60194-40272-32740-00331 tcl slt th3 src
If an expression has an OFFSET clause, then the first M rows are omitted from the result set returned by the SELECT statement and the next N rows are returned, where M and N are the values that the OFFSET and LIMIT clauses evaluate to, respectively.
/* IMP: R-20467-43422 */
# EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then # the first M rows are omitted from the result set returned by the # SELECT statement and the next N rows are returned, where M and N are # the values that the OFFSET and LIMIT clauses evaluate to, # respectively.
R-34648-44875-03220-54825-01081-20056-64407-58283 tcl slt th3 src
Or, if the SELECT would return less than M+N rows if it did not have a LIMIT clause, then the first M rows are skipped and the remaining rows (if any) are returned.
/* IMP: R-34648-44875 */
# EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than # M+N rows if it did not have a LIMIT clause, then the first M rows are # skipped and the remaining rows (if any) are returned.
R-23293-62447-32888-51584-58025-47542-61505-16122 tcl slt th3 src
If the OFFSET clause evaluates to a negative value, the results are the same as if it had evaluated to zero.
/* IMP: R-23293-62447 */
# EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a # negative value, the results are the same as if it had evaluated to # zero.
R-19509-40356-60366-52142-26522-07330-31595-37095 tcl slt th3 src
Instead of a separate OFFSET clause, the LIMIT clause may specify two scalar expressions separated by a comma.
/* IMP: R-19509-40356 */
# EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the # LIMIT clause may specify two scalar expressions separated by a comma.
R-33788-46243-41692-58767-41171-41314-21464-32804 tcl slt th3 src
In this case, the first expression is used as the OFFSET expression and the second as the LIMIT expression.
/* IMP: R-33788-46243 */
# EVIDENCE-OF: R-33788-46243 In this case, the first expression is used # as the OFFSET expression and the second as the LIMIT expression.
R-35668-58241-09537-53108-57582-24768-01452-25578 tcl slt th3 src
The phrase "VALUES(expr-list)" means the same thing as "SELECT expr-list".
th3/cov1/select30.test:41 th3/req1/select02.test:11
/* IMP: R-35668-58241 */
# EVIDENCE-OF: R-35668-58241 The phrase "VALUES(expr-list)" means the # same thing as "SELECT expr-list".
R-47188-50799-37892-57634-15721-61381-60158-50947 tcl slt th3 src
The phrase "VALUES(expr-list-1),...,(expr-list-N)" means the same thing as "SELECT expr-list-1 UNION ALL ... UNION ALL SELECT expr-list-N".
th3/cov1/select30.test:37 th3/req1/select02.test:22
/* IMP: R-47188-50799 */
# EVIDENCE-OF: R-47188-50799 The phrase # "VALUES(expr-list-1),...,(expr-list-N)" means the same thing as # "SELECT expr-list-1 UNION ALL ... UNION ALL SELECT expr-list-N".
R-12747-43999-37880-44715-10819-05870-41962-30541 tcl slt th3 src
A VALUES clause cannot be followed by ORDER BY.
th3/req1/select02.test:34
/* IMP: R-12747-43999 */
# EVIDENCE-OF: R-12747-43999 A VALUES clause cannot be followed by ORDER # BY.
R-12259-19638-21315-58129-32389-49540-45137-32104 tcl slt th3 src
A VALUES clause cannot be followed by LIMIT.
th3/req1/select02.test:44
/* IMP: R-12259-19638 */
# EVIDENCE-OF: R-12259-19638 A VALUES clause cannot be followed by # LIMIT.
R-32586-01296-10189-28566-58677-02270-23105-07978 tcl slt th3 src
SELECT statements may be optionally preceded by a single WITH clause that defines one or more common table expressions for use within the SELECT statement.
th3/cov1/with01.test:10
/* IMP: R-32586-01296 */
# EVIDENCE-OF: R-32586-01296 SELECT statements may be optionally # preceded by a single WITH clause that defines one or more common table # expressions for use within the SELECT statement.
R-47973-48020-37967-63330-01173-43664-55232-05863 tcl slt th3 src
you cannot say "INNER OUTER JOIN", because that would be contradictory.
tcl/join.test:345 tcl/vtab6.test:281
/* IMP: R-47973-48020 */
# EVIDENCE-OF: R-47973-48020 you cannot say "INNER OUTER JOIN", because # that would be contradictory.
R-52129-05406-09638-18525-56854-38344-25069-09729 tcl slt th3 src
you can say things like "OUTER LEFT NATURAL JOIN" which means the same as "NATURAL LEFT OUTER JOIN".
/* IMP: R-52129-05406 */
# EVIDENCE-OF: R-52129-05406 you can say things like "OUTER LEFT NATURAL # JOIN" which means the same as "NATURAL LEFT OUTER JOIN".
R-33754-02880-14847-48352-49156-46492-55020-40559 tcl slt th3 src
you can say "LEFT RIGHT JOIN" which is the same as "FULL JOIN".
tcl/join8.test:23 tcl/join8.test:232
/* IMP: R-33754-02880 */
# EVIDENCE-OF: R-33754-02880 you can say "LEFT RIGHT JOIN" which is the # same as "FULL JOIN".