This sql really worked, thanks a lot!
2018-06-06 skyyws 发件人：Fredy Wijaya <fwij...@cloudera.com> 发送时间：2018-06-06 10:14 主题：Re: A question about AnalysisException 收件人："dev"<dev@impala.apache.org> 抄送： It looks like a bug in Impala SQL rewrite. We've had quite a number of SQL rewrite bugs lately. Try disabling the SQL rewrite by running "set ENABLE_EXPR_REWRITES=0". [localhost:21000] default> select datediff(day,now()) from test_table where day>=(now() - interval 5 days) group by datediff(day,now()); Query: select datediff(day,now()) from test_table where day>=(now() - interval 5 days) group by datediff(day,now()) Query submitted at: 2018-06-05 21:11:23 (Coordinator: http://impala-dev:25000) ERROR: AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): datediff(day, TIMESTAMP '2018-06-05 21:11:23.320564000') [localhost:21000] default> set ENABLE_EXPR_REWRITES=0; ENABLE_EXPR_REWRITES set to 0 [localhost:21000] default> select datediff(day,now()) from test_table where day>=(now() - interval 5 days) group by datediff(day,now()); Query: select datediff(day,now()) from test_table where day>=(now() - interval 5 days) group by datediff(day,now()) Query submitted at: 2018-06-05 21:11:31 (Coordinator: http://impala -dev:25000) Query progress can be monitored at: http://impala-dev:25000/ query_plan?query_id=b64c8eabcedc58e5:da9edc8600000000 <http://fwijaya-dev:25000/query_plan?query_id=b64c8eabcedc58e5:da9edc8600000000> Fetched 0 row(s) in 0.21s On Tue, Jun 5, 2018 at 9:08 PM, skyyws <sky...@163.com> wrote: > Thanks for your reply, and I knew that both column number and alias worked > like this: > ------ > select datediff(day,now()) from test_table where day>='2018-06-01' group > by 1 > select datediff(day,now()) d from test_table where day>='2018-06-01' group > by d > ------ > I just wonder why built-in function in where clause instead of constants > would resulting in this exception. It's impala syntax ? > On 06/5/2018 21:54，Mike Labman<mi...@gwsolutions.com> wrote： > Just use column number in your group by. > > select datediff(day,now()) from test_table where day>='2018-06-01' group > by 1 > > -----Original Message----- > From: skyyws [mailto:sky...@163.com] > Sent: Tuesday, June 05, 2018 9:49 AM > To: dev > Subject: Re: A question about AnalysisException > > Here is the corret result of the sql below: > select datediff(day,now()) from test_table where day>='2018-06-01' group > by datediff(day,now()); > > | datediff(day, now()) | > +----------------------+ > | -4 | > | 0 | > | -3 | > | -1 | > | -2 | > > > > > 2018-06-05 > skyyws > > > > 发件人：skyyws <sky...@163.com> > 发送时间：2018-06-05 21:44 > 主题：A question about AnalysisException > 收件人："dev@impala.apache.org"<dev@impala.apache.org> > 抄送： > > Hello all, > Recently, I found a probelm when I used impala to do ad-hoc analysis. When > I executed the sql below: > select datediff(day,now()) from test_table where day>=(now() - interval 5 > days) group by datediff(day,now()); > I got an exception like this: > ------------------------------------------------------------ > ----------------------------------------------------------------- > Status: AnalysisException: select list expression not produced by > aggregation output (missing from GROUP BY clause?): datediff(day, TIMESTAMP > '2018-06-05 21:24:28.403393000') > ------------------------------------------------------------ > ----------------------------------------------------------------- > and if I execute this sql: > select datediff(day,now()) from test_table where day>='2018-06-01' group > by datediff(day,now()); > I got the correct result like this: > > This situation happend both on 2.10.0 and 3.0.0 version. > I'm not sure it's a bug or it's just designed like this, anyone who can > give me some advice? Thanks. > (test_table is stored as parquet, and day is the partition column, string > type.) > ------------------------------------------------------------ > -------------------- > 2018-06-05 > skyyws > > ________________________________ > > Confidentiality Note: This e-mail, and any attachment to it, contains > privileged and confidential information intended only for the use of the > individual(s) or entity named on the e-mail. If the reader of this e-mail > is not the intended recipient, or the employee or agent responsible for > delivering it to the intended recipient, you are hereby notified that > reading it is strictly prohibited. If you have received this e-mail in > error, please immediately return it to the sender and delete it from your > system. Thank you > >
