Bug 36546 - Internal HSQLDB 1.8 fails to cascade updates through multiple paths and deletions through some cyclic structures
Summary: Internal HSQLDB 1.8 fails to cascade updates through multiple paths and delet...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.2 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on: 51780
Blocks:
  Show dependency treegraph
 
Reported: 2011-04-24 11:09 UTC by R.D.Nielsen
Modified: 2017-03-07 08:58 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Integrity constraint violation (2.92 KB, text/plain)
2011-06-19 02:39 UTC, ribotb
Details
Stack overflow (813 bytes, text/plain)
2011-06-19 02:40 UTC, ribotb
Details

Note You need to log in before you can comment on or make changes to this bug.
Description R.D.Nielsen 2011-04-24 11:09:42 UTC
When cascading updates are enabled, and for some data structures containing multiple paths from one table to another, Base fails to propagate updates made to the parent table.

Base also fails to propagate deletions through some data structures containing cyclic relationships.

A complete description of the circumstances under which these errors occur, including examples with test DDL and DML, is at http://splinterofthesingularity.blogspot.com/2011/04/converging-and-cyclic-cascades-in.html
Comment 1 ribotb 2011-06-18 04:23:35 UTC
Hi,

Tests with LibO 340 under Windows 7 SP1.

A) Cascade 

I confirm : Cascade an update through multiple paths doesn't work.

1. Cascade an update through multiple paths

result : "Integrity constraint violation - no parent FK_SAMPLESTUDY table: D_SAMPLE"

2. Cascade an update of multiple columns

result : ok

3. Cascade a deletion through multiple paths

delete from d_study where study='StudyZ';
because result of test 1, changed SQL statement to to : delete from d_study where study='StudyA';

result : ok

B) Cyclic Relationships (cycle #1)

1. test update :

result : ok

2. test delete with cascade

result : ok (empty table)

3. Test deletion with set null on deletes

result : ok (PARENT-NODE for NODE_NAME 'NodeB' is '')

C) Cyclic Relationships (cycle #2)

A crash occurs when testing delete with cascade

1. test update :

result : ok

2. test delete with cascade

result : "S1000 General error java.lang.StackOverflowError"

3. Test deletion with set null on deletes

result : ok (PARENT-NODE for NODE_NAME 'NodeB' is '')

Bernard Ribot
Comment 2 ribotb 2011-06-19 02:39:34 UTC
Created attachment 48156 [details]
Integrity constraint violation
Comment 3 ribotb 2011-06-19 02:40:26 UTC
Created attachment 48157 [details]
Stack overflow
Comment 4 ribotb 2011-06-19 02:44:46 UTC
To reproduce quickly the two bugs I added to attachments : SQL for reproduce "Integrity constraint violation" and SQL to reproduce "Stack overflow".

I don't know if the platform must be changed to "all".

Bernard Ribot
Comment 5 Björn Michaelsen 2011-12-23 12:04:59 UTC Comment hidden (obsolete)
Comment 6 ribotb 2012-07-27 09:55:27 UTC
Hi,

I redid the tests included in the attachments of the comment#2 and comment#3 with LibO 3.5.5.3.
The results are identical to those described in comment#1 :

Bernard Ribot
Comment 7 robert 2013-02-12 06:56:34 UTC
All tests were made with the internal HSQLDB. If you would use a connection to PostgreSQL it will work, when I understand the link of the report in the right way.
Is this really a bug of Base or isn't it a bug of HSQLDB 1.8? This database wouldn't be fixed. Actual is version 2.3.0. It could be a hint for the discussion, to which database LO should change in future.
Comment 8 ribotb 2013-02-16 09:17:36 UTC
Hi,

1) Test SQL for reproduce "Integrity constraint violation" made with a MariaDB 5.5 database and HeidiSQl (therefore, not with LO Base) gives same result : "Integrity constraint violation - no parent FK_SAMPLESTUDY table: D_SAMPLE").

It seems that is not the fact of LO Base but something not logical in the example of R.D. Nielsen ?

2) Test SQL to reproduce "Stack overflow" is OK with MariaDB/HeidiSQL; it is also OK with LO 4.0.0.3 and a MySQL/ODBC connection to a MariaDB database;  but crashes with LO 4.0.0.3 (Java 1.7.0_13)  and an internal HSQLDB database.

Bernard Ribot
Comment 9 robert 2013-02-16 16:27:53 UTC
(In reply to comment #8) 
> 1) Test SQL for reproduce "Integrity constraint violation" made with a
> MariaDB 5.5 database and HeidiSQl (therefore, not with LO Base) gives same
> result : "Integrity constraint violation - no parent FK_SAMPLESTUDY table:
> D_SAMPLE").
> 
> It seems that is not the fact of LO Base but something not logical in the
> example of R.D. Nielsen ?
> 
> 2) Test SQL to reproduce "Stack overflow" is OK with MariaDB/HeidiSQL; it is
> also OK with LO 4.0.0.3 and a MySQL/ODBC connection to a MariaDB database; 
> but crashes with LO 4.0.0.3 (Java 1.7.0_13)  and an internal HSQLDB database.
> 
Please read http://splinterofthesingularity.blogspot.com/2011/04/converging-and-cyclic-cascades-in.html (description of this report)
I have tested with Base and PostgreSQL. Both attachments work well. No problem. It is, as described in the link, a problem of the database you use, not a problem of Base as a frontend of the database.
So I ask again: Could we say, that this is a bug of LO and Base? I don't think so.
Comment 10 ribotb 2013-02-16 19:18:28 UTC
(In reply to comment #9)

> Please read
> http://splinterofthesingularity.blogspot.com/2011/04/converging-and-cyclic-
> cascades-in.html (description of this report)
> 
> So I ask again: Could we say, that this is a bug of LO and Base? I don't
> think so.

I have read that, my tests were made with that DDL and DML and my conclusion in comment#8 :
> It seems that is not the fact of LO Base but something not logical in the
> example of R.D. Nielsen ?
Comment 11 bfoman (inactive) 2013-07-20 21:23:44 UTC
Firebird will become internal database and commits start to appear in master so this report is potential WONTFIX. Please see bug 51780 for details.
Comment 12 ribotb 2013-07-21 11:52:52 UTC
Hi,

1) Test SQL for reproduce "Integrity constraint violation" made with a Firebird Server 2.5 database and IBEasy+ 1.5.4 (therefore, not with LO Base) aborts in DDL execution : Firebird does not accept NULL for field "element" in table D_SAMPLE.

2) Test SQL to reproduce "Stack overflow" is also OK (no stack overflow) with Firefird Server 2.5 database and IBEASY+ 1.5.4.

Bernard Ribot
Comment 13 Fred Toussi 2013-07-22 00:45:37 UTC
Both scripts work fine with HSQLDB 2.3.0. You can use this version with non-embedded database files.
Comment 14 R.D.Nielsen 2013-08-05 03:36:28 UTC
Although Firebird does not support the "null" specification for d_sample.element, this is the default behavior, and eliminating the specification allows the table to be created without error.  With only this change to the DDL, Firebird 2.5 successfully completed all of the tests of cascading updates and deletions through converving and cyclic data structures.

http://splinterofthesingularity.blogspot.com/2013/08/converging-and-cyclic-cascades-with.html
Comment 15 ribotb 2013-08-05 06:51:02 UTC
I agree.
Bernard Ribot
Comment 16 Popa Adrian Marius 2014-01-31 09:45:40 UTC
This can be marked as fixed once #51780 is marked as done
Comment 17 Alex Thurgood 2015-01-03 17:38:59 UTC Comment hidden (no-value)
Comment 18 QA Administrators 2016-01-17 20:03:54 UTC
** Please read this message in its entirety before responding **

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 on a currently supported version of LibreOffice (5.0.4 or later)  https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System

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)

http://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: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for your help!

-- The LibreOffice QA Team This NEW Message was generated on: 2016-01-17
Comment 19 ribotb 2016-01-19 08:34:06 UTC
Hi,

I redid the test "Integrity constraint violation" with embedded HSQLDB:
--> Integrity constraint violation - no parent FK_SAMPLESTUDY table: D_SAMPLE

Test and also with srandalone HSQLDB 1.8:
--> java.sql.SQLException: Integrity constraint violation - no parent FK_SAMPLESTUDY table: D_SAMPLE

No problem with embedded Firebird or Firebird Database Server 2.5 (except Firebird does not accept NULL for field "element" in table D_SAMPLE).

Bernard
Comment 20 ribotb 2016-01-19 09:09:18 UTC
I redid the "Stack overflow" test.

With embedded HSQLDB;
-->  S1000 General error java.lang.StackOverflowError on statement "delete from d_node;").
Same error with standalone HSQLDB 1.8:
--> java.sql.SQLException: SIOOO General error java.lang.StackOverfiowError

No problem with embedded Firebird.

Version: 5.1.0.2
Build ID: ecd3574d51754b043f865cf5bafee286d24db7cc
CPU Threads: 2; OS Version: Windows 6.1; UI Render: default; 
Locale : fr-FR (fr_FR)
on Win7/x86

Bernard
Comment 21 robert 2016-01-19 16:18:16 UTC
Don't know if we should spend our time with this bug. It is a bug of the internal HSQLDB. Nobody would ever fix it. With other database (also together with Base as GUI) the bug doesn't appear.

Good to know about bugs of old HSQLDB 1.8 - but that's all.

Robert
Comment 22 ribotb 2016-01-22 19:08:02 UTC
Hi,

I redid the tests "Integrity constraint violation" and  "Stack overflow"
with HSQLDB Server 2.3.3
--> No problem.

It seems there is a problrm with HSQLDB 1.8 (embedded  and standalone).

I have not tested with HSQLDB Server 1.8.

Bernard
Comment 23 ribotb 2016-01-22 20:59:44 UTC
"Intefrity violation" and "Stack overflow" occurs with embedded and standalone HSQLDB and with HSQLDB Server 1.8.1

Bernard
Comment 24 QA Administrators 2017-03-06 14:18:28 UTC
** Please read this message in its entirety before responding **

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 on a currently supported version of LibreOffice 
(5.2.5 or 5.3.0  https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and 
your operating system, and any changes you see in the bug behavior
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave 
a short comment that includes your version of LibreOffice and Operating System

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)

http://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: http://webchat.freenode.net/?channels=libreoffice-qa

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

Warm Regards,
QA Team

MassPing-UntouchedBug-20170306
Comment 25 ribotb 2017-03-07 08:58:10 UTC
Hi,

I redid the tests included in the attachments of the comment#2 and comment#3 with:

Version: 5.3.0.3
Build ID: 7074905676c47b82bbcfbea1aeefc84afe1c50e1
Threads CPU : 2; Version de l'OS :Windows 6.1; UI Render : par défaut; Moteur de mise en page : nouveau; 
Locale : fr-FR (fr_FR); Calc: group

The results are identical to those described in comment#1

Bernard