Comment by zandorg
17 years ago
The sad truth is that there are bugs in SQLite which aren't being fixed.
For instance, .quit doesn't exit the program when running a script from the command-line (or C's system() ) via .init.
Also, have more than 2 inner joins and a query takes 10 minutes. I fixed this by writing some custom queries.
D. Richard Hipp (the author) refuses to accept these are bugs, when they obviously are. Also, people like Mozilla get first call on bug fixes, so the 'little guy' gets no support for no $$ - I read it costs $75,000 for 'special' status and $1500 a year for support.
The sad truth is that there are bugs in everything which aren't getting fixed. If a well-written patch were submitted, would it be ignored? Or are they just not placing priority on it themselves?
I can understand if somebody who wrote an incredibly useful system and released it into the public domain isn't always willing to take the time to fix bugs they consider minor, without being compensated to do so. (I'm glad he's sharing it, at all.) I don't think the issue is Ulrich Drepper-like behavior here.
About the inner joins - SQLite can be small because it doesn't have the tremendous amount of query optimization that e.g. Postgresql does. That's like complaining that a bicycle makes a lousy tow truck; significantly improving its query optimizer would require changing it into a completely different kind of tool, and a big part of its utility comes from being small enough for embedded use.
I think a patch would be looked at, but he'd have to admit it was a bug to accept it.
My complaint isn't that he won't fix the bug for free, it's that he won't acknowledge it's a bug, and thus won't fix it even in a year's time.
Also, I'd pay for the bug to be fixed, by hours, but I saw it costs from $1500 to $75,000 for special service. So I had to fix the bug myself.
The .quit bug, I found already mentioned on the Internet, but unsolved. I fixed that one myself with a simple hack [.quit = exit(0) ] and recompiled SQLite.
" About the inner joins - SQLite can be small because it doesn't have the tremendous amount of query optimization that e.g. Postgresql does. That's like complaining that a bicycle makes a lousy tow truck; significantly improving its query optimizer would require changing it into a completely different kind of tool, and a big part of its utility comes from being small enough for embedded use. "
What I realised is that some free libraries are worth the effort of having to cope with odd bugs. For instance, I'd rather spend 5 hours writing a 'cascading filter' which does its own inner joining but on top of SQLite, than 6 months writing an SQL database engine!
Also, I didn't change a line of SQLite code, it's all in my queries.
Can you expand a bit on the inner join issue, how you fixed it, and when and how the author refused to accept that it was a bug?
Basically, I can't go into it properly because the project is confidential, and I can't think of a similiar table example.
In SQLite:
2 inner joins took about 10 seconds. 3 inner joins in one query were taking 10 minutes.
I was using inner joins to filter - ie shrink the table - when a row had multiple fields in one column (eg, a house has 1 front door but 3 bathrooms).
What should happen in SQLite is that each progressive inner join should make the search table smaller, not bigger. So a 3-join query should be quicker than a 2-join query.
The solution was to create some temp tables, do 1 inner join at a time, and shrink the temp tables down myself.
Now a query with 4 inner joins takes 8 seconds, without changing a line of SQLite. You can't tell me that's not a bug! :-)
That's not a 'bug', it's the expected behavior of the current design and implementation. From the optimization overview (http://www.sqlite.org/optoverview.html):
The current implementation of SQLite uses only loop joins. That is to say, joins are implemented as nested loops.
Know your tools.
I think you misunderstand the goals of sqlite, which is why you think it's a bug and the author does not.
Sqlite doesn't intend to include a very smart query planner simply because that would make it much larger and more complex. That means it'll do stupid things for some queries sometimes, but in return, you get a small library that's quick for common queries.