I think I'd prefer 2), the SQL behaviour. We could also get the convenience of 3) by adding CQL functions such as "ifNull(column, default)" or "zeroIfNull(column)", as it's done by other dbs. So we could do things like "UPDATE ... SET name = zeroIfNull(name) + 42".
On Wed, 31 Aug 2022 at 04:54, Caleb Rackliffe <calebrackli...@gmail.com> wrote: > Also +1 on the SQL behavior here. I was uneasy w/ coercing to "" / 0 / 1 > (depending on the type) in our previous discussion, but for some reason > didn't bring up the SQL analog :-| > > On Tue, Aug 30, 2022 at 5:38 PM Benedict <bened...@apache.org> wrote: > >> I’m a bit torn here, as consistency with counters is important. But they >> are a unique eventually consistent data type, and I am inclined to default >> standard numeric types to behave as SQL does, since they write a new value >> rather than a “delta” >> >> It is far from optimal to have divergent behaviours, but also suboptimal >> to diverge from relational algebra, and probably special casing counters is >> the least bad outcome IMO. >> >> >> On 30 Aug 2022, at 22:52, David Capwell <dcapw...@gmail.com> wrote: >> >> >> 4.1 added the ability for LWT to support "UPDATE ... SET name = name + >> 42", but we never really fleshed out with the larger community what the >> semantics should be in the case where the column or row are NULL; I opened >> up https://issues.apache.org/jira/browse/CASSANDRA-17857 for this issue. >> >> As I see it there are 3 possible outcomes: >> 1) fail the query >> 2) null + 42 = null (matches SQL) >> 3) null + 42 == 0 + 42 = 42 (matches counters) >> >> In SQL you get NULL (option 2), but CQL counters treat NULL as 0 (option >> 3) meaning we already do not match SQL (though counters are not a standard >> SQL type so might not be applicable). Personally I lean towards option 3 >> as the "zero" for addition and subtraction is 0 (1 for multiplication and >> division). >> >> So looking for feedback so we can update in CASSANDRA-17857 before 4.1 >> release. >> >> >>
