[
https://issues.apache.org/jira/browse/PHOENIX-953?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14654280#comment-14654280
]
James Taylor edited comment on PHOENIX-953 at 8/4/15 8:15 PM:
--------------------------------------------------------------
Questions from [~ram_krish] & [~Dumindux]:
{quote}
-> What is the initial scope of UNNEST we will target?
-> As I can read from the description UNNEST can be used a full table like
structure for doing JOINS etc.
-> There can be cases like
SELECT ARRAY(SELECT DISTINCT UNNEST(stuff) FROM Foo where id = 1);
-> The UNNEST without 'FROM' clause.
Coming to the implementation for a SELECT UNNEST (ARRAY) from TABLE, we will
not implement UNNEST as a function I believe.
We will add an entry in the grammar file and have an expression for UNNEST and
for the UNNEST expression we may need a new type of compilation and a new type
of result iterator on the Column Projector right?
So the KV that is getting returned back to the client ( I mean per KV) we will
need to iterate the value part of it. Am not sure whether the normal iterators
would do this work.
{quote}
Yes, we'll need to add UNNEST support to the grammar on par with other
expressions in the term rule:
{code}
term returns [ParseNode ret]
: e=literal_or_bind { $ret = e; }
| field=identifier { $ret = factory.column(null,field,field); }
| UNNEST LPAREN e=expression RPAREN { $ret = factory.unnest(e); }
...
{code}
We should be able to handle UNNEST purely at the compile layer. It should be
equivalent to the following (which should already work):
{code}
SELECT ( SELECT a[1],a[2],a[3],a[4]... )
{code}
The inner select will return one row per array element. We may have a special
QueryPlan derived from EmptyTableQueryPlan specific for this case (like
UnnestArrayQueryPlan) that'll simply project all the elements of the array
expression from the UNNEST call.
I don't think any runtime changes will be necessary. If there are places in
which we don't support this, then those can be fixed in future work (and/or
when we move to Calcite).
First cut, I don't think we need to support the DISTINCT keyword. Not sure if
our SELECT without FROM clause supports that currently. In theory, you might be
able to put it in an outer SELECT instead.
Thoughts, [~maryannxue]?
was (Author: jamestaylor):
Questions from [~ram_krish] & [~Dumindux]:
{quote}
-> What is the initial scope of UNNEST we will target?
-> As I can read from the description UNNEST can be used a full table like
structure for doing JOINS etc.
-> There can be cases like
SELECT ARRAY(SELECT DISTINCT UNNEST(stuff) FROM Foo where id = 1);
-> The UNNEST without 'FROM' clause.
Coming to the implementation for a SELECT UNNEST (ARRAY) from TABLE, we will
not implement UNNEST as a function I believe.
We will add an entry in the grammar file and have an expression for UNNEST and
for the UNNEST expression we may need a new type of compilation and a new type
of result iterator on the Column Projector right?
So the KV that is getting returned back to the client ( I mean per KV) we will
need to iterate the value part of it. Am not sure whether the normal iterators
would do this work.
{quote}
Yes, we'll need to add UNNEST support to the grammar on par with other
expressions in the term rule:
{code}
term returns [ParseNode ret]
: e=literal_or_bind { $ret = e; }
| field=identifier { $ret = factory.column(null,field,field); }
| UNNEST LPAREN e=expression RPAREN { $ret = factory.unnest(e); }
...
{code}
We should be able to handle UNNEST purely at the compile layer. It should be
equivalent to the following (which should already work):
{code}
SELECT ( SELECT a[1],a[2],a[3],a[4]... )
{code}
The inner select will return one row per array element. We may have a special
QueryPlan derived from EmptyTableQueryPlan specific for this case (like
UnnestArrayQueryPlan) that'll simply project all the elements of the array
expression from the UNNEST call.
I don't think any runtime changes will be necessary. If there are places in
which we don't support this, then those can be fixed in future work (and/or
when we move to Calcite).
Thoughts, [~maryannxue]?
> Support UNNEST for ARRAY
> ------------------------
>
> Key: PHOENIX-953
> URL: https://issues.apache.org/jira/browse/PHOENIX-953
> Project: Phoenix
> Issue Type: Sub-task
> Reporter: James Taylor
> Assignee: Dumindu Buddhika
>
> The UNNEST built-in function converts an array into a set of rows. This is
> more than a built-in function, so should be considered an advanced project.
> For an example, see the following Postgres documentation:
> http://www.postgresql.org/docs/8.4/static/functions-array.html
> http://www.anicehumble.com/2011/07/postgresql-unnest-function-do-many.html
> http://tech.valgog.com/2010/05/merging-and-manipulating-arrays-in.html
> So the UNNEST is a way of converting an array to a flattened "table" which
> can then be filtered on, ordered, grouped, etc.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
