At the risk of preserving this thread well past its end of life cycle...
On 2018/01/05 6:58 PM, Dinu wrote:
Ryan,
You cannot ask SQL a Method query, that's where my whole RDBMS understanding
takes me.
Everything you ask SQL is underpinned by a specific Method. Perhaps I
should have been more clear - by METHOD I mean, in the case of the join
method, "the /way/ you expect the query to enumerate rows" such as an
Inner join being one method, and outer join being another method.
Sorting/Ordering is a method of output, grouping, etc.
Why would the SQL standard propose these different methods if they were
not meaningful and distinct?
As I said before, there need not be an AI to judge that the query
uttered by the programmer can in fact, in a narrow case, be recomputed
as another query because the result will be the same and hopefully that
the alternate method would be more efficient.
Note that I said there needn't be... I did not say there /can't/ be one,
indeed, query flattening is a good example, but with query flattening
the cost is low and the reward is high for a really broad spectrum of cases.
Why did I say it is not needed? Well, what you propose has a relatively
high cost (added heuristic AI) considering it is paid across all DB
engine query planning to achieve a small advantage in the narrowest of
use cases, not to mention that - should the programmer wish for a
speed-up for the left join that conforms to this narrow set of
circumstances, he or she could instantly change it to a normal join (the
way it should have been to start with) and enjoy the fruits of the added
speed with zero cost to the rest of us who wouldn't have made the
imperfect query in the first place.
Why do I call it narrow? Have you looked at your example queries in
detail? Do you know how many things must be exactly just so (or how many
other normal query things must be absent) for that join replacement to
work algebraically? At least in the case of query flattening, it
improved a query construct that is found abundantly and considered the
correct construct for the expected results.
You essentially want the engine to second-guess programmers who didn't
write the best query for their expected results. That kind of
hand-holding belongs to the realms of Microsoft and MySQL.
(Apologies for all the word clarifications, but I'd rather avoid having
this turn into a "semantics" debate, so trying to be as clear as
possible on meanings of statements... not sure I succeeded though) :)
Cheers,
Ryan
PS: I'm not judging MySQL, at least it has the benefit of being a fully
fledged server-side software and greatly tweak-able on the fly for all
its hand-holdy functionality.
PPS: I will say this - If you're not using MySQL in STRICT mode, you
are n hours away from some disaster, where n is a not-too-big positive
integer.
PPPS: I wish SQLite had a STRICT mode. :)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
