Bug 73399 - VIEWING: HSQLDB don't sort varchar2 properly
Summary: VIEWING: HSQLDB don't sort varchar2 properly
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.1.2 release
Hardware: Other Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2014-01-08 14:53 UTC by Marcos Souza
Modified: 2014-01-09 18:00 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Marcos Souza 2014-01-08 14:53:36 UTC
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
Comment 1 Alex Thurgood 2014-01-08 15:46:19 UTC
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
Comment 2 Alex Thurgood 2014-01-08 15:47:39 UTC
If the problem is with hsqldb 1.8 itself, then it won't get fixed here.

Alex
Comment 3 Alex Thurgood 2014-01-08 15:52:18 UTC
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.
Comment 4 Alex Thurgood 2014-01-08 15:55:31 UTC
What does your SCRIPT file (contained within the ODB file) declare, if anything, about the SET IGNORECASE parameter ?


Alex
Comment 5 Marcos Souza 2014-01-08 21:14:19 UTC
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
Comment 6 Marcos Souza 2014-01-08 21:15:16 UTC
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
Comment 7 Robert Großkopf 2014-01-09 18:00:28 UTC
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.