MySQL simple stored procedure calling another simple one and never ending

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