[QGIS-Developer] Handling NULL differs in concat vs || in expressions: let's remove ||

[Richard Duivenvoorde]

Hi Devs,

tldr; should the '||'-button in QGIS expressions not either behave like
a real 'String concat' function,
or
as a logical OR
or
be removed?   <= my favourite

Longer story:

If you want to play, this zip contains a geopackage and shp and QGIS
project: http://duif.net/concat_issue.zip

I had to create a new column by concatenating 3 strings from other
columns in field calculator (but label expressions behave the same).
I used:

 "col1"   || ' '  || "col2"   || ' '  || "col3"

but all records which had one or more NULL values in (tested both gpkg
and shp) showed nothing (NULL)

So thought to create an issue, but then found:

https://github.com/qgis/QGIS/issues/16460
https://github.com/qgis/QGIS/issues/11504
https://github.com/qgis/QGIS/issues/15848
https://github.com/qgis/QGIS/issues/18608

Then I thought "if || invalidates the string if it contains a NULL, it
more behaves like an OR and that should be shown in the help text"
BUT: these buttons do not have an help text (only a small label).
Then I searched for a 'concat' function and found it

String/concat: "Concatenates several strings to one. NULL values are
converted to empty strings. Other values (like numbers) are converted to
strings."

So this just works flawlessly:

concat( "col1", ' ',  "col2", ' ',  "col3" )

Should the '||' not be removed from the expression buttons?
As it works (in my opinion) counter intuitive?

Or else the label could be changed? It now says 'String concatenation'.
Which it is not, as it also concatenates integers to strings etc.

The argument that it behaves like sql is also not valid: || is behaving
differently on all kind of database:

Oracle apparently only returns NULL if ALL the arguments are NULL, else
it creates an empty string from the NULL, see
http://www.sqlines.com/oracle-to-sql-server/string_concat

Mysql and SQL server apparently handle this differently too:
https://stackoverflow.com/questions/39453742/sql-like-with-double-pipe-concatenation

Opinions?

Regards,

Richard Duivenvoorde