Issue 27286 - SQL parse problem with GROUP BY clauses
Summary: SQL parse problem with GROUP BY clauses
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 1.1.1
Hardware: All All
: P3 Trivial (vote)
Target Milestone: OOo 1.1.2
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords:
: 28181 28531 (view as issue list)
Depends on:
Blocks:
 
Reported: 2004-04-01 09:36 UTC by bryancole
Modified: 2006-05-31 14:29 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description bryancole 2004-04-01 09:36:55 UTC
I've just upgraded to v1.1.1 and one of my database forms has stopped working.
The error complains:

[MySQL][ODBC 3.51 Driver][mysqld-3.23.58]Mixing of GROUP columns     
(MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP
BY clause --- Error Code 1140

The SQL clause I'm using is:

SELECT `Orders`.`order_id`,   `Suppliers`.`Full Name`,  
SUM(`OrderInventory`.`cost` * `OrderedItems`.`quantity`) AS `Total Cost`,  
`Suppliers`.`currency`,  `Orders`.`tvl_order_no`,   `Orders`.`date`,  
`Orders`.`placed_by`,   `Orders`.`Status` FROM `Orders`,`OrderedItems` LEFT JOIN
`Suppliers` ON `Orders`.`supplier_id` = `Suppliers`.`ID` LEFT JOIN
`OrderInventory` ON `OrderedItems`.`Item` = `OrderInventory`.`ID`WHERE
(`Orders`.`project` = :budget AND `Orders`.`order_id` =
`OrderedItems`.`order_id`)  GROUP BY `Orders`.`order_id` 

As you can see, there clearly IS a GROUP BY clause.

The query is for a slave form (with the field ':budget'), hence I must set
'Analyse SQL Command' to YES to correctly parse the master/slave fields. The
query works fine if I set 'Analyse SQL Command' to NO and replace the :budget
field with something else, but then I don't have the correct link to the master
form.

Everything worked fine in version 1.1.0
Comment 1 marc.neumann 2004-04-01 12:17:30 UTC
Hi,

I can reproduce this.
a simple query like 

SELECT SUM(field1) , id FROM table1 GROUP BY id

doesn't work anymore.
This regression come in with issue 21125

Bye Marc
Comment 2 ocke.janssen 2004-04-05 10:21:49 UTC
Fixed in cws oj12
Comment 3 ocke.janssen 2004-04-05 13:08:12 UTC
For you :-)
Comment 4 marc.neumann 2004-04-05 13:25:16 UTC
set to fixed
Comment 5 marc.neumann 2004-04-05 13:25:36 UTC
verifiy in cws oj12
Comment 6 Frank Schönheit 2004-04-21 16:35:38 UTC
*** Issue 28181 has been marked as a duplicate of this issue. ***
Comment 7 marc.neumann 2004-04-27 09:23:29 UTC
fixed in OOo 1.1.2 which will be available soon -> close
Feel free to reopen if this issue is not fixed in the final 1.1.2 build
Comment 8 Frank Schönheit 2004-04-30 06:53:08 UTC
*** Issue 28531 has been marked as a duplicate of this issue. ***