On Tue, Jun 20, 2023, at 14:10, Tomas Vondra wrote: > This is also what the SQL standard does for multisets - there's SQL:20nn > draft at http://www.wiscorp.com/SQLStandards.html, and the <member > predicate> section (p. 475) explains how this should work with NULL.
I've looked again at the paper you mentioned and found something intriguing in section 2.6 (b). I'm a bit puzzled about this: why would we want to return null when we're certain it's not null but just doesn't have any elements? In the same vein, it says, "If it has more than one element, an exception is raised." Makes sense to me, but what about when there are no elements at all? Why not raise an exception in that case too? The ELEMENT function is designed to do one simple thing: return the element of a multiset if the multiset has only 1 element. This seems very similar to how our INTO STRICT operates, right? The SQL:20nn seems to still be in draft form, and I can't help but wonder if we should propose a bit of an improvement here: "If it doesn't have exactly one element, an exception is raised." Meaning, it would raise an exception both if there are more elements, or zero elements (no elements). I think this would make the semantics more intuitive and less surprising. /Joel
