Re: SEARCH and CYCLE clauses

On 2020-05-20 21:28, Vik Fearing wrote:
> 1)
> There are some smart quotes in the comments that should be converted to
> single quotes.

ok, fixing that

> 2)
> This query is an infinite loop, as expected:
>
> with recursive a as (select 1 as b union all select b from a)
> table a;
>
> But it becomes an error when you add a cycle clause to it:
>
> with recursive a as (select 1 as b union all table a)
> cycle b set c to true default false using p
> table a;
>
> ERROR: each UNION query must have the same number of columns

table a expands to select * from a, and if you have a cycle clause, then
a has three columns, but the other branch of the union only has one, so
that won't work anymore, will it?

> 3)
> If I take the same infinite loop query but replace the TABLE syntax with
> a SELECT and add a cycle clause, it's not an infinite loop anymore.
>
> with recursive a as (select 1 as b union all select b from a)
> cycle b set c to true default false using p
> table a;
>
> b | c | p
> ---+---+-----------
> 1 | f | {(1)}
> 1 | t | {(1),(1)}
> (2 rows)
>
> Why does it stop? It should still be an infinite loop.

If you specify the cycle clause, then the processing will stop if it
sees the same row more than once, which it did here.

> 4)
> If I use NULL instead of false, I only get one row back.
>
> with recursive a as (select 1 as b union all select b from a)
> cycle b set c to true default false using p
> table a;
>
> b | c | p
> ---+---+-------
> 1 | | {(1)}
> (1 row)

If you specify null, then the cycle check expression will always fail,
so it will abort after the first row. (We should perhaps prohibit
specifying null, but see below.)

> 5)
> I can set both states to the same value.
>
> with recursive a as (select 1 as b union all select b from a)
> cycle b set c to true default true using p
> table a;

> This is a direct violation of 7.18 SR 2.b.ii.3 as well as common sense.
> BTW, I applaud your decision to violate the other part of that rule and
> allowing any data type here.
>
>
> 5)
> The same rule as above says that the value and the default value must be
> literals but not everything that a human might consider a literal is
> accepted. In particular:
>
> with recursive a as (select 1 as b union all select b from a)
> cycle b set c to 1 default -1 using p
> table a;
>
> ERROR: syntax error at or near "-"
>
> Can we just accept a full a_expr here instead of AexprConst? Both
> DEFAULT and USING are fully reserved keywords.

This is something we need to think about. If we want to check at parse
time whether the two values are not the same (and perhaps not null),
then we either need to restrict the generality of what we can specify,
or we need to be prepared to do full expression evaluation in the
parser. A simple and practical way might be to only allow string and
boolean literal. I don't have a strong opinion here.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services