Pourquoi un CTE récursif dans Transact-SQL nécessite-t-il un ALL UNION et non un UNION?

Je comprends qu'une ancre est nécessaire, cela a du sens. Et je sais qu'un UNION ALL est nécessaire, si votre CTE récursif n'en a pas, ça ne marche pas … mais je ne trouve pas une bonne explication de pourquoi c'est le cas. Toute la documentation indique simplement que vous en avez besoin.

Pourquoi ne pouvons- nous pas utiliser UNION au lieu d' UNION ALL dans une requête récursive? Il semble que ce serait une bonne idée de ne pas inclure les duplicates sur une récursion plus profonde, n'est-ce pas? Quelque chose comme ça devrait déjà fonctionner sous le capot, je pense.

Je suppose que la raison en est qu'ils n'ont tout simplement pas considéré cela comme une priorité à mettre en œuvre. Il semble que Postgres supporte à la fois UNION et UNION ALL .

Si vous avez un argumentaire solide pour cette fonctionnalité, vous pouvez fournir des commentaires à Connect (ou quel que soit l'URL de son rlocation sera).

Empêcher l'ajout de duplicates pourrait être utile car une ligne dupliquée ajoutée dans une étape ultérieure à une précédente entraînerait presque toujours une boucle infinie ou dépassant la limite maximale de récursivité.

Il y a pas mal d'endroits dans les standards SQL où le code est utilisé pour démontrer UNION tel que ci-dessous

entrez la description de l'image ici

Cet article explique comment ils sont implémentés dans SQL Server . Ils ne font rien comme ça "sous le capot". Le spool stack supprime les lignes au fur et à mesure, il ne sera donc pas possible de savoir si une ligne ultérieure est une copy d'une ligne supprimée. Soutenir UNION aurait besoin d'une approche quelque peu différente.

En attendant, vous pouvez très facilement atteindre la même chose dans un multi-TVF.

Pour prendre un exemple stupide ci-dessous ( Postgres Fiddle )

 WITH R AS (SELECT 0 AS N UNION SELECT ( N + 1 )%10 FROM R) SELECT N FROM R

Changer l' UNION à UNION ALL et append un DISTINCT à la fin ne vous sauvera pas de la récursion infinie.

Mais vous pouvez implémenter ceci comme

 CREATE FUNCTION dbo.F () RETURNS @R TABLE(n INT PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)) AS BEGIN INSERT INTO @R VALUES (0); --anchor WHILE @@ROWCOUNT > 0 BEGIN INSERT INTO @R SELECT ( N + 1 )%10 FROM @R END RETURN END GO SELECT * FROM dbo.F ()

Ce qui précède utilise IGNORE_DUP_KEY pour éliminer les duplicates. Si la list des colonnes est trop large pour être indexée, vous devez utiliser DISTINCT et NOT EXISTS place. Vous voudrez probablement aussi un paramètre pour définir le nombre maximum de récursions et éviter les loops infinies.

C'est de la pure spéculation, mais je dirais que l'UNION ALL assure que le résultat de chaque itération peut être calculé individuellement. Essentiellement, cela garantit qu'une itération ne peut pas interférer avec une autre.

Une UNION nécessiterait une opération de sorting en arrière-plan qui pourrait modifier le résultat des itérations précédentes. Le programme ne devrait pas changer l'état d'un appel précédent dans la stack d'appels, il devrait interagir avec lui en utilisant les parameters d'input et le résultat de l'itération suivante (dans un cadre procédural). Cela devrait probablement s'appliquer aux opérations basées sur un set, donc aux CTE récursifs de SQL Server.

Je peux me tromper, les brain-dumps de fin de nuit ne sont pas fiables à 100% 🙂

Edit (juste une autre pensée):

Quand une récursivité commence, vous avez une stack d'appels. Chaque niveau de cette stack commence à calculer son résultat, mais doit attendre le résultat de tous les appels suivants avant de pouvoir terminer et returnner le résultat. UNION essaierait d'éliminer la duplication, mais vous n'avez aucun logging jusqu'à ce que vous atteigniez la condition de terminaison (et la finale serait construite du bas vers le haut), mais le résultat de l'appel suivant est requirejs par ceux au-dessus. . L'UNION serait réduite à DISTINCT à la toute fin.