Problem description: When sorting a varchar2 column with a record containing a space it sorts wrongly
Steps to reproduce:
1. .... Open Base and create a new HSQLDB file
2. .... Create a table with one column as varchar2
3. .... Insert two records: One with "AA" and another with "A B"
It sorts as:
It needs to sort as:
OBS: I tested using Base connecting with a Oracle database and it ordered correctly. I tested LO 4.3 with Firebird and it sorts correctly too.
Maybe the problem is in the HSQLDB driver?
Code pointers are welcome :)
Operating System: Linux (Other)
Version: 18.104.22.168 release
What is VARCHAR2 ?
I thought we only had :
as variable length text fields in hsqldb 1.8
If the problem is with hsqldb 1.8 itself, then it won't get fixed here.
CHAR and VARCHAR and LONGVARCHAR columns are by default compared and sorted according to POSIX standards. See the SET DATABASE COLLATION section above to modify this behavior. The property sql.compare_in_locale is no longer supported. Instead, you can define a collation to be used for all character comparisons.
What does your SCRIPT file (contained within the ODB file) declare, if anything, about the SET IGNORECASE parameter ?
(In reply to comment #1)
> Hi Marco,
> What is VARCHAR2 ?
Sorry, varchar2 is from Oracle! I wanted to say 'varchar'
> I thought we only had :
> Text [VARCHAR]
> Text [VARCHAR_IGNORECASE]
> MEMO [LONGVARCHAR]
> as variable length text fields in hsqldb 1.8
(In reply to comment #4)
> What does your SCRIPT file (contained within the ODB file) declare, if
> anything, about the SET IGNORECASE parameter ?
I don't know... I just created a new ODB file, and tested. In Oracle and Firebird it works, so it *needs* to work in HSQLDB right?
If you sort a table, the sorting-function of the database is used. Sorting of a table is different, if you set another collation to it.
This isn't a function of Base. Base uses the function of the HSQLDB. Don't know if it is POSIX-Standard, when HSQLDB sorts like this. Have tried it with a leading Space and then " A" - it is sorted to the first position before "AA", "AB" and "A B".
I could confirm this behavior, but I don't think we could do anything with this bug, because it's a bug of HSQLDB 1.8 - and HSQLDB in the actual version is 2.3.
I will set this on "RESOLVED" and "NOTOURBUG".
If you think it's wrong feel free to reopen the bug.