Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 0.16.14

Bug #345 SQL compatibility suggestions [2]
Submitted: 2003-12-03 20:40 UTC
From: anon at anon dot com Assigned: arnaud
Status: Closed Package: LiveUser
PHP Version: 4.3.3 OS: ANY
Roadmaps: (Not assigned)    
Subscription  


 [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.

Comments

 [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-04 20:32 UTC] arnaud
Can you try latest cvs version of the two files ? I added all the fields to the GROUP BY clause.
 [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.
 [2003-12-05 20:18 UTC] arnaud
This bug has been fixed in CVS. In case this was a documentation problem, the fix will show up at the end of next Sunday (CET) on pear.php.net. In case this was a pear.php.net website problem, the change will show up on the website in short time. Thank you for the report, and for helping us make PEAR better. thanks. I made the comment field another bug. It is better to have one issue per bug report, relating the other bug id if needed.