
SQLite: Infinite loop due to the ‘order by limit’ optimization - ScottWRobinson
http://www.sqlite.org/cgi/src/info/9936b2fa443fec03ff25
======
hyperman1
I think most of us agree that SQLite is close to the gold standard for
stability and testing. This post reminds us that even SQLite has critical
bugs.

But looking at
[https://www.sqlite.org/cgi/src/rptview?rn=7](https://www.sqlite.org/cgi/src/rptview?rn=7)
I see a few Core Crash Bugs every month (April has 7). Most of them are not
'critical', to be clear, plenty of 'severe' and 'important' in the list.

I don't want to disparage SQLite. They have a free, fantastic product, go to
great lengths to have stability no matter what, and document their processes.
I learned a lot of them.

But clearly, our gold standard is not perfect. So now what?

* We might turn to theorem proving. But is it possible at SQLite scale? Are the proofs themselves enough? I remember about a critical piece of software with a proof no incorrect answers would come out. Turns out they forgot to prove the program would end, so they got no answer ever instead of an incorrect one. How do you prove your proofs are complete enough?

* We might turn to fuzzing, code analyses, linting,... SQLite does all of them.

* Or simply admit we are human, fallible, and try to do the best we can while knowing our limits? Sounds defeatist.

~~~
laumars
It's impossible to write bug free code. All these tools we have do is mitigate
the number and severity of bugs around. If the remaining ones are weird edge
cases that don't affect many people then that's a net win for everyone else.
But we shouldn't assume that even "gold standards" are bug free

> _Or simply admit we are human, fallible, and try to do the best we can while
> knowing our limits? Sounds defeatist._

Not all bugs are manmade (though obviously the vast majority are). But to give
examples of other types of bugs: there could be bugs created by compiler
optimisations or bugs due to esoteric hardware. Or even features that was
intended behaviour but the landscape has since shifted in ways the developers
were not aware (I guess you could argue that last point is manmade bit it's
arguably not the failure of the developer).

~~~
anfilt
It's not impossible to write, but just extremely hard for anything that is not
trivial.

For example take function that takes a byte as an input and has byte as an
output. Moreover has no external or internal state. You can run through all
256 inputs and check the outputs.

However, if the function has 128 bits of input. You could not possibly
enumerate all possible inputs and check the output. So it takes other methods
to prove it's bug free. This can be hard to find, and there is no way to
automate this in all cases. (Halting problem)

However, bug free code is not impossible to write. I might say a bug free
system is more likely to be impossible since would involve perfect hardware
that is also immune to interference, and the physical world can cause a bit to
flip while performing a computation.

~~~
laumars
That code doesn't run independently. You need a compiler, compilers aren't bug
free. You need other functions to gain it's input and functions to do
something with the output - those might all contain bugs. Even if you're not
targeting different OS's or even "POSIX's", and even if you're not targeting
different CPU architectures, you still get subtle variations in AMD64 chips
that can - if you're working low level enough - throw up some bugs. And if
you're not working that low level then you have the entire runtime stack and
their bugs to contend with.

If computing was still just flipping switches and waiting for LEDs to flash
then you might have a point. But in real terms, it's just not possible to
write 100% bug free code anymore. The real trick is eliminating all the bugs
that people are likely to experience in normal operation and all the bugs that
attackers might exploit. Even that is no small feat.

~~~
vfclists
I think Dijkstra put paid to that argument ages ago. The program is an
abstract that the compiler implements. If the compiler is faulty that doesn't
make the program faulty.

It is like arguing that because a ship's navigation system was faulty the
directions given for a destination were wrong.

To use his words we supply computers to programs, we don't supply programs to
computers. or something to that effect.

[https://www.cs.utexas.edu/~EWD/transcriptions/EWD10xx/EWD103...](https://www.cs.utexas.edu/~EWD/transcriptions/EWD10xx/EWD1036.html)
Paragraph beginning "But before a computer is ready to perform a class of
meaningful manipulations ..."

~~~
laumars
I agree with you to an extent but the issue isn't so much who's to blame for
unexpected behaviour but rather who's responsible for fixing it. You could
make all kinds of reasoned arguments about who's to blame if a compiler
optimisation or even outright compiler bug caused good code to behave badly
under certain conditions but ultimately the end users don't care as they would
still expect the application developers to fix the build scripts and/or
project code regardless.

To use the ships navigation system example, if you have some kind of "auto-
pilot" system that would steer a ship into central London when a bad
destination was set, then you'd quickly demand the authors of the auto-pilot
to write exceptions for bad destinations.

That's the ugly side of development; all too often us developers are having to
write code to handle ugly systems. It's not fun but it's usually necessary.

------
nathancahill
For the curious, this was the resolution:
[http://www.sqlite.org/cgi/src/info/206720129ed2fa88](http://www.sqlite.org/cgi/src/info/206720129ed2fa88)

------
danso
> _This problem was originally reported on the SQLite users mailing list. It
> took considerable effort to reproduce the problem and then boil it down to
> the repro script shown above._

The resulting script for reproducing the bug is indeed concise, but I almost
want to read about the starting point, and how much of a struggle it was to
investigate and deduce the factors behind this bug.

------
lixtra
I find the infinite loop much less troublesome than the wrong data that
apparently this bug may cause as well.

------
exikyut
ARG. The sqlite mailing list manager sent me my plaintext password back in its
"welcome" email.

D':

~~~
akkartik
Mailing lists traditionally rely minimally on passwords. The "password" field
at [http://mailinglists.sqlite.org/cgi-
bin/mailman/listinfo/sqli...](http://mailinglists.sqlite.org/cgi-
bin/mailman/listinfo/sqlite-users) has this copy:

 _" You may enter a privacy password below. This provides only mild security,
but should prevent others from messing with your subscription. Do not use a
valuable password as it will occasionally be emailed back to you in
cleartext."_

This is arguably better security because you can leave the password field
blank, and a random one will be generated for you. That protects non-tech
people from accidentally using a valuable password for something that is not
actually security-sensitive.

~~~
IshKebab
Clearly not everyone reads the small print and even if they did that doesn't
really excuse it in today's internet.

Hashing passwords is not hard.

~~~
joesb
Hashing password has nothing to do with sending email in plaintext.

You can hash password and also email password in clear text at sign up, when
you still have access to the password.

And it doesn't even make sense to send hash of the password back in email.

~~~
IshKebab
I wasn't talking about sending emails.

~~~
mort96
But... the conversation was about sending the password in emails.

~~~
IshKebab
Read the comment I replied to. It explains that the passwords are not hashed
at all. They are not sent in an email and then hashed.

I was talking about the practice quoted in that comment of not hashing
passwords.

Edit: To be clear it doesn't say it explicitly, but it says that the cleartext
password is sent periodically and the only way to do that is to not hash it.

------
pornel
I guess the test suite doesn't check for the halting problem yet.

(but seriously, it's great when a project is so stable that having a bug is
newsworthy).

------
spdegabrielle
I wonder if a a usable subset of SQLite/sql92 would be amiable to to formal
methods, or if that is still to big?

~~~
jfk13
I think you meant "amenable" rather than "amiable" here.

------
yread
Is it going to end up in 3.25?

------
nathancahill
Better title: "SQLite: Infinite loop due to the ORDER BY LIMIT optimization"

~~~
dang
Changed from "SQLite: View Ticket". Thanks!

~~~
hyperman1
To start a tangent @dang: Do you really read every individual comment, or is
there some pattern matching on specific strings like 'Better title'? If so,
what are the patterns?

Just want to know if we can help you in any way. I'm in awe of moderation
efficiency on this site.

~~~
dang
There are too many comments to read them all. We rely on flags, emails,
browsing the threads, and a few tricks. But not really pattern matching on
strings.

I once thought it would be fun to have a control DSL for HN, such that people
could plant phrases in comments to signal things to either moderators or the
software. Like most ideas of that sort, it never made it over the 'too
complicated' speed bump.

------
yoklov
This probably needs a rename, "SQLite: View Ticket" is not a terribly
descriptive title.

That said, it's a little surprising to see a bug of this type in SQLite which
generally has such solid test coverage... Oh well, nothing is bug free after
all.

~~~
akira2501
> That said, it's a little surprising to see a bug of this type in SQLite
> which generally has such solid test coverage

I was just thinking that's the sign of an extremely mature system. No system
will never be free of problems, but you will have less of them, and
paradoxically, the problems you are left with have far more complicated and
usually emergent paths to activation.

~~~
peteretep
> usually emergent paths to activation

What do you mean by this?

~~~
akira2501
In this particular case, the fault wasn't in any one component and was
subsequently hidden and then revealed due to the complicated interplay between
multiple modules. This is usually where case-by-case testing breaks down and
you have to turn to other methods like fuzzing to elicit these behaviors.

