You will need a direct connection to MariaDB (or MySQL) for testing this bug. Create a field in a table as field type bit(1). Something like CREATE TABLE `test`.`tbl_Test` ( `ID` int(10) NOT NULL PRIMARY KEY, `Text` varchar(100) DEFAULT NULL, `Confirm` bit(1) DEFAULT NULL ); Add some content and click on "Confirm" for some rows. Execute this query in SQL-Mode: SELECT * FROM `test`.`tbl_Test` Will show the content of `Confirm` without problems. Now go to Tools → SQL. Set "Show output …". Execute the same query. No content appears for column `Confirm`. Same behavior will appear if you start the query by macro. No value could be found. Change the query: SELECT `ID`, `Text`, CASE WHEN `Confirm` THEN 1 ELSE 0 END FROM `test`.`tbl_Test` This query will give the correct 'values' for `Confirm`. Field type bit(1) couldn't be read directly through Tools → SQL or by macro with direct connection to MariaDB. This bug won't appear with JDBC connection to the same database. Tested with LO 7.4.5.1 on OpenSUSE 15.3 64bit rpm Linux.
On pc Debian x86-64 with master sources updated today, I could reproduce this. I noticed this on console: (soffice:20736): Gtk-CRITICAL **: 12:12:39.920: gtk_text_buffer_emit_insert: assertion 'g_utf8_validate (text, len, NULL)' failed I retrieved a bt and here's a part of it: #2 0x00007f29d00d8e95 in gtk_text_buffer_set_text () at /lib/x86_64-linux-gnu/libgtk-3.so.0 #3 0x00007f29d0bf3736 in (anonymous namespace)::GtkInstanceTextView::set_text(rtl::OUString const&) (this=0x55ffb80163d0, rText="1,test1,\000,\n") at vcl/unx/gtk3/gtkinst.cxx:17867 #4 0x00007f29ab33ea01 in dbaui::DirectSQLDialog::addOutputText(std::basic_string_view<char16_t, std::char_traits<char16_t> >) (this=0x55ffb8a298f0, _rMessage=u"1,test1,\000,") at dbaccess/source/ui/dlg/directsql.cxx:350 #5 0x00007f29ab33e89d in dbaui::DirectSQLDialog::display(com::sun::star::uno::Reference<com::sun::star::sdbc::XResultSet> const&) (this=0x55ffb8a298f0, xRS=uno::Reference to (connectivity::mysqlc::OResultSet *) 0x55ffb8a46e50) at dbaccess/source/ui/dlg/directsql.cxx:331 #6 0x00007f29ab33d8e4 in dbaui::DirectSQLDialog::implExecuteStatement(rtl::OUString const&) (this=0x55ffb8a298f0, _rStatement="SELECT * FROM `test`.`tbl_Test`") at dbaccess/source/ui/dlg/directsql.cxx:234 #7 0x00007f29ab33ef69 in dbaui::DirectSQLDialog::executeCurrent() (this=0x55ffb8a298f0) at dbaccess/source/ui/dlg/directsql.cxx:366 #8 0x00007f29ab33f469 in dbaui::DirectSQLDialog::OnExecute(weld::Button&) (this=0x55ffb8a298f0) at dbaccess/source/ui/dlg/directsql.cxx:418
No idea why it doesn't work with MariaDB direct connector and it works with Firebird=>uncc myself.
I can reproduce this also in Windows 11 Version: 7.5.0.2 (X86_64) / LibreOffice Community Build ID: c0dd1bc3f1a385d110b88e26ece634da94921f58 CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win Locale: es-ES (es_ES); UI: es-ES Calc: CL threaded MariaDB 10.10 (x64)
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/16a8943f7dbf338377fe486cc75116b89742c7b0 tdf#153317: direct SQL: try to detect Bit field in Mysql/MariaDB It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/42547b6472cad484bced8aa47ecadd6d21d587ac Related tdf#153317: extra check since we deal with signed integers It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/6d0470ee00cbf9f9d293c92f92a759cdacbebef0 tdf#153317: direct SQL: try to detect Bit field in Mysql/MariaDB It will be available in 7.5.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/a4238b12ff0d5f77b3240a5f22c20ae8c0e2f828 Related tdf#153317: extra check since we deal with signed integers It will be available in 7.5.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Works now in Tools → SQL with Version: 7.6.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: b76a3bdc996f275f9d615b32d6ab89d533a7505c CPU threads: 6; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: en-US Calc: threaded … but doesn't solve the same problem in macro: SUB Test oDatasource = thisDatabaseDocument.CurrentController IF NOT (oDatasource.isConnected()) THEN oDatasource.connect() oConnection = oDatasource.ActiveConnection() stSql = "SELECT `Confirm`, `Text` FROM `test`.`tbl_Test`" oSQL_Statement = oConnection.createStatement() oResult = oSQL_Statement.executeQuery(stSql) WHILE oResult.next inVar = oResult.getInt(1) stText = oResult.getString(2) msgbox inVar & " " & stText WEND END SUB Will show 0 (or False, if it is set to getBoolean) for every row in the table. Should I open a new bug for this?
(In reply to Robert Großkopf from comment #8) > ... > … but doesn't solve the same problem in macro: >... Yes quite expected since I change the specific part which deals with queries in Tools → SQL. > ... > Should I open a new bug for this? I wonder if querying with macro (so programmatically) isn't a case where the user must deal himself with the field type and should know the structure of the database and know that he may need to cast the value retrieved. After all, when you begin to create some macro, you're more a power user than a casual user who just uses the tool. Of course, it's just a point of view and not a strong opinion here. Now, to respond to your question, I must recognize I don't know, perhaps we should reopen this bugtracker and rename it to indicate that "Bit" field of Mysql/MariaDB displays incorrectly in various situations. (eg: I don't know the behavior of a bit field in the condition of a query made by UI or in forms, reports). BTW, trying to be generic would mean we should check the type of each field retrieved (and perhaps also the fields used in condition, in a group by, a count...) for every request and convert it if they're BIT fields? If yes, I don't think I'd be adventurous enough to give it a try since I suppose we'd get many regressions or at least unexpected behaviours. Lionel: I've not forgotten what you wrote to me but when you come back, if you've got just some ideas here, it'll certainly help.