SQL CTE self recursion goes infinite

Question

I've seen many answers, but I can't quite get understanding how CTE is working in this case.

I've formula - i = x / y, but I want every consequent rn > 1 to calculate i = x(rn-1)/y(rn-1)/y(rn)

The code I've written to do it:

drop table if exists #tmp

create table #tmp
(
    rn int,
    x int,
    y int
)

insert into #tmp
values (1, 20, 2),
       (2, 30, 3),
       (3, 40, 4);

with cte as
(
    select 
        *, x / y as i
    from 
        #tmp curr
    where 
        rn = 1
    union all
    select 
        curr.rn, curr.x, curr.y, prev.x / prev.y / curr.y as i  
    from 
        cte curr
    join 
        #tmp prev on prev.rn = curr.rn + 1
)
select *
from cte
option (maxrecursion 0)

I'm getting infinite loop and incorrect results from preview.

Here's what my result table should look like:

rn	x	y	i
1	20	2	10.00
2	30	3	3.33
3	40	4	0.83

Answer 1

If I understand this right, we can do it without recursion at all. But we have to do some math.

---

First, understanding the results.

It looks to me like the first row x value is the only one that matters for calculating i. Every row is the result of that initial first x divided by the running multiplication (not sum) of all the rows up to that point. The other x values never matter:

rn	calculation	result
1	20 / 2	10.00
2	20 / (2 * 3)	3.33
3	20 / (2 * 3 * 4)	0.83

These totals match the expected results from the sample data, so I believe this plan should be sound. And when expressed this way, this looks like a linear accumulation issue, meaning it should not need to be recursive.

---

But now we need to find a running multiplication, which is a little tricky. Getting a running total (SUM() OVER) is easy and well understood, but there's no simple MUL() aggregate function we can use to replace SUM()

Thankfully, this IS possible. The math is weird, but others have figured it out so we only need to follow the formula:

EXP(SUM(LOG(column)))

We can see here that it works:

https://dbfiddle.uk/NxS-mFtx

(Remember: I'm only looking for the running multiplication at this point.)

---

Now that I have this much, I can finally write the query to do the whole thing:

with runningMult As (
    select rn, x, y, EXP(SUM(LOG(y)) over (order by rn) ) rm
    from #tmp
)
select rn, x, y, round((select top 1 x from #tmp order by rn)/rm,2) i
from runningMult

See it work here:

https://dbfiddle.uk/-3zLos5_

I believe this should perform as well or better vs the recursion, and if you add a comment pointing to the formula explanation (plus use a better name than my rm) it should be easier to understand and maintain as well. It's definitely a lot less code.

But it's possible the (SELECT TOP 1 ...) subquery to get the initial x will need to be optimized so it doesn't run per-row. If you need to see it, it looks like this:

https://dbfiddle.uk/xBNleIvz

Answer 2

Thanks to MartinSmith.

Working code:

drop table if exists #tmp

create table #tmp
(
rn int,
x int,
y int
)
insert into #tmp
values (1, 20, 2),
(2, 30, 3),
(3, 40, 4);

with cte
as
(
select *
        ,cast(x / cast(y as decimal(4,2)) as float) as i
from #tmp curr
where rn = 1
union all
select curr.rn
        ,curr.x
        ,curr.y
        ,cast(prev.i / cast(curr.y as decimal(4,2)) as float) as i
from #tmp curr
join cte prev on prev.rn + 1 = curr.rn
)

select *
from cte
option (maxrecursion 0)