1.Dd January 24, 2024 2.Dt SQLITE3_VTAB_DISTINCT 3 3.Os 4.Sh NAME 5.Nm sqlite3_vtab_distinct 6.Nd determine if a virtual table query is DISTINCT 7.Sh SYNOPSIS 8.In sqlite3.h 9.Ft int 10.Fo sqlite3_vtab_distinct 11.Fa "sqlite3_index_info*" 12.Fc 13.Sh DESCRIPTION 14This API may only be used from within an xBestIndex method 15of a virtual table implementation. 16The result of calling this interface from outside of xBestIndex() is 17undefined and probably harmful. 18.Pp 19The sqlite3_vtab_distinct() interface returns an integer between 0 20and 3. 21The integer returned by sqlite3_vtab_distinct() gives the virtual table 22additional information about how the query planner wants the output 23to be ordered. 24As long as the virtual table can meet the ordering requirements of 25the query planner, it may set the "orderByConsumed" flag. 26.Bl -enum 27.It 28.Pp 29If the sqlite3_vtab_distinct() interface returns 0, that means that 30the query planner needs the virtual table to return all rows in the 31sort order defined by the "nOrderBy" and "aOrderBy" fields of the sqlite3_index_info 32object. 33This is the default expectation. 34If the virtual table outputs all rows in sorted order, then it is always 35safe for the xBestIndex method to set the "orderByConsumed" flag, regardless 36of the return value from sqlite3_vtab_distinct(). 37.It 38.Pp 39If the sqlite3_vtab_distinct() interface returns 1, that means that 40the query planner does not need the rows to be returned in sorted order 41as long as all rows with the same values in all columns identified 42by the "aOrderBy" field are adjacent. 43This mode is used when the query planner is doing a GROUP BY. 44.It 45.Pp 46If the sqlite3_vtab_distinct() interface returns 2, that means that 47the query planner does not need the rows returned in any particular 48order, as long as rows with the same values in all "aOrderBy" columns 49are adjacent. 50Furthermore, only a single row for each particular combination of values 51in the columns identified by the "aOrderBy" field needs to be returned. 52It is always ok for two or more rows with the same values in all "aOrderBy" 53columns to be returned, as long as all such rows are adjacent. 54The virtual table may, if it chooses, omit extra rows that have the 55same value for all columns identified by "aOrderBy". 56However omitting the extra rows is optional. 57This mode is used for a DISTINCT query. 58.It 59.Pp 60If the sqlite3_vtab_distinct() interface returns 3, that means that 61the query planner needs only distinct rows but it does need the rows 62to be sorted. 63The virtual table implementation is free to omit rows that are identical 64in all aOrderBy columns, if it wants to, but it is not required to 65omit any rows. 66This mode is used for queries that have both DISTINCT and ORDER BY 67clauses. 68.El 69.Pp 70For the purposes of comparing virtual table output values to see if 71the values are same value for sorting purposes, two NULL values are 72considered to be the same. 73In other words, the comparison operator is "IS" (or "IS NOT DISTINCT 74FROM") and not "==". 75.Pp 76If a virtual table implementation is unable to meet the requirements 77specified above, then it must not set the "orderByConsumed" flag in 78the sqlite3_index_info object or an incorrect answer 79may result. 80.Pp 81A virtual table implementation is always free to return rows in any 82order it wants, as long as the "orderByConsumed" flag is not set. 83When the the "orderByConsumed" flag is unset, the query planner will 84add extra bytecode to ensure that the final results returned 85by the SQL query are ordered correctly. 86The use of the "orderByConsumed" flag and the sqlite3_vtab_distinct() 87interface is merely an optimization. 88Careful use of the sqlite3_vtab_distinct() interface and the "orderByConsumed" 89flag might help queries against a virtual table to run faster. 90Being overly aggressive and setting the "orderByConsumed" flag when 91it is not valid to do so, on the other hand, might cause SQLite to 92return incorrect results. 93.Sh IMPLEMENTATION NOTES 94These declarations were extracted from the 95interface documentation at line 9889. 96.Bd -literal 97SQLITE_API int sqlite3_vtab_distinct(sqlite3_index_info*); 98.Ed 99.Sh SEE ALSO 100.Xr sqlite3_index_info 3 101