QGIS » QGIS Application

(perhaps this should be a separate ticket...)

Also,
If a NULL value is concatenated, the result of the expression is currently null. For example:

I have a layer of property boundaries for my street (well, a friend's street :) )
There are attribute fields which contain:
- the house number of each property in the street.
- a list of the people living at that address (if somebody does currently live there, and we have already recorded their name(s) ).
I want to label each property with both fields. Using this expression:

"HOUSENUM" || '
'|| "Occupants"

, for properties where the "Occupants" field is empty, there is no label.

I think this behaviour would surprise most users, who would expect the property to still be labelled with the house number. I don't know if the expected behaviour would be consistent with normal SQL, but it would certainly be consistent with the principle of least astonishment ;)

Alister Hood wrote:

I don't know if the expected behaviour would be consistent with normal SQL, but it would certainly be consistent with the principle of least astonishment ;)

It is. Any operation on NULL (except IS NULL / IS NOT NULL) is NULL. So you could use CASE WHEN foo IS NULL THEN '' ELSE foo END if you happen to have NULL values in foo.

Jürgen Fischer wrote:

Alister Hood wrote:

I don't know if the expected behaviour would be consistent with normal SQL, but it would certainly be consistent with the principle of least astonishment ;)

It is.

If the SQL compatibility must stay, I guess we could at least add a warning to the help text for ||.

Any operation on NULL (except IS NULL / IS NOT NULL) is NULL. So you could use CASE WHEN foo IS NULL THEN '' ELSE foo END if you happen to have NULL values in foo.

What do you think of the idea of creating a new operation for that, so instead of using a long CASE statement on every field you could just use NEW_OPERATION_NAME(foo)? I can imagine this would make some expressions a lot shorter, and easier for people to decipher.

Alister Hood wrote:

What do you think of the idea of creating a new operation for that, so instead of using a long CASE statement on every field you could just use NEW_OPERATION_NAME(foo)? I can imagine this would make some expressions a lot shorter, and easier for people to decipher.

we could call it coalesce

I agree with Jürgen. The semantic is the same in other SQL databases - both for the operator precedence and NULL handling. People sometimes misunderstand the NULL value - it does not mean "nothing" and it is definitely not a synonym for a zero or an empty string. NULL value has the meaning of "missing data" and therefore must be handled specially. The coalesce function would be the right way to go.

Yes, that would be a lot easier than building (or reading) a whole lot of CASE statements.

But I guess the most useful for many labelling use cases would be a concat() function like PostgreSQL:
http://www.postgresql.org/docs/9.1/static/functions-string.html#FUNCTIONS-STRING-OTHER

Ah, the "More info on expression error" is helpful now :)

For the original feature description above, I see that adding some brackets works (now?):
'AREA = '||($AREA/10000)||' ha'

I'm pretty sure I tried that at the time, but maybe I'm mistaken...

I've found a couple of places listing SQL operator precedence, which indicate that / is higher precedence than ||, so the current behaviour is correct. I guess operator precedence is something that should really be covered in the help, or at least the manual. It doesn't really fit into the the current approach of separate help for each operator.