I think the issue is with the group by and using the column name and alias clause 'group by valid_from as valid_from_raw'.
Removing the alias clause in the group by statement works as Aman shows. Can you check your Tableau TDC file to see if these lines are present. <customization name='CAP_QUERY_GROUP_BY_ALIAS' value='no' /> <customization name='CAP_QUERY_GROUP_BY_DEGREE' value='no' /> --Andries > On Dec 21, 2015, at 8:25 AM, Aman Sinha <[email protected]> wrote: > > The aliases work for me in the following query similar to yours. However, > I am using latest master branch and running directly through sqlline > command, not through Tableau. Can you confirm what Drill version you are > using and check if you can repro the behavior through sqlline ? If so, you > should file a JIRA. > > select o_custkey as x, date_add(to_date(o_orderdate), 1) as y from > cp.`tpch/orders.parquet` where o_orderkey < 10 group by o_custkey, > date_add(to_date(o_orderdate), 1) limit 2; > +------+-------------+ > | x | y | > +------+-------------+ > | 370 | 1996-01-03 | > | 781 | 1996-12-02 | > +------+-------------+ > > On Mon, Dec 21, 2015 at 2:18 AM, Petri Lehtonen <[email protected]> > wrote: > >> Hi all, >> >> I'm using Tableau with MapR Drill ODBC drivers. I have successfully set >> up the drivers and am able to query the data from our source which is >> basically parquet files. All seems well as long as I'm using all rows from >> the source but when I try to limit the size of the results by grouping and >> rolling up the data I came across something that feels like a bug: >> >> When I use any functions that return dates and add a group by to my query, >> the column alias in the underlying sql won't show up in the resultset. >> >> Plain select from the source works: >> >> SELECT >> valid_from as valid_from_raw, >> date_add(to_date(valid_from),1)as valid_from_to_date >> FROM >> dw.`/directory/parquet-file` >> WHERE >> valid_from = '1356991200000' >> >> ---------------+--------------------- >> valid_from_raw | valid_from_to_date >> ---------------+--------------------- >> 1356991200000 | 2013-01-01 >> ---------------+--------------------- >> >> When you add GROUP BY to the SQL, Drill looses the original column alias >> and replaces it with it's internal variable or something similar ($f1..n): >> >> SELECT >> valid_from as valid_from_raw, >> date_add(to_date(valid_from),1) as valid_from_to_date >> FROM >> dw.`/directory/parquet-file` >> WHERE >> valid_from = '1356991200000' >> GROUP BY >> valid_from as valid_from_raw, >> date_add(to_date(valid_from),1) >> >> ---------------+------------- >> valid_from_raw | $f1 >> ---------------+------------- >> 1356991200000 | 2013-01-01 >> ---------------+------------- >> >> And naturally this creates a problem with Tableau which is mapping the >> column aliases from the initial SQL and fails to find the corresponding >> aliases from the resultset. Only way to get this working is to use $f0..n >> column aliases in the original query and rename them in Tableau... >> Something that one definitely shouldn't do. I'd appreciate if anyone has a >> solution or a more solid workaround. >> >> Thanks, >> Petri >> >>
