Description: From a quick examination it seems that the COLLATE "UNICODE" statements in an order by clause is suddenly producing an error. It worked fine in 24.7.2 and striking that from the statements removes the SQL error but of course gives crappy sorting results. And it gets even sillier: I have forms using the very queries that are throwing an error when called as a query ... and these forms are working fine showing the desired query results with correct sorting! What is going on here????? Steps to Reproduce: 1. Make a query using COLLATE "UNICODE" in an order by statement. 2. Try to save query and get an error. 3. If you already have such a query and it is used in a form however, it works fine. Actual Results: Query as query is not running at all due to an (imagined?) error. Expected Results: The query should simply run and produce the correctly sorted results. Reproducible: Always User Profile Reset: Yes Additional Info: Version: 24.8.5.2 (X86_64) / LibreOffice Community Build ID: fddf2685c70b461e7832239a0162a77216259f22 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: de-DE Calc: CL threaded
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
I need Info what I can do to make LibreOffice NOT report "errors" it doesn't see itself when calling queries from a form. The person asking for the data is myself. And while I can get to the data by some workaround that is not exactly what I would call "expected baviour" (being a software developer myself). It is time consuming and if I had not made the mistake of throwing away the install file for 24.7.2 I would have reverted to that in an instant.
The only thing I could do is send you the complete database. Everthing else is clearly written here. SELECT "Artist"."Name" || ' - ' || "Herkunft"."Kuerzel" || ' - ' || "Artist"."Laendercounter", "Artist"."ID", "Artist"."Name", "Herkunft"."Kuerzel", "Artist"."Laendercounter", "Artist"."Gruppenkennzeichen", "Artist"."Ordner" FROM "Artist", "Herkunft" WHERE "Artist"."Herkunft" = "Herkunft"."ID" ORDER BY UPPER ( "Artist"."Name" ) COLLATE "UNICODE" ASC, "Herkunft"."Kuerzel" ASC saved as "artist_for_selection" in the previous version 24.7.2 shows "SQL syntax error" when opened for editing or called for running. A form using "artist_for_selection" runs just fin with the correct sorting. SELECT "Artist"."Name" || ' - ' || "Herkunft"."Kuerzel" || ' - ' || "Artist"."Laendercounter", "Artist"."ID", "Artist"."Name", "Herkunft"."Kuerzel", "Artist"."Laendercounter", "Artist"."Gruppenkennzeichen", "Artist"."Ordner" FROM "Artist", "Herkunft" WHERE "Artist"."Herkunft" = "Herkunft"."ID" ORDER BY UPPER ( "Artist"."Name" ) ASC, "Herkunft"."Kuerzel" ASC also works fine and does not throw an sql error but of course has crappy sorting. No idea, what else you need.
Created attachment 199467 [details] The database in question query's exact name is "_Artist-for-Auswahl" (although every other that has an order by with text shows the same behaviour) Form using that without problem is "Song"
The SQL command (In reply to ernstfritsch6 from comment #3) > The only thing I could do is send you the complete database. Everthing else > is clearly written here. > SELECT "Artist"."Name" || ' - ' || "Herkunft"."Kuerzel" || ' - ' || > "Artist"."Laendercounter", "Artist"."ID", "Artist"."Name", > "Herkunft"."Kuerzel", "Artist"."Laendercounter", > "Artist"."Gruppenkennzeichen", "Artist"."Ordner" FROM "Artist", "Herkunft" > WHERE "Artist"."Herkunft" = "Herkunft"."ID" ORDER BY UPPER ( "Artist"."Name" > ) COLLATE "UNICODE" ASC, "Herkunft"."Kuerzel" ASC > This command works for me just using the option "Run SQL command directly" Version: 25.2.0.3 (X86_64) / LibreOffice Community Build ID: e1cf4a87eb02d755bce1a01209907ea5ddc8f069 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: es-ES Calc: CL threaded
Yes, that works here as well. But I cannot see the results in a meaningful way here. (it runs quickly if I don't look at the results of the select but if I do, it takes forever. Longer than I would expect a 9,500 records resultset to take). But starting the query _Artist-for-Auswahl which contains this exact SQL statement reults in an SQL-error that can be removed by removing the COLLATE "UNICODE". By the way (nothing to do with this problem but it has been reported ages ago and I would need that feature) Extras-> SQL still only takes a single command. Adding a ";" to separate several commands results in an error. I knew @filename.sql to simply execute an sql file with several commands from Oracle but I have not fouund anything like that here (if that worked, it wouldn't matter that I can only execute single commands under that menue item)
Works with Version: 24.2.0.3 (X86_64) / LibreOffice Community Build ID: da48488a73ddd66ea24cf16bbc4f7b9c08e9bea1 CPU threads: 16; OS: Windows 10.0 Build 26100; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded But fails with Version: 24.8.5.2 (X86_64) / LibreOffice Community Build ID: fddf2685c70b461e7832239a0162a77216259f22 Version: 25.2.1.2 (X86_64) / LibreOffice Community Build ID: d3abf4aee5fd705e4a92bba33a32f40bc4e56f49 Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 1fc03eaed2899ac041f660f54cb1facb71390ccf CPU threads: 16; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Raster; VCL: win Locale: en-US (es_ES); UI: en-GB Calc: CL threaded
Regression introduced by: commit 9530664d07f400f4d8dcbe54d0e82d69168844de [log] author Julien Nabet <serval2412@yahoo.fr> Wed Feb 07 18:40:38 2024 +0100 committer Julien Nabet <serval2412@yahoo.fr> Sat Feb 10 12:50:46 2024 +0100 tree 78e1390aff829106d9a1d0375cdfd410742a74f1 parent b370357b40b55cece8407eea2dc0014a5aec0d17 [diff] tdf#159588: Query-GUI: LOWER isnt supported in Query-GUI if condition is LIKE Bisected with: linux64-24.8
@ m_a_riosv It worked until 24.7.2 (that was the version I had before upgrading) so whatever changed in 25.x is responsible. @Xisco Faulí You sure you are in the right bug thread? ;-)
Sorry, I meant 24.2.7 instead of 7.2
(In reply to ernstfritsch6 from comment #9) > @Xisco Faulí > You sure you are in the right bug thread? ;-) yes, if the mentioned commit is reverted, then it works...
Sorry, I had just seen that in MY SQL there wasn't a lower or upper. I didn't know that you were talking about the inner workings of this. But I understand now.
Crap, I wish I could edit comments here. I mean I knew I didn't have a LIKE in my statement. There was of course an upper in there.
Revert submitted here: https://gerrit.libreoffice.org/c/core/+/182314
Revert for 25.2 waiting for review here: https://gerrit.libreoffice.org/c/core/+/182315 and for 24.8 branch: https://gerrit.libreoffice.org/c/core/+/182316
if someone could create a minimal reproducer file with the issue, I might be able to create a unittest for it. Unfortunately Base is a complete stranger to me
Created attachment 199523 [details] Minimal test database