[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16262041#comment-16262041 ] Christian Beikov commented on CALCITE-1940: --- I haven't yet looked into the JdbcConvention issue and I'm not sure I will have time for that before mid-december, so don't think this will make it into the release. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16258809#comment-16258809 ] Julian Hyde commented on CALCITE-1940: -- [~christian.beikov], Is there any chance this change (or part of it) will be ready for release 1.15? > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16173843#comment-16173843 ] Julian Hyde commented on CALCITE-1940: -- I've just pushed commit https://github.com/julianhyde/calcite/commit/b17a2aa068d07f5a3483099e9efcc2252e348a55 to my branch https://github.com/julianhyde/calcite/tree/1940-dialect-sequence and I think the process of creating SqlDialect with sequence support is in good shape. In particular I replaced SequenceSupportResolver, which was frankly difficult to understand, with OptionalSequenceSupport, which is more concrete and only need to be implemented for one dialect (SQL Server). I don't intend to look into the JdbcConvention issues any time soon. But anyway, commit b17a2aa0 would be a good point to start when you next start work. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16172813#comment-16172813 ] Christian Beikov commented on CALCITE-1940: --- I looked through your changes and they look good to me so far, but as I mentioned a few times, the sequence PR isn't ready yet :D I am still struggling with getting the sequence call being executed with the JdbcConvention because of (wrong?) planning, but maybe you have some time to take a look and find a solution for this. I'm a bit busy with other stuff now until the weekend anyway, so just tell me when you are ready so that I can pick up work from that point. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16160314#comment-16160314 ] Christian Beikov commented on CALCITE-1940: --- [~julianhyde] I don't think it makes sense as that would require quite a rewrite and might not be fully what you agree with either. Apart from that, the sequence calls are still not pushed down to the DBMS yet so this is still a bit away from being done. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16159255#comment-16159255 ] Julian Hyde commented on CALCITE-1940: -- It seems that we cannot fix this case (CALCITE-1940) without first doing CALCITE-1913 (make SqlDialect more extensible). [~christian.beikov], Do you think it makes sense to re-organize PR 514 into commits that fix 1913 followed by commits that fix 1940? It would make it easier to review. The PR is already large, and I disagree with some parts but not others. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16157306#comment-16157306 ] Julian Hyde commented on CALCITE-1940: -- Yes, I meant 1913. Thanks for the correction. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16157225#comment-16157225 ] Chris Baynes commented on CALCITE-1940: --- [~julianhyde] I think you mean [CALCITE-1913|https://issues.apache.org/jira/browse/CALCITE-1913] where there's a discussion about including the db version in the dialect. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16156072#comment-16156072 ] Christian Beikov commented on CALCITE-1940: --- The idea is to encapsulate all the dialect specifics and at the same time making the system pluggable. By doing the non-standard unparsing stuff through dialect methods, it is possible for consumers of calcite to define a custom dialect targeted for their needs. Right now, only sequence fetch rendering is handled that way, but I plan to remove all checks against the database product of a dialect like e.g. {{dialect.getDatabaseProduct() == DatabaseProduct.MYSQL}} that are currently spread around the code base. I'd rather have methods that return whether a dialect supports a specific feature or handle rendering/unparsing an expression, clause or whole query than checks in the main code base that only work for specific database products. One of the most recent changes of supported features is MySQL since version 8 will have support for e.g. CTEs. How would the current code handle that? The way I am proposing it, the MysqlDialect can self configure during instantiation, detect based on the version if a feature is supported and make the capability accessible to the code that handles the unparsing. Apart from that, I'd still need to know how to actually make the planner choose the correct plan. Since there is no Jdbc input to the Project node, the query isn't rendered with the JdbcConvention. Any idea how I could achieve that? > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16152922#comment-16152922 ] Christian Beikov commented on CALCITE-1940: --- No worries. I didn't bet on this getting into 1.14 especially since I wanted to tackle the sql dialect specific handling as part of this. The lifecycle doesn't change, it's still bound to the schema, the handler was just merged into the dialect and dialect is now self configuring in the constructor. This is only a first step, so please don't look to deep into that yet. For now, I would just like to make a {{RexSeqCall}} be executed with the appropriate convention. Currently, the call is implemented via RexImpTable and I have no idea how to force that expression be implemented by the JdbcConvention. Any idea how to do that? After that, I would replace all the static {{DatabaseProduct}} usages with appropriate methods in the dialect to handle implementing the SQL translation. When I'm done with that, you can review it as a whole, but the current state is still incomplete. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16151573#comment-16151573 ] Christian Beikov commented on CALCITE-1940: --- I introduced a custom RexCall subclass since I need to store the RelNode representing the sequence table, but now I'd need to enforce the usage of the "correct" plan. Currently the optimizer generates plans that use the linq4j implementation which is "wrong". Maybe you could take a look at the PR and tell me how to do that best? > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16151561#comment-16151561 ] Christian Beikov commented on CALCITE-1940: --- Ok I understand how that works now, I was just confused because the SQL string was generated(apparently for no reason?) within the Prepare class. Anyway, I'm having a different problem now with the sequence implementation. The plan looks like {{LogicalProject(NEXT VALUE FOR S2) -> LogicalValues( (0) )}} and during rule registration, no JDBC rules are registered since there is no JDBC relation used in RelNode. Any hints on how I can achieve that the plan gets converted to {{JdbcProject(NEXT VALUE FOR S2) -> JdbcValues( (0) )}}? > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16149174#comment-16149174 ] Chris Baynes commented on CALCITE-1940: --- I tried this out with postgres and noticed a problem - postgres (unlike other dbs) already returns a list of sequences in the jdbc metadata. So the table map building fails because of duplicates. It probably makes sense to just always skip {{SEQUENCE}} and {{TEMPORARY_SEQUENCE}} in the loop, and only get them from the dialect sequence information. I can't really comment right now on your dialect handling improvement, since I'm still not sure why the dialect handling here needs something different than what already exists. The dialect handlers are already able to push-down calls to different dbs as long as the planner finds the right plan. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16148807#comment-16148807 ] Christian Beikov commented on CALCITE-1940: --- I need the dialect since I have to call the unparse method on it. If we have a query like e.g. "select next value for s1.seq, next value for s2.seq" and s1 and s2 are schemas that have different dialects, I have to unparse the sequence call once for the dialect of s1 and once for s2. I somehow need to know in the {{SqlWriter}} which dialect a schema uses so that I can call the appropriate unparse method. Could you([~chris-baynes]) maybe comment on the mailing list thread "JdbcAdapter Sort Limit and Offset" which is somewhat related in the overall discussion about how to handle the different dialect quirks. I'd like to essentially merge the {{Handler}} and other methods into a DBMS specific {{SqlDialect}} implementation and do the dialect construction through a configurable factory. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16148071#comment-16148071 ] Christian Beikov commented on CALCITE-1940: --- Depends on what you want changed. I was kind of waiting for a comment from [~chris-baynes] on how to do that best. The part about retrieving the dialect during unparsing is still unclear to me. How should that be done? Currently I am, as you stated, wrongly assuming a single dialect. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16147982#comment-16147982 ] Julian Hyde commented on CALCITE-1940: -- Do you think we could get this done for 1.14? OK if not. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16139487#comment-16139487 ] Christian Beikov commented on CALCITE-1940: --- Seems I only use {{JdbcTable.jdbcTableName}} to get a proper toString representation for the sequence. I wasn't sure how to get the dialect during unparsing and since the writer already had a getter, I thought it might be appropriate to just get the proper one in. I suppose the writer should be extended to be able to return a dialect for a schema name? I'll adapt the PR to the requested changes and let you know when it's done. > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16139445#comment-16139445 ] Julian Hyde commented on CALCITE-1940: -- Good idea. Reviewing https://github.com/apache/calcite/pull/514/commits/99e6ca890431d5f55be23924844f6d3f0058f364: * Why do you need {{JdbcTable.jdbcTableName}}? * The code in {{CalcitePrepareImpl}} that grabs a {{SqlDialect}} (in fact the idea of a unique {{SqlDialect}} for the whole query) is dubious. What the query is a union between a MySQL database and a PostgreSQL database? * I like the idea of {{interface Sequence}}, and also {{JdbcSequence extends JdbcTable implements Sequence}} is just right * We use camel case in class names, even those containing acronyms. Please rename {{DB2SequenceSupport}} to {{Db2SequenceSupport}}, {{MSSQLSequenceSupportResolver}} to {{MssqlSequenceSupportResolver}} * Don't use {{FakeUtil.newInternal}}; just use {{new RuntimeException}} Should {{SequenceSupport}} be rolled into {{Handler}}? {{SequenceSupport.unparseSequenceVal}} definitely fits into {{Handler.unparseCall}}. {{SequenceSupport.extract}} could go to either {{Handler}} or {{SqlDialect}}. [~chris-baynes], what do you think? > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1940) Implement dialect specific support for sequences
[ https://issues.apache.org/jira/browse/CALCITE-1940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16134428#comment-16134428 ] Christian Beikov commented on CALCITE-1940: --- See the following PR for a fix: https://github.com/apache/calcite/pull/514 > Implement dialect specific support for sequences > > > Key: CALCITE-1940 > URL: https://issues.apache.org/jira/browse/CALCITE-1940 > Project: Calcite > Issue Type: Improvement >Reporter: Christian Beikov >Assignee: Julian Hyde > > The Calcite parser and validator already supports sequences but the push down > to the JDBC level is currently limited. SInce sequences are not supported on > all DBMS every sql dialect should have the possibility to render it's own way > of extracting sequence information or incrementing the value. -- This message was sent by Atlassian JIRA (v6.4.14#64029)