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/0629a104-12d8-46e0-a0dd-08401fb6f67bn%40googlegroups.com.
