[jira] [Updated] (CALCITE-3067) Splunk Calcite adapter cannot parse right session keys from Splunk 7.2
[ https://issues.apache.org/jira/browse/CALCITE-3067?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-3067: Labels: pull-request-available (was: ) > Splunk Calcite adapter cannot parse right session keys from Splunk 7.2 > -- > > Key: CALCITE-3067 > URL: https://issues.apache.org/jira/browse/CALCITE-3067 > Project: Calcite > Issue Type: Bug > Components: splunk >Affects Versions: 1.19.0 > Environment: Splunk 7.2 on Mac OS 10.14 > Calcite 1.19 >Reporter: Shawn Chen >Assignee: Shawn Chen >Priority: Major > Labels: pull-request-available > Fix For: next > > Original Estimate: 2h > Remaining Estimate: 2h > > *SplunkConnectionImpl.java* successfully builds a connection to Splunk 7.2, > but it cannot parse the correct session key due to the outdated regular > expression in this class, therefore it gets HTTP 401 response after sending > further search commands to Splunk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Resolved] (CALCITE-2965) Implement string functions: REPEAT, SPACE, SOUNDEX, DIFFERENCE
[ https://issues.apache.org/jira/browse/CALCITE-2965?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Chunwei Lei resolved CALCITE-2965. -- Resolution: Fixed > Implement string functions: REPEAT, SPACE, SOUNDEX, DIFFERENCE > -- > > Key: CALCITE-2965 > URL: https://issues.apache.org/jira/browse/CALCITE-2965 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: Chunwei Lei >Assignee: Chunwei Lei >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 2h 40m > Remaining Estimate: 0h > > Some string functions including REPEAT, SPACE, SOUNDEX, DIFFERENCE are not > implemented now. It would be great if these functions can be implemented. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2965) Implement string functions: REPEAT, SPACE, SOUNDEX, DIFFERENCE
[ https://issues.apache.org/jira/browse/CALCITE-2965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841904#comment-16841904 ] Chunwei Lei commented on CALCITE-2965: -- Fixed in [https://github.com/apache/calcite/commit/4d04773054c5bda3b43c3dbeb2be01df19cbe3e3]. > Implement string functions: REPEAT, SPACE, SOUNDEX, DIFFERENCE > -- > > Key: CALCITE-2965 > URL: https://issues.apache.org/jira/browse/CALCITE-2965 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: Chunwei Lei >Assignee: Chunwei Lei >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 2h 40m > Remaining Estimate: 0h > > Some string functions including REPEAT, SPACE, SOUNDEX, DIFFERENCE are not > implemented now. It would be great if these functions can be implemented. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Resolved] (CALCITE-3074) Move MySQL's JSON operators to SqlLibraryOperators
[ https://issues.apache.org/jira/browse/CALCITE-3074?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Hongze Zhang resolved CALCITE-3074. --- Resolution: Fixed Fixed in [ca92ce27ce9f9a40d755a165041d64538a467a10|https://github.com/apache/calcite/commit/ca92ce27ce9f9a40d755a165041d64538a467a10]. Thanks for your review, [~hyuan]! > Move MySQL's JSON operators to SqlLibraryOperators > -- > > Key: CALCITE-3074 > URL: https://issues.apache.org/jira/browse/CALCITE-3074 > Project: Calcite > Issue Type: Sub-task >Reporter: Hongze Zhang >Assignee: Hongze Zhang >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 20m > Remaining Estimate: 0h > > This is a follow-up to CALCITE-2846. We should move the instantiating logic > to SqlLibraryOperators and deprecated old ones that are in std operator table. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-3075) Evaluate Travis CI for Windows build
[ https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841867#comment-16841867 ] Hongze Zhang edited comment on CALCITE-3075 at 5/17/19 3:20 AM: Thanks Kevin and Francis! I somehow missed the talk in that thread :), sorry about that. And looks like it is been a time (> 5 months?) after that talk. May be I can help to test the Travis Windows build on my personal branch of Calcite/Avatica first, to see if it is enough to be used in production. By the way, [~krisden] - are there some known issues of the new Windows CI you have found last time? Maybe we can double check if they get fixed/improved at this time. was (Author: zhztheplayer): Thanks Kevin and Francis! I somehow missed the talk in that thread :), sorry about that. And looks like it is been a time (> 5 months?) after that talk. May be I can help to test the Travis Windows build on my personal branch of Calcite/Avatica first, to see if it is enough to be used in production. By the way, [~krisden] - are there some known issues of the new Windows CI you have found last time? Maybe we can double check if they get fixed/improved at the time. > Evaluate Travis CI for Windows build > > > Key: CALCITE-3075 > URL: https://issues.apache.org/jira/browse/CALCITE-3075 > Project: Calcite > Issue Type: Improvement >Reporter: Hongze Zhang >Priority: Major > > Travis.org supports running CI on Windows now: > https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably > worth to investigate if running Windows CI using Travis would benefit us more > than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3075) Evaluate Travis CI for Windows build
[
https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841871#comment-16841871
]
Hongze Zhang commented on CALCITE-3075:
---
{quote}I kind of have interest on this topic, i will try to find some
references this weekend.{quote}
Thanks for your help, Danny!
> Evaluate Travis CI for Windows build
>
>
> Key: CALCITE-3075
> URL: https://issues.apache.org/jira/browse/CALCITE-3075
> Project: Calcite
> Issue Type: Improvement
>Reporter: Hongze Zhang
>Priority: Major
>
> Travis.org supports running CI on Windows now:
> https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably
> worth to investigate if running Windows CI using Travis would benefit us more
> than AppVevor can do.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3075) Evaluate Travis CI for Windows build
[ https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841867#comment-16841867 ] Hongze Zhang commented on CALCITE-3075: --- Thanks Kevin and Francis! I somehow missed the talk in that thread :), sorry about that. And looks like it is been a time (> 5 months?) after that talk. May be I can help to test the Travis Windows build on my personal branch of Calcite/Avatica first, to see if it is enough to be used in production. By the way, [~krisden] - are there some known issues of the new Windows CI you have found last time? Maybe we can double check if they get fixed/improved at the time. > Evaluate Travis CI for Windows build > > > Key: CALCITE-3075 > URL: https://issues.apache.org/jira/browse/CALCITE-3075 > Project: Calcite > Issue Type: Improvement >Reporter: Hongze Zhang >Priority: Major > > Travis.org supports running CI on Windows now: > https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably > worth to investigate if running Windows CI using Travis would benefit us more > than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3075) Evaluate Travis CI for Windows build
[ https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841866#comment-16841866 ] Danny Chan commented on CALCITE-3075: - I kind of have interest on this topic, i will try to find some references this weekend. > Evaluate Travis CI for Windows build > > > Key: CALCITE-3075 > URL: https://issues.apache.org/jira/browse/CALCITE-3075 > Project: Calcite > Issue Type: Improvement >Reporter: Hongze Zhang >Priority: Major > > Travis.org supports running CI on Windows now: > https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably > worth to investigate if running Windows CI using Travis would benefit us more > than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2969) Improve design of join-like relational expressions
[ https://issues.apache.org/jira/browse/CALCITE-2969?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841860#comment-16841860 ] Danny Chan commented on CALCITE-2969: - Thanks for all of your nice review comments [~julianhyde] [~hyuan] [~zabetak] [~rubenql], i think the PR is kind of stable now(no vital bugs). So i'm planning to merge this PR in 24 hours if there are no more comments. Be free to fire new comments and i'm glad to do fix up in later patches if we really found some fatal defects with current patch. > Improve design of join-like relational expressions > -- > > Key: CALCITE-2969 > URL: https://issues.apache.org/jira/browse/CALCITE-2969 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.19.0 >Reporter: Stamatis Zampetakis >Assignee: Danny Chan >Priority: Major > Labels: pull-request-available > Time Spent: 23h 10m > Remaining Estimate: 0h > > The existing join-like (Join, SemiJoin, Correlate, etc.) logical and physical > relational expressions have a few design issues which make some parts of the > codebase complicated and difficult to understand. > The goal of this ticket is to improve the design of the respective > expressions based on the discussion in the dev list (see thread [Join, > SemiJoin, > Correlate|https://mail-archives.apache.org/mod_mbox/calcite-dev/201903.mbox/%3C8EEA04A0-4A77-4283-BD20-B019E19AE126%40apache.org%3E]). > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Assigned] (CALCITE-3067) Splunk Calcite adapter cannot parse right session keys from Splunk 7.2
[ https://issues.apache.org/jira/browse/CALCITE-3067?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Shawn Chen reassigned CALCITE-3067: --- Assignee: Shawn Chen > Splunk Calcite adapter cannot parse right session keys from Splunk 7.2 > -- > > Key: CALCITE-3067 > URL: https://issues.apache.org/jira/browse/CALCITE-3067 > Project: Calcite > Issue Type: Bug > Components: splunk >Affects Versions: 1.19.0 > Environment: Splunk 7.2 on Mac OS 10.14 > Calcite 1.19 >Reporter: Shawn Chen >Assignee: Shawn Chen >Priority: Major > Fix For: next > > Original Estimate: 2h > Remaining Estimate: 2h > > *SplunkConnectionImpl.java* successfully builds a connection to Splunk 7.2, > but it cannot parse the correct session key due to the outdated regular > expression in this class, therefore it gets HTTP 401 response after sending > further search commands to Splunk. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3065) RexLiteral#getValueAs should consider primitive type
[
https://issues.apache.org/jira/browse/CALCITE-3065?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841856#comment-16841856
]
Danny Chan commented on CALCITE-3065:
-
[~Aron.tao] I don't know where your plan comes from, but for my test, the query
{code:java}
select 1 from tbl1;
{code}
will produce a project with SqlLiteral with type name Integer(after validated).
> RexLiteral#getValueAs should consider primitive type
>
>
> Key: CALCITE-3065
> URL: https://issues.apache.org/jira/browse/CALCITE-3065
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiatao Tao
>Priority: Major
> Labels: pull-request-available
> Attachments: image-2019-05-13-12-04-36-365.png
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> !image-2019-05-13-12-04-36-365.png!
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3075) Evaluate Travis CI for Windows build
[ https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841846#comment-16841846 ] Francis Chuang commented on CALCITE-3075: - We last talked about this when releasing Avatica 1.13.0. At the time I think Windows support on travis was immature and not ready. See: [https://lists.apache.org/thread.html/e8346bdbe1ac87d19eb13e6308fcc05b1bf8a1150150f3cfdc4f58fc@%3Cdev.calcite.apache.org%3E] > Evaluate Travis CI for Windows build > > > Key: CALCITE-3075 > URL: https://issues.apache.org/jira/browse/CALCITE-3075 > Project: Calcite > Issue Type: Improvement >Reporter: Hongze Zhang >Priority: Major > > Travis.org supports running CI on Windows now: > https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably > worth to investigate if running Windows CI using Travis would benefit us more > than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3069) Make the JDBC Connection more extensible like the FrameworkConfig API
[ https://issues.apache.org/jira/browse/CALCITE-3069?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841608#comment-16841608 ] Stamatis Zampetakis commented on CALCITE-3069: -- I'm more with [~zhztheplayer] in this. It makes more sense to try to make JDBC APIs call Framework/Planner than the opposite. I'm not sure what you would like to make configurable in JDBC connection. The way I read the description it seems that what you need is already done by the FrameworkConfig API. It would help if you had some concrete examples of what you want to achieve. Moreover, I don't understand well what you mean in the last paragraph. It would help if you could clarify a bit. > Make the JDBC Connection more extensible like the FrameworkConfig API > - > > Key: CALCITE-3069 > URL: https://issues.apache.org/jira/browse/CALCITE-3069 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.19.0 >Reporter: Lai Zhou >Priority: Major > > More and more users are interested in building custom sql engines on top of > Calcite. > But for different sql engines, there're differences on sql parsing, > expression conversions, implicit type casting ...even the physical > implementations for logical plan. > I think the FrameworkConfig API now provided a better way than JDBC > Connection to custom these things.Are there any plans in the roadmap to > enhance the JDBC Connection config , like FrameworkConfig API , to improve > Calcite's extensibility ? > Otherwise, implementing the whole physical plan like the default > Enumerable-implementation will be boring , that also require a lot of work. > May be we can do something to make the physical and execution plan(that says > code-gen ) more customizable. > Are there any thoughts on this issue? > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Resolved] (CALCITE-3062) Do not populate provenanceMap if not used
[
https://issues.apache.org/jira/browse/CALCITE-3062?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Laurent Goujon resolved CALCITE-3062.
-
Resolution: Fixed
Fix Version/s: 1.20.0
Merged as
https://github.com/apache/calcite/commit/c6b6800c220e513f1d9a2b167b2f14cb689c0b06
> Do not populate provenanceMap if not used
> -
>
> Key: CALCITE-3062
> URL: https://issues.apache.org/jira/browse/CALCITE-3062
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Laurent Goujon
>Assignee: Laurent Goujon
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.20.0
>
> Time Spent: 1h 40m
> Remaining Estimate: 0h
>
> {{VolcanoPlanner}}'s field {{provenanceMap}} tracks the provenance of each
> node being added to the planner, but the information is only used when the
> log level of the planner of the debug, so when planner is not running in
> debug mode, this data goes to waste and used memory unnecessary.
> The planner should be changed so that the information is only captured if
> used later.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3028) Support FULL OUTER JOIN with AggregateJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-3028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841568#comment-16841568 ] Vineet Garg commented on CALCITE-3028: -- Thanks [~hyuan] > Support FULL OUTER JOIN with AggregateJoinTransposeRule > --- > > Key: CALCITE-3028 > URL: https://issues.apache.org/jira/browse/CALCITE-3028 > Project: Calcite > Issue Type: Improvement >Reporter: Vineet Garg >Assignee: Vineet Garg >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 20m > Remaining Estimate: 0h > > This is continuation of CALCITE-3011, which supported LEFT OUTER and RIGHT > OUTER joins without aggregate functions. > FULL OUTER JOIN was not supported at the time due to CALCITE-3012 -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3075) Evaluate Travis CI for Windows build
[ https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841479#comment-16841479 ] Kevin Risden commented on CALCITE-3075: --- I thought there was a thread about using Travis CI on Windows at one point. I couldn't find it with some quick searches. Not going to stop forward progress if someone wants to try to move forward with testing. I think it should be possible to make whatever changes to .travis.yml and open a PR to see if the changes work. > Evaluate Travis CI for Windows build > > > Key: CALCITE-3075 > URL: https://issues.apache.org/jira/browse/CALCITE-3075 > Project: Calcite > Issue Type: Improvement >Reporter: Hongze Zhang >Priority: Major > > Travis.org supports running CI on Windows now: > https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably > worth to investigate if running Windows CI using Travis would benefit us more > than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3075) Evaluate Travis CI for Windows build
[
https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841473#comment-16841473
]
Hongze Zhang commented on CALCITE-3075:
---
Also, sometimes I slightly feel that AppVeyor build has more chances to get
stuck at {{Queued}} status than Travis when we update PRs. I am not sure if
their build concurrency are different. Does anyone know something about that?
> Evaluate Travis CI for Windows build
>
>
> Key: CALCITE-3075
> URL: https://issues.apache.org/jira/browse/CALCITE-3075
> Project: Calcite
> Issue Type: Improvement
>Reporter: Hongze Zhang
>Priority: Major
>
> Travis.org supports running CI on Windows now:
> https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably
> worth to investigate if running Windows CI using Travis would benefit us more
> than AppVevor can do.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-3075) Evaluate Travis CI for Windows build
[ https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841454#comment-16841454 ] Hongze Zhang edited comment on CALCITE-3075 at 5/16/19 3:33 PM: One possible disadvantage of AppVeyor might be the performance - Now Calcite's AppVevor CI need ~18 min (seems 15 min for most cases, 20+ min for bad ones) to finish a build, as a comparison Travis CI costs ~13 min. Not sure if for Windows build Travis can still be faster. It would be much appreciated if anyone has experience on Travis+Windows and be willing to share some. was (Author: zhztheplayer): One possible disadvantage of AppVeyor might be the performance - Now Calcite's AppVevor CI need ~18 min to finish a build, as a comparison Travis CI costs ~13 min. Not sure if for Windows build Travis can still be faster. It would be much appreciated if anyone has experience on Travis+Windows and be willing to share some. > Evaluate Travis CI for Windows build > > > Key: CALCITE-3075 > URL: https://issues.apache.org/jira/browse/CALCITE-3075 > Project: Calcite > Issue Type: Improvement >Reporter: Hongze Zhang >Priority: Major > > Travis.org supports running CI on Windows now: > https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably > worth to investigate if running Windows CI using Travis would benefit us more > than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-3075) Evaluate Travis CI for Windows build
[ https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841454#comment-16841454 ] Hongze Zhang edited comment on CALCITE-3075 at 5/16/19 3:28 PM: One possible disadvantage of AppVeyor might be the performance - Now Calcite's AppVevor CI need ~18 min to finish a build, as a comparison Travis CI costs ~13 min. Not sure if for Windows build Travis can still be faster. It would be much appreciated if anyone has experience on Travis+Windows and be willing to share some. was (Author: zhztheplayer): One possible disadvantage of AppVeyor might be the performance - Now Calcite's AppVevor CI need ~20 min to finish a build, as a comparison Travis CI costs ~13 min. Not sure if for Windows build Travis can still be faster. It would be much appreciated if anyone has experience on Travis+Windows and be willing to share some. > Evaluate Travis CI for Windows build > > > Key: CALCITE-3075 > URL: https://issues.apache.org/jira/browse/CALCITE-3075 > Project: Calcite > Issue Type: Improvement >Reporter: Hongze Zhang >Priority: Major > > Travis.org supports running CI on Windows now: > https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably > worth to investigate if running Windows CI using Travis would benefit us more > than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3075) Evaluate Travis CI for Windows build
[ https://issues.apache.org/jira/browse/CALCITE-3075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841454#comment-16841454 ] Hongze Zhang commented on CALCITE-3075: --- One possible disadvantage of AppVeyor might be the performance - Now Calcite's AppVevor CI need ~20 min to finish a build, as a comparison Travis CI costs ~13 min. Not sure if for Windows build Travis can still be faster. It would be much appreciated if anyone has experience on Travis+Windows and be willing to share some. > Evaluate Travis CI for Windows build > > > Key: CALCITE-3075 > URL: https://issues.apache.org/jira/browse/CALCITE-3075 > Project: Calcite > Issue Type: Improvement >Reporter: Hongze Zhang >Priority: Major > > Travis.org supports running CI on Windows now: > https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably > worth to investigate if running Windows CI using Travis would benefit us more > than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2712) Add rule to remove null-generating side of a Join
[
https://issues.apache.org/jira/browse/CALCITE-2712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841439#comment-16841439
]
Chunwei Lei commented on CALCITE-2712:
--
Thank you very much for your review [~julianhyde]，[~hyuan]. I would like to
merge this PR if there are no more comments in 24 hours.
> Add rule to remove null-generating side of a Join
> -
>
> Key: CALCITE-2712
> URL: https://issues.apache.org/jira/browse/CALCITE-2712
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 3.5h
> Remaining Estimate: 0h
>
> Add a rule to remove the null-generating side of a Join. Here is an example
> where we eliminate the "many" side of a "one-to-many" join:
> {code:sql}
> # Example 1: one-to-many
> SELECT c.id, COUNT(DISTINCT o.productId)
> FROM Customers AS c
> LEFT JOIN SupportCalls AS s ON c.id = s.customerId
> LEFT JOIN Orders AS o ON c.id = o.customerId{code}
> We can remove {{SupportCalls}} and the join to it, so the query becomes
> {code:sql}
> SELECT c.id, COUNT(DISTINCT o.productId)
> FROM Customers AS c
> LEFT JOIN Orders AS o ON c.id = o.customerId{code}
> Necessary conditions are:
> # no columns from {{SupportCalls}} are used
> # the join is LEFT, so customers will not be eliminated if they have no
> support calls,
> # there is an Aggregate on top, so we don't care if there are >1 support call
> per customer.
> A simpler example of one-to-many:
> {code:sql}
> # Example 2: simpler one-to-many
> SELECT DISTINCT c.id
> FROM Customers AS c
> LEFT JOIN SupportCalls AS s ON c.id = s.customerId{code}
> An example of many-to-one, where we eliminate the "one" side ({{Orders}}):
> {code:sql}
> # Example 3: many-to-one
> SELECT c.id, p.color
> FROM LineItems AS i
> LEFT JOIN Orders AS o ON o.id = i.orderId
> LEFT JOIN Products AS p ON p.id = i.orderId{code}
> so that the query becomes
> {code:sql}
> SELECT c.id, p.color
> FROM LineItems AS i
> LEFT JOIN Products AS p ON p.id = i.orderId{code}
> Here, necessary side-conditions are:
> # no columns from {{Orders}} are used;
> # unique key on {{Orders.id}}.
> We do not require aggregation, because the primary key on {{Orders.id}}
> ensures that {{Orders}} contributes at most one row.
> We can deal with similar cases like
> {code:sql}
> # Example 4: many-to-one, column aliasing required
> SELECT c.id, p.color
> FROM LineItems AS i
> LEFT JOIN Orders AS o ON o.id = i.orderId
> LEFT JOIN Products AS p ON p.id = o.id{code}
> if we use aliasing ({{o.id}} = {{i.orderId}}) and a foreign key that ensures
> the existence of an record in {{Orders}}.
> For examples 1 and 2 (one-to-many), we would need to match {{Aggregate}} on
> {{Join}}, therefore make a variant of {{AggregateJoinTransposeRule}}.
> For examples 3 and 4 (many-to-one or one-to-one)), we would match {{Project}}
> on {{Join}}, therefore make a variant of {{ProjectJoinTransposeRule}}.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-3075) Evaluate Travis CI for Windows build
Hongze Zhang created CALCITE-3075: - Summary: Evaluate Travis CI for Windows build Key: CALCITE-3075 URL: https://issues.apache.org/jira/browse/CALCITE-3075 Project: Calcite Issue Type: Improvement Reporter: Hongze Zhang Travis.org supports running CI on Windows now: https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably worth to investigate if running Windows CI using Travis would benefit us more than AppVevor can do. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-3074) Move MySQL's JSON operators to SqlLibraryOperators
[ https://issues.apache.org/jira/browse/CALCITE-3074?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-3074: Labels: pull-request-available (was: ) > Move MySQL's JSON operators to SqlLibraryOperators > -- > > Key: CALCITE-3074 > URL: https://issues.apache.org/jira/browse/CALCITE-3074 > Project: Calcite > Issue Type: Sub-task >Reporter: Hongze Zhang >Assignee: Hongze Zhang >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > > This is a follow-up to CALCITE-2846. We should move the instantiating logic > to SqlLibraryOperators and deprecated old ones that are in std operator table. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-3074) Move MySQL's JSON operators to SqlLibraryOperators
[ https://issues.apache.org/jira/browse/CALCITE-3074?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Hongze Zhang updated CALCITE-3074: -- Description: This is a follow-up to CALCITE-2846. We should move the instantiating logic to SqlLibraryOperators and deprecated old ones that are in std operator table. (was: This is a follow-up to CALCITE-2846. We have some JSON operators in the ) > Move MySQL's JSON operators to SqlLibraryOperators > -- > > Key: CALCITE-3074 > URL: https://issues.apache.org/jira/browse/CALCITE-3074 > Project: Calcite > Issue Type: Sub-task >Reporter: Hongze Zhang >Assignee: Hongze Zhang >Priority: Major > Fix For: 1.20.0 > > > This is a follow-up to CALCITE-2846. We should move the instantiating logic > to SqlLibraryOperators and deprecated old ones that are in std operator table. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-3074) Move MySQL's JSON operators to SqlLibraryOperators
[ https://issues.apache.org/jira/browse/CALCITE-3074?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Hongze Zhang updated CALCITE-3074: -- Description: This is a follow-up to CALCITE-2846. We have some JSON operators in the (was: This is a follow-up to CALCITE-2846.) > Move MySQL's JSON operators to SqlLibraryOperators > -- > > Key: CALCITE-3074 > URL: https://issues.apache.org/jira/browse/CALCITE-3074 > Project: Calcite > Issue Type: Sub-task >Reporter: Hongze Zhang >Assignee: Hongze Zhang >Priority: Major > Fix For: 1.20.0 > > > This is a follow-up to CALCITE-2846. We have some JSON operators in the -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-3074) Move MySQL's JSON operators to SqlLibraryOperators
Hongze Zhang created CALCITE-3074: - Summary: Move MySQL's JSON operators to SqlLibraryOperators Key: CALCITE-3074 URL: https://issues.apache.org/jira/browse/CALCITE-3074 Project: Calcite Issue Type: Sub-task Reporter: Hongze Zhang Assignee: Hongze Zhang Fix For: 1.20.0 This is a follow-up to CALCITE-2846. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Resolved] (CALCITE-2975) Implement JSON_REMOVE function
[
https://issues.apache.org/jira/browse/CALCITE-2975?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hongze Zhang resolved CALCITE-2975.
---
Resolution: Fixed
Fix Version/s: 1.20.0
Fixed in [999c41d5ff893a30b2622442a448102c72fc475e
|https://github.com/apache/calcite/commit/999c41d5ff893a30b2622442a448102c72fc475e].
Thanks [~x1q1j1]!
> Implement JSON_REMOVE function
> --
>
> Key: CALCITE-2975
> URL: https://issues.apache.org/jira/browse/CALCITE-2975
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Forward Xu
>Assignee: Forward Xu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.20.0
>
> Time Spent: 3.5h
> Remaining Estimate: 0h
>
> JSON_REMOVE(json_doc, path[, path] ...)
> Removes data from a JSON document and returns the result. Returns NULL if any
> argument is NULL. An error occurs if the json_doc argument is not a valid
> JSON document or any path argument is not a valid path expression or is $ or
> contains a * or ** wildcard.
> The path arguments are evaluated left to right. The document produced by
> evaluating one path becomes the new value against which the next path is
> evaluated.
> It is not an error if the element to be removed does not exist in the
> document; in that case, the path does not affect the document.
> JSON_REMOVE SQL:
> {code:java}
> SELECT JSON_REMOVE(v, '$[1]') AS c1
> FROM (VALUES ('["a", ["b", "c"], "d"]')) AS t(v);
> {code}
> RESULT:
> ||c1||
> |["a", "d"]|
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2985) Implement JSON_STORAGE_SIZE function
[
https://issues.apache.org/jira/browse/CALCITE-2985?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hongze Zhang updated CALCITE-2985:
--
Summary: Implement JSON_STORAGE_SIZE function (was: Add the
JSON_STORAGE_SIZE function)
> Implement JSON_STORAGE_SIZE function
>
>
> Key: CALCITE-2985
> URL: https://issues.apache.org/jira/browse/CALCITE-2985
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Forward Xu
>Assignee: Forward Xu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> JSON_STORAGE_SIZE(json_val)
> This function returns the number of bytes used to store the binary
> representation of a JSON document. When the argument is a JSON column, this
> is the space used to store the JSON document. json_val must be a valid JSON
> document or a string which can be parsed as one. In the case where it is
> string, the function returns the amount of storage space in the JSON binary
> representation that is created by parsing the string as JSON and converting
> it to binary. It returns NULL if the argument is NULL.
> An error results when json_val is not NULL, and is not—or cannot be
> successfully parsed as—a JSON document.
> To illustrate this function's behavior when used with a JSON column as its
> argument, we create a table named jtable containing a JSON column jcol,
> insert a JSON value into the table, then obtain the storage space used by
> this column with JSON_STORAGE_SIZE(), as shown here:
> {code:java}
> SELECT
> JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
> JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
> JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
> JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
> {code}
> | A | B | C | D |
> | 29 | 37 | 40 | 36 |
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2884) Implement JSON_INSERT, JSON_REPLACE, JSON_SET
[
https://issues.apache.org/jira/browse/CALCITE-2884?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hongze Zhang updated CALCITE-2884:
--
Summary: Implement JSON_INSERT, JSON_REPLACE, JSON_SET (was: Add the
JSON_INSERT,JSON_REPLACE,JSON_SET function)
> Implement JSON_INSERT, JSON_REPLACE, JSON_SET
> -
>
> Key: CALCITE-2884
> URL: https://issues.apache.org/jira/browse/CALCITE-2884
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Forward Xu
>Assignee: Forward Xu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> {{JSON_INSERT(jsondoc}}, _{{path}}_, _{{val}}_[, path, val] )
> {{JSON_REPLACE(jsondoc}}, _{{path}}_, _{{val}}_[, path, val] )
> {{JSON_SET(jsondoc}}, _{{path}}_, _{{val}}_[, path, val] )
> Inserts data into a JSON document and returns the result. Returns {{NULL}} if
> any argument is {{NULL}}. An error occurs if the _{{json_doc}}_ argument is
> not a valid JSON document or any _{{path}}_ argument is not a valid path
> expression or contains a *or {{**}} wildcard.
> The path-value pairs are evaluated left to right. The document produced by
> evaluating one pair becomes the new value against which the next pair is
> evaluated.
> A path-value pair for an existing path in the document is ignored and does
> not overwrite the existing document value. A path-value pair for a
> nonexisting path in the document adds the value to the document if the path
> identifies one of these types of values:
> * A member not present in an existing object. The member is added to the
> object and associated with the new value.
> * A position past the end of an existing array. The array is extended with
> the new value. If the existing value is not an array, it is autowrapped as an
> array, then extended with the new value.
> Otherwise, a path-value pair for a nonexisting path in the document is
> ignored and has no effect.
> For a comparison of
> [{{JSON_INSERT()}}|https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-insert],
>
> [{{JSON_REPLACE()}}|https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-replace],
> and
> [{{JSON_SET()}}|https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-set],
> see the discussion of
> [{{JSON_SET()}}|https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-set].
> JSON_INSERT SQL:
> {code:java}
> SELECT JSON_INSERT(v, '$.a', 10, '$.c', '[true, false]') AS c1
> FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);{code}
> Result:
> ||c1||
> |{"a": 1, "b": [2, 3], "c": "[true, false]"}|
> JSON_REPLACE SQL:
> {code:java}
> SELECT JSON_REPLACE(v, '$.a', 10, '$.c', '[true, false]') AS c1
> FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);{code}
> Result:
> ||c1||
> |{"a": 10, "b": [2, 3],}|
> JSON_SET SQL:
> {code:java}
> SELECT JSON_INSERT(v, '$.a', 10, '$.c', '[true, false]') AS c1
> FROM (VALUES ('{ "a": 1, "b": [2, 3]}')) AS t(v);{code}
> Result:
> ||c1||
> |{"a": 10, "b": [2, 3], "c": "[true, false]"}|
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2975) Implement JSON_REMOVE function
[
https://issues.apache.org/jira/browse/CALCITE-2975?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841347#comment-16841347
]
Hongze Zhang commented on CALCITE-2975:
---
Changed the title to "Implement JSON_REMOVE function". We can use the word
"implement" in future issues/PRs.
> Implement JSON_REMOVE function
> --
>
> Key: CALCITE-2975
> URL: https://issues.apache.org/jira/browse/CALCITE-2975
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Forward Xu
>Assignee: Forward Xu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 3h 20m
> Remaining Estimate: 0h
>
> JSON_REMOVE(json_doc, path[, path] ...)
> Removes data from a JSON document and returns the result. Returns NULL if any
> argument is NULL. An error occurs if the json_doc argument is not a valid
> JSON document or any path argument is not a valid path expression or is $ or
> contains a * or ** wildcard.
> The path arguments are evaluated left to right. The document produced by
> evaluating one path becomes the new value against which the next path is
> evaluated.
> It is not an error if the element to be removed does not exist in the
> document; in that case, the path does not affect the document.
> JSON_REMOVE SQL:
> {code:java}
> SELECT JSON_REMOVE(v, '$[1]') AS c1
> FROM (VALUES ('["a", ["b", "c"], "d"]')) AS t(v);
> {code}
> RESULT:
> ||c1||
> |["a", "d"]|
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2975) Implement JSON_REMOVE function
[
https://issues.apache.org/jira/browse/CALCITE-2975?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hongze Zhang updated CALCITE-2975:
--
Summary: Implement JSON_REMOVE function (was: Add the JSON_REMOVE function)
> Implement JSON_REMOVE function
> --
>
> Key: CALCITE-2975
> URL: https://issues.apache.org/jira/browse/CALCITE-2975
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Forward Xu
>Assignee: Forward Xu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 3h 20m
> Remaining Estimate: 0h
>
> JSON_REMOVE(json_doc, path[, path] ...)
> Removes data from a JSON document and returns the result. Returns NULL if any
> argument is NULL. An error occurs if the json_doc argument is not a valid
> JSON document or any path argument is not a valid path expression or is $ or
> contains a * or ** wildcard.
> The path arguments are evaluated left to right. The document produced by
> evaluating one path becomes the new value against which the next path is
> evaluated.
> It is not an error if the element to be removed does not exist in the
> document; in that case, the path does not affect the document.
> JSON_REMOVE SQL:
> {code:java}
> SELECT JSON_REMOVE(v, '$[1]') AS c1
> FROM (VALUES ('["a", ["b", "c"], "d"]')) AS t(v);
> {code}
> RESULT:
> ||c1||
> |["a", "d"]|
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-3073) support both from/to timestamp/offset in KafkaAdapter
Xu Mingmin created CALCITE-3073: --- Summary: support both from/to timestamp/offset in KafkaAdapter Key: CALCITE-3073 URL: https://issues.apache.org/jira/browse/CALCITE-3073 Project: Calcite Issue Type: Improvement Reporter: Xu Mingmin Currently the KafkaAdapter consumes data from default offset(latest/earliest/last_offset) and runs forever. With support of from/to timestamp/offset, it's more flexible for users to query a specific range. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2601) Add REVERSE function
[
https://issues.apache.org/jira/browse/CALCITE-2601?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841183#comment-16841183
]
pingle wang commented on CALCITE-2601:
--
ok, thank you!
> Add REVERSE function
>
>
> Key: CALCITE-2601
> URL: https://issues.apache.org/jira/browse/CALCITE-2601
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: TANG Wen-hui
>Priority: Major
> Labels: pull-request-available
> Time Spent: 2h 20m
> Remaining Estimate: 0h
>
> Returns the string _{{str}}_ with the order of the characters reversed.
> I think ’Reverse‘ seems to be a generic function.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2933) In Druid adapter, expression like "cast(cast(\"timestamp\" as timestamp) as varchar)" returns as epoch millisecond
[
https://issues.apache.org/jira/browse/CALCITE-2933?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841122#comment-16841122
]
Danny Chan commented on CALCITE-2933:
-
Fixed in
[https://github.com/apache/calcite/commit/7df06784a725bd9bde421b5d86ac69a3e30d66b4]
!
> In Druid adapter, expression like "cast(cast(\"timestamp\" as timestamp) as
> varchar)" returns as epoch millisecond
> --
>
> Key: CALCITE-2933
> URL: https://issues.apache.org/jira/browse/CALCITE-2933
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.18.0
>Reporter: Hongze Zhang
>Assignee: Danny Chan
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> SQL 1:
> {code}
> select cast("timestamp" as timestamp) as t from "foodmart" order by t limit 1
> {code}
> Result:
> {code}
> T=1997-01-01 00:00:00
> {code}
> SQL 2:
> {code}
> select cast(cast("timestamp" as timestamp) as varchar) as t from "foodmart"
> order by t limit 1
> {code}
> Result:
> {code}
> T=85207680
> {code}
> The second query should returns with the same format as the first one.
> See test case:
> https://github.com/apache/calcite/blob/1229ef27094ea73ad9c7a397f442285c7e1df9b0/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT2.java#L3930
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Resolved] (CALCITE-2933) In Druid adapter, expression like "cast(cast(\"timestamp\" as timestamp) as varchar)" returns as epoch millisecond
[
https://issues.apache.org/jira/browse/CALCITE-2933?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Danny Chan resolved CALCITE-2933.
-
Resolution: Fixed
Fix Version/s: 1.20.0
> In Druid adapter, expression like "cast(cast(\"timestamp\" as timestamp) as
> varchar)" returns as epoch millisecond
> --
>
> Key: CALCITE-2933
> URL: https://issues.apache.org/jira/browse/CALCITE-2933
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.18.0
>Reporter: Hongze Zhang
>Assignee: Danny Chan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.20.0
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> SQL 1:
> {code}
> select cast("timestamp" as timestamp) as t from "foodmart" order by t limit 1
> {code}
> Result:
> {code}
> T=1997-01-01 00:00:00
> {code}
> SQL 2:
> {code}
> select cast(cast("timestamp" as timestamp) as varchar) as t from "foodmart"
> order by t limit 1
> {code}
> Result:
> {code}
> T=85207680
> {code}
> The second query should returns with the same format as the first one.
> See test case:
> https://github.com/apache/calcite/blob/1229ef27094ea73ad9c7a397f442285c7e1df9b0/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT2.java#L3930
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2624) Add a rule to copy a sort below a join operator
[ https://issues.apache.org/jira/browse/CALCITE-2624?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841097#comment-16841097 ] Ruben Quesada Lopez commented on CALCITE-2624: -- Thanks for the explanation [~hyuan], I also see your point. The idea behind this ticket was to try to create a new rule to generalize the already existing one SortJoinTransposeRule (which works just for LEFT / RIGHT join, but also pushing limit and offset), i.e. to have a less powerful but more likely applicable rule to push a Sort into a Join. Having said that, perhaps your approach is more correct, and we should rethink the solution more in terms of physical property enforcement. > Add a rule to copy a sort below a join operator > --- > > Key: CALCITE-2624 > URL: https://issues.apache.org/jira/browse/CALCITE-2624 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.17.0 >Reporter: Stamatis Zampetakis >Assignee: Khawla Mouhoubi >Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > Currently, the only rule that allows a sort to traverse a binary operator is > the SortJoinTransposeRule. The rule was introduced mainly to push limits in > the case of left and right outer joins (see CALCITE-831). > I assume that the main reason that we don't have more rules is that sorts > with limits and offsets cannot be pushed safely below many types of join > operators. However, in many cases, it is possible and beneficial for > optimization purposes to just push the sort without the limit and offset. > Since we do not know in advance if the join operator preserves the order we > cannot remove (that is why I am saying copy and not transpose) the sort > operator on top of the join. The latter is not really a problem since the > SortRemoveRule can detect such cases and remove the sort if it is redundant. > A few concrete examples where this optimization makes sense are outlined > below: > * allow the sort to be later absorbed by an index scan and disappear from > the plan (Sort + Tablescan => IndexScan with RelCollation); > * allow operators that require sorted inputs to be exploited more easily > (e.g., merge join); > * allow the sort to be performed on a possibly smaller result (assuming that > the physical binary operator that is going to be used preserves the order of > left/right input and the top sort operator can be removed entirely). > I propose to add a new rule (e.g., SortCopyBelowJoinRule, > SortJoinCopyBelowRule) which allows a sort to be copied to the left or right > (or to both if it is rather easy to decompose the sort) of a join operator > (excluding the limit and offset attributes) if the respective inputs are not > already sorted. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3070) Inner join between MySQL tables produce wrong results if join order changed
[
https://issues.apache.org/jira/browse/CALCITE-3070?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841091#comment-16841091
]
Stamatis Zampetakis commented on CALCITE-3070:
--
It is probably the EnumerableCalc
{noformat}
EnumerableCalc(expr#0..84=[\{inputs}], expr#85=[1997-02-12 00:00:00],
expr#86=[>=($t78, $t85)], expr#87=[1997-02-12 00:01:00], expr#88=[<($t78,
$t87)], expr#89=[AND($t86, $t88)], product_name=[$t5], customer_id=[$t22],
$f85=[$t86], $f86=[$t88], $condition=[$t89]): rowcount = 25.0, cumulative cost
= {135.0 rows, 9611.0 cpu, 0.0 io}, id = 338
{noformat}
operator in the second plan that is filtering more than necessary. Two possible
reasons may be the following:
* the JdbcToEnumerableConverter does not convert correctly the timestamp
columns;
* the comparison operators (>=, <) does not work correctly for timestamps (less
likely).
Other than that, as far as I can see from the cost of the plan both base tables
have row estimation of 100, which rather means that statistics are not
exploited so the optimal plan cannot be found in any case.
> Inner join between MySQL tables produce wrong results if join order changed
> ---
>
> Key: CALCITE-3070
> URL: https://issues.apache.org/jira/browse/CALCITE-3070
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.19.0
>Reporter: Zhibin Zhou
>Priority: Critical
> Attachments: log.txt
>
>
> Summary of issue:
> * We are trying to inner join 2 MySQL tables.
> ** persons table: a very small table
> ** foodmart table: a large table
> * The query produce different results if we change the join order in the
> "from" clause: "from A join B" vs "from B join A". One query produces correct
> results while the other query produces empty results.
> * The physical plan generated for the produce-empty query was not optimal.
> it tried to scan be big table.
>
> Working query and physical plan:
> {code:sql}
> select "t2"."product_name", "t2"."customer_id" from
> "foodmart-mysql"."foodmart" as "t2" join "persons"."persons" as "t1"
> on"t1"."person_id_int"="t2"."customer_id" and"t2"."timestamp" >=
> '1997-02-12 00:00:00' and "t2"."timestamp" < '1997-02-12 00:01:00'
> {code}
> {noformat}
> EnumerableCalc(expr#0..4=[\{inputs}], proj#0..1=[\{exprs}]): rowcount =
> 375.0, cumulative cost = {1287.971895621705 rows, 3719.5 cpu, 0.0 io}, id =
> 671
> EnumerableJoin(condition=[=($1, $4)], joinType=[inner]): rowcount = 375.0,
> cumulative cost = {912.971895621705 rows, 1094.5 cpu, 0.0 io}, id = 667
> JdbcToEnumerableConverter: rowcount = 25.0, cumulative cost = {147.5
> rows, 283.5 cpu, 0.0 io}, id = 660
> JdbcProject(product_name=[$5], customer_id=[$22], $f85=[>=($78,
> 1997-02-12 00:00:00)], $f86=[<($78, 1997-02-12 00:01:00)]): rowcount = 25.0,
> cumulative cost = {145.0 rows, 281.0 cpu, 0.0 io}, id = 658
> JdbcFilter(condition=[AND(>=($78, 1997-02-12 00:00:00), <($78,
> 1997-02-12 00:01:00))]): rowcount = 25.0, cumulative cost = {125.0 rows,
> 201.0 cpu, 0.0 io}, id = 656
> JdbcTableScan(table=[[foodmart-mysql, foodmart]]): rowcount =
> 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 340
> EnumerableCalc(expr#0..5=[\{inputs}], person_id_int=[$t5]): rowcount =
> 100.0, cumulative cost = {210.0 rows, 811.0 cpu, 0.0 io}, id = 673
> JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0
> rows, 111.0 cpu, 0.0 io}, id = 663
> JdbcTableScan(table=[[persons, persons]]): rowcount = 100.0,
> cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 341
> {noformat}
> Produce-empty query and physical plan:
> {code:sql}
> select "t2"."product_name", "t2"."customer_id" from
> "persons"."persons" as "t1" join "foodmart-mysql"."foodmart" as "t2"
> on "t1"."person_id_int"="t2"."customer_id" and"t2"."timestamp" >=
> '1997-02-12 00:00:00' and "t2"."timestamp" < '1997-02-12 00:01:00'
> {code}
> {noformat}
> EnumerableCalc(expr#0..4=[\{inputs}], proj#0..1=[\{exprs}]): rowcount =
> 375.0, cumulative cost = {1255.471895621705 rows, 12427.0 cpu, 0.0 io}, id =
> 334
> EnumerableJoin(condition=[=($1, $4)], joinType=[inner]): rowcount = 375.0,
> cumulative cost = {880.471895621705 rows, 9802.0 cpu, 0.0 io}, id = 328
> EnumerableCalc(expr#0..84=[\{inputs}], expr#85=[1997-02-12 00:00:00],
> expr#86=[>=($t78, $t85)], expr#87=[1997-02-12 00:01:00], expr#88=[<($t78,
> $t87)], expr#89=[AND($t86, $t88)], product_name=[$t5], customer_id=[$t22],
> $f85=[$t86], $f86=[$t88], $condition=[$t89]): rowcount = 25.0, cumulative
> cost = {135.0 rows, 9611.0 cpu, 0.0 io}, id = 338
> JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0
> rows, 111.0 cpu, 0.0 io}, id = 317
>
[jira] [Updated] (CALCITE-3070) Inner join between MySQL tables produce wrong results if join order changed
[
https://issues.apache.org/jira/browse/CALCITE-3070?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-3070:
-
Description:
Summary of issue:
* We are trying to inner join 2 MySQL tables.
** persons table: a very small table
** foodmart table: a large table
* The query produce different results if we change the join order in the
"from" clause: "from A join B" vs "from B join A". One query produces correct
results while the other query produces empty results.
* The physical plan generated for the produce-empty query was not optimal. it
tried to scan be big table.
Working query and physical plan:
{code:sql}
select "t2"."product_name", "t2"."customer_id" from
"foodmart-mysql"."foodmart" as "t2" join "persons"."persons" as "t1"
on"t1"."person_id_int"="t2"."customer_id" and"t2"."timestamp" >= '1997-02-12
00:00:00' and "t2"."timestamp" < '1997-02-12 00:01:00'
{code}
{noformat}
EnumerableCalc(expr#0..4=[\{inputs}], proj#0..1=[\{exprs}]): rowcount = 375.0,
cumulative cost = {1287.971895621705 rows, 3719.5 cpu, 0.0 io}, id = 671
EnumerableJoin(condition=[=($1, $4)], joinType=[inner]): rowcount = 375.0,
cumulative cost = {912.971895621705 rows, 1094.5 cpu, 0.0 io}, id = 667
JdbcToEnumerableConverter: rowcount = 25.0, cumulative cost = {147.5 rows,
283.5 cpu, 0.0 io}, id = 660
JdbcProject(product_name=[$5], customer_id=[$22], $f85=[>=($78,
1997-02-12 00:00:00)], $f86=[<($78, 1997-02-12 00:01:00)]): rowcount = 25.0,
cumulative cost = {145.0 rows, 281.0 cpu, 0.0 io}, id = 658
JdbcFilter(condition=[AND(>=($78, 1997-02-12 00:00:00), <($78,
1997-02-12 00:01:00))]): rowcount = 25.0, cumulative cost = {125.0 rows, 201.0
cpu, 0.0 io}, id = 656
JdbcTableScan(table=[[foodmart-mysql, foodmart]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 340
EnumerableCalc(expr#0..5=[\{inputs}], person_id_int=[$t5]): rowcount =
100.0, cumulative cost = {210.0 rows, 811.0 cpu, 0.0 io}, id = 673
JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0
rows, 111.0 cpu, 0.0 io}, id = 663
JdbcTableScan(table=[[persons, persons]]): rowcount = 100.0, cumulative
cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 341
{noformat}
Produce-empty query and physical plan:
{code:sql}
select "t2"."product_name", "t2"."customer_id" from
"persons"."persons" as "t1" join "foodmart-mysql"."foodmart" as "t2"
on "t1"."person_id_int"="t2"."customer_id" and"t2"."timestamp" >= '1997-02-12
00:00:00' and "t2"."timestamp" < '1997-02-12 00:01:00'
{code}
{noformat}
EnumerableCalc(expr#0..4=[\{inputs}], proj#0..1=[\{exprs}]): rowcount = 375.0,
cumulative cost = {1255.471895621705 rows, 12427.0 cpu, 0.0 io}, id = 334
EnumerableJoin(condition=[=($1, $4)], joinType=[inner]): rowcount = 375.0,
cumulative cost = {880.471895621705 rows, 9802.0 cpu, 0.0 io}, id = 328
EnumerableCalc(expr#0..84=[\{inputs}], expr#85=[1997-02-12 00:00:00],
expr#86=[>=($t78, $t85)], expr#87=[1997-02-12 00:01:00], expr#88=[<($t78,
$t87)], expr#89=[AND($t86, $t88)], product_name=[$t5], customer_id=[$t22],
$f85=[$t86], $f86=[$t88], $condition=[$t89]): rowcount = 25.0, cumulative cost
= {135.0 rows, 9611.0 cpu, 0.0 io}, id = 338
JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {110.0
rows, 111.0 cpu, 0.0 io}, id = 317
JdbcTableScan(table=[[foodmart-mysql, foodmart]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 1
JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = {190.0 rows,
191.0 cpu, 0.0 io}, id = 326
JdbcProject(person_id_int=[$5]): rowcount = 100.0, cumulative cost =
{180.0 rows, 181.0 cpu, 0.0 io}, id = 324
JdbcTableScan(table=[[persons, persons]]): rowcount = 100.0, cumulative
cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 0
{noformat}
was:
Summary of issue:
* We are trying to inner join 2 MySQL tables.
** persons table: a very small table
** foodmart table: a large table
* The query produce different results if we change the join order in the
"from" clause: "from A join B" vs "from B join A". One query produces correct
results while the other query produces empty results.
* The physical plan generated for the produce-empty query was not optimal. it
tried to scan be big table.
Working query and physical plan:
select "t2"."product_name", "t2"."customer_id" from
"foodmart-mysql"."foodmart" as "t2" join "persons"."persons" as "t1"
on"t1"."person_id_int"="t2"."customer_id" and"t2"."timestamp" >= '1997-02-12
00:00:00' and "t2"."timestamp" < '1997-02-12 00:01:00'
EnumerableCalc(expr#0..4=[\{inputs}], proj#0..1=[\{exprs}]): rowcount = 375.0,
cumulative cost = \{1287.971895621705 rows, 3719.5 cpu, 0.0 io}, id = 671
EnumerableJoin(condition=[=($1, $4)], joinType=[inner]): rowcount = 375.0,
cumulative cost = \{912.971895621705 rows, 1094.5
