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.
Which database type do you use? Embedded HSQLDB, Embedded Firebird, other? Could you provide a file to try to reproduce this?
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.
Created attachment 170592 [details] spreadsheet used to create database
Created attachment 170593 [details] database file associated with attached spreadsheet
[Automated Action] NeedInfo-To-Unconfirmed
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?
Created attachment 170608 [details] screencast
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.
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.
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 …
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.
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.
(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.
(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 …
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?
(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.
(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.
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.
(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
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.
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.
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.