Bug 122408 - Form couldn't connect to subform (Analyze SQL 'yes') with fields of different data types, which could be connected in queries (GUI and direct SQL).
Summary: Form couldn't connect to subform (Analyze SQL 'yes') with fields of different...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) rc
Hardware: All All
: high major
Assignee: Not Assigned
Keywords: bibisectRequest, regression
Depends on:
Blocks: Database-Forms
  Show dependency treegraph
Reported: 2019-01-01 17:04 UTC by Ulf
Modified: 2019-03-11 11:07 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

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

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.

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
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.

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 - works only when changing the query. Then tested with LO - 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, 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 This is the first installed version here where the bug appears:
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
Comment 9 Ulf 2019-01-03 12:19:34 UTC
You are right:
> CAST(YEAR( "dt_termindatum" ) AS DECIMAL(4,0)) "LaufJahr"
> will solve this problem in the code for the queries.

Comment 10 ribotb 2019-02-19 21:37:14 UTC
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

Comment 11 ribotb 2019-02-19 21:40:34 UTC
Created attachment 149435 [details]
Comment 12 ribotb 2019-02-19 21:41:32 UTC
Created attachment 149436 [details]
form crash