Opened 6 months ago

Last modified 5 months ago

#1411 new question

Updates should ignore null values?

Reported by: dmisev Owned by:
Priority: major Milestone: Future
Component: undecided Version: development
Keywords: Cc: pbaumann, vmerticariu, bphamhuu, negroscuro7@…
Complexity: Medium

Description (last modified by dmisev)

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. If we would introduce a mechanism to attach null values to any array value, we could say that then they can be ignored in the update. Something like:

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

Attachments (1)

map_mosaic_missing.png (51.3 KB) - added by bphamhuu 6 months ago.
missing data in the intersection

Download all attachments as: .zip

Change History (9)

comment:1 Changed 6 months ago by dmisev

  • Description modified (diff)

comment:2 Changed 6 months ago by bphamhuu

  • Cc bphamhuu added

Changed 6 months ago by bphamhuu

missing data in the intersection

comment:3 Changed 6 months ago by bphamhuu

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

comment:4 Changed 6 months ago by dmisev

  • Cc negroscuro7@… added

comment:5 Changed 6 months ago by pbaumann

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.
Last edited 6 months ago by pbaumann (previous) (diff)

comment:6 Changed 6 months ago by dmisev

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?)

comment:7 Changed 5 months ago by pbaumann

why not explicitly:

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

comment:8 Changed 5 months ago by pbaumann

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.

Note: See TracTickets for help on using tickets.