sql - source - ¿Cuál es el comportamiento esperado para múltiples funciones de retorno de conjuntos en la cláusula SELECT?

sql source type in ssis (3)

Estoy tratando de obtener una "unión cruzada" con el resultado de dos funciones de retorno de conjuntos, pero en algunos casos no obtengo la "unión cruzada", ver ejemplo

Comportamiento 1 : cuando las longitudes de los conjuntos son las mismas, coincide con elemento por elemento de cada conjunto

postgres=# SELECT generate_series(1,3), generate_series(5,7) order by 1,2; generate_series | generate_series -----------------+----------------- 1 | 5 2 | 6 3 | 7 (3 rows)

Comportamiento 2 : cuando las longitudes de los conjuntos son diferentes, se "cruzan" en los conjuntos

postgres=# SELECT generate_series(1,2), generate_series(5,7) order by 1,2; generate_series | generate_series -----------------+----------------- 1 | 5 1 | 6 1 | 7 2 | 5 2 | 6 2 | 7 (6 rows)

Creo que no estoy entendiendo algo aquí, ¿alguien puede explicar el comportamiento esperado?

Otro ejemplo, incluso más extraño:

postgres=# SELECT generate_series(1,2) x, generate_series(1,4) y order by x,y; x | y ---+--- 1 | 1 1 | 3 2 | 2 2 | 4 (4 rows)

Estoy buscando una respuesta a la pregunta en el título, idealmente con enlaces a la documentación.

Postgres 10 o más reciente

agrega valores nulos para los conjuntos más pequeños. Demostración con generate_series() :

SELECT generate_series( 1, 2) AS row2 , generate_series(11, 13) AS row3 , generate_series(21, 24) AS row4;

row2 | row3 | row4 -----+------+----- 1 | 11 | 21 2 | 12 | 22 null | 13 | 23 null | null | 24

dbfiddle here

El manual para Postgres 10 :

Si hay más de una función de retorno de conjunto en la lista de selección de la consulta, el comportamiento es similar al que se obtiene al colocar las funciones en un solo elemento de CLASES LATERALES LATERAL ROWS FROM( ... ) FROM LATERAL ROWS FROM( ... ) FROM . Para cada fila de la consulta subyacente, hay una fila de salida que usa el primer resultado de cada función, luego una fila de salida que usa el segundo resultado, y así sucesivamente. Si algunas de las funciones de retorno de conjuntos producen menos salidas que otras, los valores nulos se sustituyen por los datos faltantes, de modo que el número total de filas emitidas para una fila subyacente es el mismo que para la función de retorno de conjuntos que produjo la mayoría de las salidas . Por lo tanto, las funciones de devolución de conjuntos se ejecutan "en bloque" hasta que se agoten, y luego la ejecución continúa con la siguiente fila subyacente.

Esto termina el comportamiento tradicionalmente extraño.

Postgres 9.6 o anterior

El número de filas de resultados (¡algo sorprendente!) Es el mínimo común múltiplo de todos los conjuntos en la misma lista SELECT . (¡Solo como CROSS JOIN si todos los tamaños de conjunto no tienen divisor común!) Demostración:

SELECT generate_series( 1, 2) AS row2 , generate_series(11, 13) AS row3 , generate_series(21, 24) AS row4;

row2 | row3 | row4 -----+------+----- 1 | 11 | 21 2 | 12 | 22 1 | 13 | 23 2 | 11 | 24 1 | 12 | 21 2 | 13 | 22 1 | 11 | 23 2 | 12 | 24 1 | 13 | 21 2 | 11 | 22 1 | 12 | 23 2 | 13 | 24

dbfiddle here

Documentado en el manual para Postgres 9.6 el capítulo Conjuntos devueltos de funciones SQL , junto con la recomendación para evitarlo:

Nota: El problema clave con el uso de funciones de devolución de conjuntos en la lista de selección, en lugar de la cláusula FROM , es que poner más de una función de devolución de conjuntos en la misma lista de selección no se comporta de manera muy sensata. ( Lo que realmente obtienes si lo haces es un número de filas de salida igual al mínimo común múltiplo de los números de filas producidos por cada función de retorno de conjuntos ) . La sintaxis LATERAL produce resultados menos sorprendentes cuando se llaman múltiples funciones de retorno de conjuntos, y generalmente debería usarse en su lugar.

Negrita énfasis mío.

Una sola función de retorno de conjuntos está bien (pero aún más limpia en la lista FROM ), pero ahora se desaconseja múltiples en la misma lista SELECT . Esta fue una característica útil antes de tener uniones LATERAL . Ahora es simplemente lastre histórico.

Relacionado:

Existe la única nota sobre el problema en la documentación . No estoy seguro de si esto explica el comportamiento descrito o no. Quizás lo más importante es que dicho uso de la función está en desuso:

Actualmente, las funciones que devuelven conjuntos también se pueden invocar en la lista de selección de una consulta. Para cada fila que la consulta genera por sí misma, se invoca el conjunto que devuelve la función y se genera una fila de salida para cada elemento del conjunto de resultados de la función. Sin embargo, tenga en cuenta que esta capacidad está en desuso y podría eliminarse en futuras versiones.

No puedo encontrar ninguna documentación para esto. Sin embargo, puedo describir el comportamiento que observo.

Las funciones generadoras del conjunto devuelven un número finito de filas. Postgres parece ejecutar las funciones generadoras de conjuntos hasta que todas estén en su última fila o, más probablemente, se detengan cuando todos vuelvan a sus primeras filas. Técnicamente, este sería el múltiplo menos común (LCM) de las longitudes de la serie.

No estoy seguro de por qué este es el caso. Y, como digo en un comentario, creo que es mejor generalmente poner las funciones en la cláusula from .