947624 - (SQLite3.8.2) Upgrade to SQLite 3.8.2

Status: RESOLVED FIXED

(Toolkit :: Storage, defect)

Description

[Boaz Dodin]

http://www.sqlite.org/news.html

SQLite version 3.8.2 is a regularly scheduled maintenance release. Upgrading from the previous release is optional.

Version 3.8.2 adds support for WITHOUT ROWID tables. This is a significant extension to SQLite. Database files that contain WITHOUT ROWID tables are not readable or writable by prior versions of SQLite, however databases that do not use WITHOUT ROWID tables are fully backwards and forwards compatible.

The 3.8.2 release contains a potentially incompatible change. In all prior versions of SQLite, a cast from a very large positive floating point number into an integer resulted in the most negative integer. In other words, CAST(+99.9e99 to INT) would yield -9223372036854775808. This behavior came about because it is what x86/x64 hardware does for the equivalent cast in the C language. But the behavior is bizarre. And so it has been changed effective with this release so that a cast from a floating point number into an integer returns the integer between the floating point value and zero that is closest to the floating point value. Hence, CAST(+99.9e99 to INT) now returns +9223372036854775807. Since routines like sqlite3_column_int64() do an implicit cast if the value being accessed is really a floating point number, they are also affected by this change.

Besides the two changes mentioned above, the 3.8.2 release also includes a number of performance enhancements. The skip-scan optimization is now available for databases that have been processed by ANALYZE. Constant SQL functions are now factored out of inner loops, which can result in a significant speedup for queries that contain WHERE clause terms like "date>datetime('now','-2 days')". And various high-runner internal routines have been refactored for reduced CPU load.

Comment 1

[Boaz Dodin]

http://www.sqlite.org/releaselog/3_8_2.html

* Changed the defined behavior for the CAST expression when floating point values greater than +9223372036854775807 are cast into into integers so that the result is the largest possible integer, +9223372036854775807, instead of the smallest possible integer, -9223372036854775808. After this change, CAST(9223372036854775809.0 as INT) yields +9223372036854775807 instead of -9223372036854775808. ← Potentially Incompatible Change!
* Added support for WITHOUT ROWID tables.
* Added the skip-scan optimization to the query planner.
* 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.
* Update the R-Tree extension to make use of the enhanced virtual table interface.
* Add the SQLITE_ENABLE_EXPLAIN_COMMENTS compile-time option.
* Enhanced the comments that are inserted into EXPLAIN output when the SQLITE_ENABLE_EXPLAIN_COMMENTS compile-time option is enabled.
* Performance enhancements in the VDBE, especially to the OP_Column opcode.
* Factor constant subexpressions in inner loops out to the initialization code in prepared statements.
* Enhanced the ".explain" output formatting of the command-line shell so that loops are indented to better show the structure of the program.
* Enhanced the ".timer" feature of the command-line shell so that it shows wall-clock time in addition to system and user times.

Comment 2

[Ryan VanderMeulen [:RyanVM]]

I actually already had this running through Try.
https://tbpl.mozilla.org/?tree=Try&rev=a2c1cd6105c8

Talos results can be compared against m-c rev 393f409b98ec.

Comment 3

[Ryan VanderMeulen [:RyanVM]]

Attachment: Upgrade to SQLite 3.8.2 - Mozilla changes

Ready to go whenever you want :)

Comment 4

[Landry Breuil (:gaston)]

Fwiw, 3.8.1 update has broken the build on OpenBSD because now in struct unixFile nFetchOut member is within #if SQLITE_MAX_MMAP_SIZE>0, and on OpenBSD that define is 0 - sadly, nFetchOut is used inconditionally in unixUnlock(), leading to build failure:

/home/landry/src/m-c/db/sqlite3/src/sqlite3.c:25058:55: error: no member named 'nFetchOut' in 'struct unixFile'
  assert( eFileLock==SHARED_LOCK || ((unixFile *)id)->nFetchOut==0 );
                                    ~~~~~~~~~~~~~~~~  ^
But rejoice ! 3.8.2 has a fix for that exact issue in https://hg.mozilla.org/try/rev/7fbee95244e8#l1.2755 so i'll definitely welcome the update :)

Comment 5

[Landry Breuil (:gaston)]

The commit/fix upstream was http://www.sqlite.org/cgi/src/info/090db8c81d and i didnt see the failure earlier because it's only in the SQLITE_DEBUG case.

Comment 6

[Ryan VanderMeulen [:RyanVM]]

(In reply to Landry Breuil (:gaston) from comment #4)
> But rejoice ! 3.8.2 has a fix for that exact issue in
> https://hg.mozilla.org/try/rev/7fbee95244e8#l1.2755 so i'll definitely
> welcome the update :)

Sounds like we may want to take this on Aurora then too. Mak, we should probably get this on m-c too so we have some time to dogfood before an uplift.

Comment 7

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

Comment on attachment 8344222 [details] [diff] [review]
Upgrade to SQLite 3.8.2 - Mozilla changes

Review of attachment 8344222 [details] [diff] [review]:
-----------------------------------------------------------------

while I sympathize with the openbsd issue, it's not a tier-1 platform, so I don't think we can evaluate backporting, luckily shouldn't be hard to workaround the issue on the porting code itself for one version

Comment 8

[Ryan VanderMeulen [:RyanVM]]

https://hg.mozilla.org/integration/mozilla-inbound/rev/c653810ce027
https://hg.mozilla.org/integration/mozilla-inbound/rev/d18a4d380644

What about a 3.8.1.1 spot release with just the BSD fix for Aurora?

Comment 9

[Ryan VanderMeulen [:RyanVM]]

https://hg.mozilla.org/mozilla-central/rev/c653810ce027
https://hg.mozilla.org/mozilla-central/rev/d18a4d380644

Comment 10

[Landry Breuil (:gaston)]

Either way, i dont care since i do beta & release builds against systemwide sqlite (which, that said, i need to get updated). And i rarely build with debug set.

Comment 11

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

(In reply to Ryan VanderMeulen [:RyanVM UTC-5] from comment #8)
> What about a 3.8.1.1 spot release with just the BSD fix for Aurora?

I don't think it's part of our policy for backporting, I may be wrong but that's my feeling.