Bug 141115 - Crash in: connectivity::OSQLParseTreeIterator::getOrderTree
Summary: Crash in: connectivity::OSQLParseTreeIterator::getOrderTree
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.1.1.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:7.2.0 target:7.1.3
Keywords: haveBacktrace
Depends on:
Blocks:
 
Reported: 2021-03-19 20:16 UTC by mike
Modified: 2021-03-30 09:32 UTC (History)
3 users (show)

See Also:
Crash report or crash signature: ["connectivity::OSQLParseTreeIterator::getOrderTree"]
Regression By:


Attachments
spreadsheet used to create database (1.33 MB, application/vnd.oasis.opendocument.spreadsheet)
2021-03-20 20:42 UTC, mike
Details
database file associated with attached spreadsheet (1.78 KB, application/vnd.oasis.opendocument.database)
2021-03-20 20:43 UTC, mike
Details
screencast (4.70 MB, video/mp4)
2021-03-21 15:28 UTC, mike
Details
bt with debug symbols (16.15 KB, text/plain)
2021-03-21 17:54 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mike 2021-03-19 20:16:27 UTC
This bug was filed from the crash reporting server and is br-0920eb7b-ebda-4fc5-8e77-79486e2e85d6.
=========================================
Repeatable.
Create new Query (SQL View). "Run SQL command directly" is set. Query is:
  select * from table1 union select * from table2
Run Query (F5) immediately causes crash.
Comment 1 Julien Nabet 2021-03-20 11:58:10 UTC
Which database type do you use? Embedded HSQLDB, Embedded Firebird, other?

Could you provide a file to try to reproduce this?
Comment 2 mike 2021-03-20 13:09:21 UTC
Database was created via Connect to Existing spreadsheet. The spreadsheet is .ods with 10+ sheets and several thousand rows of financial data. It will take me some time to de-personalize it, so that it can be shared here.
Comment 3 mike 2021-03-20 20:42:36 UTC
Created attachment 170592 [details]
spreadsheet used to create database
Comment 4 mike 2021-03-20 20:43:28 UTC
Created attachment 170593 [details]
database file associated with attached spreadsheet
Comment 5 QA Administrators 2021-03-21 04:18:55 UTC Comment hidden (obsolete)
Comment 6 Julien Nabet 2021-03-21 07:35:35 UTC
On pc Debian x86-64 with master sources updated today, I don't reproduce this.

If I take the steps:
Create new Query (SQL View)
=> OK

"Run SQL command directly" is set
=> Not for me

select * from table1 union select * from table2
Run Query (F5)
=> error:
"
The data content could not be loaded. /home/julien/lo/libreoffice/connectivity/source/commontools/dbtools.cxx:749
SQL Status: HY000

The query can not be executed. It contains no valid table. /home/julien/lo/libreoffice/connectivity/source/commontools/dbexception.cxx:400

The SQL command leading to this error is:

select * from table1 union select * from table2
"

Indeed there's no "table1" or "table2".

Did I miss something?
Would it be possible you attach a screencast?
Comment 7 mike 2021-03-21 15:28:03 UTC
Created attachment 170608 [details]
screencast
Comment 8 Julien Nabet 2021-03-21 17:54:26 UTC
Created attachment 170609 [details]
bt with debug symbols

Thank you for your feedback.

On pc Debian x86-64 with master sources updated today, I could reproduce this.
Comment 9 Julien Nabet 2021-03-21 19:48:44 UTC
If I don't click "Run SQL directly", I don't a crash but got an error:
"SQL Status: HY000
Error code: 1000

The given command is not a SELECT statement.
Only queries are allowed. /home/julien/lo/libreoffice/dbaccess/source/core/api/SingleSelectQueryComposer.cxx:134"
SQL Status: HY000
Error code: 1000

SELECT * FROM "AAdvantage" UNION SELECT * FROM "Fidelity" /home/julien/lo/libreoffice/dbaccess/source/core/api/SingleSelectQueryComposer.cxx:133

Now I got no idea how to fix this.
=> uncc myself since I can't help.
Comment 10 Robert Großkopf 2021-03-22 10:54:45 UTC
Tested this one with LO 7.1.1.2 on OpenSUSE 15.2 rpm Linux.
The crash wouldn't appear every time. 
It will appear also in older versions of LO, for Example LO 7.0.4.2 and also LO 6.4.7.2.

It seems to be a special bug together with spreadsheets. Never saw such a buggy behavior in HSQLDB. First I wonder "UNION" would work together with Spreadsheets, because in databases it will only work with fields, which contain always the same data type. But in this query I saw parts of the field-description on last row …
Comment 11 mike 2021-03-22 20:32:14 UTC
Spreadsheets can be union'd in MS/Excel provided the same number of fields are selected.
I have never tried it with LO before but, clearly, a crash is the wrong response.
Comment 12 Alex Thurgood 2021-03-26 09:02:59 UTC
My understanding was that multiple datasource selects, such as UNION, were not possible from spreadsheets used as datasources.

In essence, I had understood that each tab in a spreadsheet is seen by LO's spreadsheet db connector driver as a separate datasource, and that the functionality to query over multiple read-only datasources doesn't exist.
Comment 13 Alex Thurgood 2021-03-26 09:04:10 UTC
(In reply to mike from comment #11)
> Spreadsheets can be union'd in MS/Excel provided the same number of fields
> are selected.
> I have never tried it with LO before but, clearly, a crash is the wrong
> response.

Asbolutely, there should at least be some kind of error message instead if the functionality simply isn't there.
Comment 14 Robert Großkopf 2021-03-26 14:07:19 UTC
(In reply to Alex Thurgood from comment #13)
> (In reply to mike from comment #11)
> > Spreadsheets can be union'd in MS/Excel provided the same number of fields
> > are selected.
> > I have never tried it with LO before but, clearly, a crash is the wrong
> > response.
> 
> Asbolutely, there should at least be some kind of error message instead if
> the functionality simply isn't there.

But: The functionality is implemented. Sometimes it works without problems here. I have tested it with the attached example database and could execute the query in every LO version. It produces crashes very often together with LO 7.1. 

An error message indeed must be better than producing crashes …
Comment 15 Julien Nabet 2021-03-27 09:15:32 UTC
band-aid fix:
https://gerrit.libreoffice.org/c/core/+/113189

No crash, no error message but the result seems wrong, see comments in gerrit, perhaps it should be another bug?
Comment 16 Lionel Elie Mamane 2021-03-27 09:22:22 UTC
(In reply to Robert Großkopf from comment #14)
> (In reply to Alex Thurgood from comment #13)
>> (In reply to mike from comment #11)

>>> Spreadsheets can be union'd in MS/Excel provided the same number of fields
>>> are selected.

> But: The functionality is implemented. Sometimes it works without problems
> here. I have tested it with the attached example database and could execute
> the query in every LO version.

Can you please show an example where the UNION of file/spreadsheet data works? Reading the code, it seems to blindly assume a simple select, and I don't see how it can ever work. Thanks.
Comment 17 Robert Großkopf 2021-03-27 10:10:05 UTC
(In reply to Lionel Elie Mamane from comment #16)
> (In reply to Robert Großkopf from comment #14)
> > (In reply to Alex Thurgood from comment #13)
> >> (In reply to mike from comment #11)
> 
> >>> Spreadsheets can be union'd in MS/Excel provided the same number of fields
> >>> are selected.
> 
> > But: The functionality is implemented. Sometimes it works without problems
> > here. I have tested it with the attached example database and could execute
> > the query in every LO version.
> 
> Can you please show an example where the UNION of file/spreadsheet data
> works? Reading the code, it seems to blindly assume a simple select, and I
> don't see how it can ever work. Thanks.

I have taken the example of this bug description. Testing it again it seems to work some times.
SELECT * FROM "AAdvantage" UNION SELECT * FROM "Fidelity"

Now I have had a look at the result. It only shows the content of the first table "AAdvantage" (3780 rows) but not of the second table "Fidelity" (615 rows).

UNION isn't executed, when the query doesn't crash. I wonder there is a query, which seems to be executed but doesn't show the right content …

You are right - UNION isn't executed there.
Comment 18 Commit Notification 2021-03-27 10:50:29 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

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

tdf#141115: correctly find the ORDER BY clause of a UNION

It will be available in 7.2.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 19 Lionel Elie Mamane 2021-03-27 14:49:25 UTC
(In reply to Robert Großkopf from comment #17)

> UNION isn't executed, when the query doesn't crash. I wonder there is a
> query, which seems to be executed but doesn't show the right content …

> You are right - UNION isn't executed there.

OK, thanks for the confirmation.

So, I put in place that LibreOffice refuses to execute an UNION query and gives an error message instead. I think that's better than silently returning wrong data.

See https://gerrit.libreoffice.org/113207
Comment 20 Commit Notification 2021-03-27 16:34:33 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

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

tdf#141115 semi-userfriendly message on UNION query on file driver

It will be available in 7.2.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 21 Commit Notification 2021-03-30 09:29:49 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

https://git.libreoffice.org/core/commit/3e0714839d3c8cecbe02dea80b372364f4712373

tdf#141115 semi-userfriendly message on UNION query on file driver

It will be available in 7.1.3.

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 22 Commit Notification 2021-03-30 09:32:04 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

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

tdf#141115: correctly find the ORDER BY clause of a UNION

It will be available in 7.1.3.

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.