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 >
