Hi Rob
From the below, I think there's plenty of "bug" going on which should
be fixed.
Just leaving it is not really fixing the issue. We want to get rid of
such quirks, right?
Regards,
Arjen.
On 20/08/2008, at 3:40 PM, Rob Wultsch wrote:
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
--
Arjen Lentz, Founder @ Open Query
Training and Expertise for MySQL in Australia and New Zealand
http://openquery.com.au/training/ (ph. +61-7-3103 0809)
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
