Scott Marlowe <smarlowe@g2switchworks.com> writes:
> Hehe. When I turn on my windshield wipers and my airbag deploys, is it
> a documented "feature" if the dealership told me about this behaviour
> ahead of time?
Well it's more like my car where the dashboard dims when I turn on my
headlights which annoys me to no end since I learned to always put my
headlights on even in the day.
> In much the same way, while this behaviour may be documented by MySQL, I
> can't imagine it really being called a feature. But at least this
> misbehaviour is documented. However, I think most people in the MySQL
> universe just stumble onto it by accident when they try it and it works. I'd
> at least prefer it to throw a warning or notice or something.
I don't see why you think people stumble on this by accident. I think it's
actually an extremely common need. So common that Postgres has the same
feature (though less general) and invented a whole syntax to handle it.
I think most MySQL users don't stumble on it, they learn it as the way to
handle the common use case when you join a master table against a detail table
and then want to aggregate all the detail records. In standard SQL you have to
write GROUP BY ... and list every single column you need from the master
table. Forcing the database to do a lot of redundant comparisons and sort on
uselessly long keys where in fact you only really need it to sort and group by
the primary key.
Remember, most MySQL users learn MySQL first, and only later learn what is
standard SQL and what isn't.
> A Subselect would let you do such a thing as well, and while it's more
> complicated to write, it is likely to be easier to tell just what it's
> doing.
Subselects have their own problems here. Mainly Postgres's optimizer, as good
as it is, doesn't treat them with the same code paths as joins and can't find
all the same plans for them. But in any case you cannot always write a
subselect that's equivalent to an arbitrary join.
--
greg