Tuesday, 8 February 2011

The end of implicit cartesian products

I've done it before, and I'm sure many others have.  You type:
SELECT * FROM t1,t2;

Without any conditions, and then just wait as your console spews out every combination of the two tables possible in what is called an implicit cartesian join.  Worst still when you are hosting and one of your client's apps does this (I've seen this too many moons ago).

So, in Drizzle trunk today and in our RC release next week we have a new error "Implicit cartesian join attempted." which will fire every time you try a query such as the one above.  If you really want a full cartesian join without a WHERE or ON condition (sometimes, it is needed) then you can use the CROSS keyword.  For example:
SELECT * FROM t1 CROSS JOIN t2;

7 comments:

  1. [...] This post was mentioned on Twitter by Zuissi, Andrew Hutchings. Andrew Hutchings said: The end of implicit cartesian products http://j.mp/fXWvw8 [...]

    ReplyDelete
  2. Did you think about backward compatibility? If one would update to newer version of Drizzle the old applications could stop working...

    ReplyDelete
  3. Hi Petr,

    That was a concern, but this is a feature we already said was implemented and only found out a couple of days ago that it was only half implemented. Essentially it is a bug which has been fixed in our Beta phase.

    The changelog for our release next week will probably contain a warning about this change.

    But if an application is using an implicit cartesian join it very likely needs fixing anyway.

    ReplyDelete
  4. "But if an application is using an implicit cartesian join it very likely needs fixing anyway."
    I understand the reason, but I just wanted to point out thet even if there might be a mistake in the application, the application runs, but now it would not run at all. (Ok, maybe the probability is small.)

    ReplyDelete
  5. Sure, and in that case an error would be generated which the app should handle (whether it be pipe it up to the admin or whatever, that is up to the app/developer).

    It is a good point and something we do try and think about when making changes to the parser and optimizer. In fact recently there have been a few things we have relaxed on for compatibility, such as supporting the braces for INT(10) again (although the braces and their contents are ignored).

    ReplyDelete
  6. [...] Implicit Cartesian Joins no longer work this is to prevent runaway queries. [...]

    ReplyDelete