Re: fool-toleranced optimizer

On Wed, 2005-03-09 at 11:02 +1100, Neil Conway wrote:
> Oleg Bartunov wrote:
> > I just noticed a little optimizer problem - in second query there is
> > unused 'tycho t2' table alias which gets backend buried.
>
> It's not an "unused table alias", it is specifying the cartesian product
> of `tycho' with itself. I don't see how this is an optimizer problem:
> it's a perfectly legitimate query, albeit one that is unlikely to
> execute very quickly.

Turn this thought around a bit and the request makes sense.

Oleg is saying that the optimizer doesn't protect against foolish SQL
requests. His query is an example of a foolishly written query.

It is reasonably common SQL mistake to inadvertently request a cartesian
product join, when that was not actually desired. This is mostly
prevalent in Data Warehouse situations where people are attempting to
request complex result sets.

It seems a reasonable that there might be a GUC such as
enable_cartesian = on (by default)

If an admin felt that this was a problem, they could enable it for their
novice users only, or perhaps across the whole system.

If enable_cartesian = off, then queries with cartesian product joins
would be made to fail. Which should be easy to detect in early stages of
optimization.

So, Oleg, for me, the request makes sense, though somebody would need to
code it...

Best Regards, Simon Riggs