From 18657a960e125336f704ea058e25c27bd3900dcb Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 5 May 2024 19:28:19 +0200 Subject: Adding upstream version 3.40.1. Signed-off-by: Daniel Baumann --- www/c3ref/vtab_distinct.html | 197 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 197 insertions(+) create mode 100644 www/c3ref/vtab_distinct.html (limited to 'www/c3ref/vtab_distinct.html') diff --git a/www/c3ref/vtab_distinct.html b/www/c3ref/vtab_distinct.html new file mode 100644 index 0000000..665e817 --- /dev/null +++ b/www/c3ref/vtab_distinct.html @@ -0,0 +1,197 @@ + + + + + +Determine if a virtual table query is DISTINCT + + + +
+ + + +
+
+Small. Fast. Reliable.
Choose any three. +
+
+ +
+ +
+
+ + + +
+
+
+ + +
+

SQLite C Interface

 +

Determine if a virtual table query is DISTINCT

+
+
+int sqlite3_vtab_distinct(sqlite3_index_info*);
+
+

+This API may only be used from within an xBestIndex method +of a virtual table implementation. The result of calling this +interface from outside of xBestIndex() is undefined and probably harmful.

+ +

The sqlite3_vtab_distinct() interface returns an integer between 0 and +3. The integer returned by sqlite3_vtab_distinct() +gives the virtual table additional information about how the query +planner wants the output to be ordered. As long as the virtual table +can meet the ordering requirements of the query planner, it may set +the "orderByConsumed" flag.

+ +

  1. +If the sqlite3_vtab_distinct() interface returns 0, that means +that the query planner needs the virtual table to return all rows in the +sort order defined by the "nOrderBy" and "aOrderBy" fields of the +sqlite3_index_info object. This is the default expectation. If the +virtual table outputs all rows in sorted order, then it is always safe for +the xBestIndex method to set the "orderByConsumed" flag, regardless of +the return value from sqlite3_vtab_distinct(). +

  2. +If the sqlite3_vtab_distinct() interface returns 1, that means +that the query planner does not need the rows to be returned in sorted order +as long as all rows with the same values in all columns identified by the +"aOrderBy" field are adjacent. This mode is used when the query planner +is doing a GROUP BY. +

  3. +If the sqlite3_vtab_distinct() interface returns 2, that means +that the query planner does not need the rows returned in any particular +order, as long as rows with the same values in all "aOrderBy" columns +are adjacent. Furthermore, only a single row for each particular +combination of values in the columns identified by the "aOrderBy" field +needs to be returned. It is always ok for two or more rows with the same +values in all "aOrderBy" columns to be returned, as long as all such rows +are adjacent. The virtual table may, if it chooses, omit extra rows +that have the same value for all columns identified by "aOrderBy". +However omitting the extra rows is optional. +This mode is used for a DISTINCT query. +

  4. +If the sqlite3_vtab_distinct() interface returns 3, that means +that the query planner needs only distinct rows but it does need the +rows to be sorted. The virtual table implementation is free to omit +rows that are identical in all aOrderBy columns, if it wants to, but +it is not required to omit any rows. This mode is used for queries +that have both DISTINCT and ORDER BY clauses. +

+ +

For the purposes of comparing virtual table output values to see if the +values are same value for sorting purposes, two NULL values are considered +to be the same. In other words, the comparison operator is "IS" +(or "IS NOT DISTINCT FROM") and not "==".

+ +

If a virtual table implementation is unable to meet the requirements +specified above, then it must not set the "orderByConsumed" flag in the +sqlite3_index_info object or an incorrect answer may result.

+ +

A virtual table implementation is always free to return rows in any order +it wants, as long as the "orderByConsumed" flag is not set. When the +the "orderByConsumed" flag is unset, the query planner will add extra +bytecode to ensure that the final results returned by the SQL query are +ordered correctly. The use of the "orderByConsumed" flag and the +sqlite3_vtab_distinct() interface is merely an optimization. Careful +use of the sqlite3_vtab_distinct() interface and the "orderByConsumed" +flag might help queries against a virtual table to run faster. Being +overly aggressive and setting the "orderByConsumed" flag when it is not +valid to do so, on the other hand, might cause SQLite to return incorrect +results. +

See also lists of + Objects, + Constants, and + Functions.

+ -- cgit v1.2.3