
Ten-year-old severe SQLite bug fixed - turrini
https://www.sqlite.org/src/info/2df0107b
======
winteriscoming
When I read the subject I thought the bug was reported 10 years back and got
fixed now. But this looks like it was reported very recently and got fixed
immediately. The bug report doesn't have any discussion except that it says
the bug even affects versions which were released around 10 years back.

~~~
dx034
It is also worth pointing out that in this case the result of a query wasn't
returned, an error that would probably be noticed in most cases and bears no
security implications (at least none I can see). That alone makes it far
better than a 10y old security relevant bug.

The fact that it was only reported now probably shows that it rarely occurs in
real world applications.

~~~
colanderman
> The fact that it was only reported now probably shows that it rarely occurs
> in real world applications.

And/or that those who encounter it assume they're misunderstanding left joins,
and try something different.

------
colanderman
I encountered a similar bug to this (basic JOIN semantics error) about 4 years
ago. Though it had been fixed in the head version by the time I found it, it
had clearly been present for years. Turned me off to SQLite ever since -- it's
painful not to be able to trust query results.

Goes to show that even literal "millions and millions of test cases" don't
catch bugs you didn't think of in the first place. This is a domain where a
theorem prover could really shine -- checking transformation invariance in a
non-Turing-complete language.

~~~
PopsiclePete
I doubt "enterprise" databases like Oracle or MS SQL Server don't have similar
bugs. Actually, what non-trivial software doesn't have bugs? I had the
opposite reaction to you from reading this bug. They fixed it amazingly
quickly, after it was reported, no fuss.

~~~
colanderman
I've used Postgres a lot more and for more complex queries than SQLite, and
have never encountered nor heard of a query semantics bug with it (though I'm
sure they exist). I know that is total anecdata, but my experience with
Postgres is – whether thanks to more collective query hours, better testing
methodology, or simply higher quality design – an experience that leads me to
trust it more than SQLite.

