9 ms·
SQLite: Infinite loop due to the ‘order by limit’ optimization
- yoklov 8y agoThis 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 8y ago> 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 8y ago> usually emergent paths to activation What do you mean by this?
- akira2501 8y agoIn 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.
- contravariant 8y agoOn the other hand this is the kind of bugs you'd expect to see when programmers rely too heavily on the tests to catch bugs.
- shakna 8y agoIt takes 20 lines to reproduce the bug minimally, and relies on a confluence of code-generator bugs. I believe most fuzzers would take a considerable amount of time to find a bug like this... How are you suggesting a team on a very large project find this elsewise?
- jessaustin 8y agorely too heavily on the tests Presumably they are to find it using something besides tests? Intuition? Casting lots? Code review?
- lifthrasiir 8y agoI think the intention was to embrace formal methods, which "proves" the absence of bugs (to the limited extents though). For a heavily-used critical piece of code like SQLite the lack or weak use of formal methods might actually be a reasonable criticism. EDIT: Not meant to say that the original comment was reasonable.
- shakna 8y agoSurprisingly SQLite does actually embrace formal methods in various places. Such as, in their draft goals [0], under S30200, they outline a memory allocator goal by referring to a proof. Though, if the parents goal was to imply that the whole of SQLite should be verified... I'm not convinced its entirely possible. Quite a lot of SQLite revolves around things around where the halting problem may come into play. [0] http://www.sqlite.org/draft/sysreq.html http://www.sqlite.org/draft/sysreq.html
- adrianN 8y agoAlmost all things you'd want to prove about a program are undecidable in the general case. That doesn't mean much about the particular case of your program.
- 8y ago
- exikyut 8y agoI agree with the rename. I'd suggest "SQLite (Severity: Critical): Infinite loop due to ORDER BY LIMIT optimization" It's very arguable that the bit in the brackets is editorialization. On the one hand I added it so nontechnical manager types would go "wait, what" and forward the issue internally so techs learn sooner. On the other hand, the subject itself is bad enough that _most_ will figure it out anyway, and pragmatically speaking this probably isn't going to kill anything by not being fixed within the next 30.8 seconds, so... Although this could be classed as a security vulnerability because now SQL injection can get you DoS. But the likelihood of the DoS being on a server is arguably low. Definitely nonzero but arguably low.
- quietbritishjim 8y agoI think most readers of Hacker News realise that an infinite loop bug is pretty severe.
- deleted 8y ago[deleted]
- nathancahill 8y agoBetter title: "SQLite: Infinite loop due to the ORDER BY LIMIT optimization"
- dang 8y agoChanged from "SQLite: View Ticket". Thanks!
- ScottWRobinson 8y agoThanks for fixing my mistake on the title :)
- hyperman1 8y agoTo 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.
- larkeith 8y agoMy guess is that they can check who flagged a post, then just view those users' recent comments.
- ScottWRobinson 8y agoI contacted @dang asking to change the title since the edit period had passed. To his credit, he responded in less than 20 minutes, after hours.
- dang 8y agoThere 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.
- nathancahill 8y agoFor the curious, this was the resolution: http://www.sqlite.org/cgi/src/info/206720129ed2fa88 http://www.sqlite.org/cgi/src/info/206720129ed2fa88
- exikyut 8y agoARG. The sqlite mailing list manager sent me my plaintext password back in its "welcome" email. D':
- Rychard 8y agoYou should consider submitting this to http://plaintextoffenders.com/ http://plaintextoffenders.com/
- ajdlinux 8y agoAlong with every mailman setup ever. If you're not using a default-generated password in your mailman subscription you're doing it wrong. (mailman should really just get rid of passwords altogether for non-mod/admin users.)
- akkartik 8y agoMailing lists traditionally rely minimally on passwords. The "password" field at http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqli... 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 8y agoClearly 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 8y agoHashing 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.
- lixtra 8y agoI find the infinite loop much less troublesome than the wrong data that apparently this bug may cause as well.
- anafh83 8y agoPlease, someone post again that document that says how cool and thorough the sqlite test suite is.
- arcticbull 8y agoJust to be clear, are you arguing for less tests?
- deleted 8y ago[deleted]
- deleted 8y ago[deleted]
- deleted 8y ago[deleted]
- deleted 8y ago[deleted]
- deleted 8y ago[deleted]
- amufh83 8y agoNo, just a low blow to the circlejerk that occurs here every time that thing is posted (i.e. every week).
- adrianN 8y agoIt is about as cool and thorough as the test suite that is used for the software in the airplanes you use.
- yread 8y agoIs it going to end up in 3.25?
- hyperman1 8y agoI 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.
- ainar-g 8y agoI recall reading somewhere that the code of SQLite also has some UB. Which makes me wonder, if they use runtime sanitizers, like ASan, UBSan, etc.
- hyperman1 8y agoMight be this: https://blog.regehr.org/archives/1292 https://blog.regehr.org/archives/1292 Looks like SQLite used the sanitizers, and a new tool still found plenty UB. I believe the SQLite auther verified the compiler output for the UB found, and saw it had no impact in practice, so there was no critical bug yet. Then he fixed the UB anyhow. Last paragraph was from vague memories, not from the article, BTW. I would welcome better links.
- selckin 8y agoOr that the basic building blocks/foundations are just terrible
- spdegabrielle 8y agoI wonder if a a usable subset of SQLite/sql92 would be amiable to to formal methods, or if that is still to big?
- jfk13 8y agoI think you meant "amenable" rather than "amiable" here.
- pornel 8y agoI 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).
- danso 8y ago> 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.