Issue 98115 - "Edit" is a different SQL from "Edit in SQL View"
Summary: "Edit" is a different SQL from "Edit in SQL View"
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 3.0
Hardware: All Windows XP
: P2 Trivial (vote)
Target Milestone: OOo 3.1
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-01-15 14:27 UTC by kamataki
Modified: 2009-03-27 09:10 UTC (History)
2 users (show)

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

Attachments
sample odb file (54.04 KB, application/vnd.sun.xml.base)
2009-01-15 14:28 UTC, kamataki 		no flags Details
"Edit in SQL View" image (25.98 KB, image/png)
2009-01-15 14:28 UTC, kamataki 		no flags Details
"Edit" image (35.65 KB, image/png)
2009-01-15 14:29 UTC, kamataki 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description kamataki 2009-01-15 14:27:11 UTC 
Please open the sample odb file.

Reproduce:
1. 
- Select query1
- Right Click
- Select "Edit in SQL View"
Look at "Edit in SQL View" image file.

2. 
- Select query1
- Right Click
- Select "Edit"
Look at "Edit" image file.

Two results are different. I think SQL of "Edit" not to change.

Reference:
http://oooug.jp/faq/index.php?faq/4/1383
Comment 1 kamataki 2009-01-15 14:28:02 UTC 
作æˆã•ã‚ŒãŸæ·»ä»˜ (id=59404)
sample odb file
Comment 2 kamataki 2009-01-15 14:28:54 UTC 
作æˆã•ã‚ŒãŸæ·»ä»˜ (id=59405)
"Edit in SQL View" image
Comment 3 kamataki 2009-01-15 14:29:26 UTC 
作æˆã•ã‚ŒãŸæ·»ä»˜ (id=59406)
"Edit" image
Comment 4 drewjensen.inbox 2009-01-15 23:19:00 UTC 
Confirmed using OOO300_m15 (3.0.1 RC2), DEV300_m37 and OOo 2.4.1 / Ubuntu

Results are the same for all 3 releases

Open the bug doc
Execute the query "query1" by double clicking
- the result set returned is NOT correct

Open query1 for edit then execute it
- the result set is correct

With the query still open turn the Designer view off and execute
- Result set returned is correct

Close the query
Open the query "Edit in SQL view" and execute it
- the result set returned is NOT correct
Comment 5 drewjensen.inbox 2009-01-15 23:19:39 UTC 
assign to developer
Comment 6 drewjensen.inbox 2009-01-15 23:29:11 UTC 
This seems to have to do with the Colmn names returned in the result set.

If you open the query and add anlias for the two columns using Averge and Count
funcitons the problems disappear in all cases. Running the query directly or
opening in SQL view return the correct SQL statement and result set.
Comment 7 Frank Schönheit 2009-01-17 20:52:52 UTC 
returning incorrect query results is a P2, and should be fixed for the next
release, if possible.

fs->atjensen: please don't be that shy in assigning targets - if you don't
assign one, the issue has a good chance to be overlooked, if you do, the worst
thing which can happen is that it will be corrected :)
Comment 8 ocke.janssen 2009-01-19 06:35:26 UTC 
Open the query in edit mode results in:

SELECT "Code", AVG( "Price" ), COUNT( "Price" ) FROM "table1" AS "table1" GROUP
BY "Code" HAVING ( ( COUNT( "Price" ) >= 4 ) ) ORDER BY "Code" ASC

and switching from design in edit mode results in

SELECT "Code", AVG( "Price" ), COUNT( "Price" ) FROM "table1" AS "table1" GROUP
BY "Code" HAVING ( ( AVG( "Price" ) >= 4 ) ) ORDER BY "Code" ASC

The difference is in the having clause. COUNT or AVG
Comment 9 ocke.janssen 2009-01-19 06:56:35 UTC 
Fixed in cws dba31h
Comment 10 ocke.janssen 2009-01-26 07:08:43 UTC 
Please verify. Thanks.
Comment 11 marc.neumann 2009-01-28 10:43:17 UTC 
verified in CWS dba31h

find more information about this CWS, like when it is available in the master
builds, in EIS, the Environment Information System:
http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fdba31h
Comment 12 amy2008 2009-03-27 09:10:28 UTC 
Verified in DEV310m7 on WinXP.
Now, there is no difference in having clause. Well done.
Closing
Li Meiying