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" Current behavior: It sorts as: AA A B Expected behavior: It needs to sort as: A B AA 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: 4.1.1.2 release
Hi Marco, What is VARCHAR2 ? I thought we only had : Text [VARCHAR] Text [VARCHAR_IGNORECASE] MEMO [LONGVARCHAR] 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. Alex
From : http://www.hsqldb.org/doc/guide/ch09.html CHAR and VARCHAR and LONGVARCHAR columns are by default compared and sorted according to POSIX standards. See the SET DATABASE COLLATION[2] 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 ? Alex
Hi Alex, (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
Hi ALex, (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? > > > Alex
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.