On Mon, 2005-09-12 at 07:15 -0400, Ned Batchelder wrote: > 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.
I don't want a control. A control is a conscious device that I want no part of. Perhaps swapping the order of entries, or performing some bogus event, etc. These are things that the SQLite list can mention, that can always be considered SQLite oddities, but that users shouldn't be concerned with trying "on their own". These controls _are_bugs_. Period. Comments and pragmas have this nasty tendency to stick around longer than necessary. I realize that if the Pg people have decided _this_particular_ control is necessary- that is, the bug cannot be fixed, then pragmas are not the way to go, but instead SQL itself needs to be fixed. I like, however, to follow the conventions set up by others, so I think CROSS isn't such a bad idea as a result. That all said, it seems like this problem is already solved- SQLite does the right thing after ANALYZE is called. Perhaps it wouldn't be too difficult to update the statistics that ANALYZE collects in after COMMIT, or perhaps after a COMMIT that runs longer than a particular amount of time. This would obsolete the need for a control, and as I've said, controls are bad. > As a few of us have mentioned, other database engines have used inline > pragmas in the form of specially formatted comments: Other database engines are wrong so...? Other database engines support [] for attribute boundaries or backslash as an escaping character. I suppose these should be used too? After all, they solve a very specific non-problem at a horrible expense to the user, so they must be good right? Note what I am saying: _optimizer_ controls are bad. They make the user miserable because they lead the user to believe that their SQL is invalid or incorrect instead of Pg or DB2 or MSSQL or MySQL or SQLite being incapable. gcc -O2 makes faster code as long as it's not smaller. Why isn't it default? find -noleaf on the converse works around a bug in the kernel. Why not fix the kernel? -noleaf should do NOTHING on current systems. Why do I need to intermix -O0 on some non-speed sensitive code because of gcc bugs, and why should I the programmer bother? I am much more willing to accept and workaround the failures of gcc, some unixes, and SQLite than I am to have by own intelligence insulted by a program I know very little about. ... after all, if I try a new program and it tells me I performed some kind of syntax invocation error, I tend to believe it. I think _I_ must have done something wrong. Controls simply reinforce this mind-think. > 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. Agreed. That's exactly why I recommend them over code-hiding. Code-hiding has the distinct advantage of keeping bugs around. People love the fact that they can hide CSS from MSIE so that they can pretend to have fully conforming XHTML1.1+CSS web sites that look absolutely stunning in some latest version of Firefox, but they don't change the fact that MSIE had a bug in it. People who visit a website that looks like junk _NEVER_ say "oh my web browser is being a piece of shit." Instead, by forcing these bugs to be deliberate and visible, we prevent people from deploying broken code to broken systems, but STILL allow people who "have no other option" to proceed with the full knowledge that a future SQLite release will require they change their code. > 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. Which brings me back to PostgreSQL - which was commented as not having a special pragma for this but instead a sly little hack identical to the one SQLite is using. People who target PostgreSQL will already have had the fix. > -----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. > > :) > >
