Created attachment 140407 [details] screenshot showing issue v5.4 and before doesn't do this. It doesn't always happen. Will add more details as I can get them. GNU/Linux Debian 9.3 (Also, sorry, but I accidentally hit return after entering the subject line and it submitted the nearly blank bug report w/o these details. First time that's probably ever happened here.)
It seems to do this only when connected to MariadDB. So far I can't get this behavior in HSQLDB 1.8. (It's hard to tell for 100% sure, but time will tell.) Also if the field is formatted as currency, (US$), then it inserts a $0.00 in the text box, rather than just leaving it blank (null) as it should do. It seems to do this only on nullable fields (those that allow a null value).
@Howard : please indicate how you are connecting to the MariaDB backend : - native driver ; - myODBC ; - myJDBC ; - some other JDBC driver and the corresponding versions of those drivers. Thanks. Setting NEEDINFO pending requested information. Please set back to UNCONFIRMED once you have provided this information.
-------------------------------------------------------------- 1) It also does this in LO version 5.4.4.2 -------------------------------------------------------------- 2) This bug is in a Table control when you insert a NEW record -------------------------------------------------------------- 3) MySQL fields defined with: Allow NULL = true and Default = NULL -------------------------------------------------------------- 4) Database connection used: Menu to: Tools | Options: LibreOffice > Advanced In the 'Options - LibreOffice - Advanced' dialog: X Use a Java runtime environment: Oracle Corporation v 1.8.0_171 Then click 'Class Path...' In the 'Class Path' dialog: Assigned folders and archives: mariadb-java-client-1.5.7.jar Menu to: Edit | Database | Properties... | Additional Settings tab: Connection Settings Host name: localhost Port number: 3306 MySQL JDBC driver class: org.mariadb.jdbc.Driver Data Conversion Character set: System Menu to: Edit | Database | Connection type... Database type: MySQL (JDBC) Menu to: Edit | Database | Advanced Settings... Special settings: x Use keyword AS before table alias names x Ignore the privileges from the database driver _ Display version columns (when available) _ End text lines with CR+LF x Ignore currency field information Comparison of Boolean values: Default -------------------------------------------------------------------------------------- 5) It only happens if I don't enter a value in a field: i) in a VARCHAR field it shows 'NULL' (bug) (rather than quietly showing nothing which is what I would expect) the SQL record gets a (NULL) as verified by HeidiSQL (as expected) ii) in a DATETIME field it shows '1/1/13' (bug) (rather than quietly showing nothing which is what I would expect) the SQL record gets a (NULL) as verified by HeidiSQL (as expected) iii) in a DECIMAL field it shows '$0.00' (when formatted to USD) (bug) (rather than quietly showing nothing which is what I would expect) the SQL record gets a (NULL) as verified by HeidiSQL (as expected) iv) in a INTEGER field it shows nothing (when looked up with a listbox) (ok) the SQL record gets a (NULL) as verified by HeidiSQL (as expected) v) in a TINYINT fields it shows nothing (as expected) (ok) the SQL record gets a 0 as verified by HeidiSQL (bug) (rather than getting (NULL) which is what I would expect) This happens both if settings are: Input required: Yes Tristate: Yes or are: Input required: No Tristate: No -------------------------------------------------------------- 6) This occurs if you set at least one field in the new record, and then either hit up-arrow, down-arrow or enter to insert the new record. -------------------------------------------------------------- 7) A secondary bug then occurs if you had hit up-arrow. If you then down arrow back into the newly created record, and especially into a VARCHAR field, the 'NULL' remains from before and now acts like it's actual text data! ============================================================== Partial workarounds: First, except for TINYINT's and for with #7 above, the data actually in the SQL is correct. For #7 delete the 'NULL' text and you're ok. Clicking the refresh icon ('Refreshes the display data.') seems to repaint the screen correctly. (Simply scrolling up and down doesn't fix it.)
If you set a VARCHAR field's Default to: xx (rather than Null), then what actually gets displayed after record insertion is: 'xx' (with the quotes) (another bug) Expected result: the single quotes should not be displayed. Fortunately, what actually gets inserted into the data base is just xx without the single quotes.
Please attach a sample document, as this makes it easier for us to verify the bug. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
@ Xisco Faulí, 1) Using HeidiSQL I first made a copy of my underlying MySQL database (using an an sql dump), 2) then manually edited the dump to change the reload target name, and 3) then reloaded the database/tables to the newly named database, and then 4) also copied the broken LO .odb to a new file, and then 5) in LO pointed this new .odb to the new database from step 3, (by changing the database it was defaulting to, and also editing a whole bunch of SQL in queries and forms in it), and the problem went away. (Yes the bug went away). So then 6) I took another copy of the new working .odb, and 7) again re-pointed it back to the old MySQL database and tables, and it also works properly. So it appears to me that somehow my original .odb got corrupted. And then somehow strangely by just copying it and re-pointing it to a new copy of the database this fixed it. Baffled, but pleased that the bug is gone, at least for now. Thanks for looking at this. I marked this as RESOLVED, but not sure what you want with it, i.e. FIXED? - H
let's mark this as RESOLVED WORKSFORME as the commit fixing this issue hasn't been identified.
STEPS TO RECREATE THIS BUG: 1) Load the SQL (please cut and paste from bottom of this description) into a MariaDB database. This creates a simple database called 'journal' with two tables. 2) Open 'NULL bug - mariadb.odb' (attached). 3) Right click on one of the tables and set the database properties so you can connect to, and then view the tables. I use the following setup but yours may vary: --------------------------------------------------------- Advanced Properties tab: General Name of the MySQL database: journal User Authentication User name: _____________ x Password required --------------------------------------------------------- Additional Settings tab: Host name: localhost Port number: 3306 MySQL JDBC driver class: org.mariadb.jdbc.Driver Data Conversion Character set: System --------------------------------------------------------- 4) In Forms, open the form named 'JOURNAL'. 5) In the Table Control, in the white space under 'Debit' enter any number and then save the record (either by clicking down arrow, Return-Return (twice), or the save icon). BUGGY RESULT: 'NULL' appears just to the right of the number you just entered, (same line but under the 'To (in Combo Box)' column header). Expected result: This box should have remained empty, as nothing was entered in it. Notes: * Embedded HSQLDB 1.8 doesn't fail this way. Test with 'NULL bug - hsqldb.odb' (also attached). * Setting the Filter property seems to make this bug become visible. If you edit the 'JOURNAL' form and open the Form Navigator, then right click on 'Main', and open properties, and then select the Data tab, you'll notice that the Filter property is set to '( `journal`.`Account ID` = 11 )' which is used to provide a sub-set of the table to edit. ============================================================================= === Cut and use this SQL below to create your MariaDB test database ========= ============================================================================= -- -------------------------------------------------------- -- Host: 127.0.0.1 -- Server version: 10.2.15-MariaDB-10.2.15+maria~stretch-log - mariadb.org binary distribution -- Server OS: debian-linux-gnu -- HeidiSQL Version: 9.5.0.5196 -- -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!50503 SET NAMES utf8mb4 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- Dumping database structure for journal CREATE DATABASE IF NOT EXISTS `journal` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `journal`; -- Dumping structure for table journal.journal CREATE TABLE IF NOT EXISTS `journal` ( `Journal ID` int(11) NOT NULL AUTO_INCREMENT, `Account ID` int(11) DEFAULT NULL COMMENT 'Financial account', `Debit` decimal(19,2) DEFAULT NULL, `To` varchar(50) DEFAULT NULL COMMENT 'For debits who the money goes to. For credits who the money goes from.', PRIMARY KEY (`Journal ID`), KEY `Account ID` (`Account ID`), KEY `To` (`To`) ) ENGINE=InnoDB AUTO_INCREMENT=22364 DEFAULT CHARSET=utf8; -- Dumping data for table journal.journal: ~10 rows (approximately) /*!40000 ALTER TABLE `journal` DISABLE KEYS */; /*!40000 ALTER TABLE `journal` ENABLE KEYS */; -- Dumping structure for table journal.tos CREATE TABLE IF NOT EXISTS `tos` ( `To ID` int(11) NOT NULL AUTO_INCREMENT, `To` varchar(50) NOT NULL, PRIMARY KEY (`To`), KEY `To ID` (`To ID`) ) ENGINE=InnoDB AUTO_INCREMENT=715 DEFAULT CHARSET=utf8; -- Dumping data for table journal.tos: ~2 rows (approximately) /*!40000 ALTER TABLE `tos` DISABLE KEYS */; /*!40000 ALTER TABLE `tos` ENABLE KEYS */; /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Created attachment 142595 [details] A simple bug demonstration database; (must connect to included MariaDB tables)
Created attachment 142596 [details] A similar simple database using embedded HSQLDB which does not fail
@ Xisco Faulí, The bug reappeared, and so I dug back in to it, and have now provided you with the example you previously requested. Thanks for the help w/ this.
(In reply to Howard Johnson from comment #12) > @ Xisco Faulí, The bug reappeared, and so I dug back in to it, and have now > provided you with the example you previously requested. Thanks for the help > w/ this. Hi Howard Johnson, Thanks for retesting it... Since no one has confirmed it so far, let put it back to UNCONFIRMED until someone confirms it.
No repro for me with: Version: 6.2.0.0.alpha0+ Build ID: 28653d4addd19397200cf880a2e109bedd081fb7 CPU threads: 4; OS: Mac OS X 10.13.5; UI render: default; Locale: fr-FR (fr_FR.UTF-8); Calc: group threaded and using mysql server / mysql connector JDBC 5.1.43 Could be a bug with the mariadb server / JDBC connector ? Wouldn't be the first time that this kind of bug had occurred (we had previous past bug reports directed only against mariadb / odbc / native connectors).
Per your instructions in comment 9 : "Expected result: This box should have remained empty, as nothing was entered in it." This is precisely what I get, a default blank entry in the To combobox, with a blank dropdown list if I click on the dropdown list arrow.
So, for my next test, I changed the connector driver from mysql to mariadb JDBC connector 2.2.5. Still no repro. So apparently not a connector driver problem, at least in combination with a mysql server. Unfortunately, I can't test against a mariadb server on Mac as they are not readily available without polluting one's OS env with homebrew...
Created attachment 143733 [details] A new simple bug demonstration database (uses NullBug.sql in MariaDB 10.2.16 w/ mariadb-java-client-2.2.6.jar))
Created attachment 143735 [details] New NullBug test: SQL to create new test database and table Overview of basic things necessary to demonstrate this bug: 1) MySQL data connection, via MariaDB (Not Oracle's MySQL). (HSQLDB seems to be immune from this.) 2) A table control. 3) The table control's Form properties, Data tab, 'Filter' set, e.g. (`Value` = 1) ============================================================ Steps to reproduce this bug: 1) Create a test database and table in a MariaDB 10.2 server using the following SQL: ----------------------------------------------------- DROP DATABASE IF EXISTS `NullBug`; CREATE DATABASE IF NOT EXISTS `NullBug`; USE `NullBug`; DROP TABLE IF EXISTS `NullBug`; CREATE TABLE IF NOT EXISTS `NullBug` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Value` int(11) DEFAULT NULL, `Text` varchar(50) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `Account ID` (`Value`), KEY `To` (`Text`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; ----------------------------------------------------- 2) Download and run the NullBug.odb. 3) Open the form. 4) Under 'Value' enter the number 5 and then hit the down arrow (to insert the record). Expected result: the box under 'Text' should remain empty. Buggy result: the box under 'Text' gets the text 'NULL' entered in it.
Created attachment 143736 [details] New screenshot Enter 5 under Value. Then hit DOWN ARROW. Then 'NULL' appears under Text, but it should not appear. Rather the box under Text should remain blank.
I'm running: a) Debain 9.5 (x86-64) / Linux 4.9.0-7-amd64 b) $ mysql --version mysql Ver 15.1 Distrib 10.2.16-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 c) Connector upgraded to: /usr/share/java/mariadb-java-client-2.2.6.jar Then installed in LO with: Menu> Tools> Option> LibreOffice> Advanced> Java Options> Class Path...> Assigned folders and archives: /usr/share/java/mariadb-java-client-2.2.6.jar
Hmm, no repro either with Linux Mint 18 Version: 5.1.6.2 Build ID: 1:5.1.6~rc2-0ubuntu1~xenial3 Threads CPU : 4; Version de l'OS :Linux 4.10; UI Render : par défaut; Locale : fr-FR (fr_FR.UTF-8); Calc: group mariadb server : 10.0.34-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 mariadb jdbc connector : mariadb-java-clinet-2.2.6.jar
No repro either with: LibreOffice Snap Version: 6.0.6.2 Build ID: libreoffice-6.0.6.2-snap1 Threads CPU : 4; OS : Linux 4.10; UI Render : par défaut; VCL: gtk3; Locale : fr-FR (fr_FR.UTF-8); Calc: group mariadb server : 10.0.34-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 mariadb jdbc connector : mariadb-java-clinet-2.2.6.jar
Thanks. I'll see if I can find some other machines to test it on. ... And perhaps I should try to do a fresh install and try it, but that is involved. Thanks again.
I created the database "journal" with DDL of comment#9/ I created a database .odb and connect it to "journal" using MariaDB Connector/J. In Edit > Database > Connection Type : Database type: MySQL (JDBC) Connection Settings Host name: localhost Port number: 3306 MySQL JDBC driver class: org.mariadb.jdbc.Driver I created a form with a control 'Table control' associated to the table "journal.journal". I defined a combo box on the column "To" to display the content of the table "journal.tos" (List content type : Sql and List Content : SELECT `To` FROM `journal`.`tos` `tos`) and select an item. With the form I inserted new records in the table "journal.journal". I don't notice problem with the field "To" in the form : no "NULL" displayed in the field even with an empty list in the combo box (table "journal.tos" empty). Maybe I didn't understand the problem :-) but I didn't notice anything abnormal with an empty combo box associated with a field where"NULL" is allowed. Tested with : - Win10(x64), - LibreOffice 5.4.7 and LibreOffice 6.1.0, - MariaDB 5.5.61, - mariadb-java-client-2.3.0.jar, - Java 1.8.0_181. Bernard
On pc Debian x86-64 with MariaDB 10.3.13 + Mariadb Connector jdbc 2.4.0, I could reproduce behavior from comment 18. Indeed, I had "NULL" displayed.
Thank Julian. This is wonderful news! It definitely has something to do with the form filtering. Get rid of form filtering and the problem goes away. --- I wish I could help more. Best I can do right now is to offer my updated and expanded system specs: a) OS # lsb_release -a No LSB modules are available. Description: Debian GNU/Linux 9.8 (stretch) b) KERNEL # uname -a Linux <hostname> 4.9.0-8-amd64 #1 SMP Debian 4.9.144-3.1 (2019-02-19) x86_64 GNU/Linux c) GCC # cat /proc/version Linux version 4.9.0-8-amd64 (debian-kernel@lists.debian.org) (gcc version 6.3.0 20170516 (Debian 6.3.0-18+deb9u1) ) #1 SMP Debian 4.9.144-3.1 (2019-02-19) d) DATABASE $ mysql --version mysql Ver 15.1 Distrib 10.2.16-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 e) CONNECTOR # ls -l /usr/share/java/mariadb-java-client-2.2.6.jar lrwxrwxrwx 1 root root 77 Mar 9 16:54 /usr/share/java/mariadb-java-client-2.2.6.jar -> /home/howard/Lang/java/mariadb/ConnectorJ/2.2.6/mariadb-java-client-2.2.6.jar Installed in LO with: Menu> Tools> Option> LibreOffice> Advanced> Java Options> Class Path...> Assigned folders and archives: /usr/share/java/mariadb-java-client-2.2.6.jar f) JAVA $ java -version openjdk version "1.8.0_181" OpenJDK Runtime Environment (build 1.8.0_181-8u181-b13-2~deb9u1-b13) OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode) g) CPU # lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 2 On-line CPU(s) list: 0,1 Thread(s) per core: 1 Core(s) per socket: 2 Socket(s): 1 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 23 Model name: Intel(R) Core(TM)2 Duo CPU P8800 @ 2.66GHz Stepping: 10 CPU MHz: 800.000 CPU max MHz: 2667.0000 CPU min MHz: 800.0000 BogoMIPS: 5319.96 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 3072K NUMA node0 CPU(s): 0,1 Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant_tsc arch_perfmon pebs bts rep_good nopl aperfmperf pni dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm sse4_1 xsave lahf_lm kaiser tpr_shadow vnmi flexpriority dtherm ida
Dear Howard Johnson, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug