I'm not sure if we agree or disagree. You say: > I don't think the number of optimizer controls should grow. > It should be kept as close to zero as possible.
But then you say: > If [the optimizer] can make a mistake, I want a simple > and _unobtrusive_ way to correct _just_that_mistake_ for > _just_that_query_. As the optimizer grows, the number of mistakes it could make will grow. You seem to want a control for each mistake. The number of controls will grow. As a few of us have mentioned, other database engines have used inline pragmas in the form of specially formatted comments: select /* sqlite_stupid */ A.parent, A.child from /* sqlite_stupid */ revision_ancestry ... New keywords will also work. The comments have the advantage that they are "portable": you can pass this exact query to any database. Only SQLite will interpret the comments. If SQLite invents a new STUPID keyword (or SQLITE_STUPID), the query will only be parsable by SQLite. Granted, using pragma comments in queries is unlikely in a system where queries are fed to multiple DBMS's, but at least it would be possible. --Ned. http://nedbatchelder.com -----Original Message----- From: Mrs. Brisby [mailto:[EMAIL PROTECTED] Sent: Sunday, 11 September, 2005 10:07 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] CROSS keyword disables certain join optimizations 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. :)
