On 2018/01/05 4:24 AM, Dinu wrote:
Thank you for your answer, Keith. I had my problem "fixed" before I wrote the
first mail. Also with every problem I also provided the fix that worked, for
anyone that might run into the same problem.
However, it's difficult to not get a little frustrated with your answer.
At https://sqlite.org/queryplanner.html I read:
"The best feature of SQL (in all its implementations, not just SQLite) is
that it is a declarative language, not a procedural language. When
programming in SQL you tell the system what you want to compute, not how to
compute it."
I'm sure his frustration is on a par. :)
While we've both stated that your queries are not equivalent, Keith took
the time to write an explanation of why that is, which seemingly did not
hit home, and now I will try again with an analogy:
You are essentially have a delivery person usually tasked to take a
truck, go to a farm and pick up a load of eggs. Now you ask the same
driver to use the same truck to go to the corner cafe and pick up 6
eggs, and then you exclaim "Wow, why he takes the truck? he can just
take the scooter!! The truck is sooo inefficient for this job!".
And yes, you are right, but the problem is you asked for it to be done
by truck. Now I agree the ideal in SQL is (as the quote above states)
that one should merely ask for a result and the engine should decide how
best to achieve it, but in practice there are many nuances in the
programming of the engine that thwarts this ideal, not to mention how
many programmers like to tweak their queries to get the engine to
execute the quickest, and there is nothing wrong with this. It does
however mean that the engine should in all circumstances, while trying
to find the best query plan, still adhere to the type of question that
was asked of it. You asked a METHOD A question, it won't (and shouldn't)
apply a METHOD B to reach the accidental similar result.
Second problem, your queries show the narrowest of use cases. The engine
has to work for ALL use cases which can get very involved and complex.
The engine needn't have another level of abstraction AI going "Oh this
one is simple, we will disregard what the programmer asked for and use
our own more simple query because it should get the same result."
To sum it up: I think it's every DB's intention to optimize as best possible
a query into an execution plan. None does it perfectly, but all try to, very
hard. With this intention, I reported a case where the query planner COULD
be improved. I think you will at least agree with me that making it better
can't be wrong. Whether that happens tomorrow, in a year or never, that's up
to the mercy, resources and priorities of the developers, so I am really am
not interested in an argue over this.
It's a good idea to report possible improvements, and thank you for
that, but this case isn't able to improve since mangling an outer join
into an inner join when sometimes it might yield the same result is as
unsafe as it gets. However, that doesn't mean the devs (who would have
read all this) doesn't find something of interest and could possibly
think of a tweak that might improve things, so having this debate is
never a waste, but the specific algebraic essence of what you are
suggesting is not correct - 's all we're sayin.
Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
