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 <[email protected]> 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 <[email protected]> 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 <[email protected] > > > > 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 <[email protected]> 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 < > [email protected] > >>> > >>> 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
