skyyws - Can you file a bug with the code to reproduce it if you have
not yet done so already?
On Tue, Jun 5, 2018 at 7:28 PM skyyws <sky...@163.com> wrote:
>
> 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
> >
> >
