Re: recursive search

[Quoted] On 09/11/2016 04:38 AM, Thomas 'PointedEars' Lahn wrote:
> mireero wrote:
> ^^^^^^^
> Your real name belongs there.

Why ?
[Quoted] Is this a part of the "net-attitude" ?

[Quoted] I thought the signature was meant to be able to identify someone from one message to another.

[Quoted] Real name or pseudo, either way that do the job, no ?

[Quoted] FYI, I'm Michael Robertson, south of France, 38 :)

>
>> Thanks for the answers.
>>
>> What I get is:
>> - No recursive support in mysql […]
>
> Incorrect. There are no recursive stored *FUNCTIONs* in MySQL (yet).
>
> But recursive stored *PROCEDUREs* are supported, and with “call by
> reference” they can work like FUNCTIONs. For example, consider the textbook
> recursive function, the factorial function, written as a procedure:
>
> $ echo '
> /* Show original GRANTS */
> SHOW GRANTS;
>
> DROP PROCEDURE IF EXISTS fac;
>
> /* For mysql(1) only: Pass “;” in procedure code through to server */
> DELIMITER //
>
> CREATE PROCEDURE fac (IN i TINYINT, OUT result BIGINT) DETERMINISTIC
> BEGIN
> DECLARE fac_result BIGINT;
>
> IF i < 0 THEN
> SET result = NULL;
> ELSEIF i < 2 THEN
> SET result = 1;
> ELSE
> CALL fac(i - 1, fac_result);
> SET result = fac_result * i;
> END IF;
> END//
>
> /* For mysql(1) only: Restore statement delimiter */
> DELIMITER ;
>
> /* Show that GRANTS have changed to include the new stored procedure */
> SHOW GRANTS;
>
> /* Required for recursion, the default is 0 */
> SET _at__at_max_sp_recursion_depth = 255;
>
> CALL fac(15, _at_result);
> SELECT _at_result AS `15!`\G
> ' | mysql -u guest tmp
> Grants for guest_at_localhost
> GRANT USAGE ON *.* TO 'guest'_at_'localhost'
> GRANT SELECT, CREATE ROUTINE, ALTER ROUTINE ON `tmp`.* TO
> 'guest'_at_'localhost'
> Grants for guest_at_localhost
> GRANT USAGE ON *.* TO 'guest'_at_'localhost'
> GRANT SELECT, CREATE ROUTINE, ALTER ROUTINE ON `tmp`.* TO
> 'guest'_at_'localhost'
> GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `tmp`.`fac` TO 'guest'_at_'localhost'
> *************************** 1. row ***************************
> 15!: 1307674368000
>
> $ mysql --version
> mysql Ver 14.14 Distrib 5.6.25, for debian-linux-gnu (x86_64) using
> EditLine wrapper
>
> [15! appears to be the maximum factorial that can be computed with this
> procedure in the default MySQLd setup.]
>

[Quoted] Ok,I won't answer every messages, just some comments here.

[Quoted] I had a look at MariaDB's oqgraph, well kind of fascinating, very different from my way of thinking databases. Any good tutorial ? [Quoted] Other major differences between MySQL and MariaDB (you don't really have to answer that, I'll google it...) ?
[Quoted] Non standard MySQL stuff like "insert ignore" supported in MariaDB ?

I'm interested in this kind of recursive procedure with a reference. And actually, the logic is very similar to other languages so I get It.

Comments:

[Quoted]
• How can I store found results while the procedure is "recursing" to avoid an infinite loop ? [Quoted] I would need something like an array which would grow on each iteration and be tested against each result of the current iteration so I can get out of the procedure if needed (I'm clear?).
• Cursors to loop, yes! [Quoted]
• I don't yet see how I could use oqgraph (I haven't read enough about if though), but if I understand the different answers, it's not really suited for my situation. [Quoted]
• What the hell! setting max_sp_recursion_depth = 255 and being limited to "15!" (factorial(15)) ? [Quoted]
• If I do manage to do it the procedural way, what about performance ? I [Quoted] mean compared to adding a PHP layer ?
• Storing results: -> Factorial example, one scalar variable updated on each recursion. -> My situation: I need something like an array to store the results (same idea as above, could also be handy to avoid an infinite loop).

By the way, "variant 1" was decided to be the way to go. To sum up, I need to get all descendants regardless of their positions in the "descendant chain" (so a 1 level result) and to get rid of duplicates.

It's time to read, knocking my head, understanding, discovering more and more to read... A never ending story...
I think I'm in the middle of an infinite loop (where the hell is the "return statement"?) !

--
mireero