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.
On Tuesday, 14 October 2025 at 11:13:34 UTC+2 Xavier Dury wrote:
> 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
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/h2-database/26185ea2-c2ea-4765-b947-5469e62dfab2n%40googlegroups.com.
