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: Database (show other bugs)
Version: 4.1.1.2 release
Hardware: Other Linux (All)
: medium normal
Assignee: Not Assigned
QA Contact:
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:
i915 platform:
i915 features:


Attachments

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 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.


Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.