Updates should ignore null values?

[Dimitar Misev]

In an update, if the source array contains null values, should they overwrite the existing values in the target array?

If they do then we cannot do mosaics properly, see e.g. ​https://groups.google.com/d/msg/rasdaman-users/OFuT_hkk_Iw/HjiubaIRAgAJ

The source array in an update statement doesn't have null values associated to it, only the persistent arrays (and intermediate arrays derived from these) in the database have. #1763 adds support for specifying null values for any array object in a query on the fly, e.g. this attaches null values 0 to c:

SELECT avg_cells(c NULL VALUES [0]) FROM coll as c

This can be used in an update to not overwrite the target array with null values, e.g.

UPDATE coll AS c SET c ASSIGN decode($1) NULL VALUES [0]

[Bang Pham Huu]

missing data in the intersection

[Bang Pham Huu]

the result image when download the combined coverage in PNG (it should keep the data from 1 of 2 overlapped files).

​http://rasdaman.org/attachment/ticket/1411/map_mosaic_missing.png

[Peter Baumann]

just read the next post, we seem to share thoughts:

• input nulls do not change
• if users want to have this value updated they first strip off null info from the input array somehow.

[Dimitar Misev]

Yes essentially the decision boils down to what is the default behavior.

1. nulls overwrite existing data
2. nulls do not overwrite existing data

I think 2. would be the default (as you suggest), while the user needs to explicitly indicate if they want 1.

To achieve this we need mechanisms to

1. associate null values with arrays on the fly in a query
2. remove null values from an array (which could be achieved through 1. by specifying an empty set of null values?)

[Peter Baumann]

why not explicitly:

UPDATE coll AS c SET c ASSIGN 
  CASE decode($1) WHEN 0 THEN c ELSE decode($1) END

[Peter Baumann]

an intensive powwow has led to this solution:

• add a null assignment to an array E through "E WITH NULLS [nullvaluelist]"
• update statement overwrites value only if input is not null

effectively, this is what has been described in the ticket description. Implementation to be available with v9.4.