Query works in design SQL but nowhere else

[TrevJ]

In Forms in Base I am trying to filter a table on a subform with a filter table on the form.

My filter query on the subform is :-

SELECT * FROM “WordsTable”
WHERE (UPPER(“English”) = UPPER(:EnglishFilter) OR :EnglishFilter IS NULL) AND
(UPPER(“Spanish”) = UPPER(:SpanishFilter) OR :SpanishFilter IS NULL) AND
(“Part of Speech” = :SpeechFilter OR :SpeechFilter IS NULL)

This works in SQL view in the query designer but if I go to design view I get as shown in the attachment QueryMess:

This gives the following SQL :-

SELECT * FROM “WordsTable” WHERE ( “Part of Speech” = :SpeechFilter AND UPPER ( “English” ) = UPPER ( :EnglishFilter ) AND UPPER ( “Spanish” ) = UPPER ( :SpanishFilter ) OR “Part of Speech” = :SpeechFilter AND UPPER ( “English” ) = UPPER ( :EnglishFilter ) AND :SpanishFilter IS NULL OR UPPER ( “English” ) = UPPER ( :EnglishFilter ) AND UPPER ( “Spanish” ) = UPPER ( :SpanishFilter ) AND :SpeechFilter IS NULL OR UPPER ( “English” ) = UPPER ( :EnglishFilter ) AND :SpanishFilter IS NULL AND :SpeechFilter IS NULL OR “Part of Speech” = :SpeechFilter AND UPPER ( “Spanish” ) = UPPER ( :SpanishFilter ) AND :EnglishFilter IS NULL OR “Part of Speech” = :SpeechFilter AND :SpanishFilter IS NULL AND :EnglishFilter IS NULL OR UPPER ( “Spanish” ) = UPPER ( :SpanishFilter ) AND :SpeechFilter IS NULL AND :EnglishFilter IS NULL OR :SpanishFilter IS NULL AND :SpeechFilter IS NULL AND :EnglishFilter IS NULL )

This also happens if I save the query in SQL view and then open it again.

If I run the query in Tools>SQL I get the following error :-

1: Assert failed: S0000 Direct execute with param count > 0java.lang.Exception
at org.hsqldb.Trace.getStackTrace(Unknown Source)
at org.hsqldb.Trace.doAssert(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source)
at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source)
in statement [SELECT * FROM “WordsTable” WHERE ( UPPER ( “English” ) = UPPER ( :EnglishFilter ) OR :EnglishFilter IS NULL ) AND ( UPPER ( “Spanish” ) = UPPER ( :SpanishFilter ) OR :SpanishFilter IS NULL ) AND ( “Part of Speech” = :SpeechFilter OR :SpeechFilter IS NULL )]

I am running Linux Mint 19.1 LibreOfficeVersion: 6.0.7.3 with HSQLDB

I thought I had this working once but now I can’t get it to go.

(Edit: activated screenshot -AK)

[Ratslinger]

Hello,

For me saving the query in SQL view and not changing to design view, the SQL remains OK no matter what. It only goes bad when viewing in design mode. Even then, if not saved (and closing does not asked to be saved), the original entry remains. Report as a problem → Bugzilla.

As for Tools->SQL, don’t recall parameters working there.

[TrevJ]

Sorry, I did not see the menu option Edit in SQL view. Just selected the edit option. Will report as problem. Thanks for the patience and prompt help.

[TrevJ]

This was not a bug but a feature. The criterion are parsed Left to right with AND between each column going across and OR when going down to the next row. It will work as I was hoping for one column but not for more.

I have submitted an enhancement request.

[Ratslinger]

@TrevJ,

As your question is written, what you experienced and I duplicated by switching between design & SQL modes, this is a BUG! What you actually reported is a feature request and not what your question stated.

[TrevJ]

Yes, I realised after I wrote the comment. I will submit another bug report as the other one is marked as complete. I will get this right in the end!