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.
--
