Style Editor: problems with "<null>"?

[Peter2_1]

Actually a simple thing..

The data-table has some values, like "Val A", "Val B" "Val C" ..

In Style Editor I have a definition like

VAL A = 1 or VAL B is 'red'

Should be clear, but sometimes I have "<null>" in Val A - and this "kills" the entire statement. Although "Val B is red", the statement returns false ...

Can you confirm this? Is it a correct behaviour?

I think "null is not 1, so go to the part behind the OR condition" .. Right or wrong?

[parkr4st]

change 

VAL A = 1 or VAL B is 'red'

to

VAL A = '1' or VAL B is 'red'

[swt-gis · Accepted solution]

In my experience, the style editor interprets SQL differently than other programs.

To solve the problem we do the following (assumption: NULL may occur in field VAL_A):

Instead of

VAL_A = 1

We use:

VAL_A NULL or VAL_A = 1

It is important that the NULL part is at the front. This does not work:

VAL_A = 1 or VAL_A NULL

So your complete statement should be (to handle NULL in VAL_A):

(VAL_A NULL or VAL_A = 1) or VAL_B is 'red'

Regards

Jens

[Peter2_1]

@parkr4st 

thanks, but I think that Jens's answer it the correct one.

@swt-gis 

Jens, do you know if these details are documented somewhere in the official(!!) documentation?

So, as a result, the user has to modify 50% or more of his statement, because "null" can happen in a lot of fields ...

(I'm just thinking about it - if the "different behaviour of SQL statements in style editor and in datacheck and in ...." should be called a "Map SQL dialect" or simple a collection of bugs and strange implementations ...)

[swt-gis]

No, I don't know any documentary on the subject. What i've written is based only on my experiences.

Another problem that we solved by adding  NULL to the query:

Snapping to lines did not work:

NOT ID_DISPOSITION_STATE IN (10003,10004,10011,10012) and NOT ID_FUNCTION in ( 10006 , 10007)

That's how it works:

( NOT ID_DISPOSITION_STATE IN (10003,10004,10011,10012) OR ID_DISPOSITION_STATE NULL ) and ( NOT ID_FUNCTION in ( 10006 , 10007) OR ID_FUNCTION NULL)

Jens