SQLite 3.39.3 | Hacker News Reader

SQLite 3.39.3

sqlite.org

104 pointsmarcobambini34 comments

Open articleView on HN

For discussion on SQLite 3.39.0, see https://news.ycombinator.com/item?id=31876604

I'm not sure why a minor SQLite bugfix release needs to be posted here.

HN is really up on SQLite at the moment.

If an AI were trained to write hit HN posts, "Writing Rust Extensions For SQLite" would probably be in the top results.

I actually wrote a blog post about that:

https://ricardoanderegg.com/posts/extending-sqlite-with-rust...

Why are you posting that in a comment? Submit it and the karma is yours!

I submitted it when I wrote that blog post, but it went unnoticed [0].

[0]: https://news.ycombinator.com/item?id=31388241

Maybe it's a question of timing? I've just resubmitted it, let's see what happens.

And it's now made the front page... twice at the same time :)

Might wanna go for something like this to be safe:

"Why I'm quitting my job to migrate from Rust to Common Lisp for my crypto and SQLite based 'self-hosting email sucks' themed nft tweet generating SaaS platform to prepare it for IPO, after $faang_corp cancelled my account."

That's a pretty good start but I'd extend it with:

* Something about self-driving cars

* Something about Uber

* A casual "Im so cool" reference to the SF Bay Area, like Folsom street or something

* Accidentally letting slip you're in your late teens but giving business advice anyways

What's so cool about Folsom? I would argue Mission Street is a lot more interesting. Folsom (especially the southern bit) is beside that massive I80/101 flyover and that area is mostly ghettos and warehouses. I think Airbnb has their HQ there.

And refused to let me work from home!

"Rewriting SQLite in Rust" would probably come in second :)

Launch HN: ElectronSucks (YC W22) – Rust Extensions for SQLite

I consider these to be reminders that even with a state-of-the-art testing suite and 100 % branch coverage, bugs still find a way, heh.

Branch coverage is a tiny fraction of total program state coverage.

Could yo I elaborate on this? Do you mean things like exceptions that are not explicitly dealt with in the code?

Branch coverage just means you’ve taken every branch, not every combination of every branch, for starters.

Then there’s branchless boundary conditions e.g. your software might fuck up on overflow, but there’s no branching, at least not in your code. Just things which get incoherent in ways you had not anticipated.

A simple example is when somewhere in code you set a value that causes you to go into the wrong branch later. You may have tested that every single branch does what it is supposed to, but this "state here causes unexpected behavior there" can still be open.

> Could yo I elaborate on this?

Think about it for a second. Imagine you have

function(bool foo, bool bar) {

   if(foo) {
      //Something
   } else {
      //Something else
   }

   if(bar) {
      //Something
   } else {
      //Something else
   }

}

If you add two unit tests that test foo(true,true) and foo(false, false) you get 100% line and branch coverage, but only hit 50% path coverage.

> I'm not sure why a minor SQLite bugfix release needs to be posted here.

Karma, probably.

This for sure. "Minor release of well-liked software" is an easy hit on the front page.

Frankly, I don't get it. What do you do with this karma? What does it even mean? If we could exchange it for money or whatever, I could understand. It's not even an indicator of competence or anything, just being in line with the majority and spending time on HN. It's the equivalent of points and badges on Google Maps.

Internet points is the most important currency of your heart. It defines your worth as a human being.

Also I think some HN features are gated on karma? I’d like a feature where the markup isn’t complete shit but apparently I don’t have enough karma for that.

It's cause of the finally added outer join?

That was released more than two months ago, though. This point release is much smaller.

I would also like to see SQLite (core) achieve compliance with the SQL/PSM standard from ISO.

https://en.m.wikipedia.org/wiki/SQL/PSM

i appreciate it.

I would never touch a .0 release of a new version of SQLite. No shame against them or their fantastic quality--it's just that I have lived through literal decades of watching minor database bugs shatter data value and undermine all efficiency.

When I see a .3 or better, I start thinking a version is usable. The fact the SQLite moves their quality needle this quickly is encouraging.

But maybe .4 can go more quietly.

You really do not need to exercise such caution with SQLite.

Because of the DO-178B status, SQLite is sufficiently reliable to be used with in-flight avionics (on Airbus A350 models, noted so far).

The test suite requires every branch instruction to be exercised at the machine-language level, in both directions if conditional.

The software that we are used to is not held to this exacting standard.

If we were talking about Oracle or SQL Server, .0 version hesitance is valid, and SQLite tests have crashed both of them, repeatedly. Obviously, they cannot be used with applications requiring DO-178B compliance.

This document is part of that testing compliance:

https://www.sqlite.org/qmplan.html

And yet TFA shows that 3.39.0 introduced a new feature (right joins), and 3.39.1 fixes a logic bug in exactly that new feature. I suppose you're right that it's not a crash, just an incorrect result.

I am actually happy to have the USING clause available; I find it more convenient than ON.

RIGHT and FULL probably should have come first, but this works for my tastes.

Changes in version 3.39.0 (2022-06-25):

Add (long overdue) support for RIGHT and FULL OUTER JOIN.

Add new binary comparison operators IS NOT DISTINCT FROM and IS DISTINCT FROM that are equivalent to IS and IS NOT, respective, for compatibility with PostgreSQL and SQL standards.

Add a new return code (value "3") from the sqlite3_vtab_distinct() interface that indicates a query that has both DISTINCT and ORDER BY clauses.

Added the sqlite3_db_name() interface.

The unix os interface resolves all symbolic links in database filenames to create a canonical name for the database before the file is opened.

If the SQLITE_OPEN_NOFOLLOW flag is used with sqlite3_open_v2() or similar, the open will fail if any element of the path is a symbolic link.

Defer materializing views until the materialization is actually needed, thus avoiding unnecessary work if the materialization turns out to never be used.

The HAVING clause of a SELECT statement is now allowed on any aggregate query, even queries that do not have a GROUP BY clause.

Many microoptimizations collectively reduce CPU cycles by about 2.3%.

From the changelog.

We had a story on the 3.39.0 release at the time, with 20 comments:

https://news.ycombinator.com/item?id=31876604

I wanted to find something more interesting than news of a minor patch release about what is probably the worlds most installed database.

Turns out the developers sell licenses and a few proprietary extensions - I never knew about CEROD or SEE before. https://www.sqlite.org/purchase/license?

I wonder if proceeds from this commercialisation alone are sufficient to keep the OSS effort going or whether the devs also have other paid endeavours.

My understanding is that the source is open for anybody to see and do whatever, but closed to contributions.

Also, they have some big names who have paid them a lot of money to keep the project alive for the next few decades. Airbus the plane manufacturer is one example.

There are these two podcast episodes [1] featuring sqlite founder Richard Hipp which I encourage you to listen. He is a thoroughbred.

[1] https://changelog.com/person/drh/podcasts#feed

Reply on news.ycombinator.com