SQLite Forum

Summary of Virtual Table Changes
I'm doing research on SQLite Virtual Tables and thought I'd share this. I'm looking for recommendations on tutorials and/or books to read more about it.

2009-05-07 (3.6.14)
* Enhanced the query optimizer so that virtual tables are able to make use of OR and IN operators in the WHERE clause.

2009-08-10 (3.6.17)
* Remove the restriction on virtual tables and shared cache mode. Virtual tables and shared cache can now be used at the same time.

2011-09-19 (3.7.8)
* Bug fix: Virtual tables now handle IS NOT NULL constraints correctly.

2013-03-18 (3.7.16)
* Enhance virtual tables so that they can potentially use an index when the WHERE clause contains the IN operator.

2013-12-06 (3.8.2)
* Extended the virtual table interface, and in particular the sqlite3_index_info object to allow a virtual table to report its estimate on the number of rows that will be returned by a query.

2014-02-03 (3.8.3)
* Enhance the spellfix1 virtual table so that it can search efficiently by rowid.

2016-01-06 (3.10.0)
* Added support for LIKE, GLOB, and REGEXP operators on virtual tables.
* Added the colUsed field to sqlite3_index_info for use by the sqlite3_module.xBestIndex method.

2016-03-29 (3.12.0)
* The query planner is able to optimize IN operators on virtual tables even if the xBestIndex method does not set the sqlite3_index_constraint_usage.omit flag of the virtual table column to the left of the IN operator.
* The query planner now does a better job of optimizing virtual table accesses in a 3-way or higher join where constraints on the virtual table are split across two or more other tables of the join.

2016-04-18 (3.12.2)
* Fix a bug in the code generator that can cause incorrect results if two or more virtual tables are joined and the virtual table used in outer loop of the join has an IN operator constraint.

2016-08-08 (3.14)
* Added support for WITHOUT ROWID virtual tables.
* Improved the query planner so that the OR optimization can be used on virtual tables even if one or more of the disjuncts use the LIKE, GLOB, REGEXP, MATCH operators.

2016-10-14 (3.15.0)
* Enhance the fts5vocab virtual table to handle "ORDER BY term" efficiently.

2017-08-01 (3.20.0)
* Do not use the flattening optimization for a sub-query on the RHS of a LEFT JOIN if that subquery reads data from a virtual table as doing so prevents the query planner from creating automatic indexes on the results of the sub-query, which can slow down the query.

2017-10-24 (3.21.0)
* Pass information about !=, IS, IS NOT, NOT NULL, and IS NULL constraints into the xBestIndex method of virtual tables.
Allow WITHOUT ROWID virtual tables to be writable if the PRIMARY KEY contains exactly one column.

2018-04-10 (3.23.1)
* Fix the generate_series virtual table so that it correctly returns no rows if any of its constraints are NULL.

2018-11-05 (3.25.3)
* Fix the behavior of typeof() and length() on virtual tables. Ticket 69d642332d25aa3b7315a6d385

2018-12-01 (3.26.0)
* Allow the xBestIndex() method of virtual table implementations to return SQLITE_CONSTRAINT to indicate that the proposed query plan is unusable and should not be given further consideration.