Re: Proposing WITH ITERATIVE

On Tue, Apr 28, 2020 at 11:51 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings Jonah!
>

Hey, Stephen. Hope you're doing well, man!

> One of the first question that we need to ask though, imv anyway, is- do
> the other databases use the WITH ITERATIVE syntax? How many of them?
> Are there other approaches? Has this been brought up to the SQL
> committee?
>

There are four that I'm aware of, but I'll put together a full list. I
don't believe it's been proposed to the standards committee, but I'll see
if I can find transcripts/proposals. I imagine those are all still public.

In general, we really prefer to avoid extending SQL beyond the standard,
> especially in ways that the standard is likely to be expanded. In
> other words, we'd really prefer to avoid the risk that the SQL committee
> declares WITH ITERATIVE to mean something else in the future, causing us
> to have to have a breaking change to become compliant.

Agreed. That's my main concern as well.

> Now, if all the other major DB vendors have WITH ITERATIVE and they all
> work the same
> way, then we can have at least some confidence that the SQL committee
> will end up defining it in that way and there won't be any issue.
>

This is where it sucks, as each vendor has done it differently. One uses
WITH ITERATE, one uses WITH ITERATIVE, others use, what I consider to be, a
nasty operator-esque style... I think ITERATIVE makes the most sense, but
it does create a future contention as that definitely seems like the syntax
the committee would use as well.

Oracle ran into this issue as well, which is why they added an additional
clause to WITH that permits selection of depth/breadth-first search et al.
While it's kinda nasty, we could always similarly amend WITH RECURSIVE to
add an additional ITERATE or something to the tail of the with_clause rule.
But, that's why I'm looking for feedback :)

We do have someone on the committee who watches these lists, hopefully
> they'll have a chance to comment on this. Perhaps it's already in
> discussion in the committee.
>

That would be awesome.

--
Jonah H. Harris