> On Oct 2, 2016, at 6:20 AM, Clemens Ladisch <clem...@ladisch.de> wrote: > > Changing the function in any way (including not registering the > function) would essentially corrupt the index.
Well, the same can be said of using a custom collation function, which has been supported since 3.0; or of using a function in the expression being indexed, which is supported since 3.9. - “Changing the function” is covered by the SQLITE_DETERMINISTIC flag, which is a promise that the function is pure, i.e. always produces the same output given the same input. If that guarantee were to fail, you’d need to drop and re-create the index. - “Not registering the function” is handled by SQLite — any query/command that would involve calling an unregistered function will instead return an error. (I already run into this in the current version of my library, which uses a custom collator, when I try to query databases with the `sqlite3` tool.) So I don’t see this as a reason not to support (deterministic) functions in the WHERE clause. > If only 10% of your rows have a professor, the first 90% of the index > entries will be NULL. This is probably not a performance problem when > searching in a B-tree (you can test this with a table with 'real' > columns). Whether the storage overhead matters is something you have > to decide yourself. It’s 1%, not 10%, in my example; but in reality I can’t predict or control what types of data sets might be used by customers. I don’t know the details of SQLite’s b-tree, but I’d guess that the root node would have one child pointer for ‘null’ values, with the rest being non-null, so there’s little overhead in lookup time. As for storage overhead, hopefully if the key is null and the table has a rowid, the node would be small, on the order of 10 bytes or so … ? —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
