Trying to calculate the sum of n x k-combinations (ie. non-ordered) for m elements.

To calculate a k-combination, I need to create a function which calculates factorial.

The code is quite simple, and works :

BEGIN DECLARE factorial INT DEFAULT 1; DECLARE counter INT; SET counter = m; -- m is my variable factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END

Then I want to create anothe simple function adding the k-combinations until a given threshold.

Exemple : I want to know how many k-combinations exist for 7 elements from 4 elements.

It’s :

- the combination of 7 elements within 7 elements = 1 unordered combination
- the combination of 6 elements within 7 elements = 7 unordered combination
- the combination of 5 elements within 7 elements = 21 unordered combination
- the combination of 4 elements within 7 elements = 35 unordered combination

TOTAL = 64

For this I created a second stored procedure, calling the first one.

BEGIN DECLARE tempo INT DEFAULT 0; DECLARE counter2 INT; SET counter2 = X; -- X is the number of elements, in the example 7 combisum_loop: REPEAT SET tempo = tempo + factorial(x)/(factorial(counter2)*factorial(x-counter2)); -- the mathematical formula for k-combinations SET counter2 = counter2 - 1 ; UNTIL counter2 = Y -- Y is the thresold, in the example 4 END REPEAT; RETURN tempo; END

Problem : calling the second function with even these small numbers never ends. What am I doing wrong ?

PS: some thoughts :

- if I remplace the mathematical function by the number 1, it returns 4 (the correct result)
- if I replace the mathematical formula by a unique call to the factorial function, it works ! (ie. it finishes, but of course that’s not the result I want to achieve)
- if I create a k-combination function with the whole formula but without the loop it also works !!!
- if I call the k-combination function within my loop, it fails

So the problem seems to be when I have both a loop and several (indirect) calls to the – indeed simple – factorial function.

NB: tried to modify the second stored procedure in order to avoid the factorial(0) like suggested by @nbk, but this still fails :

BEGIN DECLARE tempo INT DEFAULT 1; DECLARE counter2 INT; SET counter2 = X-1; voila3_loop: REPEAT SET tempo = tempo + factorial(x)/(factorial(counter2)*factorial(x-counter2)); SET counter2 = counter2 - 1 ; UNTIL counter2 = Y END REPEAT; RETURN tempo; END

the first iteration should be tempo = 1 + factorial(7)/(factorial(6)*factorial(7-6)); and should definitely work

## Answer

Besides minor problems in your code.

your first round of your second function starts with a divison by NULL

because `factorial(0) =0`

I am unfamiliar with the formula you are using

And so the function doesn’t run at all.

so the formula doesn’t seem to be right.

CREATE Function factorial(m INT) RETURNS BIGINT unsigned DETERMINISTIC BEGIN DECLARE factorial BIGINT UNSIGNED DEFAULT 1; DECLARE counter INT; SET counter = m; -- m is my variable factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter <= 1 END REPEAT; RETURN factorial; END

✓

SELECT factorial(4)/(factorial(4) * factorial(0))| factorial(4)/(factorial(4) * factorial(0)) | | -----------------------------------------: | |null|

SELECT factorial(4)/(factorial(3) * factorial(1))| factorial(4)/(factorial(3) * factorial(1)) | | -----------------------------------------: | | 4.0000 |

SELECT factorial(4)/(factorial(2) * factorial(2))| factorial(4)/(factorial(2) * factorial(2)) | | -----------------------------------------: | | 6.0000 |

SELECT factorial(4)/(factorial(1) * factorial(3))| factorial(4)/(factorial(1) * factorial(3)) | | -----------------------------------------: | | 4.0000 |

CREATE Function factor(x INT) RETURNS BIGINT unsigned DETERMINISTIC BEGIN DECLARE tempo BIGINT UNSIGNED DEFAULT 0; DECLARE counter2 INT; SET counter2 = x; -- X is the number of elements, in the example 7 combisum_loop: REPEAT SET @a := counter2; SET tempo = tempo + factorial(x)/(factorial(counter2)*factorial(x-counter2)); -- the mathematical formula for k-combinations SET counter2 = counter2 - 1 ; UNTIL counter2 <= Y -- Y is the thresold, in the example 4 END REPEAT; RETURN tempo; END

SELECT factor(4)

SELECT @a| @a | | -: | | 4 |

*db<>fiddle here*