Description: I wanted to generate the following query SELECT * FROM "Table1" WHERE ( "FieldA" = :AskA OR "FieldA" IS NULL) AND ("FieldB" = :AskB OR "FieldB" IS NULL ) Steps to Reproduce: 1.Queries>Create Form In Design View 2.Enter query as per attached screenshot 3.Click on Switch Design View On/Off Actual Results: SELECT * FROM "Table1" WHERE ( "FieldA" = :AskA AND "FieldB" = :AskB OR "FieldA" IS NULL AND "FieldB" IS NULL ) Expected Results: SELECT * FROM "Table1" WHERE ( "FieldA" = :AskA OR "FieldA" IS NULL) AND ("FieldB" = :AskB OR "FieldB" IS NULL ) Reproducible: Always User Profile Reset: No Additional Info: It works fine for a single field. The more fields you add the more confused it gets.
Created attachment 162006 [details] Screenshot of the query
Which kind of database do you use? Also you use an old LO version, could you give a try to last stable LO version 6.4.4?
I could confirm it isn't working like you expect. Query-GUI works this way: All you write in the same row will be linked with 'AND'. All you write in different rows will be linked with 'OR'. Create your query like you created the expected query, switch back to the GUI and you see: FieldA FieldB :AskA :AskB :AskA IS EMPTY IS EMPTY :AskB IS EMPTY IS EMPTY You get this query SELECT * FROM "Table1" WHERE "FieldA" = :AskA AND "FieldB" = :AskB OR "FieldA" = :AskA AND "FieldB" IS NULL OR "FieldA" IS NULL AND "FieldB" = :AskB OR "FieldA" IS NULL AND "FieldB" IS NULL This will work and give the same result you wanted. Don't know if you expect too much from the query-GUI. I have only used it at the beginning of creating databases ... We could set this as Enhancement: A possibility to set brackets so you can get more than two Fields working this way in the GUI. The example above fills nearly all fields ... Tested all with OpenSUSE 15.1 64bit rpm Linux
Julien Database is HSQLDB . Version is that released as package in my operating system (Linux Mint 19.1) which is why I use this one. Robert Thanks for the explanation on how it works. Perhaps I will put in an enhancement request.
I'm sorry but the query does not give the result I was expecting. I was trying to generate a query where I could put a value in just one of the fields and get a result set from that. Although the SQL looks like it might work, the way it is interpreted does not give the desired result. My original problem was, I had a table of Spanish words and their English translations and I wanted to be able to select on either the Spanish or the English word or what part of speech it was ie all the verbs or all the nouns etc.
Sorry but I had forgotten. I have changed the version of LibreOffice. It is now 6.4.4.2. This may be the reason for different results.
(In reply to tj_trevor.jones from comment #5) > I'm sorry but the query does not give the result I was expecting. You have to attach a database with more information. Have tested this again for the German Base Handbuch. Query will work as I expect and as the original SQL you reported will work.
A big apology from me. I tried to simplify my problem in order to post it here and in doing so changed it. What I was trying to do was - SELECT "Table1".* FROM "Table1" WHERE ( "Field1A" = :AskA OR :AskA IS NULL) AND ("Field1B" = :AskB OR :AskB IS NULL) What originally confused me was when you switch from design to SQL and then back again the query is presented in a different form. In fact (In release 6.4.4.2) it all works. Sorry to have wasted you time. A very embarrassed Trevor.
*** Bug 134108 has been marked as a duplicate of this bug. ***