Re: UNNEST with multiple args, and TABLE with multiple funcs

On Thu, Nov 21, 2013 at 12:22:57PM -0500, Tom Lane wrote:
> Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> > If there isn't a reasonable syntax alternative to TABLE(...) for the
> > multiple functions case, then frankly I think we should go ahead and
> > burn compatibility with a spec feature which appears to be of negative
> > value.
>
> TBH, I'm getting close to that conclusion too. The more I look at the
> spec, the more I think it must be a mistake, or else I'm somehow reading
> it wrong, because it sure makes no sense for them to have invented
> something that's just an alternative and less-clear syntax for a feature
> they already had.
>
> Can anyone who's following this thread check the behavior of Oracle or
> DB2 to see if they interpret TABLE() the way I think the spec says?

Oracle's closest analog to SQL-standard arrays is its "varray" feature, and
TABLE() behaves like our UNNEST() for those. Note that Oracle has no UNNEST.

*SQL> CREATE OR REPLACE TYPE intarray AS VARRAY(100) OF int;
* 2 /

Type created.

*SQL> select * from table(intarray(1,2,3));

COLUMN_VALUE
------------
1
2
3

I don't have a DB2 installation within reach, but its documentation implies
that UNNEST and TABLE are interchangeable:

http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0055064.html

If anyone can test "select x from table(trim_array(array[1,2,3], 1)) as t(x);"
in DB2 and provide the output, that would be helpful.

On Thu, Nov 21, 2013 at 10:07:53AM -0500, Tom Lane wrote:
> The whole business with the spec's reading of TABLE() seems bizarre.
> AFAICS there is nothing about TABLE(foo()) that you can't get with
> greater clarity by writing UNNEST(foo()) instead. And it's not like
> it's a legacy feature --- SQL99 has single-argument UNNEST() but not
> TABLE(), so why'd they add TABLE() later, and why'd they make it a
> strict subset of what UNNEST() can do? I can't escape the suspicion
> that I'm misreading the spec somehow ... but the text seems perfectly
> clear.

That's how I read it, too. My hypothesis is that the standard adopted TABLE()
to rubber-stamp Oracle's traditional name for UNNEST().

On Wed, Nov 20, 2013 at 03:07:17PM -0500, Tom Lane wrote:
> I do like the basic concept of this syntax, but I think it's a serious
> error to appropriate the TABLE() spelling for something that doesn't
> agree with the spec's semantics for that spelling. We need to spell it
> some other way.

I realize you may have changed your mind later in the thread, but I share this
original sentiment. I think of this feature as optimization of and syntactic
sugar for full outer joins on ordinality columns. Compare these queries:

select * from table(generate_series(1,3), generate_series(2,5))
with ordinality as t(g1,g2);
select g1, g2, ordinality from generate_series(1,3) with ordinality as g1
full join generate_series(2,5) with ordinality as g2 using (ordinality);

The new syntax is limited to function calls, but I could imagine extending it
to take arbitrary subqueries (or, at the cost of inviting folks to depend on
subject-to-change row order, arbitrary from_item's). If this project were
just starting, I'd probably favor optimizing ordinality joins in the planner
rather than introducing special syntax to request the optimization. I don't
claim that's sufficiently better to justify the extensive rework it would now
entail, though. Therefore, I propose merely changing the syntax to "TABLE FOR
ROWS (...)". As a comparison, think of the standard syntax as "TABLE [FOR
ELEMENTS] (...)". Here is a longer list of conflict-free syntax choices that
I considered before settling on that one:

FUNCTIONS TABLE
FUNCTIONS TO TABLE
ROWS FOR
ROWS FOR EACH
ROWS FROM
ROWS FROM EACH
ROWS FROM FUNCTIONS
ROWS TO TABLE
TABLE (ROWS OF f0(), ROWS OF f1())
TABLE BY FUNCTIONS
TABLE BY ROW
TABLE FOR
TABLE FOR FUNCTION ROWS
TABLE FOR FUNCTIONS
TABLE FOR ROWS
TABLE FOR ROWS OF
TABLE FROM
TABLE FROM FUNCTION ROWS
TABLE FROM FUNCTIONS
TABLE OF
TABLE OF EACH
TABLE OF FUNCTION ROWS
TABLE OF FUNCTIONS
TABLE OF ROWS
TABLE OF ROWS OF EACH

Thanks,
nm

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com