On Sat, 2005-09-10 at 21:38 -0400, Ned Batchelder wrote:
> Rather than overload an existing SQL keyword, would it be possible to
> provide pragmas to control the optimizer? Assigning meanings to particular
> combinations of SQL queries won't scale as the number of optimizer controls
> grows.
I don't think the number of optimizer controls should grow. It should be
kept as close to zero as possible.
The query optimizer isn't (and shouldn't be) designed to help out people
who don't know SQL, but should be for people who know SQL but don't know
SQLite.
When SQLite does this optimization of table reorganization, it's doing
so because it doesn't have a real optimal way to answer the
query-as-stated but if the query is transformed into something seemingly
(and sometimes algebraically) equivalent, then it does so it can.
Consider a mythical SQL engine that doesn't reorder where terms before
searching an index of the form "a,b".
It can answer queries like:
WHERE a='abc' AND b='def';
but give it:
WHERE b='def' AND a='abc';
and it could take forever. Anyone reasonably aware of the various SQL
engines out there would undoubtedly be aware of engines that DO treat
them as identical, but here is one that isn't.
Given that this is indeed a _query_optimization_ do you really want a
control for this?
While I'm sure many SQL engines don't put this nonsense in the block of
code that looks for query optimizations, I want the query optimizer to
do the best job that it can. If it can make a mistake, I want a simple
and _unobtrusive_ way to correct _just_that_mistake_ for
_just_that_query_.
I think anyone close to the various SQL working groups-that-be should
consider a "STUPID" operator that takes the next term (whatever that
might mean to the SQL engine) and stops any query optimization on that
term. The STUPID keyword would be allowed everywhere. It'll improve the
readability of SQL as it is:
SELECT STUPID A.parent, STUPID A.child STUPID FROM STUPID
revision_ancestry STUPID AS STUPID A ...
I don't like CROSS, but it is the closest thing to what I might find
acceptable.
:)
