QGIS expressions: creating an array based on integer fields returns 0 for field value NULL

Question

Problem

I use QGIS expressions (tested in QGIS 3.32.3 Lima and 3.34.0 Prizren on Win 10) to create an array based on attribute fields like array("field1,"field2"). I have fields of type integer with value NULL. In the array, these are represented as 0, thus a numerical value.

I expect it to be an unknown/undefined value, so either an element with value NULL or an empty element in the array. For text/string fields, fields that are empty or NULL result in an empty element in the array.

By the way: apart from array(), the function attributes() shows the same behaviour.

Question

Is there a way to get NULL data cells to be represented as NULL or empty in the array?

Creating an array of the three fields using array(id,no,txt). Last field returns the result with converting the array to string with array_to_string(). In feature with id=8 you see that the NULL integer field results in 0, NULL textstring field results in an empty element (as expected): enter image description here

• MrXsquared: Looks like a bug to me, occuring when more than one field of a feature is NULL: i.sstatic.net/Iy94Y.png.
• Babel: It even occurs when only one field of the feature is NULL and even with just one array-element: array("field1").
• Babel: Created a bug report: github.com/qgis/QGIS/issues/55186

Answer 1

It seem that java and PHP (and maybe other langage as well...) revert to 0 for null integer value as the default behavior in array. QGIS likely do the same.

To bypass this default behavior you have to explicitly define how to represent null value. you may for exemple replace

array("field1","field2")

by

array(if( "field1" IS NULL, NULL,"field1" ),if( "field2" IS NULL, NULL,"field2"))

• Babel: So you say that this is expected behaviour? Seems strange to me
• J.R: @Babel not sure this is expected nor desirable, at last if this is the normal behavior it should be stated in the documentation for the array() fonction...

Answer 2

For some reason, a explicit cast to the desired type is needed to avoid the conversion:

array(to_int("no"), to_string("txt"))

I don't know if the implicit cast is being performed by QGIS or Qt, but it seems to be more of a fail-safe conversion, probably to some generic type.