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]> > 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
