Re: Proposing WITH ITERATIVE

Hello,

more random thoughts about syntax, semantics, and keeping it relational.

> While I'm not a huge fan of it, one of the other databases implementing
> this functionality does so using the syntax:
>
> WITH ITERATIVE R AS '(' R0 ITERATE Ri UNTIL N (ITERATIONS | UPDATES) ')' Qf
>
> Where N in ITERATIONS represents termination at an explicit count and, in
> UPDATES, represents termination after Ri updates more than n rows on table
> R.
>
> One of the main reasons I dislike the above is that it assumes N is
> known. In some cases, however, you really need termination upon a
> condition.

Yes, definitely, a (boolean?) condition is really needed, but possibly
above N could be an expression, maybe with some separator before the
query.

ISTM that using SELECT iterations is relational and close to the currently
existing RECURSIVE. Separating the initialization and iterations with
ITERATE is kind of the same approach than Peter's REPLACE, somehow, i.e. a
new marker.

The above approach bothers me because it changes the query syntax a lot.
The inside-WITH syntax should be the same as the normal query syntax.

First try. If we go to new markers, maybe the following, which kind of
reuse Corey explicit condition, but replacing UPDATE with SELECT which
makes it more generic:

WITH R AS (
ITERATE [STARTING] FROM R0
WHILE/UNTIL condition REPEAT Ri
);

Ok, it is quite procedural. It is really just a reordering of the syntax
shown above, with a boolean condition thrown in and a heavy on (key)words
SQL-like look and feel. It seems to make sense on a simple example:

-- 1 by 1 count
WITH counter(n) (
ITERATE STARTING FROM
SELECT 1
WHILE n < 10 REPEAT
SELECT n+1 FROM counter
);

However I'm very unclear about the WHILE stuff, it makes some sense here
because there is just one row, but what if there are severals?

-- 2 by 2 count
WITH counter(n) (
ITERATE [STARTING FROM? OVER? nothing?]
SELECT 1 UNION SELECT 2 -- cannot be empty? why not?
WHILE n < 10 REPEAT
-- which n it is just above?
-- shoult it add a ANY/ALL semantics?
-- should it really be a sub-query returning a boolean?
-- eg: WHILE TRUE = ANY/ALL (SELECT n < 10 FROM counter)
-- which I find pretty ugly.
-- what else could it be?
SELECT n+2 FROM counter
);

Also, the overall syntax does not make much sense outside a WITH because
one cannot reference the initial query which has no name.

Hmmm. Not very convincing:-) Let us try again.

Basically iterating is a 3 select construct: one for initializing, one for
iterating, one for the stopping condition, with naming issues, the last
point being exactly what WITH should solve.

by restricting the syntax to normal existing selects and moving things
out:

WITH stuff(n) AS
ITERATE OVER/FROM/STARTING FROM '(' initial-sub-query ')' -- or a table?
WHILE/UNTIL '(' condition-sub-query ')'
-- what is TRUE/FALSE? non empty? other?
-- WHILE/UNTIL [NOT] EXISTS '(' query ')' ??
REPEAT/DO/LOOP/... '(' sub-query-over-stuff ')'
);

At least the 3 sub-queries are just standard queries, only the wrapping
around (ITERATE ... WHILE/UNTIL ... REPEAT ...) is WITH specific, which is
somehow better than having new separators in the query syntax itself. It
is pretty relational inside, and procedural on the outside, the two levels
are not mixed, which is the real win from my point of view.

ISTM that the key take away from the above discussion is to keep the
overhead syntax in WITH, it should not be moved inside the query in any
way, like adding REPLACE or WHILE or whatever there. This way there is
minimal interference with future query syntax extensions, there is only a
specific WITH-level 3-query construct with pretty explicit markers.

--
Fabien.