Bug 81336 - Base Query with IS NULL statement gives "... too complex"
Summary: Base Query with IS NULL statement gives "... too complex"
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.4.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:4.4.0 target:4.2.6 target:4.3.1
Keywords: regression
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2014-07-14 09:55 UTC by JuergenD
Modified: 2014-07-22 13:46 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Simple ods and odb file for testing sql statements with IS NULL query (10.81 KB, application/zip)
2014-07-14 09:55 UTC, JuergenD
Details

Note You need to log in before you can comment on or make changes to this bug.
Description JuergenD 2014-07-14 09:55:10 UTC
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
Comment 1 Alex Thurgood 2014-07-14 10:44:17 UTC
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!
Comment 2 Alex Thurgood 2014-07-14 10:45:09 UTC
As this is a regression, upping to blocker status.
Comment 3 Alex Thurgood 2014-07-14 10:51:32 UTC
Already broken in Version: 4.2.4.2
Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8
Comment 4 Terrence Enger 2014-07-14 12:42:49 UTC
(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 />
Comment 5 Robert Großkopf 2014-07-14 13:48:49 UTC
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.
Comment 6 JuergenD 2014-07-14 14:04:54 UTC
Thanks to all

The workarounds and explanations from Terrence and Robert helps me to work with the current version.

Juergen
Comment 7 Terrence Enger 2014-07-14 14:17:45 UTC
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.
Comment 8 JuergenD 2014-07-15 07:19:41 UTC
(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
Comment 9 Commit Notification 2014-07-19 07:47:56 UTC
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.
Comment 10 Commit Notification 2014-07-21 08:16:05 UTC
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.
Comment 11 Commit Notification 2014-07-21 08:16:19 UTC
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.
Comment 12 Commit Notification 2014-07-22 13:46:52 UTC
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.