BOOLEAN Filter problem with split HSQLDB 2.3.2

[papijo]

Hi all "split HSQLDB" users!

I am having a problem with filtering BOOLEAN fields in my form. I guess the problem comes from the change of "nature" of the BOOLEAN type between HSQLDB version 1.8 (embedded in OpenOffice) and version 2.3.2 which I am using in my split database setting.

1.- The HSQLDB documentation here says:
The BOOLEAN type conforms to the SQL Standard and represents the values TRUE, FALSE and UNKNOWN. This type of column can be initialised with Java boolean values, or with NULL for the UNKNOWN value.
The three-value logic is sometimes misunderstood. For example, x IN (1, 2, NULL) does not return true if x is NULL.
In previous versions of HyperSQL, BIT was simply an alias for BOOLEAN. In version 2, BIT is a single-bit bit map.

2.- I have a TABLE1 table with ID [INTEGER], NAME [VARCHAR] and REGISTERED [BOOLEAN] fields. In my form, if I use the Standard Filter on that BOOLEAN field, my records are correctly filtered if I enter TRUE or FALSE in the CONDITION = box.

3.- But, if I enter 0 or 1, my form simply freezes and becomes un-usable. If I have opened my form in Edit mode, I can then switch back to Design mode and go to Form properties > Data > Filter which contains e.g. the following formula: ( "TABLE1"."REGISTERED" = '1' ). Then if I switch the Design mode OFF, I get this error message:
"The data content could not be loaded. data exception: invalid character value for cast".

4.- Now, if before switching the Design mode OFF I correct the filter formula by removing the single quotes surrounding the 1 (or 0) value, like this: ( "TABLE1"."REGISTERED" = 1 ) and then switch the Design mode OFF, the filter is correctly applied to my form.

5.- Diagnostic. When I enter a 0 or 1 value in the Standard filter CONDITION = box, OpenOffice automatically adds single quotes, and this results in freezing my form.

6.- Of course I might avoid this annoying freezing problem by avoiding using 0 and 1 but using FALSE or TRUE instead. But since I intend to make my application available to the general public, I would rather that OpenOffice + HSQLDB v2 did not behave in such a buggy way.

7.- Re-reading the HSQLDB documentation here I thought I might use the BIT type instead of the BOOLEAN type in my tables. This Bit (fix) [BIT] type is indeed available in the list of field types when I create a table in my HSQLDB 2.3.2 based installation. It is available, but ... it does not work in OpenOffice! Whenever I try to tick the check-box of a [BIT] field and try to save the record, I get this error message:
"Error inserting the new record / value cannot be converted to target type"

8.- I appreciate that the "responsibility" for the current problem is shared between OpenOffice and HSQLDB. Can anyone suggest a solution? Should this "bug" be reported to the OpenOffice/LibreOffice developers?

EDIT.-
I should add that if I try to filter my form using the Form Navigation bar's "Form-based filters" button, the 0 or 1 values are automatically added, and not surrounded by single quotes, so that the filter works fine.

[MTP]

Have you tried adding a BIT column through Tools->SQL window instead of the GUI? (I don't know anything about 2.3.2, but agree with your logic that trying a BIT column is worthwhile.)

[papijo]

@MTP you misunderstood me. I can add a BIT column using the OpenOffice interface. But I can't save a record with a BIT type field in it!

[Villeroy]

When I turn on the form based filter, the boolean fields are shown as triple state check boxes (null, true, false).

[papijo]

@Villeroy, I do not see this. Could you please attach a screenshot?

Anyway, I find it hard to understand that tri-state logic for BOOLEAN fields. I have set all the BOOLEAN type fields in my tables as Entry required Yes and Default value No and on my form I have set Tri-state No.
I don't think my problem is directly related to this tri-state situation.

[Villeroy]

[papijo]

@Villeroy, I do not understand your screenshot. When using the Form-based filters on a BOOLEAN tri-state column, I maintain that it is only possible to filter by TRUE (1) or FALSE (0). It is not possible to filter the NULL/UNKNOWN state. See attached screenshot.

[papijo]

EDIT.- Actually, it is possible to edit the "Filter for" filter for a BOOLEAN tri-state column. Using the usual method of clicking on the check-box in the form you can only get 0 or 1 states (as illustrated in my previous message). In order to set the filter value to something different, you must right-click the filter, then you can enter "NULL". As seen in (1) on the screenshot. But this still does not work as expected, it does not filter the "NULL/UNKNOWN/NOT SET" fields when you apply the filter. If you go back to the Form-based filters button, you notice that OpenOffice has automatically changed the "NULL" value you entered in the filter into an "EMPTY" value (2). This is getting more and more weird.