https://bugs.freedesktop.org/show_bug.cgi?id=83171
Priority: medium
Bug ID: 83171
Assignee: [email protected]
Summary: QUERY EDITOR: Unexpected handling of function-created
NULL values from subqueries, GUI syntax error
Severity: minor
Classification: Unclassified
OS: All
Reporter: [email protected]
Hardware: Other
Status: UNCONFIRMED
Version: unspecified
Component: Database
Product: LibreOffice
Created attachment 105366
--> https://bugs.freedesktop.org/attachment.cgi?id=105366&action=edit
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.
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
