12 ms·
SQLite Release 3.37.0
- cryptonector 5y ago> 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.
- kzrdude 5y agoNot trying to be mean, but it's like when someone who doesn't understand the point of the feature implements it.
- cryptonector 5y agoI don't understand your point; I'm not sure why.
- djbusby 5y agoI think they mean that your up-thread (is that OP or GP or?) comment is asking for the wrong thing. Like one of those XY type problems
- kzrdude 5y agoI always underestimate how easy it is to be misunderstood. Why it's worthwhile to spend time on clear communication! I'm chiding SQLite, a bit unfairly, that they only implemented the strict feature half right, still leaving some type coercion in.
- cryptonector 5y agoThanks, I understand your point now, and I agree. The SQLite3 devs seem to think that because other SQL RDBMSes do this, they should too. At this time I'm of the opinion that more strictness is better.
- kiwijamo 5y agoWhich other SQL engines do this? The ones I'm familiar with (MySQL, PostgreSQL, Microsoft SQL, Orcale to name a few) certainly don't behave anywhere close to how SQLite behaves.
- alberth 5y agoSTRICT + BedrockDB will be an interesting pairing. I wonder if this will provide more adoption of this client server database. https://bedrockdb.com/ https://bedrockdb.com/ EDIT: for this not aware, Expensify is a paid sponsor of SQLITE and there’s an official branch (Bedrock) of SQLite that has client / server capabilities.
- ComputerGuru 5y agoThe 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 https://www.sqlite.org/stricttables.html
- Lio 5y agoThere’s nothing obvious is linked release but is this expected to have an affect on performance either good or bad?
- SQLite 5y agoThe SQLite dev's internal performance testing using cachegrind shows that STRICT mode uses about 0.34% more CPU cycles. So STRICT is slightly slower, but not enough that you could measure the difference reliably in a real-world system.
- ripley12 5y agoI 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 5y ago> 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.
- prirun 5y ago
- jokoon 5y agoI wonder if Spatialite is still maintained...
- durkie 5y agoseems to be -- there was a major new version out in february this year i believe. still works great, even though it's kind of weird to switch between spatialite and postgis.
- cryptonector 5y ago> 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.
- ComputerGuru 5y agoIt 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 5y agoWell, 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.
- emmelaich 5y ago> what "semantics" means An almost Clintonian phrase! :-)
- cldellow 5y agoI 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 https://sqlite.org/forum/forumpost/878ca7a9be0862af?t=h
- 3dfan 5y agoIt 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 5y agoI 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 5y agoIf anything, this behavior is far simpler than trying to automagically coerce types and marshal differing types to/from one column.
- nikeee 5y agoI think the point here is that there are now two modes of operation, which leads to more complexity overall.
- dkjaudyeqooe 5y agoStrict tables still do both of those things.
- curiousmindz 5y agoAs 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).
- ComputerGuru 5y agoIt’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.
- pstuart 5y ago> especially if we want to preserve high-precision (nanoseconds). Why not just by convention as ints, nanoseconds UTC post epoch?
- jatone 5y agobecause the lack of a type annotation via introspection makes tools automatically generating code for your less than useful.
- karteum 5y agoGreat 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) ?
- mastax 5y agoWell 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.
- phiresky 5y agoAccording to the docs, looks like the ANY type in a strict table might do that: > In a STRICT table, a column of type ANY always preserves the data exactly as it is received. For an ordinary non-strict table, a column of type ANY will attempt to convert strings that look like numbers into a numeric value, and if successful will store the numeric value rather than the original string There's still much less data types than in most SQL databases though, e.g. float4, boolean, timestamp etc are missing.
- mastax 5y agoSQLite converts NaNs to NULL, and SQLite libraries tend to throw on encountering NaN.
- marcosdumay 5y ago> SQLite will expand it to a double Is that part a real problem? (The NaN thing certainly is.) Doubles are a strict superset of floats. You don't risk losing any precision on the float to double conversion, and if you then convert the result back to float, you won't lose any precision either.
- mastax 5y agoIt's can be a real problem when you need to store 13,000,000 floats per minute. Is it hard to work around? No. But every time I use SQLite it requires half a dozen little hacks to work around it's strangeness and I would prefer to not have to do that.
- gigatexal 5y agoyup - 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
- 41b696ef1113 5y agoThe order by optimization was a work-around for auto-generated SQL. The original (?) issue [0] [0] https://sqlite.org/forum/forumpost/2d76f2bcf65d256a https://sqlite.org/forum/forumpost/2d76f2bcf65d256a
- zzzeek 5y agowow, 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).
- malkia 5y agoFinally - table_list - You can't imagine in how many places sqlite pops up (web too - for example https://perfetto.dev/ https://perfetto.dev/), and having introspection built-in abilities helps a lot!
- remram 5y agoYou could already select from sqlite_master
- TedShiller 5y agoThe Postgres documentation should adopt these types of state diagrams to describe the syntax: https://www.sqlite.org/lang_createtable.html#ckconst https://www.sqlite.org/lang_createtable.html#ckconst
- Keyframe 5y agoSyntax diagram, just like ye olde Pascal syntax poster!
- cryptonector 5y agoThey don't look very accessible to me, but yes, they are pretty.
- tpetry 5y agoMany years ago when learning SQL i was looking at these state diagrams despite using another database. So yes, they should be added! They may not be usefull for anyone SQL who is able to read the Postgresql grammar definition but for someone learning it it‘s helping very much.
- ako 5y agoOracle has been using these in their reference guides for at least 30 years, e.g.: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6 https://docs.oracle.com/en/database/oracle/oracle-database/2... Personally i'm more helped by good examples than these syntax diagrams.
- pepoluan 5y ago"state diagrams"? Did you mean "syntax diagrams"?
- unbanned 5y agoCascade deletes yet?
- 41b696ef1113 5y agoWould this be bundled into existing Python releases or would I have to wait until Python 11 to benefit from strict?
- jftuga 5y agoIf you don't want to wait, you can pre-link a custom Python build with your own-built sqlite3: https://stackoverflow.com/a/28045550/452281 https://stackoverflow.com/a/28045550/452281
- barosl 5y ago> To keep things simple, the current parser accepts duplicate options without complaining, but that might change in future releases, so applications should not rely on it. I love this level of attention to detail and kindness in the SQLite documentation. Very few projects do this.
- brigandish 5y ago> Some developers appreciate the freedom that SQLite's flexible typing rules provide and use that freedom to advantage. But other developers are aghast at SQLite's flagrant rule-breaking and prefer the traditional rigid type system found in all other SQL database engines and in the SQL standard. For this latter group, SQLite supports a strict typing mode, as of version 3.37.0 (2021-11-27), that is enabled separately for each table. At last.