Re: SEARCH and CYCLE clauses

On 2020-05-22 12:40, Vik Fearing wrote:
>> 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.)
> 
> I would rather make the cycle check expression work with null.

It works correctly AFAICT.  What is your expectation?

>> 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.
> 
> 
> I'm with Pavel on this one.  Why does it have to be a parse-time error?
>   Also, I regularly see people write functions as sort of pauper's
> variables, but a function call isn't allowed here.

If not parse-time error, at what time do you want to check it?

> As an improvement over the spec, I think the vast majority of people
> will be using simple true/false values.  Can we make that optional?
> 
>      CYCLE f, t SET is_cycle USING path
> 
> would be the same as
> 
>      CYCLE f, t SET is_cycle TO true DEFAULT false USING path

I was also considering that.  It would be an easy change to make.

(Apparently, in DB2 you can omit the USING path clause.  Not sure how to 
make that work, however.)

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