Support unnest multiple array columns

Hi Calcite experts: In Presto, multiple array columns could be flattened "together", as Presto docs shows: SELECT numbers, animals, n, aFROM ( > VALUES > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), > (ARRAY[7, 8, 9], ARRAY['cow', 'pig']

Re: Support unnest multiple array columns

I haven't tried it, but I'd guess that you join to each separately, i.e. take the cartesian product: SELECT numbers, animals, n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST (numbers) AS t1 (

Re: Support unnest multiple array columns

Hi Julian, Thanks for the reply. Tried it with SqlToRelConverter and it seems to be Cartesian product but not the desired behavior: > @Test public void testUnnestArrayPlan() { > final String sql = "select d.deptno, e2.empno, e3.detail\n" > + "from dept_nested as d,\n" > + " UN

Re: Support unnest multiple array columns

It’s very possible that there is a bug. This functionality is not well tested. Can you please log it. I don’t particularly see the need for Presto’s SQL extension but I have no objection to adding it under a conformance flag. I believe we can implement it as a rewrite (i.e. syntactic sugar). Le

Re: Support unnest multiple array columns

On Wed, Feb 12, 2020 at 11:30 AM Julian Hyde wrote: > It’s very possible that there is a bug. This functionality is not well > tested. Can you please log it. > Will do. I also test the same thing in Presto: (CROSS JOIN UNNEST(array) as a CROSS JOIN UNNEST(another_array) as b) and it also yields a

Re: Support unnest multiple array columns

> Hmm, man, there seems no UNNEST in Hive. But maybe it's introduced in a new > version. I was mistaken. It’s PostgreSQL that has an UNNEST function and allows it in the SELECT clause [1]. Hive allows table functions in the SELECT clause, which has similar problems of multiplying rows [2].

Re: Support unnest multiple array columns

Could someone take a look at this tiny PR: https://github.com/apache/calcite/pull/1811 ? Thanks in advance! On Wed, Feb 12, 2020 at 12:46 PM wmy7ymw wrote: > Got it. Good to learn these usages. Thanks for sharing. > > On Wed, Feb 12, 2020 at 12:22 PM Julian Hyde wrote: > >> >> >> > Hmm, man, th

Re: Support unnest multiple array columns

I don’t know whether you are joking, but that is not a small PR. > On Feb 24, 2020, at 10:34 PM, wmy7ymw wrote: > > Could someone take a look at this tiny PR: > https://github.com/apache/calcite/pull/1811 > ? Thanks in advance! > > On Wed, Feb 12,

Re: Support unnest multiple array columns

Oh, maybe not well calibrated. Let me know if  there is anything I could do to help on review. Thanks! On Feb 24, 2020, 10:53 PM -0800, Julian Hyde , wrote: > I don’t know whether you are joking, but that is not a small PR. > > > > On Feb 24, 2020, at 10:34 PM, wmy7ymw wrote: > > > > Could someo