sql: address discrepancy with pg and SRFs

[justinj]

Postgres:

justin=# select generate_series(1,2), generate_series(1,2);
 generate_series | generate_series
-----------------+-----------------
               1 |               1
               2 |               2
(2 rows)

Us:

root@:26257/> select generate_series(1,2), generate_series(1,2);
+-----------------+-----------------+
| generate_series | generate_series |
+-----------------+-----------------+
|               1 |               1 |
|               1 |               2 |
|               2 |               1 |
|               2 |               2 |
+-----------------+-----------------+
(4 rows)

See https://stackoverflow.com/questions/39863505/what-is-the-expected-behaviour-for-multiple-set-returning-functions-in-select-cl/39864815#39864815

cc @knz @benesch

[knz]

(Paraphrasing the SO post)

Multiple SRFs in the same render list (either within a single scalar expr or across scalar exprs) should be migrated to a single join operand in the FROM clause, that zip the values togethers into a single table (filling with NULLs where some SRFs produce fewer rows than others).

pg supports this with a special lateral join called LATERAL ROWS FROM (...) (it's not really a join).

[knz]

Note that there is yet another case, not covered by this issue:

>  SELECT generate_series(generate_series(1, 3), 3);

Still unclear to me what this does. But it does not appear to use the general lateral join with the set projection that the side-by-side SRFs use.

[benesch]

In that case you just unnest the SRFs into multiple joins:

SELECT b FROM generate_series(1, 3) a, generate_series(a, 3) b;

[knz]

"just". Humpf.

Thanks Nikhil for checking though. Consider this funny beast:

SELECT generate_series(generate_series(1, 3)+generate_series(10,20), 3);

According to your rule and the one we found out earlier, does this gets translated to:

SELECT b 
  FROM (SELECT @1+@2 AS start
          FROM (ROWS FROM (generate_series(1,3), generate_series(10,20)))) AS a, 
       LATERAL generate_series(a.start, 3) AS b

?

[knz]

Note: ROWS FROM (a,b,c) will create a single table with the "zip" of the generators a,b,c

[benesch]

I think so! I had to rewrite your example like this to get it to work:

SELECT f2
  FROM (SELECT a + b AS start
          FROM ROWS FROM (generate_series(1,3), generate_series(10,20)) _ (a, b)) AS f1, 
       LATERAL generate_series(f1.start, 3) AS f2

Your example also doesn't produce any data. It's more interesting with a bigger upper bound:

SELECT generate_series(generate_series(1, 3)+generate_series(10,20), 20);
SELECT f2
  FROM (SELECT a + b AS start
          FROM ROWS FROM (generate_series(1,3), generate_series(10,20)) _ (a, b)) AS f1, 
       LATERAL generate_series(f1.start, 20) AS f2;

[knz]

yeah ok. This is a hairy transform. I don't like it, although I fear we'll have to implement it eventually.

[knz]

thanks @benesch, props for figuring it out.

[knz]

@awoods187 we need to table that on the roadmap sometime. Perhaps it will slip to 2.2 (unless a free friday takes care of it)

[knz]

I'd say size M

[knz]

Update: the relationship between SRFs and ROWS FROM identified above is incorrect. See #26234 for details.

[benesch]

omg ❤️ ❤️ ❤️