Home » Authentication » LiveUser » Bug #345

[2003-12-03 20:40 UTC] anon at anon dot com

Description:
------------
According to the mysql manual, mysql supports an extended GROUP BY clause that is not a SQL-99 standard....

6.3.7.3 GROUP BY with Hidden Fields

MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions that don't appear in the GROUP BY part. This stands for any possible value for this group. You can use this to get better performance by avoiding sorting and grouping on unnecessary items. For example, you don't need to group on customer.name in the following query:

mysql> SELECT order.custid,customer.name,MAX(payments)
-> FROM order,customer
-> WHERE order.custid = customer.custid
-> GROUP BY order.custid;

In standard SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the name is redundant if you don't run in ANSI mode.

Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results.

Liveuser has queries making use of "GROUP BY with Hidden Fields" and should be corrected to support the standard.

The 2 queries that use a GROUP BY clause are located in the getRights() method in Admin/Perm/DB_Common.php and Admin/Perm/MDB_Common.php.

[2003-12-03 21:51 UTC] anon at anon dot com

As a side note...

It appears that Oracle doesn't support the use of aliases in the GROUP BY clause. It might be worth it to just drop the use of aliases in those methods, since all they are really good for is for shortening the length of the query.

[2003-12-05 19:35 UTC] kipaten21 at hotmail dot com

Looks good. I just had to change the "comment" field to "comments" because "comment" is a reserved word in Oracle.