[sqlite] Question regarding the LEFT JOIN bug in 3.7.10
Alexandr Němec
Dear all,
I've just read the 3.7.11 release notes. This version fixes the LEFT JOIN bug with OR in the WHERE clause introduced in 3.7.10. Since our software is currently based on 3.7.10 and it is not easy for us to upgrade to 3.7.11, I just want to be sure about the bug impacts.
1. I have double checked our code and there is no LEFT JOIN at all, but there are a lot of INNER JOINS and complex WHERE clauses with multiple column indices and the OR operator. Is the LEFT JOIN a requirement for this bug to appear? With other words, are we safe with 3.7.10 if no LEFT JOIN keywords are used at all? I think that one could rewrite a LEFT JOIN query using an UNION and the NOT EXISTS operators. Would this bug appear in this case as well?
2. A more general question. We like SQLite for its performance, robustness and reliability. We also read the "How SQLite is tested" article, which states that millions of SQL queries are run before any SQLite version is released. Well, how is it possible, that a SQLite version passes these tests with something fundamental like LEFT JOIN not working correctly?
Thanks.
Alex
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Richard Hipp
>
> Dear all,
>
> I've just read the 3.7.11 release notes. This version fixes the LEFT JOIN
> bug with OR in the WHERE clause introduced in 3.7.10. Since our software is
> currently based on 3.7.10 and it is not easy for us to upgrade to 3.7.11, I
> just want to be sure about the bug impacts.
>
> 1. I have double checked our code and there is no LEFT JOIN at all, but
> there are a lot of INNER JOINS and complex WHERE clauses with multiple
> column indices and the OR operator. Is the LEFT JOIN a requirement for this
> bug to appear? With other words, are we safe with 3.7.10 if no LEFT JOIN
> keywords are used at all?
Yes. If you do not use LEFT JOIN then you cannot be hit by this bug.
> I think that one could rewrite a LEFT JOIN query using an UNION and the
> NOT EXISTS operators. Would this bug appear in this case as well?
>
> 2. A more general question. We like SQLite for its performance, robustness
> and reliability. We also read the "How SQLite is tested" article, which
> states that millions of SQL queries are run before any SQLite version is
> released. Well, how is it possible, that a SQLite version passes these
> tests with something fundamental like LEFT JOIN not working correctly?
>
Prior to version 3.7.11, we didn't have any test cases involving a 3-way or
larger join in which one of the joins to the left was a LEFT JOIN and in
which there were OR terms in the WHERE clause that could be indexed. Rest
assured that we have such test cases now.
In any system (software or otherwise) there is tension between reliability
and performance. Whenever you tweak a system to get it to perform better,
you introduce additional risk that the system will fail. It is if there is
an invisible line (which is probably a fractal line) that divides working
and not-working systems, and to achieve optimal performance, your job is to
push the system as close to that line as you can without crossing it.
Sometimes we push a little too hard and mistakenly cross that line.
The LEFT JOIN problem in 3.7.10 is exactly such a case. We enhanced the
query optimizer to take better advantage of indices when there are OR terms
in the WHERE clause, and in so doing, we accidentally busted LEFT JOIN
processing for joins to the left of the one where the new optimization was
applied.
>
> Thanks.
>
> Alex
> _______________________________________________
> sqlite-users mailing list
> sqlite...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org