On Tue, Aug 19, 2008 at 9:52 PM, Arjen Lentz <[EMAIL PROTECTED]> wrote:
> Hi Rob, Brian > > On 20/08/2008, at 2:38 PM, Rob Wultsch wrote: > >> On Tue, Aug 19, 2008 at 9:25 PM, Brian Aker <[EMAIL PROTECTED]> wrote: >> I am cleaning out the last of the modes. >> >> What is the skinny on ONLY_FULL_GROUP_BY? This is a compatibility question >> vs the standard. >> >> Cheers, >> -Brian >> >> For whatever it's worth I think it would be missed if ONLY_FULL_GROUP_BY >> was permanently set on or not configurable. Many MySQL users are like a >> fish out of water when they start getting errors from GROUP BY queries that >> other MySQL environments do not find objectionable. Some very smart people >> find the setting too restrictive: >> >> http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html >> > > > Let's simplify. > The MySQL bug is described here: http://bugs.mysql.com/bug.php?id=8510 > Essentially, the ONLY_FULL_GROUP_BY setting also rejects constructs that it > should allow: > > create table foo (a int, b int); > insert into foo values (1, 2), (1, 3), (null, null); > select sum(a), count(*) from foo group by a; -- This one works > select round(sum(a)), count(*) from foo group by a; -- This fails > select ifnull(a, 'xyz') from foo group by a; -- This fails > > I would be in favour of fixing this actual problem and then forcing > only_full_group_by (getting rid of sql_mode). > Regards, > Arjen. > -- > I say that is not the only problem: Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) mysql> create table foo (a int, b int, PRIMARY KEY(a)); Query OK, 0 rows affected (0.22 sec) mysql> insert into foo values (1, 2), (2, 4); Query OK, 2 rows affected (0.14 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> create table bar (a int, c int); Query OK, 0 rows affected (0.33 sec) mysql> insert into bar values (1, 45), (2, 86),(2, 123) ; Query OK, 3 rows affected (0.16 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT a,b, sum(c) -> FROM foo -> INNER JOIN bar USING(a) -> GROUP BY a; ERROR 1055 (42000): 'test.foo.b' isn't in GROUP BY mysql> set sql_mode =''; Query OK, 0 rows affected (0.00 sec) mysql> SELECT a,b, sum(c) -> FROM foo -> INNER JOIN bar USING(a) -> GROUP BY a; +---+------+--------+ | a | b | sum(c) | +---+------+--------+ | 1 | 2 | 45 | | 2 | 4 | 209 | +---+------+--------+ 2 rows in set (0.11 sec) I feel like socking myself repeatedly for it, but I think that if ability to configure the mode is going to be ditched, then I think that the setting should be left off. Those that understand what is going on will not lose anything, and those that don't will have a lower barrier to entry. Those that have a significant amount of existing code that assumes that ONLY_FULL_GROUP_BY is not in the sql_mode will have an easier transition. I think it can be useful be able to do what I showed an example of above. I think users that don't know better (and some that do) will find ways to abuse RDMBS. Just my 2c. -- Rob Wultsch
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
