You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@impala.apache.org by skyyws <sk...@163.com> on 2018/06/05 13:44:41 UTC
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
Re: Re: A question about AnalysisException
Posted by Greg Rahn <gr...@gmail.com>.
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 <sk...@163.com> wrote:
>
> This sql really worked, thanks a lot!
>
>
>
>
> 2018-06-06
> skyyws
>
>
>
> 发件人：Fredy Wijaya <fw...@cloudera.com>
> 发送时间：2018-06-06 10:14
> 主题：Re: A question about AnalysisException
> 收件人："dev"<de...@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 <sk...@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:skyyws@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 <sk...@163.com>
> > 发送时间：2018-06-05 21:44
> > 主题：A question about AnalysisException
> > 收件人："dev@impala.apache.org"<de...@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: Re: A question about AnalysisException
Posted by skyyws <sk...@163.com>.
This sql really worked, thanks a lot!
2018-06-06
skyyws
发件人：Fredy Wijaya <fw...@cloudera.com>
发送时间：2018-06-06 10:14
主题：Re: A question about AnalysisException
收件人："dev"<de...@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 <sk...@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:skyyws@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 <sk...@163.com>
> 发送时间：2018-06-05 21:44
> 主题：A question about AnalysisException
> 收件人："dev@impala.apache.org"<de...@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
Posted by Fredy Wijaya <fw...@cloudera.com>.
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 <sk...@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:skyyws@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 <sk...@163.com>
> 发送时间：2018-06-05 21:44
> 主题：A question about AnalysisException
> 收件人："dev@impala.apache.org"<de...@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
>
>
return value is wrong
Posted by 周胜为 <86...@qq.com>.
I develop a UDA function as follow:
input:string
Output:string
I want to caculate every name' md5 code and then caculate the md5 code with XOR. I will get one value which is all md5 code caculate.
md5 code is saved as unsigned char [16], as I know StringVal has a member called ptr(uint8_t *) I set the md5 code(caculate result) as StringVal' ptr.
Every time I get null value,that is why?
------------------ 原始邮件 ------------------
发件人: "skyyws"<sk...@163.com>;
发送时间: 2018年6月6日(星期三) 上午10:08
收件人: "dev@impala.apache.org"<de...@impala.apache.org>;
主题: 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<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:skyyws@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 <sk...@163.com>
发送时间：2018-06-05 21:44
主题：A question about AnalysisException
收件人："dev@impala.apache.org"<de...@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
Posted by skyyws <sk...@163.com>.
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:skyyws@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 <sk...@163.com>
发送时间：2018-06-05 21:44
主题：A question about AnalysisException
收件人："dev@impala.apache.org"<de...@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
Posted by Mike Labman <mi...@gwsolutions.com>.
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:skyyws@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 <sk...@163.com>
发送时间：2018-06-05 21:44
主题：A question about AnalysisException
收件人："dev@impala.apache.org"<de...@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
Posted by skyyws <sk...@163.com>.
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 <sk...@163.com>
发送时间：2018-06-05 21:44
主题：A question about AnalysisException
收件人："dev@impala.apache.org"<de...@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