Re: Re: A question about AnalysisException
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 wrote: > > This sql really worked, thanks a lot! > > > > > 2018-06-06 > skyyws > > > > 发件人：Fredy Wijaya > 发送时间：2018-06-06 10:14 > 主题：Re: A question about AnalysisException > 收件人："dev" > 抄送： > > 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:da9edc86 > <http://fwijaya-dev:25000/query_plan?query_id=b64c8eabcedc58e5:da9edc86> > Fetched 0 row(s) in 0.21s > > On Tue, Jun 5, 2018 at 9:08 PM, skyyws 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 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 > > 发送时间：2018-06-05 21:44 > > 主题：A question about AnalysisException > > 收件人："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 > > > >
Re: A question about AnalysisException
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 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 发送时间：2018-06-05 21:44 主题：A question about AnalysisException 收件人："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
RE: A question about AnalysisException
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 发送时间：2018-06-05 21:44 主题：A question about AnalysisException 收件人："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
A question about AnalysisException
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
