On 2019/04/17 1:23 PM, Poor Yorick wrote:
That's an apt and accessible description of the issue, but at the denotational level the meanings of the queries are in fact identical under the conditions you enumerated. Ideally sqlite would notice and adjust its query plan accordingly.
Ideally yes, but the heart of the matter, I would like to reiterate, is that the query in question has to be just so for the optimization to work, anything you change (using b.anything anywhere, having duplicate b values or in any way not using rowids or at least unique columns of some flavour, using a collation, etc.) would render the optimization opportunity void. The opportunity hangs by the tiniest of threads, or put another way: the decision tree + checks to arrive at this optimization opportunity is very long, and I doubt a similar query is encountered by the QP more than one in a few million times (with a strong possibility that that should read "in a few billion times") ever - and when it does come up, the programmer most probably would opt for the latter example since it more clearly describes the plot. (The fact that it is also faster is just lucky).
If on the other hand a situation presents itself where the obvious better plot description (i.e. the second query) was /slower/ - then the optimization (to rather do it like the faster first query) would be a more worthy cause.
The fact that we can engineer a query that happens to be equivalent mathematically and is slower does not sufficiently call for an effort to improve the planner. In fact, we can engineer many such queries, and these kinds of things come up quite regularly on the forum. To be sure, they often do get optimized IF the slower query is the one that is semantically more sensible and the effort to implement plus the added code-weight and cpu cycles are justified by the gain in the general case.
If the cost of doing so doesn't justify the effort, that could be documented. As good as the sqlite documentation is, it currently lacks this sort of higher-level guidance.
I agree, perhaps some general description might be useful, though it's hard to imagine it mentioning specific scenarios. Consider that the amount of semantically-different-but-functionally-equivalent-yet-slower queries that can be engineered must be legion (the forum produces new ones almost monthly). It's hard to fathom documenting all of them - plus some of them disappear with improvements over time.
Maybe you could volunteer a paragraph of documentation that would have adequately satisfied your question in this regard and in general - the devs often do amend documentation based on suggestions here.
Cheers, Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
