Created attachment 102760 [details] Simple ods and odb file for testing sql statements with IS NULL query Switching from version 4.6.0.2 to 4.2.5.2 (and LibreOfficeDev 4.3.1.0.0) some of my queries stop working with the message: "The data content could not be loaded. The query can not be executed. It is too complex." This happens to all sql statements with NULL queries like: SELECT * FROM "Tabelle1" WHERE "Flag" IS NULL I tried to work with different statement like SELECT * FROM "Tabelle1" WHERE "Flag" = '' but this doesn't give the correct result. Switching back to 4.6.0.2 all queries works fine. Juergen
Confirming also on master 4.4 Linux 64bit. I see the following warning on the command line, but don't know whether linked to problem : warn:legacy.osl:9654:1:connectivity/source/drivers/file/fcomp.cxx:261: OPredicateCompiler::execute_COMPARE: unexpected node type!
As this is a regression, upping to blocker status.
Already broken in Version: 4.2.4.2 Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8
(In reply to comment #0) > I tried to work with different statement like > SELECT * FROM "Tabelle1" WHERE "Flag" = '' > but this doesn't give the correct result. A NULL makes every test false except of course the currently broken "IS NULL". So, although the expression looks like it would be uniformly false, your workaround could be: WHERE NOT ( "Flag" = '' OR "Flag" != '' ) Three-value logic. Are we having fun yet? <grin />
Seems to be a special problem as Terrence wrote: You have to wirte SELECT * FROM "Tabelle1" WHERE NOT "Flag" != '' instead of SELECT * FROM "Tabelle1" WHERE "Flag" IS NULL What doesn't work is SELECT * FROM "Tabelle1" WHERE "Flag" = '' A cell in calc couldn't have the same properties as a field in a database. There is a difference between an empty field an NULL in a database. But base couldn't solve this problem yet. The cell isn't recognized as empty text or as 0 ... When I start 4.2.0.0beta2 the query couldn't be opened. Te message appears: SQL Status: HY000 Error code: 1000 syntax is ambiguous Same in LO 4.2.0.0alpha0+ The query works in LO 4.1.6.2. So I set the version to the first 4.3.
Thanks to all The workarounds and explanations from Terrence and Robert helps me to work with the current version. Juergen
JurgenD, I see that the attached .odb is over a .ods as the backend. Does a spreadsheet have the concept of NULL (as opposed to merely empty)? I think not. With what backend did your "IS NULL" criterion work? FWIW, with bibisect-43all version latest (Build ID: c15927f...), I do see the error message using the files you attached to the bug report, but an embedded HSQLDB handles "IS NULL" and "IS NOT NULL" to deliver the expected results.
(In reply to comment #7) > I see that the attached .odb is over a .ods as the backend. Does a > spreadsheet have the concept of NULL (as opposed to merely empty)? I > think not. I used "IS NULL" and "IS NOT NULL" for filtering empty and undefined fields, because the database returns "NULL" for empty fields and single compares like "=''" and "!=''" doesn't work. > With what backend did your "IS NULL" criterion work? The spreadsheet is mainly a more than 10 years old adress database with currently 28 columns and only 144 rows. I am using the database with more than 20 queries for some macros, serial mails, with report builder and drag and drop actions from View > Data Source. The table structure changes from time to time and the spreadsheet gives me the flexibility to work with different versions of LibreOffice, OpenOffice and NeoOffice. Juergen
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=32551a546c83874f08aba9c0adb7a38230fa2e36 fdo#81336 bison priority increase with bigger number 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-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=665e7479205647bb0c8feceef585c29c2ac46a5b&h=libreoffice-4-2 fdo#81336 bison priority increase with bigger number It will be available in LibreOffice 4.2.7. 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-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ebe64ee44175452e9d6646e220ca7030e1019be4&h=libreoffice-4-3 fdo#81336 bison priority increase with bigger number It will be available in LibreOffice 4.3.1. 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-4-2-6": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e973c9ce30668be20994454bd974416415426a73&h=libreoffice-4-2-6 fdo#81336 bison priority increase with bigger number It will be available already in LibreOffice 4.2.6. 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.