On Wed, Jan 14, 2009 at 6:10 PM, Roan Kattouw <roan.kattouw(a)home.nl> wrote:
So DB2 renames LIMIT $n to something else and
doesn't even implement
offset handling, even though both are in the SQL specification?
LIMIT/OFFSET are completely nonstandard. See, e.g., this Google
result confirms this:
http://troels.arvin.dk/db/rdbms/#select-limit
FETCH FIRST n ROWS ONLY is the standard way, not LIMIT. A lot of
stuff that you take for granted in MySQL is completely nonstandard --
REPLACE INTO... and INSERT IGNORE INTO... are two other major
examples.
As a general rule, SQL seems to be such a terrible, slowly developed,
impractical standard that a typical app doesn't implement most of what
it says to do and implements a metric ton of things that it doesn't
mention. Which is why we need such a complicated abstraction layer in
the first place. Ideally we could just write in standard SQL with the
occasional minor workaround, the way we write standard HTML and CSS
and so on, but real life doesn't seem to work that way in this case.
What do you mean DB2 "doesn't have
them"? FORCE INDEX isn't supported in
DB2? Then unless its index choosing algorithm is extremely good, it
won't be able to run certain queries with satisfactory efficiency.
My impression is that MySQL is completely stupid when it comes to
choosing indexes. Most other serious databases are much smarter and
don't need babysitting. Our pgsql wrapper also doesn't implement
this. I don't think anything does other than DatabaseMysql.
Index behavior is very database-dependent -- even if other DBs did
implement forcing a particular index, they probably wouldn't need to
force the same indexes in the same cases. They might not even *have*
the same indexes. I know our pgsql schema doesn't have the same
indexes as MySQL in most cases: it often can make do with fewer,
because of its ability to do things like take intersections of
multiple indexes efficiently, or even retrieve by one index and order
by another.
You shouldn't rename indices like that, because
index names are used in
FORCE INDEX clauses (oh wait, but they weren't supported, right?)
Right. pgsql also renames indexes (because as noted, it doesn't use
the same ones). I'd imagine the same is true for the other DBs we
support or semi-support.