Bug 112722 - Issue on setting up Relationship
Summary: Issue on setting up Relationship
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.6.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Tables
  Show dependency treegraph
 
Reported: 2017-09-28 13:48 UTC by perie_gut
Modified: 2023-10-08 03:16 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
sample (25.25 KB, image/jpeg)
2017-09-28 13:48 UTC, perie_gut
Details
sample of the db (4.81 KB, application/vnd.sun.xml.base)
2017-09-29 09:03 UTC, perie_gut
Details
trying to create a relationship to a table with 2 primary keys (5.77 KB, application/vnd.sun.xml.base)
2018-04-16 12:35 UTC, perie_gut
Details

Note You need to log in before you can comment on or make changes to this bug.
Description perie_gut 2017-09-28 13:48:37 UTC
Created attachment 136586 [details]
sample

On the 5.3.6.1, it is possible to create a relationship between a primary column and an ordinary column, however, in the 5.4.1, it requires that the both column must be set to primary.
Comment 1 Alex Thurgood 2017-09-28 14:28:23 UTC
@perle_gut : please explain in detailed steps what it is your are attempting to do and provide a sample ODB file with instructions that we could try to follow to reproduce.

What kind of DB (embedded hsqldb, firebird, or external db) ?
How are you setting up the relationship (GUI, DDL/SQL, or backend instructions to db engine) ?

Setting to NEEDINFO.
Comment 2 perie_gut 2017-09-29 09:03:42 UTC
Created attachment 136610 [details]
sample of the db

Here is a sample of the HSQLDB. I was able to create the existing relationship using the stable release... If you are to recreate the entire db on a fresh release, then you will encounter the said error.
Comment 3 Robert Großkopf 2017-09-30 07:34:08 UTC
Have tested this one with OpenSUSE 42.2 64bit rpm Linux and 
LO Version: 5.4.2.1
Build-ID: dfa67a98bede79c671438308dc9036d50465d2cb
CPU-Threads: 4; Betriebssystem:Linux 4.4; UI-Render: Standard; VCL: kde4; 
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group

Deleted the relationship between "PropertyType"."PropertyType" and "PropertyDetails"."PropertyType". 
Then saved and closed Base and LO. 
Reopened and set relationship.
When drawing from "PropertyDetails"."PropertyType" to "PropertyType"."PropertyType" the fields were connected well. You could save and reopen and the relationship is there.
Drawing the other way there doesn't appear 'n' and '1'. You are asked for the properties. If you only confirm this nothing happens. After saving and reopening the relationship has been gone.

Same buggy behavior with LO 5.3.6.1, also with all other versions down to 3.6.7.2. Haven't installed any version before, but it seems to be a very old bug. If you change the properties in the dialog for relationship to any action when deleting or updating something the relationship will be created well in every version of LO. Seems to be a bug in the dialog.

Could be this isn't the bug the reporter has detected - but this one I could confirm.
Comment 4 Julien Nabet 2017-10-21 22:16:07 UTC
I submitted this patch for review:
https://gerrit.libreoffice.org/#/c/43680/

It fixes the precise buggy behaviour described by Robert but, as him, I don't know if it corresponds to the bug report.
Comment 5 Julien Nabet 2017-10-22 19:30:58 UTC
I added traces to compare the 2 ways of adding relationship:
1) From non primary->primary (which works)
ORelationTableView::AddConnection
ORelationTableConnectionData::SetCardinality begin
checkPrimaryKey m_vConnLineData.size()=0
checkPrimaryKey m_vConnLineData.size()=0
ORelationTableConnectionData::Update
checkPrimaryKey m_vConnLineData.size()=1
checkPrimaryKey m_vConnLineData.size()=1
ORelationTableConnectionData::SetCardinality begin
checkPrimaryKey m_vConnLineData.size()=1
checkPrimaryKey m_vConnLineData.size()=1
checkPrimaryKey m_vConnLineData.size()=1
ORelationTableConnectionData::SetCardinality Cardinality::ManyOne
ORelationTableView::AddConnection call addConnection


2) From primary->non primary (which doesn't work)
with:
@@ -61,8 +61,7 @@ ORelationTableConnectionData::ORelationTableConnectionData( const TTableWindowDa
 {
     m_aConnName = rConnName;
 
-    if ( !m_aConnName.isEmpty() )
-        SetCardinality();
+    SetCardinality();
 }

ORelationTableView::AddConnection
ORelationTableConnectionData::SetCardinality debut
checkPrimaryKey m_vConnLineData.size()=0
checkPrimaryKey m_vConnLineData.size()=0

So in this last case:
no call to Update -> m_vConnLineData has a size of 0 -> checkPrimaryKey returns always false -> SetCardinality lets m_nCardinality to Cardinality::Undefined
Comment 6 perie_gut 2017-12-11 10:48:04 UTC
Version: 5.4.4.1 (x64)
Build ID: da790616461e15a10c95a80eb8ef8ee7b726c114
CPU threads: 4; OS: Windows 6.19; UI render: default; 
Locale: en-US (en_US); Calc: CL



As of the latest version, I still was not able to saved the relationship for the following:

ContractDetails.BargainPurchaseOptionType  = BargainPurchaseOption.BargainPurchaseOption

*also for the BargainRenewalOption

and I have tried to manually create the relationship (from insert > relationship) and got the following error

SQL Status: S0011
Error code: -170

Primary or unique constraint required on main table: "BargainRenewalOption" in statement [ALTER TABLE "ContractDetails" ADD  FOREIGN KEY ("BargainRenewalOption") REFERENCES "BargainRenewalOption" ("BargainRenewalOption")]
Comment 7 Julien Nabet 2017-12-11 12:51:40 UTC
perie: why did you set this to FIXED ?
Comment 8 Robert Großkopf 2017-12-11 15:41:43 UTC
(In reply to perie_gut from comment #6)
> As of the latest version, I still was not able to saved the relationship for
> the following:
> 
> ContractDetails.BargainPurchaseOptionType  =
> BargainPurchaseOption.BargainPurchaseOption
> 
> *also for the BargainRenewalOption
> 
> and I have tried to manually create the relationship (from insert >
> relationship) and got the following error
> 
> SQL Status: S0011
> Error code: -170
> 
> Primary or unique constraint required on main table: "BargainRenewalOption"
> in statement [ALTER TABLE "ContractDetails" ADD  FOREIGN KEY
> ("BargainRenewalOption") REFERENCES "BargainRenewalOption"
> ("BargainRenewalOption")]

Error appears, because "BargainRenewalOption" isn't unique. There are two primary-keys in the table. So how should the database be able to know which of the different rows of "BargainRenewalOption" will be the right?
Comment 9 perie_gut 2017-12-12 02:27:39 UTC
Julien, because of the stupid comment that I have posted earlier. Nevertheless, the issues identified before seams okay already when I tried with 5.4.3 version. I'll just set the ticket open.
Comment 10 Julien Nabet 2018-01-11 19:22:32 UTC
Let's revert to the previous state before the FIXED put by mistake.
Comment 11 perie_gut 2018-04-16 12:35:44 UTC
Created attachment 141394 [details]
trying to create a relationship to a table with 2 primary keys

Still cannot create a relationship to a table with 2 primary keys


Version: 6.0.3.2 (x64)
Build ID: 8f48d515416608e3a835360314dac7e47fd0b821
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: en-PH (en_PH); Calc: group

log : 8f48d515416608e3a835360314dac7e47fd0b821
Comment 12 Robert Großkopf 2018-04-16 15:38:33 UTC
(In reply to perie_gut from comment #11)
> Created attachment 141394 [details]
> trying to create a relationship to a table with 2 primary keys
> 
> Still cannot create a relationship to a table with 2 primary keys

Seems you are trying to create relationships between tables, which contain both more than one primary-key. I have tried this through direct SQL:

1: Primary or unique constraint required on main table: "journal_lines" in statement [ALTER TABLE "accountcode" ADD FOREIGN KEY ("accountcode") REFERENCES "journal_lines" ("accountcode")]

2: Primary or unique constraint required on main table: "accountcode" in statement [ALTER TABLE "journal_lines" ADD FOREIGN KEY ("accountcode") REFERENCES "accountcode" ("accountcode")]

You need a unique-field if you want to create a relationship. Three of of the tables doesn't have a unique-field. The have more than one primary key. HSQLDB doesn't support this.

The only bug here is: The GUI doesn't recognize this relation isn't be allowed. It paints a connection which doesn't show 1:1 or 1:n but this connection couldn't be saved.

Which tables did you try to connect with which relation?
Comment 13 QA Administrators 2019-10-07 03:03:23 UTC Comment hidden (obsolete)
Comment 14 QA Administrators 2021-10-07 03:53:21 UTC Comment hidden (obsolete)
Comment 15 QA Administrators 2023-10-08 03:16:09 UTC
Dear perie_gut,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug