Menu

Whitespaces are ignored even if collation strength is set to 3

Help
2015-04-26
2015-04-27
  • Fabiano Freitas

    Fabiano Freitas - 2015-04-26

    Please, consider the list/values:

    ÁABBCC
    ABCD DFG
    ABCD EFG
    ABCDEFGH
    ABCD VFH
    ABCD WFH
    

    The above list is the result of the following command (using HSQLDB 2.3.2 on Windows 8.1 Pro):

    SELECT * FROM (VALUES('ÁABBCC'), ('ABCD EFG'), ('ABCD DFG'), ('ABCDEFGH'), ('ABCD VFH'), ('ABCD WFH')) ORDER BY 1 COLLATE "Portuguese 3" ASC
    

    I would expect the result to be:

    ÁABBCC
    ABCD DFG
    ABCD EFG
    ABCD VFH
    ABCD WFH
    ABCDEFGH
    

    With whitespaces not being ignored in the comparison because of strength 3 specified.

    So, am I missing anything, or is that a bug?

    Thank you.

     
  • Fred Toussi

    Fred Toussi - 2015-04-26

    HSQLDB uses Java's Collation support. It probably depends on the Java version but I've noticed it ignores spaces with some high strength settings although the docs say it shouldn't.

     
  • Fabiano Freitas

    Fabiano Freitas - 2015-04-27

    Thank you for your quick reply.

    OK, right.

    I can notice it affects any language specific collation, be it English, French, or Portuguese. Also, these parameters, e.g.:

    "English 0", "English 1", "English 2", "English 3", "English 2 1", etc.

    behave exactly the same way.. (case sensitivity is ignored regardless of strength)

    But when using SQL_TEXT or SQL_TEXT_UCC it works as desired (whitespaces are taken into account, etc.), but accented letters won't be compared correctly. That is, in my example, "ÁABBCC" will be put at the end.

    Is that expected? Wouldn't SQL_TEXT (UNICODE char) be equivalent to utf8_general_ci from MySQL?

    Live example: http://sqlfiddle.com/#!9/5e4cd/1

    Thank you.

     
  • Fred Toussi

    Fred Toussi - 2015-04-27

    SQL_TEXT or SQL_TEXT_UCC do not use Java collations. MySQL uses its own collation. We can define collations and add them to HSQLDB. You can develop one for this language and contribute it to the project.

     
  • Fabiano Freitas

    Fabiano Freitas - 2015-04-27

    OK. Thank you for all the clarification. And yes, I may consider the development.

     

Log in to post a comment.