Re: RelBuilder API on the top of a view

[Andrei Sereda]

Created JIRA: https://issues.apache.org/jira/browse/CALCITE-2441
PR (for failing test): https://github.com/apache/calcite/pull/774

I'm happy to fix it if you think it can be done in similar manner to
ViewExpanderImpl (ie I'm not missing anything).



On Thu, Aug 2, 2018 at 7:02 PM Andrei Sereda  wrote:

> I'll log a bug. To me it makes more sense to add it to RelBuilderTest
> since view can be created programatically using ViewTable.viewMacro().
>
>  Do you think implementation can be similar to (inspired from)
> PlannerImpl.ViewExpanderImpl
> 
> ?
>
> On Thu, Aug 2, 2018 at 6:40 PM Julian Hyde  wrote:
>
>> There’s no reason why it shouldn’t work, except that it hasn’t been
>> tested. It looks as if the implementation is trying to do the right thing —
>> parse the view, expand it to relational algebra, and push that algebra onto
>> RelBuilder’s stack — but it fails because the context is a dumb
>> implementation that doesn’t know how to expand views.
>>
>> Can you log a bug, please? I’m not sure whether the test case would be in
>> RelBuilderTest (using a schema that contains a view) or ServerTest (where
>> you could execute CREATE VIEW and then start a RelBuilder on the
>> connection).
>>
>> Julian
>>
>>
>>
>>
>> > On Aug 2, 2018, at 2:57 PM, Andrei Sereda  wrote:
>> >
>> > Hello,
>> >
>> > I was wondering if one can use relational algebra
>> >  API on the top of
>> existing
>> > view (or only tables are supported) ? Below is an example which fails
>> for
>> > me :
>> >
>> > // suppose one creates a view as follows
>> > CREATE VIEW view AS select * from elastic.docs;
>> >
>> > @Test
>> > public void sql() {
>> >  // works when using SQL
>> >  CalciteAssert.that()
>> >  .with(newConnectionFactory())
>> >  .query("select * from view")
>> >  .returnsCount(1);
>> > }
>> >
>> >
>> > /**
>> > * Example when querying calcite view using {@link RelBuilder} api fails.
>> > * It is working fine when using SQL or when using RelBuilder on a
>> > table (not view).
>> > */
>> > @Test
>> > public void relBuilder() throws Exception {
>> >  Connection connection = newConnectionFactory().createConnection();
>> >  SchemaPlus root =
>> connection.unwrap(CalciteConnection.class).getRootSchema();
>> >  FrameworkConfig config =
>> > Frameworks.newConfigBuilder().defaultSchema(root).build();
>> >  // querying a view using RelBuilder fails
>> >  RelBuilder builder = RelBuilder.create(config).scan("VIEW");
>> >  // querying directly a table works
>> >  // RelBuilder builder = RelBuilder.create(config).scan("elastic",
>> "docs");
>> >
>> >  int count = 0;
>> >  try (PreparedStatement stm =
>> > connection.unwrap(RelRunner.class).prepare(builder.build());
>> >   ResultSet rset = stm.executeQuery() ) {
>> >while (rset.next()) {
>> >  count++;
>> >}
>> >  }
>> >
>> >  assertEquals(1, count);
>> > }
>> >
>> > Exception
>> >
>> > Caused by: java.lang.UnsupportedOperationException
>> >   at
>> org.apache.calcite.plan.RelOptUtil$4.expandView(RelOptUtil.java:2805)
>> >   at
>> org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:124)
>> >   ... 39 more
>> >
>> >
>> > Anything I'm doing wrong ?
>> >
>> > Many thanks,
>> > Andrei.
>>
>>

Re: RelBuilder API on the top of a view

[Andrei Sereda]

I'll log a bug. To me it makes more sense to add it to RelBuilderTest since
view can be created programatically using ViewTable.viewMacro().

 Do you think implementation can be similar to (inspired from)
PlannerImpl.ViewExpanderImpl

?

On Thu, Aug 2, 2018 at 6:40 PM Julian Hyde  wrote:

> There’s no reason why it shouldn’t work, except that it hasn’t been
> tested. It looks as if the implementation is trying to do the right thing —
> parse the view, expand it to relational algebra, and push that algebra onto
> RelBuilder’s stack — but it fails because the context is a dumb
> implementation that doesn’t know how to expand views.
>
> Can you log a bug, please? I’m not sure whether the test case would be in
> RelBuilderTest (using a schema that contains a view) or ServerTest (where
> you could execute CREATE VIEW and then start a RelBuilder on the
> connection).
>
> Julian
>
>
>
>
> > On Aug 2, 2018, at 2:57 PM, Andrei Sereda  wrote:
> >
> > Hello,
> >
> > I was wondering if one can use relational algebra
> >  API on the top of
> existing
> > view (or only tables are supported) ? Below is an example which fails for
> > me :
> >
> > // suppose one creates a view as follows
> > CREATE VIEW view AS select * from elastic.docs;
> >
> > @Test
> > public void sql() {
> >  // works when using SQL
> >  CalciteAssert.that()
> >  .with(newConnectionFactory())
> >  .query("select * from view")
> >  .returnsCount(1);
> > }
> >
> >
> > /**
> > * Example when querying calcite view using {@link RelBuilder} api fails.
> > * It is working fine when using SQL or when using RelBuilder on a
> > table (not view).
> > */
> > @Test
> > public void relBuilder() throws Exception {
> >  Connection connection = newConnectionFactory().createConnection();
> >  SchemaPlus root =
> connection.unwrap(CalciteConnection.class).getRootSchema();
> >  FrameworkConfig config =
> > Frameworks.newConfigBuilder().defaultSchema(root).build();
> >  // querying a view using RelBuilder fails
> >  RelBuilder builder = RelBuilder.create(config).scan("VIEW");
> >  // querying directly a table works
> >  // RelBuilder builder = RelBuilder.create(config).scan("elastic",
> "docs");
> >
> >  int count = 0;
> >  try (PreparedStatement stm =
> > connection.unwrap(RelRunner.class).prepare(builder.build());
> >   ResultSet rset = stm.executeQuery() ) {
> >while (rset.next()) {
> >  count++;
> >}
> >  }
> >
> >  assertEquals(1, count);
> > }
> >
> > Exception
> >
> > Caused by: java.lang.UnsupportedOperationException
> >   at
> org.apache.calcite.plan.RelOptUtil$4.expandView(RelOptUtil.java:2805)
> >   at
> org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:124)
> >   ... 39 more
> >
> >
> > Anything I'm doing wrong ?
> >
> > Many thanks,
> > Andrei.
>
>

Re: RelBuilder API on the top of a view

[Julian Hyde]

There’s no reason why it shouldn’t work, except that it hasn’t been tested. It 
looks as if the implementation is trying to do the right thing — parse the 
view, expand it to relational algebra, and push that algebra onto RelBuilder’s 
stack — but it fails because the context is a dumb implementation that doesn’t 
know how to expand views.

Can you log a bug, please? I’m not sure whether the test case would be in 
RelBuilderTest (using a schema that contains a view) or ServerTest (where you 
could execute CREATE VIEW and then start a RelBuilder on the connection).

Julian




> On Aug 2, 2018, at 2:57 PM, Andrei Sereda  wrote:
> 
> Hello,
> 
> I was wondering if one can use relational algebra
>  API on the top of existing
> view (or only tables are supported) ? Below is an example which fails for
> me :
> 
> // suppose one creates a view as follows
> CREATE VIEW view AS select * from elastic.docs;
> 
> @Test
> public void sql() {
>  // works when using SQL
>  CalciteAssert.that()
>  .with(newConnectionFactory())
>  .query("select * from view")
>  .returnsCount(1);
> }
> 
> 
> /**
> * Example when querying calcite view using {@link RelBuilder} api fails.
> * It is working fine when using SQL or when using RelBuilder on a
> table (not view).
> */
> @Test
> public void relBuilder() throws Exception {
>  Connection connection = newConnectionFactory().createConnection();
>  SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();
>  FrameworkConfig config =
> Frameworks.newConfigBuilder().defaultSchema(root).build();
>  // querying a view using RelBuilder fails
>  RelBuilder builder = RelBuilder.create(config).scan("VIEW");
>  // querying directly a table works
>  // RelBuilder builder = RelBuilder.create(config).scan("elastic", "docs");
> 
>  int count = 0;
>  try (PreparedStatement stm =
> connection.unwrap(RelRunner.class).prepare(builder.build());
>   ResultSet rset = stm.executeQuery() ) {
>while (rset.next()) {
>  count++;
>}
>  }
> 
>  assertEquals(1, count);
> }
> 
> Exception
> 
> Caused by: java.lang.UnsupportedOperationException
>   at org.apache.calcite.plan.RelOptUtil$4.expandView(RelOptUtil.java:2805)
>   at 
> org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:124)
>   ... 39 more
> 
> 
> Anything I'm doing wrong ?
> 
> Many thanks,
> Andrei.

RelBuilder API on the top of a view

[Andrei Sereda]

Hello,

I was wondering if one can use relational algebra
 API on the top of existing
view (or only tables are supported) ? Below is an example which fails for
me :

// suppose one creates a view as follows
CREATE VIEW view AS select * from elastic.docs;

@Test
public void sql() {
  // works when using SQL
  CalciteAssert.that()
  .with(newConnectionFactory())
  .query("select * from view")
  .returnsCount(1);
}


/**
 * Example when querying calcite view using {@link RelBuilder} api fails.
 * It is working fine when using SQL or when using RelBuilder on a
table (not view).
 */
@Test
public void relBuilder() throws Exception {
  Connection connection = newConnectionFactory().createConnection();
  SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();
  FrameworkConfig config =
Frameworks.newConfigBuilder().defaultSchema(root).build();
  // querying a view using RelBuilder fails
  RelBuilder builder = RelBuilder.create(config).scan("VIEW");
  // querying directly a table works
  // RelBuilder builder = RelBuilder.create(config).scan("elastic", "docs");

  int count = 0;
  try (PreparedStatement stm =
connection.unwrap(RelRunner.class).prepare(builder.build());
   ResultSet rset = stm.executeQuery() ) {
while (rset.next()) {
  count++;
}
  }

  assertEquals(1, count);
}

Exception

Caused by: java.lang.UnsupportedOperationException
at org.apache.calcite.plan.RelOptUtil$4.expandView(RelOptUtil.java:2805)
at 
org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:124)
... 39 more


Anything I'm doing wrong ?

Many thanks,
Andrei.