Querying on a string field for "" yields no result, although there is records whose concerned field ir empty. Querying for an existing non empty value works. Reproducible: Always Steps to Reproduce: 1create a query which ends by something similar to ….AND (identite.courriel = '') 2.clck data 3. Actual Results: no record is found Expected Results: records whose field named identite.courriel is empty
Created attachment 76648 [details] Attached example to distinguish empty from NULL Record with id=1 has x = ''. Record with id=4 has NULL x. So SELECT id, x FROM table1 WHERE table1.x = '' ORDER BY id returns record with id = 1. Compare with SELECT id, x FROM table1 WHERE table1.x IS NULL ORDER BY id, which returns record with id = 4. Please make sure your data contains empty values (i.e. "", Kexi currently sets them if you clear a text box in table view ) or NULLs (Kexi sets NULLs when you completely omit given fields while entering new record in table view ). The former you check using = '', the latter you check using IS NULL operator. Please note, there's no way to turn non-NULL values back into NULL in a text box of table view. You can do so with external tools such as sqliteman though.
Thanks, so the bug was that I don't master SQL I checked the IS NULL and the IS NOT NULL these 2 query conditions being enough for my use. However, as far as I understand, these conditions can only be used by writing the SQL query. Trying to do it happened that I wrote some presumably incorrect statements and I screwed my kexi file. So I had to restore it from my backup. Shouldn't this point be investigated ?
Entering NULL values is indeed a good addition we shall have implemented in Kexi table view and form view. It is for advanced users though. We indeed need to have some investigation what to do to cover "99% of cases". What I see is user that has some fields set to "" and some never filles, so these are NULLs. To select "NULLs and empty strings" in one go, one has to explicitly use: select * from table1 where x is NULL or x = '' This error prone and requires advanced knowledge compared to, say, what is required from users of spreadsheet apps. So how about, in the first place, setting empty strings ("") in table and form views by default? Then the x = '' condition would be sufficient. This can be done in Kexi through default values for text data types. Advanced users that demand real NULLs would be able to turn back to NULLs as default values.
I'm no advanced user. My datas were exported to CSV from a knoda apps fed by mysql, and then used to create tables in kexi. I just localized the default value field in the table creation form. But since this shall be set before feeding any data, and since I found no way to set this when opening a CSV for import, it would indeed be convenient that the base default value for text fields be ('').
Robert, if you send me sample fragment of CSV file that imports with NULLs, I'll try to find solution for you.
OK, so I can create following wishes: - Set "" for text columns instead of NULL in CSV Import by default - Set "" for text columns instead of NULL in Tables/Forms by default
Created attachment 78434 [details] CSV file with multiple empty fields Added csv file with multiple empty fields
Depending on bug #317509 which is resolved now. Closing this one too, solution exists for CSV import. Please let me know if there's another case so we can re-open without problems.