Re: Column Ordinal Support in GROUP BY
That, I'm not sure how to do. There might be a similar config there for Power BI. Ultimately it would be nice to have support for this in Calcite, since it seems to be a pretty common extension to SQL (and makes many queries easier to write by hand) but I'm not a Calcite expert enough to know how hard that would be… Gian On Wed, Dec 14, 2016 at 9:57 PM, Kapil Ghodawat wrote: > Thanks a lot Gian. I think this would make Tableau work. > > But I am worried about connectivity from other SQL clients as i am planning > in near future connectivity from Microsoft Power BI > > On Wed, Dec 14, 2016 at 9:00 PM, Gian Merlino wrote: > > > With Tableau you can use a TDC file to tweak the SQL generation. If you > set > > CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then > Tableau > > will avoid using aliases and ordinals for GROUP BY. See > > http://kb.tableau.com/articles/knowledgebase/ > customizing-odbc-connections > > for more info. > > > > The relevant Calcite issue is > > https://issues.apache.org/jira/browse/CALCITE-1306, but if all you care > > about is getting Tableau to work then the TDC approach should do it. > > > > Gian > > > > On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat > > > wrote: > > > > > Hi Folks, > > > > > > I am using Apache Calcite to add SQL support for my custom data > source. I > > > receive SQLs from external SQL clients like Tableau and I have written > an > > > ODBC client for connecting to my data source, which basically delegates > > > these SQLs (generated by Tableau) to my Java program where I use > Calcite > > to > > > execute them. > > > > > > I am stuck at a place where I receive SQL which has GROUP BY clause > that > > > uses column ordinals instead of columns names and when I try to > validate > > > (parsing works fine) such SQLs through Calcite it fails. > > > > > > E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP BY > > 1, > > > 2 > > > > > > (The SQL says GROUP BY on prod_name and store_name fields which are > > column > > > ordinal 1 and 2 respectively in the SELECT clause) > > > > > > > > > I tried delving into the code of calcite and per my understanding I > > believe > > > that usage of column ordinals is supported in ORDER BY clause but not > in > > > the GROUP BY. > > > > > > Primarily, I want to know Is my understanding about no support of > column > > > ordinals in GROUP BY correct? Or is there something I am missing? > > > > > > Secondarily, if anyone knows about ODBC, is there a way I can > tell/force > > > the Tableau to send me column names instead of column ordinals in the > > SQL? > > > > > > > > > -- > > > Best Regards, > > > Kapil Ghodawat > > > contact: +91 94254 86638 > > > > > > > > > -- > Best Regards, > Kapil Ghodawat > contact: +91 94254 86638 >
Re: Column Ordinal Support in GROUP BY
Do you know for sure that Power BI generates this kind of SQL? I suspect it doesn't, because it's not standard SQL. And by the way, Oracle doesn't support it. I have no idea why I'm Tableau is generating this flavor of SQL. Calcite's tool integration is in pretty good shape, and that's because we've stayed close to the standard. There's no question that an extension like this would be nice, especially for end users writing SQL by hand (and contributions are most welcome!), but don't conflate it with tool integration. Julian > On Dec 15, 2016, at 06:21, Gian Merlino wrote: > > That, I'm not sure how to do. There might be a similar config there for > Power BI. Ultimately it would be nice to have support for this in Calcite, > since it seems to be a pretty common extension to SQL (and makes many > queries easier to write by hand) but I'm not a Calcite expert enough to > know how hard that would be… > > Gian > > On Wed, Dec 14, 2016 at 9:57 PM, Kapil Ghodawat > wrote: > >> Thanks a lot Gian. I think this would make Tableau work. >> >> But I am worried about connectivity from other SQL clients as i am planning >> in near future connectivity from Microsoft Power BI >> >>> On Wed, Dec 14, 2016 at 9:00 PM, Gian Merlino wrote: >>> >>> With Tableau you can use a TDC file to tweak the SQL generation. If you >> set >>> CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then >> Tableau >>> will avoid using aliases and ordinals for GROUP BY. See >>> http://kb.tableau.com/articles/knowledgebase/ >> customizing-odbc-connections >>> for more info. >>> >>> The relevant Calcite issue is >>> https://issues.apache.org/jira/browse/CALCITE-1306, but if all you care >>> about is getting Tableau to work then the TDC approach should do it. >>> >>> Gian >>> >>> On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat >> >>> wrote: >>> Hi Folks, I am using Apache Calcite to add SQL support for my custom data >> source. I receive SQLs from external SQL clients like Tableau and I have written >> an ODBC client for connecting to my data source, which basically delegates these SQLs (generated by Tableau) to my Java program where I use >> Calcite >>> to execute them. I am stuck at a place where I receive SQL which has GROUP BY clause >> that uses column ordinals instead of columns names and when I try to >> validate (parsing works fine) such SQLs through Calcite it fails. E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP BY >>> 1, 2 (The SQL says GROUP BY on prod_name and store_name fields which are >>> column ordinal 1 and 2 respectively in the SELECT clause) I tried delving into the code of calcite and per my understanding I >>> believe that usage of column ordinals is supported in ORDER BY clause but not >> in the GROUP BY. Primarily, I want to know Is my understanding about no support of >> column ordinals in GROUP BY correct? Or is there something I am missing? Secondarily, if anyone knows about ODBC, is there a way I can >> tell/force the Tableau to send me column names instead of column ordinals in the >>> SQL? -- Best Regards, Kapil Ghodawat contact: +91 94254 86638 >>> >> >> >> >> -- >> Best Regards, >> Kapil Ghodawat >> contact: +91 94254 86638 >>
RelDecorrelator on Logical operators
Hi Julian, Reldecorrelator’s logic including all rules implemented within it are written to take LogicalJoin, LogicalFilter, LogicalProject etc. Since Logical operators are final that makes extending RelDecorrelator very difficult. Is there any reason why RelDecorrelator is written in this way? It makes more sense to have RelDecorrelator operate on Join, Filter etc. Regards, Vineet G
Re: RelDecorrelator on Logical operators
I’ve no objection to this change in principle. We’d need to change RelDecorrelator to use RelNode factories (ideally a RelBuilder) so that it can create RelNodes of the appropriate sub-type. RelDecorrelator uses LogicalJoin etc. because that’s how it was originally written. It’s typically used early in the planning process, so only running on logical rels was never a hardship. Also, very few physical algebras even support correlation, so RelDecorrelator would be a no-op on these. Please log a JIRA case. Julian > On Dec 15, 2016, at 9:33 AM, Vineet Garg wrote: > > Hi Julian, > > Reldecorrelator’s logic including all rules implemented within it are written > to take LogicalJoin, LogicalFilter, LogicalProject etc. Since Logical > operators are final that makes extending RelDecorrelator very difficult. Is > there any reason why RelDecorrelator is written in this way? It makes more > sense to have RelDecorrelator operate on Join, Filter etc. > > Regards, > Vineet G
Re: RelDecorrelator on Logical operators
Thanks for the explanation Julian. I’ll create a JIRA to track this. Should the same be done for RelShuttle? This interface currently operate on Logical rels. On 12/15/16, 10:55 AM, "Julian Hyde" wrote: >I’ve no objection to this change in principle. We’d need to change >RelDecorrelator to use RelNode factories (ideally a RelBuilder) so that it can >create RelNodes of the appropriate sub-type. > >RelDecorrelator uses LogicalJoin etc. because that’s how it was originally >written. It’s typically used early in the planning process, so only running on >logical rels was never a hardship. Also, very few physical algebras even >support correlation, so RelDecorrelator would be a no-op on these. > >Please log a JIRA case. > >Julian > > > > >> On Dec 15, 2016, at 9:33 AM, Vineet Garg wrote: >> >> Hi Julian, >> >> Reldecorrelator’s logic including all rules implemented within it are >> written to take LogicalJoin, LogicalFilter, LogicalProject etc. Since >> Logical operators are final that makes extending RelDecorrelator very >> difficult. Is there any reason why RelDecorrelator is written in this way? >> It makes more sense to have RelDecorrelator operate on Join, Filter etc. >> >> Regards, >> Vineet G > >
Re: RelDecorrelator on Logical operators
> On Dec 15, 2016, at 11:53 AM, Vineet Garg wrote: … > Should the same be done for RelShuttle? This interface currently operate on > Logical rels. I think you could, but it will be a breaking change, so I’m not sure that you should. If projects have sub-classed RelShuttle and have overridden methods without using @Override they will break with no warning. I suggest that you log a separate JIRA case and do it it others feel that the breakage is worth it. Julian
[jira] [Created] (CALCITE-1541) Update RelDecorrelator to work on Filter, Join, Project etc
Vineet Garg created CALCITE-1541: Summary: Update RelDecorrelator to work on Filter, Join, Project etc Key: CALCITE-1541 URL: https://issues.apache.org/jira/browse/CALCITE-1541 Project: Calcite Issue Type: Task Components: core Reporter: Vineet Garg Assignee: Julian Hyde Reldecorrelator’s logic including all rules implemented within it are written to take LogicalJoin, LogicalFilter, LogicalProject etc Logical rel nodes. Since Logical operators are final that makes extending RelDecorrelator very difficult. It makes more sense to have RelDecorrelator operate on Join, Filter etc As Julian pointed out with this change RelDecorrelator will need to use RelNode factories (ideally a RelBuilder) so that it can create RelNodes of the appropriate sub-type -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (CALCITE-1542) Update RelShuttle interface to work on Filter, Join etc instead of Logical rel nodes
Vineet Garg created CALCITE-1542: Summary: Update RelShuttle interface to work on Filter, Join etc instead of Logical rel nodes Key: CALCITE-1542 URL: https://issues.apache.org/jira/browse/CALCITE-1542 Project: Calcite Issue Type: Task Reporter: Vineet Garg Assignee: Julian Hyde Currently RelShuttle interface works on Logical rel nodes (e.g. LogicalFilter etc). With CALCITE-1541's change on RelDecorrelator RelShuttle will need to be updated as well. Logging in a separate JIRA for it since Julian pointed out that this will be a breaking change. If projects have sub-classed RelShuttle and have overridden methods without using @Override they will break with no warning. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
Re: Column Ordinal Support in GROUP BY
Thanks Julian for writing this. I haven't checked yet if power BI is sending such SQLs but when you say Oracle also does not support this and it's not standard SQL it gave me a big relief. On Thu, Dec 15, 2016 at 10:17 PM, Julian Hyde wrote: > Do you know for sure that Power BI generates this kind of SQL? I suspect > it doesn't, because it's not standard SQL. And by the way, Oracle doesn't > support it. > > I have no idea why I'm Tableau is generating this flavor of SQL. > > Calcite's tool integration is in pretty good shape, and that's because > we've stayed close to the standard. > > There's no question that an extension like this would be nice, especially > for end users writing SQL by hand (and contributions are most welcome!), > but don't conflate it with tool integration. > > Julian > > > On Dec 15, 2016, at 06:21, Gian Merlino wrote: > > > > That, I'm not sure how to do. There might be a similar config there for > > Power BI. Ultimately it would be nice to have support for this in > Calcite, > > since it seems to be a pretty common extension to SQL (and makes many > > queries easier to write by hand) but I'm not a Calcite expert enough to > > know how hard that would be… > > > > Gian > > > > On Wed, Dec 14, 2016 at 9:57 PM, Kapil Ghodawat > > > wrote: > > > >> Thanks a lot Gian. I think this would make Tableau work. > >> > >> But I am worried about connectivity from other SQL clients as i am > planning > >> in near future connectivity from Microsoft Power BI > >> > >>> On Wed, Dec 14, 2016 at 9:00 PM, Gian Merlino wrote: > >>> > >>> With Tableau you can use a TDC file to tweak the SQL generation. If you > >> set > >>> CAP_QUERY_GROUP_BY_ALIAS and CAP_QUERY_GROUP_BY_DEGREE to "no" then > >> Tableau > >>> will avoid using aliases and ordinals for GROUP BY. See > >>> http://kb.tableau.com/articles/knowledgebase/ > >> customizing-odbc-connections > >>> for more info. > >>> > >>> The relevant Calcite issue is > >>> https://issues.apache.org/jira/browse/CALCITE-1306, but if all you > care > >>> about is getting Tableau to work then the TDC approach should do it. > >>> > >>> Gian > >>> > >>> On Wed, Dec 14, 2016 at 4:48 AM, Kapil Ghodawat < > kapilghoda...@gmail.com > >>> > >>> wrote: > >>> > Hi Folks, > > I am using Apache Calcite to add SQL support for my custom data > >> source. I > receive SQLs from external SQL clients like Tableau and I have written > >> an > ODBC client for connecting to my data source, which basically > delegates > these SQLs (generated by Tableau) to my Java program where I use > >> Calcite > >>> to > execute them. > > I am stuck at a place where I receive SQL which has GROUP BY clause > >> that > uses column ordinals instead of columns names and when I try to > >> validate > (parsing works fine) such SQLs through Calcite it fails. > > E.g. SELECT prod_name, store_name, sum(sales) from sales_table GROUP > BY > >>> 1, > 2 > > (The SQL says GROUP BY on prod_name and store_name fields which are > >>> column > ordinal 1 and 2 respectively in the SELECT clause) > > > I tried delving into the code of calcite and per my understanding I > >>> believe > that usage of column ordinals is supported in ORDER BY clause but not > >> in > the GROUP BY. > > Primarily, I want to know Is my understanding about no support of > >> column > ordinals in GROUP BY correct? Or is there something I am missing? > > Secondarily, if anyone knows about ODBC, is there a way I can > >> tell/force > the Tableau to send me column names instead of column ordinals in the > >>> SQL? > > > -- > Best Regards, > Kapil Ghodawat > contact: +91 94254 86638 > > >>> > >> > >> > >> > >> -- > >> Best Regards, > >> Kapil Ghodawat > >> contact: +91 94254 86638 > >> > -- Best Regards, Kapil Ghodawat contact: +91 94254 86638
[jira] [Created] (CALCITE-1543) Decorrelator assertion fail
Kurt Young created CALCITE-1543:
---
Summary: Decorrelator assertion fail
Key: CALCITE-1543
URL: https://issues.apache.org/jira/browse/CALCITE-1543
Project: Calcite
Issue Type: Bug
Reporter: Kurt Young
Assignee: Julian Hyde
Caused by: java.lang.AssertionError at
org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:456)
The code is:
{code}
final Frame frame = getInvoke(oldInput, rel);
if (frame == null) {
// If input has not been rewritten, do not rewrite this rel.
return null;
}
assert !frame.corVarOutputPos.isEmpty();
{code}
Seems like it's no need to assert the corVarOutputPos because in some cases,
the correlate has been fully rewritten.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (CALCITE-1544) AggregateJoinTransposeRule transform failed
Kurt Young created CALCITE-1544:
---
Summary: AggregateJoinTransposeRule transform failed
Key: CALCITE-1544
URL: https://issues.apache.org/jira/browse/CALCITE-1544
Project: Calcite
Issue Type: Bug
Reporter: Kurt Young
Assignee: Julian Hyde
Here is the sql to reproduce the failure:
{code}
select e.deptno
from sales.emp as e join sales.dept as d on e.deptno = d.deptno
group by e.deptno
{code}
After push the aggregator past join, the RowType seems been changed.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
