Description: After migration from hsqldb embedded to firebird embedded, As I have some names all in capitals and others only the first letter capital, the form opens with all capitals first (by order ASC ok) and after that all those with only first letter capital (aswell by ASC order ok). Actual Results: ASC order before migration would not make distinction if all letters are capital or only the first one. Expected Results: Normal ASC order without distinction between capitals Reproducible: Always User Profile Reset: No Additional Info:
The order it follows is: AAA -> Aaa -> BBB -> Bbb -> CCC -> Ccc
In a directly created embedded firebird ODB file, I see the following in a simple form created via the wizard : AAA AAa Aaa BBB BBb CCC CCc That seems correct to me. Reading the documentation, it appears that the default sort behaviour for varchar in Firebird is dependent on the collation parameter set when the table is created.
Perhaps the migration code sets a specific collation that doesn't match the one that the user set under hsqldb ? Without a sample hsqldb with which to try a migration, I'm just clutching at straws here. @Tim : can you please provide a sammple hsqldb embedded ODB file that shows the erroneous behaviour after migration so that we can test. Setting NEEDINFO
(In reply to Alex Thurgood from comment #2) > In a directly created embedded firebird ODB file, I see the following in a > simple form created via the wizard : > > AAA > AAa > Aaa > BBB > BBb > CCC > CCc > > > That seems correct to me. Reading the documentation, it appears that the > default sort behaviour for varchar in Firebird is dependent on the collation > parameter set when the table is created. Surely I didn't explain well. This would be the order: ACUÑA GARRIDO ALVAREZ DOMÍNGUEZ AREA GIL Acosta Martínez Alonso Fernández ÁLVAREZ Gómez Águeda Rodríguez
(In reply to Alex Thurgood from comment #3) > Perhaps the migration code sets a specific collation that doesn't match the > one that the user set under hsqldb ? > > Without a sample hsqldb with which to try a migration, I'm just clutching at > straws here. > > @Tim : can you please provide a sammple hsqldb embedded ODB file that shows > the erroneous behaviour after migration so that we can test. > > Setting NEEDINFO I tried to delete records in the tables and leave a few, because I have more than 600, and couldn't do it. This is the Error Message: SQL Status: 23000 Error code: -8 Integrity constraint violation SYS_FK_198 table: Pers_Minist in statement [DELETE FROM "Personas" WHERE "IdPers" = ?] I could delete some, one by one, but would take to much time, and it's all personal data I can't share.
(In reply to Tim from comment #4) > (In reply to Alex Thurgood from comment #2) > > In a directly created embedded firebird ODB file, I see the following in a > > simple form created via the wizard : > > > > AAA > > AAa > > Aaa > > BBB > > BBb > > CCC > > CCc > > > > > > That seems correct to me. Reading the documentation, it appears that the > > default sort behaviour for varchar in Firebird is dependent on the collation > > parameter set when the table is created. > > Surely I didn't explain well. This would be the order: > > ACUÑA GARRIDO > ALVAREZ DOMÍNGUEZ > AREA GIL > Acosta Martínez > Alonso Fernández > ÁLVAREZ Gómez > Águeda Rodríguez I changed all to lower case except first letter, and except other cases, and I get this out of order, because of accents and lower case: Alonso Salgado Alonso Álvarez -> This one should go first Zincke Pan de Colombia -> This one should go before "Zincke..." Álvarez Rodríguez -> This one should go after "Alonso..."
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. (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.)
This definitely looks like the collation set on the database/table/field is not the one that the user would find useful. Looks like a NONE/OCTETS/ASCII/UNICODE collation, while user would like some UNICODE_CI/UNICODE_CI_AI/ES_ES_CI_AI
Is there anything I can do to solve this?
[Automated Action] NeedInfo-To-Unconfirmed
@Tim see the answers in the following to set 'collate': https://ask.libreoffice.org/en/question/191187/sorting-in-base-with-german-umlaute/
Created attachment 151549 [details] Sort test I've found a sample I attach (Sort_Test) and both Query1 and Query2 do it well, and only Query2 has the command COLLATE UNICODE asc to work under SQL command directly! But if I copy the Table and both Queries into one of my Database only Query2 will give the right order ASC. I also tried the other way round, copy my tables and queries to the Sort-Test, and the order only will be right using collate unicode under sql command directly.
@Tim You did not comprehend the information presented in the post and links in all answers. The information in the post does explain what you observe. If continued lack of understanding, please ask under that post.
The real question to be answered, which may or may not be a bug, is that either: - the Firebird migration assistant ignores any previously set collation with the existing hsqldb table and sets a default collation which doesn't allow sorting in the way people expect with hsqldb ; or - if no default collation was set with the existing hsqldb table, then the Firebird default collation sort behaviour is applied. As the default behaviour under Firebird is different to hsqldb, the way to workaround this is to: - either set the collation at the Query level escaping the built-in SQL parser (run in direct SQL mode); - or ALTER the table definition to add the required collation. Currently, with the limited information Tim has provided, there is no way of telling where the problem lies. @Tim: as has been requested many times, we need a representative hsqldb ODB file that displays the behaviour you describe so that we can examine whether it is indeed the migration assistant that is not working correctly, or whether it is just down to a difference in the way Firebird behaves compared to hsqldb in the absence of any specified collation.
See also: http://www.firebirdtest.com/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-collation.html#fblangref25-ddl-tbl-collatcrt for CREATE COLLATION statements. If I have understood this page correctly, for the DDL to succeed, this requires the presence of fbintl.conf, as it is inspected when the statement is run for the presence of the corresponding collation. Unfortunately, I'm not certain that we provide the .conf file in our embedded ODB when it is created, so the DDL might fail silently anyway.
Reading bug 116935, I see that there is already a request for COLLATION options to be offered at the UI level... this implies that a DDL statement to create a collation at table/field level works. So, if it can't be shown that the migration assistant is faulty, e.g. for ignoring a default pre-set collation in hsqldb, or for setting an incongruous collation on migration, then the workaround is indeed to use the COLLATE statement when creating the table (or subsequently issue an ALTER TABLE statement to add it). If it can be shown that the migration assistant incorrectly assigns a new collation or ignores an existing collation, then we can confirm this bug report (with a modified title).
Created attachment 151701 [details] Data base wrong sort Data base sample requested to find out the reason for not sorting in proper order after migration to firebird.
@Tim : thanks ! The SCRIPT file for the embedded hsqldb reads as follows: SET DATABASE COLLATION "Spanish" CREATE SCHEMA PUBLIC AUTHORIZATION DBA CREATE CACHED TABLE "Library"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"BookID" INTEGER,"Title" VARCHAR(100),"Author" VARCHAR(50),"Publisher" VARCHAR(50),"CoverType" VARCHAR(50),"Pages" INTEGER,"PurchasedAt" VARCHAR(50),"DatePurchased" DATE,"Rating" VARCHAR(50),"Notes" LONGVARCHAR,"YearRead" NUMERIC(4),"Genre" INTEGER) CREATE CACHED TABLE "G\u00e9nero"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"G\u00e9nero" VARCHAR(20)) ALTER TABLE "Library" ADD CONSTRAINT SYS_FK_87 FOREIGN KEY("Genre") REFERENCES "G\u00e9nero"("ID") SET TABLE "Library" INDEX'6432 7368 78' SET TABLE "G\u00e9nero" INDEX'19184 13' ALTER TABLE "Library" ALTER COLUMN "ID" RESTART WITH 78 ALTER TABLE "G\u00e9nero" ALTER COLUMN "ID" RESTART WITH 13 CREATE USER SA PASSWORD "" GRANT DBA TO SA SET WRITE_DELAY 60 so at least we can confirm that collation is set in the file.
Thanks! And I've just found out that another bug I've posted seems to have something to do with this one! The other bug is that after I time opening, writing, editing Writer files, if I open one of my data base I get this error when I try to see the tables o ejecute a Query o Form... firebird_sdbc error: *connection shutdown caused by 'isc_service_attach' I close Libreoffie, resort and it will go. Now, what I've found out is that when this happens, if I open a Data base that sorts correctly, because it wasn't imported from hsqldb and was made new with firebird embedded, I don't get the error of connection...!!!
Another observation: New Firebird embedded Base I can go to Edit -> Database -> Advanced Settings... In migrated from hsqldb I go to Edit -> Database and then all options are in gray and can't access Advanced Settings...
(In reply to Tim from comment #21) > Thanks! And I've just found out that another bug I've posted seems to have > something to do with this one! > > The other bug is that after I time opening, writing, editing Writer files, > if I open one of my data base I get this error when I try to see the tables > o ejecute a Query o Form... > > firebird_sdbc error: > *connection shutdown > caused by > 'isc_service_attach' > I close Libreoffie, resort and it will go. > > Now, what I've found out is that when this happens, if I open a Data base > that sorts correctly, because it wasn't imported from hsqldb and was made > new with firebird embedded, I don't get the error of connection...!!! * "resort" I mean RESART Libreoffice!
The order of characters is Firebird-specific. I have changed the title for this. Note: Has nothing to do with migration, it is the default behaviour of a new created database in LO 6.3.0.0.beta1: Create a new database Create a table with ID - INTEGER (primrary key), "Name" - VARCHAR Input some data: 1 Aaa 2 Ba 3 Za 4 ab 5 ca This data will be sorted by a query: SELECT "Name" ORDER BY "Name" ASC It will be shown the same way as sorted above: Capital letters first. SELECT "Name" ORDER BY "Name" COLLATE UNICODE ASC It will only work in direct SQL, so unusable for input data. But it will order "Name" as the order will be in HSQLDB: Aaa ab Ba ca Za I could confirm this buggy behaviour with LO 6.3.0.0.beta1 on OpenSUSE 15, 64bit rpm Linux with a new created Firebird database.
(In reply to Robert Großkopf from comment #25) > This data will be sorted by a query: > SELECT "Name" FROM "Table" ORDER BY "Name" ASC > It will be shown the same way as sorted above: Capital letters first. > SELECT "Name" FROM "Table" ORDER BY "Name" COLLATE UNICODE ASC Wrong queries - corrected.
(In reply to Robert Großkopf from comment #25) > The order of characters is Firebird-specific. I have changed the title for > this. > > Note: Has nothing to do with migration, it is the default behaviour of a new > created database in LO 6.3.0.0.beta1: > > Create a new database > Create a table with ID - INTEGER (primrary key), "Name" - VARCHAR > Input some data: > 1 Aaa > 2 Ba > 3 Za > 4 ab > 5 ca > > This data will be sorted by a query: > SELECT "Name" ORDER BY "Name" ASC > It will be shown the same way as sorted above: Capital letters first. > SELECT "Name" ORDER BY "Name" COLLATE UNICODE ASC > It will only work in direct SQL, so unusable for input data. But it will > order "Name" as the order will be in HSQLDB: > Aaa > ab > Ba > ca > Za > > I could confirm this buggy behaviour with LO 6.3.0.0.beta1 on OpenSUSE 15, > 64bit rpm Linux with a new created Firebird database. Still the same behavior with LO 6.4.1.2 under MACOS with a new created Base File using embeded Firebird.
(In reply to Alex Thurgood from comment #16) > As the default behaviour under Firebird is different to hsqldb, the way to > workaround this is to: > > - either set the collation at the Query level escaping the built-in SQL > parser (run in direct SQL mode); > > - or ALTER the table definition to add the required collation. @Alex Thurgood comment 16-18 Please tell me the exact SQL command to alter the table definition for the whole odb file or one complete table in it to UNICODE ASC collation via Tools/SQL.
Tools/SQL ALTER CHARACTER SET UTF8 SET DEFAULT COLLATION UNICODE changes the collation for new tables to UNICODE, but not for existing tables.
Isn't this one a feature request? There should be executed ALTER CHARACTER SET UTF8 SET DEFAULT COLLATION UNICODE directly after a new Firebird database has been created. Have forgotten this in last project and have to execute the SQL-code, copy and paste the tables and reconnect the relationships. Nothing for a "normal user".