Tableau TDC has the lines present. The Drill version we have is 1.3.0. MapR developer preview.
I ran the query using sqlline and it gives me the same results: SELECT DISTINCT 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, date_add(to_date(valid_from),1) +-----------------+-------------+ | valid_from_raw | $f1 | +-----------------+-------------+ | 1356991200000 | 2013-01-01 | +-----------------+-------------+ And I get the same results regardless of the tool I'm using (sqlline, Drill explorer, dbVisualizer, Tableau). > -----Original Message----- > From: Andries Engelbrecht [mailto:[email protected]] > Sent: 21. joulukuuta 2015 18:53 > To: [email protected] > Subject: Re: Column aliases lost when using Dates + GROUP BY in SQL > > 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 > >> > >>
