I am also +1 to match SQL, option 2. Also, I like Andres’ suggestion On Wed, 31 Aug 2022 at 7:15, Claude Warren via dev <dev@cassandra.apache.org> wrote:
> I like this approach. However, in light of some of the discussions on > view and the like perhaps the function is (column value as returned by > select ) + 42 > > So a null counter column becomes 0 before the update calculation is > applied. > > Then any null can be considered null unless addressed by IfNull(), or > zeroIfNull() > > Any operation on null returns null. > > I think this follows what would be expected by most users in most cases. > > > On 31/08/2022 11:55, Andrés de la Peña wrote: > > 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. >>> >>> >>>
