diff options
Diffstat (limited to 'www/c3ref/vtab_distinct.html')
| -rw-r--r-- | www/c3ref/vtab_distinct.html | 47 |
1 files changed, 34 insertions, 13 deletions
diff --git a/www/c3ref/vtab_distinct.html b/www/c3ref/vtab_distinct.html index 16420a1..5cfef1c 100644 --- a/www/c3ref/vtab_distinct.html +++ b/www/c3ref/vtab_distinct.html @@ -152,24 +152,45 @@ is doing a GROUP BY. <li value="2"><p> 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. +order, as long as rows with the same values in all columns identified +by "aOrderBy" are adjacent. Furthermore, when two or more rows +contain the same values for all columns identified by "colUsed", all but +one such row may optionally be omitted from the result. +The virtual table is not required to omit rows that are duplicates +over the "colUsed" columns, but if the virtual table can do that without +too much extra effort, it could potentially help the query to run faster. This mode is used for a DISTINCT query. <li value="3"><p> -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 +If the sqlite3_vtab_distinct() interface returns 3, that means the +virtual table must return rows in the order defined by "aOrderBy" as +if the sqlite3_vtab_distinct() interface had returned 0. However if +two or more rows in the result have the same values for all columns +identified by "colUsed", then all but one such row may optionally be +omitted. Like when the return value is 2, the virtual table +is not required to omit rows that are duplicates over the "colUsed" +columns, but if the virtual table can do that without +too much extra effort, it could potentially help the query to run faster. +This mode is used for queries that have both DISTINCT and ORDER BY clauses. </ol></p> +<p><p>The following table summarizes the conditions under which the +virtual table is allowed to set the "orderByConsumed" flag based on +the value returned by sqlite3_vtab_distinct(). This table is a +restatement of the previous four paragraphs:</p> + +<p><table border=1 cellspacing=0 cellpadding=10 width="90%"> +<tr> +<td valign="top">sqlite3_vtab_distinct() return value +<td valign="top">Rows are returned in aOrderBy order +<td valign="top">Rows with the same value in all aOrderBy columns are adjacent +<td valign="top">Duplicates over all colUsed columns may be omitted +<tr><td>0<td>yes<td>yes<td>no +<tr><td>1<td>no<td>yes<td>no +<tr><td>2<td>no<td>yes<td>yes +<tr><td>3<td>yes<td>yes<td>yes +</table></p> + <p>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" |