[Libreoffice-bugs] [Bug 129186] New: EDITING using a firebird db after adding filtering the form becomes readonly

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=129186

            Bug ID: 129186
           Summary: EDITING using a firebird db after adding filtering the
                    form becomes readonly
           Product: LibreOffice
           Version: 6.2.7.1 release
          Hardware: x86-64 (AMD64)
                OS: Windows (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Base
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: Bugzilla at hans-helge-mueller.de

Description:
1) The following query works fine, form editable:
SELECT "Buecher".*, "Standorte"."Standort", "Buecher"."Position",
"Standorte"."Standortid" FROM "Buecher" LEFT OUTER JOIN "Standorte" ON
"Buecher"."StandortId" = "Standorte"."Standortid" ORDER BY
"Standorte"."Standort" ASC, "Buecher"."Position" ASC

2) I added as with the sample-db conditions to the where-clause and introduced
aliases. The form was no longer editable (but the form and the filter worked):
SELECT "Bue".*, "Sto"."Standort", "Bue"."Position", "Sto"."Standortid" FROM
"Buecher" "Bue" LEFT OUTER JOIN "Standorte" "Sto" ON "Bue"."StandortId" =
"Sto"."Standortid" 
WHERE coalesce("Bue"."StandortId", 0) = (SELECT coalesce("F"."StandortId",
"Bue"."StandortId") FROM "_Filter" "F" WHERE "F"."key" = TRUE)
AND
coalesce("Bue"."Titel", '') like (SELECT '%' || coalesce("F"."Titel", '') ||
'%' FROM "_Filter" "F" WHERE "F"."key" = TRUE)
AND
((SELECT "F"."Autor" FROM "_Filter" "F" WHERE "F"."key" = TRUE) IS NULL OR
 ("Bue"."AutorId1" IN (SELECT "Aut"."AutorId" FROM "_Filter" "F" INNER JOIN
"Autoren" "Aut" ON "Aut"."Autor" like '%' || "F"."Autor" || '%'  WHERE
"F"."key" = TRUE)))
ORDER BY "Sto"."Standort" ASC, "Bue"."Position" ASC

3) I removed the conditions, but leaved the aliases. Form is not editable:
SELECT "Bue".*, "Sto"."Standort", "Bue"."Position", "Sto"."Standortid" FROM
"Buecher" "Bue" LEFT OUTER JOIN "Standorte" "Sto" ON "Bue"."StandortId" =
"Sto"."Standortid" 
ORDER BY "Sto"."Standort" ASC, "Bue"."Position" ASC

4) I believed that the aliases could cause the problem, so I removed them, but
used the condition. Form not editable:
SELECT "Buecher".*, "Standorte"."Standort", "Buecher"."Position",
"Standorte"."Standortid" FROM "Buecher" LEFT OUTER JOIN "Standorte" ON
"Buecher"."StandortId" = "Standorte"."Standortid" 
WHERE coalesce("Buecher"."StandortId", 0) = (SELECT
coalesce("_Filter"."StandortId", "Buecher"."StandortId") FROM "_Filter" WHERE
"_Filter"."key" = TRUE)
AND
coalesce("Buecher"."Titel", '') like (SELECT '%' || coalesce("_Filter"."Titel",
'') || '%' FROM "_Filter" WHERE "_Filter"."key" = TRUE)
AND
((SELECT "_Filter"."Autor" FROM "_Filter" WHERE "_Filter"."key" = TRUE) IS NULL
OR
 ("Buecher"."AutorId1" IN (SELECT "Autoren"."AutorId" FROM "_Filter" INNER JOIN
"Autoren" ON "Autoren"."Autor" like '%' || "_Filter"."Autor" || '%'  WHERE
"_Filter"."key" = TRUE)))
ORDER BY "Standorte"."Standort" ASC, "Buecher"."Position" ASC

5) I removed the "coalesce", since this is different with firebird. (This does
not give the desired filtering, but just to try.) Form not editable:
SELECT "Buecher".*, "Standorte"."Standort", "Buecher"."Position",
"Standorte"."Standortid" FROM "Buecher" LEFT OUTER JOIN "Standorte" ON
"Buecher"."StandortId" = "Standorte"."Standortid" 
WHERE "Buecher"."StandortId" = (SELECT "_Filter"."StandortId" FROM "_Filter"
WHERE "_Filter"."key" = TRUE)
AND
"Buecher"."Titel" like (SELECT '%' || "_Filter"."Titel" || '%' FROM "_Filter"
WHERE "_Filter"."key" = TRUE)
AND
((SELECT "_Filter"."Autor" FROM "_Filter" WHERE "_Filter"."key" = TRUE) IS NULL
OR
 ("Buecher"."AutorId1" IN (SELECT "Autoren"."AutorId" FROM "_Filter" INNER JOIN
"Autoren" ON "Autoren"."Autor" like '%' || "_Filter"."Autor" || '%'  WHERE
"_Filter"."key" = TRUE)))
ORDER BY "Standorte"."Standort" ASC, "Buecher"."Position" ASC





Actual Results:
I used a macro as in the sample-db to actualizize the records after entering
filter values.

The diplayed record is not editable, the combo-boxes are greyed out.

Expected Results:
The values should be editable.


Reproducible: Always


User Profile Reset: No



Additional Info:
Note: using aliases in some places but not in others produces an error message
(missing column) when executing the query. Like
SELECT "Buecher".*, "Standorte"."Standort", "Buecher"."Position",
"Standorte"."Standortid" FROM "Buecher" "Bue" LEFT OUTER JOIN "Standorte" "Sto"
ON "Buecher"."StandortId" = "Standorte"."Standortid" 
ORDER BY "Sto"."Standort" ASC, "Bue"."Position" ASC

I believe this to be still correct sql.

I tried with both the last official build an the last beta version.

Version: 6.4.0.0.beta1 (x64)
Build-ID: 4d7e5b0c40ed843384704eca3ce21981d4e98920
CPU-Threads: 4; BS: Windows 6.1 Service Pack 1 Build 7601; UI-Render: Standard;
VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: CL

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20191204/80871877/attachment.html>