Bug Hunting Session
Bug 120713 - Firebird Form-Based Filter search with checkbox values of "1" or "0" fails.
Summary: Firebird Form-Based Filter search with checkbox values of "1" or "0" fails.
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.4.0.0.alpha1+
Hardware: All All
: high major
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:6.2.0 target:6.1.4
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2018-10-19 18:15 UTC by sdritchey
Modified: 2018-10-25 14:40 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
test file with test table and form (11.15 KB, application/vnd.oasis.opendocument.database)
2018-10-19 19:17 UTC, Drew Jensen
Details
Screen with erroneous behavior (2.82 MB, video/ogg)
2018-10-23 16:54 UTC, Drew Jensen
Details
Screen capture of Form-based-filter working (3.12 MB, video/ogg)
2018-10-23 16:56 UTC, Drew Jensen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sdritchey 2018-10-19 18:15:36 UTC
Description:
After migrating an existing ODB database to Firebird with the new migration assistant in LO 6.1.2 on MacOS, when I open my Form to view the database and attempt to perform a search using a Form-Based Filter and a checkbox in the form I cannot complete the search.  If I manually change the search criteria for the checkbox in the pop-up Filter Navigator from "1" to "TRUE" or from "0" to "FALSE" then the search completes with no issue.

Steps to Reproduce:
1. Click on the Form-Based Filter and the pop-up Filter Navigator appears and select a few checkboxes.
2. Click Apply Form-Based Filter.
3. Search fails.

Actual Results:
Search fails.

Expected Results:
Search should complete.


Reproducible: Always


User Profile Reset: No



Additional Info:
Changing the values of the checkbox from "1" or "0" to "TRUE" or "FALSE" manually fixes the problem, however this is tedious in actual use and a permanent fix needs to be implemented.
Comment 1 Drew Jensen 2018-10-19 19:17:46 UTC
Created attachment 145839 [details]
test file with test table and form

Confirmed with Ubuntu 18.04 and LibreOffice 6.1.2

The attached file has a single table with an ID and tst_data field (HSQL Yes/No) migrated for FB.

The form allows the form based filter to be used and if you do the following:

1 - open the attached database
2 - open the single form
3 - select 'Form Based Filter' on the toolbar
4 - Click in the Boolean field (this updated the formbased filter with a zero
5 - click apply filter

It fails and displays zero records.

NOTE the Auto Filter feature is not working properly with Boolean fields under Firebird either, but that would be a separate issue I suppose.
Comment 2 Drew Jensen 2018-10-23 16:54:34 UTC
Created attachment 145944 [details]
Screen with erroneous behavior

Ok, attached is an .ogv file from a screen capture which shows the error situation.

This is using the attached database, with a single form, and Ubuntu 18.04, LibreOffice 6.1.2.1


(next attachment will show the correct behavior)
Comment 3 Drew Jensen 2018-10-23 16:56:10 UTC
Created attachment 145945 [details]
Screen capture of Form-based-filter working

Alright in this screen capture, I use the same form but this time I edit the value inserted when I click on the form, 1, with the string 'True' and apply the filter.

With that change it works as expected.
Comment 4 Xisco Faulí 2018-10-23 17:26:41 UTC
Hi Drew,
Thanks for the screencasts!
So, I can reproduce the problem with the 1 and 0 back to 

Version: 5.4.0.0.alpha1+
Build ID: 9feb7f7039a3b59974cbf266922177e961a52dd1
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group

I don't see why the regression keyword was added...
Comment 5 Alex Thurgood 2018-10-24 07:09:09 UTC
@Xisco : because the checkbox filter setting which sets 1 or 0 worked in the original hsqldb and doesn't when the db is migrated to Firebird ?
Comment 6 Xisco Faulí 2018-10-24 09:32:46 UTC
Oh, so this is a migration problem.
@Drew, would it be possible to have the original hsqldb file before the migration?
Comment 7 Drew Jensen 2018-10-24 12:13:18 UTC
It is not a migration problem, per se.

The 0 and 1 are correct when the database is HSQLdb since HSQLdb 1.8 had no native boolean field (it was an alias for a BIT field of length 1).

Firebird has a real boolean field type, so the 0 and 1 are incorrect here, rather it expects True, False or Unknown.

ps HSQLdb 2.x added an actual boolean type and Base has this same problem when working with an external HSQLdb database. [just now I scanned the old forums and this issue has come up numerous times there]
Comment 8 Lionel Elie Mamane 2018-10-24 12:56:56 UTC
This could be a problem more widespread... Basically all DBMS that have a real boolean type and don't automatically convert between integers and booleans (not automatically converting is the SQL standard, I believe).

I wonder whether the 'advanced setting' "boolean comparison mode" has any influence on this? If we set it to the correct value (probably EQUAL_LITERAL, maybe IS_LITERAL) doesn't the problem disappear?
Comment 9 Drew Jensen 2018-10-24 13:32:04 UTC
(In reply to Lionel Elie Mamane from comment #8)
> This could be a problem more widespread... Basically all DBMS that have a
> real boolean type and don't automatically convert between integers and
> booleans (not automatically converting is the SQL standard, I believe).
> 
> I wonder whether the 'advanced setting' "boolean comparison mode" has any
> influence on this? If we set it to the correct value (probably
> EQUAL_LITERAL, maybe IS_LITERAL) doesn't the problem disappear?

There is a setting for 'boolean_comparison_mode' in the MySQL sdbc, but I believe that is regarding a special mode for full text searches. 

Otherwise I'm browsing all the sdbc driver entries in the advanced options dialog and do not see EQUAL_LITERAL, etc - maybe I'm looking in the wrong place.
Comment 10 Drew Jensen 2018-10-24 13:39:03 UTC
So the other SDBC driver which would have to deal with this is Postgresql as it also has a true boolean (mysql is also an alias either for a TINYINT pre 5.7 or bit[1] post 5.7, MSSQL is also an alias for TINYINT) 

So, wonder what happens with postgresql today?
Comment 11 Drew Jensen 2018-10-24 13:44:54 UTC
and it turns out there is an old issue about boolean comparisons and postgresql
https://bugs.documentfoundation.org/show_bug.cgi?id=88099
with a patch entered to fix it, from back in 2015.
Comment 12 Lionel Elie Mamane 2018-10-24 13:57:36 UTC
(In reply to Drew Jensen from comment #11)
> and it turns out there is an old issue about boolean comparisons and
> postgresql
> https://bugs.documentfoundation.org/show_bug.cgi?id=88099
> with a patch entered to fix it, from back in 2015.

Ah, thanks! So it turns out the BooleanComparisonMode setting was exactly what's needed to to fix the "AutoFilter". However, it does not fix the "form-based filter".

The right fix for that is probably that the form-based filter should take into account the BooleanComparisonMode.
Comment 13 Lionel Elie Mamane 2018-10-24 16:24:55 UTC
Fix in the pipeline at gerrit
https://gerrit.libreoffice.org/62319
https://gerrit.libreoffice.org/62320

However, as usual with driver properties, it will be active on *newly* *created* files. AFAIK, migrated files still need the properties reset to the new ones for firebird; it is in btomi's TODO for that to happen automatically, right?

It will also fix other DBMS (like PostgreSQL) for the "form-based filter" on bools through checkboxes.
Comment 14 Commit Notification 2018-10-25 08:04:09 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9b6906bbf3cdc3fc100339b39755bbbe02594e4f

tdf#120713 correctly handle boolean values in filters

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2018-10-25 08:04:19 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=424454997a2a464d99c0e7125ea6b143716715b0&h=libreoffice-6-1

tdf#120713 correctly handle boolean values in filters

It will be available in 6.1.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.