#1008 suggestion
Dan Kubb (dkubb)

Add ONLY_FULL_GROUP_BY to sql_mode in do_mysql

Reported by Dan Kubb (dkubb) | August 14th, 2009 @ 04:01 PM

This is a suggestion to add ONLY_FULL_GROUP_BY to the sql_mode in do_mysql. It prevents common mistakes when writing a SELECT query that has mismatching non-aggregate columns in the GROUP BY part compared to the SELECT part.

Due to some known problems in older versions of MySQL, it was removed from the ANSI option and a warning was added to the docs to explain the cases where it fails. In newer versions of MysQL (5.1+), it looks like the warning was removed.

I add this to the ticket because I wanted to discuss adding this option to sql_mode, at the very least for newer versions of MySQL if we can determine it's no longer an issue.

I also wanted to discuss our convention for adding/removing sql_mode options. In the past we've removed an option because of a known bug in MySQL, which later gets resolved, but the option is not added back. I think we should begin to add comments on which options we've removed, and why (with a link to more info), and then when it gets resolved we conditionally add it back to the sql_mode for later versions of MySQL.

The whole point of using sql_mode is to make MySQL act in a stricter manner, closer to other RDBMS', so that code we write is as portable as possible. MySQL is also extremely permissive in what it will accept, and problems can go unnoticed due to it's lax behavior -- through strict sql_mode definitions we can close the gap and resolve many of these issues.

Comments and changes to this ticket

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) August 14th, 2009 @ 04:01 PM

    • State changed from “unconfirmed” to “suggestion”
  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) May 26th, 2010 @ 02:39 AM

    @Dirkjan: I was wondering if you could comment on this?

    I seem to recall a discussion on it, but I don't think it was captured anywhere. Is enabling ONLY_FULL_GROUP_BY going to cause any problems?

    Well, I know it will cause problems for queries written with invalid GROUP BY statements -- I'm not concerned about those, what I am more concerned about is if there are any known issues with enabling this globally?

    If there are we should capture them here, and close this ticket, otherwise we should try to add this flag before the next DO release if at all possible.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »