Opened 8 years ago

Closed 6 years ago

#1411 closed question (fixed)

Updates should ignore null values?

Reported by: Dimitar Misev Owned by: Dimitar Misev
Priority: major Milestone: 9.7
Component: qlparser Version: development
Keywords: Cc: Peter Baumann, Vlad Merticariu, Bang Pham Huu, negroscuro7@…
Complexity: Medium

Description (last modified by 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]

Attachments (1)

map_mosaic_missing.png (51.3 KB ) - added by Bang Pham Huu 8 years ago.
missing data in the intersection

Download all attachments as: .zip

Change History (11)

comment:1 by Dimitar Misev, 8 years ago

Description: modified (diff)

comment:2 by Bang Pham Huu, 8 years ago

Cc: Bang Pham Huu added

by Bang Pham Huu, 8 years ago

Attachment: map_mosaic_missing.png added

missing data in the intersection

comment:3 by Bang Pham Huu, 8 years ago

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 by Dimitar Misev, 8 years ago

Cc: negroscuro7@… added

comment:5 by Peter Baumann, 8 years ago

what about this:

  • input nulls do not change
  • if users want to have this value updated they first strip off null info from the input array (how?)
Version 0, edited 8 years ago by Peter Baumann (next)

comment:6 by Dimitar Misev, 8 years ago

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 by Peter Baumann, 8 years ago

why not explicitly:

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

comment:8 by Peter Baumann, 8 years ago

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.

comment:9 by Dimitar Misev, 6 years ago

Component: undecidedqlparser
Description: modified (diff)
Milestone: Future9.7
Owner: set to Dimitar Misev
Status: newaccepted

comment:10 by Dimitar Misev, 6 years ago

Resolution: fixed
Status: acceptedclosed
Note: See TracTickets for help on using tickets.