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.
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.
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)
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.
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...
@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 ?
Oh, so this is a migration problem. @Drew, would it be possible to have the original hsqldb file before the migration?
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]
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?
(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.
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?
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.
(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.
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.
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.
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.