Issue 105959 - Query designer corrupts valid query
Summary: Query designer corrupts valid query
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: recent-trunk
Hardware: PC Linux, all
: P3 Trivial (vote)
Target Milestone: OOo 3.2
Assignee: marc.neumann
QA Contact: issues@dba
Keywords: regression
Depends on:
Blocks: 99999
  Show dependency tree
Reported: 2009-10-16 12:50 UTC by villeroy
Modified: 2017-05-20 10:28 UTC (History)
2 users (show)

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

DB with queries violated by the designer (58.89 KB, application/vnd.sun.xml.base)
2009-10-16 12:51 UTC, villeroy
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description villeroy 2009-10-16 12:50:22 UTC
I'll attach a database with 2 queries. Open any of them in SQL view, close, then
open in design view, do some null-edit(append space) in any view and store.
Either query gets modified in a way that it can not return the same row set as
the initial version.

Short description copied from the input form:
- This is how I defined "Q":
WHERE ( ( :pX = "X" ) OR ( :pX IS NULL ) ) 
  AND ( ( :pY = "Y" ) OR ( :pY IS NULL ) )

- This is what the designer makes of it when you opened it in design view and
then switch to SQL view:
WHERE("X" = :pX AND "Y" = :pY OR "X" = :pX OR "Y" = :pY)

Saving anything based on this version would break the intention.

- Same problem with the counterpart "QReport" reading the criteria directly from
table "F" where "FID"=0:
SELECT "T".* FROM "T", "F" 
AND(("T"."X" = "F"."FX")OR("F"."FX" IS NULL)) 
AND(("T"."Y" = "F"."FY")OR("F"."FY" IS NULL))

- This is what the designer makes of it when you opened it in design view and
then switch to SQL view:
SELECT "T".* FROM "F", "T" WHERE ( "F"."FID" = 0 AND "T"."X" = "F"."FX" AND
"F"."FX" IS NULL AND "T"."Y" = "F"."FY" OR "F"."FY" IS NULL )
Comment 1 villeroy 2009-10-16 12:51:55 UTC
Created attachment 65401 [details]
DB with queries violated by the designer
Comment 2 villeroy 2009-10-16 13:18:14 UTC
By the way: What is wrong with the V column in my form? It's a numeric field
with 5 decimals and it shows only 0.00000 although the field has random numbers
between 0 and 1.

Oh, and the main form works as intended when the numeric fields are replaced
with formatted fields. I did not know that numeric fields are not nullable.
Comment 3 drewjensen.inbox 2009-10-20 01:11:52 UTC
Confirmed w/ DEV320m_1, Ubuntu

This looks like a duplicate, IIRC, from a recent issue dealing with AND/OR
statements like this...have to find it. Will confirm this to new for the moment,
but not assign to a developers until I can check for that possible duplicate.

Comment 4 Frank Schönheit 2009-10-22 10:28:52 UTC
confirming, assigning, nominating as 3.2 blocker.

For the record: The issue is already fixed in CWS dba33a, though I don't find
the issue for it. However, instead of marking this as duplicate, I think we
should fix it for 3.2 (dba33a is targeted for 3.3 only).
Comment 5 ocke.janssen 2009-10-22 12:49:07 UTC
@fs: No, it isn't fixed in dba33a. I just tested it with the newest instsets.
Comment 6 ocke.janssen 2009-10-23 06:18:15 UTC
Fixed in cws dba32i
Comment 7 ocke.janssen 2009-10-28 07:59:22 UTC
Please verify. Thanks.

The query 
SELECT * FROM "T" WHERE ( ( :pX = "X" ) OR ( :pX IS NULL ) ) AND ( ( :pY = "Y" )
OR ( :pY IS NULL ) )

is now normalized to

WHERE ( "X" = :pX AND "Y" = :pY OR "X" = :pX AND :pY IS NULL OR "Y" = :pY AND

to be displayable in the query design.
Comment 8 marc.neumann 2009-10-30 09:30:31 UTC
Not fixed. 
If I execute the query QReport I get a 2 row result.
Then open the query in design view and move a table container to modify the query.
close the query and save it.
execute the query -> an empty result is displayed
Comment 9 marc.neumann 2009-10-30 09:31:24 UTC
reassign back to the developer
Comment 10 ocke.janssen 2009-10-30 14:56:40 UTC
Fixed in cws dba32i
Comment 11 ocke.janssen 2009-11-02 08:14:58 UTC
Please verify in cws dba32i. Thanks.
Comment 12 marc.neumann 2009-11-02 08:28:40 UTC
verified in CWS dba32i

find more information about this CWS, like when it is available in the master
builds, in EIS, the Environment Information System: