Recursive CTEs and stop condition

[Xavier Dury]

Hello,

I know recursive CTEs are somewhat experimental in H2 but is there an ETA to support the stopping of the recursion when it starts producing records that have been previously produced?

In the meantime, I'm using a counter like this as a stop condition (but the problem is that it will only stop when the counter reaches 0 which makes it do useless rounds):

with recursive
    data (old_value, new_value) as (
        select 'foo', 'bar' union
        select 'bar', 'baz' union
        select 'baz', 'foo' union
        select 'qux', 'bar'
    ),
    related (the_value, max_recursion) as (
        select 'qux', 10
        union
        select
case the_value when old_value then new_value else old_value end,
max_recursion - 1
        from related join data on the_value in (old_value, new_value)
        where max_recursion > 0
    )
select distinct the_value from related;

I could also do something like this which could stop earlier (but I don't know if there's a performance hit with string operations or a memory limitation compared to the previous option):

with recursive
    data (old_value, new_value) as (
        select 'foo', 'bar' union
        select 'bar', 'baz' union
        select 'baz', 'foo' union
        select 'qux', 'bar'
    ),
    related (the_value, all_values) as (
        select 'qux', '|qux|'
        union
        select
            case the_value when old_value then new_value else old_value end,
            all_values || case the_value when old_value then new_value else old_value end || '|'
        from related join data on the_value in (old_value, new_value)
        where locate('|' || case the_value when old_value then new_value else old_value end || '|', all_values) = 0
    )
select distinct the_value from related;

Is there a better way to stop recursion until CTEs support is improved on this point?

Thanks,
KR,
Xavier

[Xavier Dury]

Another way to do it using Arrays (this is a more general solution which does not need values to be converted to string/varchar (better for numeric values)):

with recursive
    data (old_value, new_value) as (

        select 'foo', 'bar' union
        select 'bar', 'baz' union
        select 'baz', 'foo' union
        select 'qux', 'bar'
    ),
    related (the_value, all_values) as (

        select 'qux', array ['qux']

        union
        select
            case the_value when old_value then new_value else old_value end,

            array_append(all_values, case the_value when old_value then new_value else old_value end)

        from related join data on the_value in (old_value, new_value)

        where not array_contains(all_values, case the_value when old_value then new_value else old_value end)

    )
select distinct the_value from related;

I would be interested in hearing about other people's techniques.