To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=83154 Issue #|83154 Summary|Group By on fields using BETWEEN or IN or ... criteria | are not interpreted well, causing the Group by to dis |sapear on those fields Component|Database access Version|OOo 2.3 Platform|PC URL| OS/Version|Windows XP Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P3 Subcomponent|none Assigned to|dbaneedsconfirm Reported by|chojin
------- Additional comments from [EMAIL PROTECTED] Wed Oct 31 10:04:24 +0000 2007 ------- I'm using OpenOffice 2.3 (nl) Base with MySQL 5.0.24a-community-nt trough JDBC Create a simple query like this, using design view: - Field Date, Function: GROUP BY, Criteria: BETWEEN #11-20-2006# AND #12-30-2006# - Field Weight, Function: SUM Resulting in this SQL-string: SELECT `date`, SUM( `weight` ) FROM `openbase`.`bakken` GROUP BY `date` HAVING ( ( `date` BETWEEN {D '2006-11-20' } AND {D '2006-12-30' } ) ) Executing this query works correctly But when the query is saved, closed and reopend in design mode, the GROUP BY Function of the Date field is dissapeared. The SQL string now looks like this: SELECT `datum`, SUM( `gewicht` ) FROM `openbase`.`bakken` WHERE ( ( `datum` BETWEEN {D '2006-11-20' } AND {D '2006-12-30' } ) ) And executing the query results in an error stating that using Grouped functions like SUM without a Group By clause is illegal. This also happens when switching from Design view to SQL view and back to Design view. So I assume the problem is in the interpreting of the SQL.. This however doesn't happen when when another Criteria is added to the Date field: - Field Date, Function: GROUP BY, Criteria: BETWEEN #11-20-2006# AND #12-30-2006#, or: #01-01-1997# - Field Weight, Function: SUM results in: SELECT `date`, SUM( `weight` ) FROM `openbase`.`bakken` GROUP BY `date` HAVING ( ( `date` BETWEEN {D '2006-11-20' } AND {D '2006-12-30' } ) OR ( `date` = {D '1997-01-01' } ) ) which gets interpreted correctly and the Group By function is kept on the Date field when the query is closed and reopened in design-view or when switching between SQL view and Design view. --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]