Download it now!
Bug 122408 - linked subform shows empty when slave field is a function call
Summary: linked subform shows empty when slave field is a function call
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.0.0.1 rc
Hardware: All All
: high major
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:7.0.0 target:6.4.5
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Database-Forms
  Show dependency treegraph
 
Reported: 2019-01-01 17:04 UTC by Ulf
Modified: 2020-05-13 08:05 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Form that works (105.68 KB, image/png)
2019-01-01 17:04 UTC, Ulf
Details
Form that dont work (82.24 KB, image/png)
2019-01-01 17:05 UTC, Ulf
Details
SubForm settings (85.20 KB, image/png)
2019-01-01 17:05 UTC, Ulf
Details
Example Database (41.39 KB, application/vnd.sun.xml.base)
2019-01-02 21:50 UTC, Ulf
Details
form (186.18 KB, application/pdf)
2019-02-19 21:40 UTC, ribotb
Details
form crash (48.89 KB, image/jpeg)
2019-02-19 21:41 UTC, ribotb
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ulf 2019-01-01 17:04:31 UTC
Created attachment 147919 [details]
Form that works

I have an existing Database with Forms that show the content in different Subforms.

The data where select by using >Sql queries< in the Subform and to filter them, i use the "verknüpfen" Form and Subform with more then 1 Field.

Until LibO 5.4.7 the Form works fine. After that, it stopt to work.

I put screenshots where you can take a look from what i mean.

Thanks
Comment 1 Ulf 2019-01-01 17:05:14 UTC
Created attachment 147920 [details]
Form that dont work
Comment 2 Ulf 2019-01-01 17:05:49 UTC
Created attachment 147921 [details]
SubForm settings
Comment 3 Robert Großkopf 2019-01-02 16:10:31 UTC
Could it be you are using Firebird? Then see bug 117589 and all the related bugs.
Comment 4 Ulf 2019-01-02 19:16:50 UTC
@Robert
This Basefile was created with the embedded HSQLDB. 

If i set the "verknüpfen von" only to the first Filterparameter <fi_id_tblm_Person> the Subform works.
But if i use only the second parameter <dt_KW_YEAR>, the SubFform contains again no data.

Could be, that the second "Table fields" don't match anymore?

I have to try to get an example Database.
Comment 5 Ulf 2019-01-02 21:50:08 UTC
Created attachment 147957 [details]
Example Database

try the Forms to see the differnz.

Thanks
Comment 6 Robert Großkopf 2019-01-03 08:15:52 UTC
You have tried to connect a DECIMAL to an INTEGER. 
You have set "dt_KW_YEAR" as DECIMAL(4,0). DECIMAL is used for values with decimal places, like "Betrag [€]" DECIMAL(8,2).
If you get YEAR("dt_termindatum") it will be an INTEGER or SMALLINTEGER.

CAST(YEAR( "dt_termindatum" ) AS DECIMAL(4,0)) "LaufJahr"

will solve this problem in the code for the queries.
Don't know why LO 5.* could connect this, but there are some problems with data types discussed (and partly solved) for Firebird.

Have tested it with LO 6.1.4.2 - works only when changing the query. Then tested with LO 5.4.6.2 - works also when connecting data types that do not match.

By the way: I'm using "Turtle Sport" for trainings-data. Works nice with all data my heart rate monitor could export.
Comment 7 Robert Großkopf 2019-01-03 08:51:45 UTC
Have tried it a little bit more. Queries, which connect a DECIMAL and YEAR("Date"), will work, but forms won't. 

The I opened the forms for editing and set "Form Properties" > "Data" > "Analyze SQL command" > 'No' and the form will work.

So there must have been something changed in the process for analyzing an SQL-command. But this shouldn't happen. A connection, which works in a query (GUI and direct SQL) should also work in a form. 

Tested with LO 6.1.4.2, OpenSUSE 15, 64bit rpm Linux.

I will set this bug to "New" and change the title for this bug a little bit.
Comment 8 Robert Großkopf 2019-01-03 08:59:50 UTC
Set the version to LO 6.0.0.1. This is the first installed version here where the bug appears:
Version: 6.0.0.1
Build-ID: d2bec56d7865f05a1003dc88449f2b0fdd85309a
CPU-Threads: 6; BS: Linux 4.12; UI-Render: Standard; VCL: gtk2; 
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group

Doesn't appear with LO 5.4.6.2
Comment 9 Ulf 2019-01-03 12:19:34 UTC
@Robert
You are right:
> CAST(YEAR( "dt_termindatum" ) AS DECIMAL(4,0)) "LaufJahr"
> will solve this problem in the code for the queries.

Thanks
Comment 10 ribotb 2019-02-19 21:37:14 UTC
Hi,
I think I have the same problem.
A form that has always worked (I think since LO 4), now crashes with LO 6.2.
See attachment 1 : form with LO up to 6.0
See attachment 2 :form with LO 6.2

Bernard
Comment 11 ribotb 2019-02-19 21:40:34 UTC
Created attachment 149435 [details]
form
Comment 12 ribotb 2019-02-19 21:41:32 UTC
Created attachment 149436 [details]
form crash
Comment 13 Buovjaga 2020-05-04 17:31:40 UTC
Bibisected with Linux 6.0 repo to https://git.libreoffice.org/core/+/2b1d6f0d3b0b025148c81986ba7f109659d838af%5E!/

tdf#96370 rework filtering to be aware of WHERE vs HAVING clause

Adding Cc: to Lionel Elie Mamane
Comment 14 Lionel Elie Mamane 2020-05-04 20:53:30 UTC
@ribotb your problem might be different. Please test after correction of this bug, and if it is not corrected, file a new bug, with a minimised reproduction example. (put me in CC)
Comment 15 Lionel Elie Mamane 2020-05-04 21:32:11 UTC
The issue was not related to types per se, but that in the non-working case, the slave field is an aggregate, and thus must be put in the HAVING clause, not the WHERE clause.

The patch is waiting for automated tests to be committed:
https://gerrit.libreoffice.org/c/core/+/93448
https://gerrit.libreoffice.org/c/core/+/93378
Comment 16 Commit Notification 2020-05-04 23:08:18 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/97a2c1fc5e376c0c00968f17a0392c6d3a5ed565

tdf#122408 make StatementComposer apply HAVING clause

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 17 Lionel Elie Mamane 2020-05-04 23:10:20 UTC
(In reply to Lionel Elie Mamane from comment #15)
> The issue was not related to types per se, but that in the non-working case,
> the slave field is an aggregate

More precisely, the LibreOffice parser not having a real clue as to what function is an aggregate or not, more or less calls them all aggregates. <sigh>
Comment 18 Commit Notification 2020-05-13 08:05:57 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/bfaa243b0cac1753330982bedb47f272724bfa1c

tdf#122408 make StatementComposer apply HAVING clause

It will be available in 6.4.5.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.