[HN Gopher] SQLite Release 3.37.0
___________________________________________________________________
SQLite Release 3.37.0
Author : massysett
Score : 147 points
Date : 2021-11-27 20:27 UTC (2 hours ago)
(HTM) web link (www.sqlite.org)
(TXT) w3m dump (www.sqlite.org)
| jokoon wrote:
| I wonder if Spatialite is still maintained...
| mastax wrote:
| Well I definitely prefer the new strict mode, I'll enable that
| right away. I've long fantasized about a mode that would make
| SQLite behave like a normal SQL database, but this is not that.
| You still can't store a float in the database and get it back out
| unmodified. SQLite will expand it to a double, and it can't store
| NaNs. And probably more overhead to be able to support storing
| strings in my REAL column. Oh well.
|
| I tried out DuckDB which is more my style but the .NET libs are
| poor and I can't shave that yak right now.
| cryptonector wrote:
| > Content inserted into the column with a datatype other than ANY
| must be either a NULL (assuming there is no NOT NULL constraint
| on the column) or the type specified. SQLite attempts to coerce
| the data into the appropriate type using the usual affinity
| rules, [...].
|
| I would like an even stricter mode with no automatic type
| coercion.
| ComputerGuru wrote:
| The biggest news, of course, is the addition of the long-awaited
| STRICT mode which fixes SQLite's biggest shortcoming (at least
| from a particular perspective) by turning into a proper,
| strongly-typed datastore.
|
| https://www.sqlite.org/stricttables.html
| Lio wrote:
| There's nothing obvious is linked release but is this expected
| to have an affect on performance either good or bad?
| ripley12 wrote:
| I wouldn't expect much difference in performance. "The on-
| disk format for the table data is the same."
|
| I would expect the additional checks on writes to be
| negligible in most cases (dwarfed by other bookkeeping and
| I/O). I imagine that strict mode could enable some
| optimizations for read queries, but I can't see those being
| huge wins.
| ComputerGuru wrote:
| > but I can't see those being huge wins.
|
| I agree from an algorithmic perspective but in the real
| world, it really depends. The size of a non-text/binary
| cell within a column is now fixed, meaning guaranteed to
| never need to resize/reallocate when streaming results.
| That could translate to non-negligible improvements
| (multiple percentage point speed up) if it's implemented
| separately from the existing code path.
| ComputerGuru wrote:
| It's really no more than an additional (hyper optimized)
| check constraint on the type; with branch prediction it's
| probably ~free.
| dkjaudyeqooe wrote:
| Given that it has the Any type, it's really not that much
| different, it's mostly the lack of the 'interesting' best-
| effort automatic conversions which confused and alarmed people
| and gave SQLite's type system a bad name. This version still
| does automatic conversions, which IMO are always a bad idea,
| but justifies them with "but everyone is doing it!".
|
| Some people seem to object to the Any type, but it's incredibly
| useful since a column may naturally contain a different type
| per row and it avoids ugly hacks to accommodate them.
| contingencies wrote:
| Couldn't resist. https://imgur.com/a/W3nufMT
| belter wrote:
| https://youtu.be/st6-DgWeuos
| michalc wrote:
| I have been particularly looking forward to that. Although I
| have to admit that I'm a bit... disappointed(?)... that
| previous versions of SQLite won't be able to open such tables
| (unless they use PRAGMA writable_schema=ON).
|
| I wonder if SQLite didn't store the schema for a table as a
| "CREATE TABLE..." string, but instead something a bit more...
| structured, then such things could be added in a more backwards
| compatible way?
| [deleted]
| cryptonector wrote:
| This could never be added in a backwards-compatible way if
| the existing CHECK() functionality was not sufficient
| already.
|
| And yes, it'd be very nice to have a relational metaschema
| that doesn't suck for SQL schema. So far all such metaschemas
| I've seen leave a lot to be desired.
| porker wrote:
| What kind of thing do you mean by a relational metaschema?
| formerly_proven wrote:
| Describing SQL tables with an SQL table
| jhgb wrote:
| That's called a catalog.
| (https://reldb.org/c/index.php/twelve-rules/, Rule #4)
| ComputerGuru wrote:
| The existing check syntax would cover inserts but wouldn't
| have supported the behavior of the new "any" type in a
| strict table.
|
| What would be nice is if SQLite had separate "compatible
| with" and "read-only compatible with" fields, since the
| read-only behavior should not have changed at all (although
| I suppose describing the table would fail depending on how
| the presence of the STRICT modifier is codified).
| cryptonector wrote:
| What they could have done is translated a STRICT table
| w/o ANY-typed columns to an older CREATE TABLE with
| CHECK()s. But anyways.
| smitty1e wrote:
| > a relational metaschema that doesn't suck for SQL schema
|
| Something like an object-relational mapper, e.g.
| SQLAlchemy?
| saurik wrote:
| Well, in a different concept of how compatibility was
| thought of with respect to stored schemes, you could have
| feature flags marked as "this feature is entirely optional
| to understand", "this feature is required to understand",
| and a third case that would work here and be extremely
| useful "this feature is required to understand if you want
| to write to the database, but if you merely open it read
| only it is optional".
| CyberDildonics wrote:
| What's the difference between a datastore and a database?
| zzzeek wrote:
| wow, that STRICT thing is going to be a big deal. It's difficult
| enough to work with databases that have an extremely mature
| strong typing model (PostgreSQL - bound parameters need super-
| explicit typing information sent in most cases, and it's pretty
| unforgiving about implicit casts). A brand new one bolted onto
| SQLite after decades of "types as a vague suggestion" should be
| ...interesting! to support in downstream products (for my end,
| it's SQLAlchemy).
| karteum wrote:
| Great news. One remark: "The on-disk format for the table data is
| the same" => I guess this means there is still some kind of
| "type/length" header for every single record... (which could
| theoretically be dropped) ?
| gigatexal wrote:
| yup - strict tables is very welcome.
|
| This also is an interesting tidbit:
|
| "The query planner now omits ORDER BY clauses on subqueries and
| views if removing those clauses does not change the semantics of
| the query."
|
| Curious how it might improve queries -- I wonder how many folks
| were doing potentially redundant order by's in subqueries
| cryptonector wrote:
| > The query planner now omits ORDER BY clauses on subqueries and
| views if removing those clauses does not change the semantics of
| the query.
|
| That's fair and allowed by the standard, but it will break some
| things.
| cldellow wrote:
| I think I recall when this feature was discussed and
| implemented.
|
| If it's what I'm thinking of, this concern was carefully
| considered: If a subquery in the FROM clause
| has an ORDER BY, that ORDER BY is omitted if all of the
| following conditions are also true: 1. There is no
| LIMIT clause in the subquery 2. The subquery was
| not one of the virtual subqueries added internally by SQLite
| for window-function processing 3. The subquery is
| not part of the FROM clause in an UPDATE-FROM statement
| 4. The outer query does not use any aggregate functions other
| than the built-in count(), min(), and/or max() functions.
| 5. Either the outer query has its own ORDER BY clause or else
| the subquery is one term of a join.
|
| from https://sqlite.org/forum/forumpost/878ca7a9be0862af?t=h
| cryptonector wrote:
| Thanks! That works for me!
| ComputerGuru wrote:
| It says "if it doesn't change the semantics of the query" and
| not "if it isn't guaranteed by the spec," which means it
| actually should only happen in cases where it doesn't break
| anything, eg "foo in (... only now without an implicit order)"
| but not "foo == (select foo from (... now without implicit
| order) limit 1))" - at least, presumably.
| cryptonector wrote:
| Well, I guess I'll have to play with it. It's not entirely
| clear from the release notes what "semantics" means in this
| case. In principle in relational algebra ORDER BY is simply
| not meaningful because it's all unordered sets. In practice
| ORDER BY is essential. In principle all ORDER BY clauses in
| sub-queries can just be deleted, but in practice it can
| affect aggregation functions not built on commutative
| operations.
| 3dfan wrote:
| It seems to be a universal law of software projects:
|
| 1: Start out lean and simple. Often replacing an old complex one
|
| 2: Become more complex over time
|
| 3: Be replaced by a new lean and simple one
| curiousmindz wrote:
| I think this is a concerning pattern when the core usage of the
| software gets more complex due to the new features.
|
| If you can take a developer using the old version that was lean
| and simple, give them the latest version full of extra
| features, and they can use that version without any need to
| change anything, then I think it's fine.
|
| And, so far, I think that SQLite has done a good job of keeping
| their core usage lean and simple.
| ComputerGuru wrote:
| If anything, this behavior is far simpler than trying to
| automagically coerce types and marshal differing types to/from
| one column.
| dkjaudyeqooe wrote:
| Strict tables still do both of those things.
| nikeee wrote:
| I think the point here is that there are now two modes of
| operation, which leads to more complexity overall.
| curiousmindz wrote:
| As long as an app is architectured around having a data layer
| with "typed" objects, the need for SQLite to support strict
| column types is not too important.
|
| However, it does make handling more "advanced" types less
| standard. For example, there isn't a standard way to store a
| date, especially if we want to preserve high-precision
| (nanoseconds).
| pstuart wrote:
| > especially if we want to preserve high-precision
| (nanoseconds).
|
| Why not just by convention as ints, nanoseconds UTC post epoch?
| jatone wrote:
| because the lack of a type annotation via introspection makes
| tools automatically generating code for your less than
| useful.
| ComputerGuru wrote:
| It's actually really important in some edge cases. For
| instance, I have a column that is declared as text but I read
| and write to it via the blob api (because the source is utf8
| bytes I don't want to parse client-side for allocation
| reasons). The flexible typing ended up - silently - storing the
| fields as blobs, but comparisons with strings continued to work
| ("foo" == table.col) until I tried changing that to a pattern
| (table.col like "%foo") at which point there were runtime
| exceptions (iirc). I had to update all existing data to cast
| the columns to the correct type, whereas the original insertion
| query should have been doing that from the start, but SQLite
| was too flexible for my own good.
___________________________________________________________________
(page generated 2021-11-27 23:00 UTC)