Re: Sequence support

[Chris Baynes]

We had a problem with LogicalProject not being converted to its more
efficient version when using the RelBuilder. Perhaps something similar is
happening to you because of the way the sequence tables are defined.
Our fix was to override the createScan method on the TableScanFactory:
https://issues.apache.org/jira/browse/CALCITE-1328?focusedCommentId=16161248&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-16161248
Maybe that can help you figure out how to fix your issue.

On Fri, Sep 8, 2017 at 8:36 AM, Christian Beikov  wrote:

> Hey,
>
> I'm having some problems implementing CALCITE-1940 and wanted to ask you
> for advice about how to tackle it.
>
> A query like "select next value for seq1" where "seq1" is a sequence in
> e.g. PostgreSQL is will generate a query plan like "LogicalProject(NEXT
> VALUE FOR seq1) -> LogicalValues( (0) )".
>
> As far as I understood, the convention to use for executing such a query
> is determined based on the input. The planner now always chooses the
> "EnumerableValues" implementation for "LogicalValues" thus the statement is
> never pushed down to PostgreSQL.
>
> My question is, how would I be able to make the expression "NEXT VALUE FOR
> seq1", represented by the new subtype RexSeqCall, be executed on
> PostgreSQL? The sequence "seq1" is an entry in the JdbcSchema, but the
> current code doesn't seem to know that. During planning it seems only the
> input types are considered for the convention determination.
>
> I hope you understand the problem I am having. Has anyone ideas of how to
> fix that?
>
> I thought it might be possible to write a planner rule that finds such
> expressions and transforms them, but that's only one part I guess.
> Currently, the planner thinks a plan like "EnumerableProject(NEXT VALUE FOR
> seq1) -> EnumerableValues( (0) )" is better than "JdbcProject(NEXT VALUE
> FOR seq1) -> JdbcValues( (0) )" which it isn't. Any ideas how I could fix
> that?
>
> --
>
> Mit freundlichen Grüßen,
> 
> *Christian Beikov*
>

Re: Sequence support

[Julian Hyde]

I don’t know what you mean by “operations”. Most relational operators can be 
pushed down. And a few contributors are improving the JDBC adapter all the time.

Sequences are only partially done. Most of the work has been done by and for 
the Phoenix project, which implements sequences as tables. They are implemented 
in Phoenix’s extensions to Calcite, but Calcite only has parse+validate 
support. It would be nice if someone got them working so that they could be 
pushed down to a JDBC data source. (Not all DBMSes support them. I know Oracle 
does, but I don’t think MySQL or MSSQL do.)

You are correct. Transactions are not implemented. Again, we’d need to push 
them down to the source.

Julian



> On Aug 10, 2017, at 10:27 AM, Christian Beikov  
> wrote:
> 
> Hello,
> 
> I am new to Calcite but I already got quite far with integrating it into a 
> test project. Now I am a bit stuck and I am not sure what is happening.
> 
> I would like to use Calcite for data federation, but first I am trying to 
> simply put it between the application and the actual JDBC connection. I read 
> something on the website that operations aren't pushed down to JDBC schemas 
> yet? That would be unfortunate as that is something I actually need. Can you 
> confirm what the status of that is?
> 
> While trying to insert data, I noticed that SQL sequences don't seem to be 
> supported. Is there a special undocumented syntax for retrieving sequence 
> values? I tried "next value for SEQUENCE" as something in SqlKind was 
> suggesting that might be the appropriate syntax but it didn't work.
> 
> Finally, I also noticed that transactions don't seem to work properly. 
> Apparently CalciteMetaImpl#commit throws an UnsupportedOperationException? Am 
> I missing something here or is that a current limitation?
> 
> Thanks in advance!
> 
> -- 
> 
> Mit freundlichen Grüßen,
> 
> *Christian Beikov*