A slight correction. It is not valid to specify both “lateral” and “unnest”. Lateral name-resolution is implicit if specify unnest.
Julian > On May 3, 2016, at 3:28 PM, Julian Hyde <[email protected]> wrote: > > In question 1, you have found a bug. When you use “unnest” it should > implicitly include “lateral”. Thus your query should be the same as > > select a.*, b.* from table1 as a, lateral unnest(a.items) as b; > > or > > select a.*, b.* from table1 as a cross join lateral unnest(a.items) as b; > > Why do you care about LATERAL? It tells the validator to allow references > tables to the left of it in the FROM clause, in this case “a”. > > I have logged https://issues.apache.org/jira/browse/CALCITE-1225 > <https://issues.apache.org/jira/browse/CALCITE-1225>. > > In question 2, the query should not work. The expression inside unnest is a > scalar expression (the kind of thing you would include in your select or > where clause) not a relational expression (the kind of thing you would > include in your from clause). And that makes sense because unnest works on > arrays and multiset column values. > > Rather than > > select * from unnest(schema1.table1.items); > > you should write > > select u.* from schema1.table1 as t, unnest(t.items) as u; > > Julian > > >> On May 3, 2016, at 1:29 PM, Johannes Schulte <[email protected] >> <mailto:[email protected]>> wrote: >> >> Hi, >> >> I am trying to build an adapter for avro files, analogous to the csv >> example. While scalar types work fine, I am having trouble getting an >> unnest query to run. The example I found in the code is something like this: >> >> select a.*, b.* from table1 as a, unnest(a.items) as b; >> >> Two questions: >> >> 1) >> I always got the error message: "table A not found". It seems that the >> corresponding test is currently set to ignore, and if ran it yields the >> same error >> >> org.apache.calcite.test.JdbcTest.testUnnestArrayColumn() >> >> 2) As a way to proceed further i now check for >> >> select * from unnest(schema1.table1.items) >> >> but this also gives a table not found error message. It seems that the >> >> org.apache.calcite.sql.validate.SqlValidatorImpl >> >> always looks for schemas and tables with a "CATALOG" prefix and never finds >> the table / schema. This might be related to my knowledge of the overall >> architecture, but i am following the csv examples. >> >> Any help would be appreciated! >> >> Johannes >
