On 6/22/23 19:52, Joel Jacobson wrote: > 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? >
I agree this looks a bit weird, but that's what I mentioned - this is an initial a proposal, outlining the idea. Inevitably some of the stuff will get reworked or just left out of the final version. It's useful mostly to explain the motivation / goal. I believe that's the case here - I don't think the ELEMENT got into the standard at all, and the NULL rules for the MEMBER OF clause seem not to have these strange bits. > 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. > Well, the simple truth is the draft is freely available, but you'd need to buy the final version. It doesn't mean it's still being worked on or that no SQL standard was released since then. In fact, SQL 2023 was released a couple weeks ago [1]. It'd be interesting to know the version that actually got into the SQL standard (if at all), but I don't have access to the standard yet. regards [1] https://www.iso.org/standard/76584.html -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
