Created attachment 105366 [details] demonstration document 1a. Create query in Base/HSQLDB that includes function that returns NULL / EMPTY value for column. This is Query1 in example, using CASE WHEN. 2a. Create second query that uses record set from first query. This is Query2 in example. 3a. Enter criteria "IS EMPTY" in Query2, for the field where Query1 returned a function-created NULL / EMPTY value. Or, in demonstration document open Query2 for editing. FIRST UNEXPECTED RESULT: Syntax error. Can switch into raw SQL view, draft and, execute query, works. However, when switch back into GUI, continues to return Syntax error (and prevent return to raw SQL editor without deleting criteria). This is Query2 in example. EXPECTED BEHAVIOR: GUI-built query should execute like raw SQL, no syntax error. 1b. Again make Query1 containing function that returns NULL values, this time with MySQL back end. For example, IF() statement that returns NULL value for some. but not all, rows 2b. Create second query to COUNT instances of output from first query. MySQL is supposed to COUNT non-NULL rows. SECOND UNEXPECTED RESULT: COUNT function from MySQL counts ALL rows, including those that are supposed to be returned NULL from the first query's IF() statement. That is, the function-generated NULL value is being treated as something else (non-NULL) in second query, unsure what, exactly, Base is treating the output as. (Because null values only are inferred in Base interface, no special character displayed) EXPECTED BEHAVIOR: Function-created NULL value from first query should be treated as NULL value in second query, not counted in MySQL COUNT function. Encountered LO 4.1.6, LO 4.3.0.4 on OpenSuse13.1 and Windows 7. Workaround is to use 'views' in native MySQL rather than stacked queries in Base. Noted potentially similar Bug 32964 in that title there involved mishandling of NULL values in queries, but this problem exists in releases subsequent to that fix.
Please don't describe more than one bug in one bugreport. This bug should only be a bug for the GUI syntax-error when NULL is expected. You want to have a GUI, which solves all the problems the direct SQL-mode could solve. Could be an ask for an enhancement. Input of IS NULL (IS EMPTY) (step 3a) isn't working in the GUI. This is correct and I could confirm this part of the bug. But I can't split the content you wrote down, so I must leave this bug unconfimred.
Not an ask for enhancement. The initial query contained the function, which was saved ok. The second query was simple and naturally should have been handled by the GUI, but it raised the error and unexpected result. Not seeking a GUI that solves all problems, although the comment does seem to sound a note of agreement with my observation these bugs make the LO query framework useful only for the simplest queries. The two unexpected functionalities point to a single bug in the way that Base handles function-created null values from queries. The first unexpected functionality is trivial, but easily reproduced as noted. The second unexpected functionality is actually quite serious, but because it involves MariaDB backend, can't give you a demonstration document. Hoping the first unexpected functionality whets an appetite to fix the second.
Try the following: Make a view of "Query1". Link "Query2" to the view. It could be edited in the GUI without any problem. The GUI tries to handle the following query, when you link Query2 to Query1: SELECT "key", "text", "dt", "nodata" FROM ( SELECT "key", "text", "dt", CASE WHEN "Table1"."key" = 1 THEN NULL ELSE 'not1' END AS "nodata" FROM "Table1" WHERE CASE WHEN "Table1"."key" = 1 THEN NULL ELSE 'not1' END IS NULL ) AS "Q1" WHERE "nodata" IS NULL I wouldn't call this a simple code. When I try this directly and want to open it with the GUI a dialog appears: "The query is based on an SQL-command which could not be parsed. The query will be opened in SQL-view." The code could be executed well in the query. It could also be executed without direct SQL-mode. When Query2 is linked to a view, the data were prepared by the database like a table. Then the code in Query2 is simple.
Swapping the under-the-hood functionality with views might address this problem and relieve pressure on Base development. I assumed that LO under-the-hood did something like create a view, meaning a second query using the first would remain simple. Had not been using LO for table management, didn't explore LO's 'view' support. Seems redundant most of the time to have both saved queries and views in the same app; I assumed that part of the reason for back ends to support "views" was to provide native saved-query-type functionality that Access and LO provide through the query manager. Seems LO would need one or other, but in most cases, not both. LO GUI query editor doesn't support subqueries anyway (a limitation for views in MySQL at least), and anyone intrepid enough to write complex query in the LO SQL direct editor could save those in the way LO does now, outside of views. Using views instead of queries seems to be a best practice on the user's end, making pseusdo-tables available on the back-end and for the reasons you describe. If you were coding LO from scratch, seems natural that nearly all queries would be 'views'. LO 'view' support already seems mature; swapping in user support would involve largely aesthetic changes. You could classify that as an enhancement request, and it is so natural that I'll bet it already is on a list somewhere.
(In reply to Doug from comment #4) > Using views instead of queries seems to be a best practice on the user's > end, making pseusdo-tables available on the back-end and for the reasons you > describe. If you were coding LO from scratch, seems natural that nearly all > queries would be 'views'. LO 'view' support already seems mature; swapping > in user support would involve largely aesthetic changes. You could classify > that as an enhancement request, and it is so natural that I'll bet it > already is on a list somewhere. So let's set this as rfe, but am not entirely convinced that the "largely aesthetic changes" you refer to are as easy to implement as you think, that would depend on a dev's evaluation, and not my place to say. If the request is already present in the system someone will mark this as duplicate. Altering bug title to reflect this
Adding self to CC if not already on