You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by "liuhuanyu@neusoft.com" <li...@neusoft.com> on 2016/08/19 02:12:00 UTC
SQL got unexpected result
Hi,I found there's a bug when I execute SQL in Insight window:
--I get the expect result when I use the where condition.
SQL1:
select a.*,b.aae019 from
(SELECT akc193,bkc231,akb020,akc190, aae072
FROm kc21_24_60_ka06 a
where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
group by akc193,bkc231,akb020,akc190,aae072) a
join
(SELECT akb020, akc190, aae072, SUm(aae019) aae019
FROm kc22_test
group by akb020, akc190, aae072) b
on a.akb020 = b.akb020
AND b.akc190 = a.akc190
AND b.aae072 = a.aae072;
result:
--when I remove the where condition,the result is null,that's not my expection.
SQL2:
select a.*,b.aae019 from
(SELECT akc193,bkc231,akb020,akc190, aae072
FROm kc21_24_60_ka06 a
--where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
group by akc193,bkc231,akb020,akc190,aae072) a
join
(SELECT akb020, akc190, aae072, SUm(aae019) aae019
FROm kc22_test
group by akb020, akc190, aae072) b
on a.akb020 = b.akb020
AND b.akc190 = a.akc190
AND b.aae072 = a.aae072;
result:
who can tell me why this happened?
best regards.
刘焕宇
技术发展部（TDD）
东软集团股份有限公司
沈阳浑南新区新秀街2号 东软软件园 A2-105A
Mobile：18602471510
Email：liuhuanyu@neusoft.com
---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is
not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------
Re: SQL got unexpected result
Posted by Li Yang <li...@apache.org>.
Please open a JIRA if you can reproduce the problem on sample cube.
On Fri, Aug 19, 2016 at 10:21 AM, liuhuanyu@neusoft.com <
liuhuanyu@neusoft.com> wrote:
> --I get the expect result when I use the where condition.
> SQL1:
> select a.*,b.aae019 from
> (SELECT akc193,bkc231,akb020,akc190, aae072
> FROm kc21_24_60_ka06 a
> where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
> group by akc193,bkc231,akb020,akc190,aae072) a
> join
> (SELECT akb020, akc190, aae072, SUm(aae019) aae019
> FROm kc22_test
> group by akb020, akc190, aae072) b
> on a.akb020 = b.akb020
> AND b.akc190 = a.akc190
> AND b.aae072 = a.aae072;
> result:
> R05 01
> 咳嗽
> 2189
> MZ492539
> MB492541
> 289.6
>
> --when I remove the where condition,the result is null,that's not my
> expection.
> SQL2:
> select a.*,b.aae019 from
> (SELECT akc193,bkc231,akb020,akc190, aae072
> FROm kc21_24_60_ka06 a
> --where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
> group by akc193,bkc231,akb020,akc190,aae072) a
> join
> (SELECT akb020, akc190, aae072, SUm(aae019) aae019
> FROm kc22_test
> group by akb020, akc190, aae072) b
> on a.akb020 = b.akb020
> AND b.akc190 = a.akc190
> AND b.aae072 = a.aae072;
> result:
> Results(0)
>
>
>
> 刘焕宇
>
> 技术发展部（TDD）
>
> 东软集团股份有限公司
> 沈阳浑南新区新秀街2号 东软软件园 A2-105A
> Mobile：18602471510
> Email：liuhuanyu@neusoft.com
>
> 发件人： liuhuanyu@neusoft.com
> 发送时间： 2016-08-19 10:12
> 收件人： dev
> 主题： SQL got unexpected result
> Hi,I found there's a bug when I execute SQL in Insight window:
>
> --I get the expect result when I use the where condition.
> SQL1:
> select a.*,b.aae019 from
> (SELECT akc193,bkc231,akb020,akc190, aae072
> FROm kc21_24_60_ka06 a
> where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
> group by akc193,bkc231,akb020,akc190,aae072) a
> join
> (SELECT akb020, akc190, aae072, SUm(aae019) aae019
> FROm kc22_test
> group by akb020, akc190, aae072) b
> on a.akb020 = b.akb020
> AND b.akc190 = a.akc190
> AND b.aae072 = a.aae072;
> result:
>
>
> --when I remove the where condition,the result is null,that's not my
> expection.
> SQL2:
> select a.*,b.aae019 from
> (SELECT akc193,bkc231,akb020,akc190, aae072
> FROm kc21_24_60_ka06 a
> --where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
> group by akc193,bkc231,akb020,akc190,aae072) a
> join
> (SELECT akb020, akc190, aae072, SUm(aae019) aae019
> FROm kc22_test
> group by akb020, akc190, aae072) b
> on a.akb020 = b.akb020
> AND b.akc190 = a.akc190
> AND b.aae072 = a.aae072;
> result:
>
>
> who can tell me why this happened?
>
> best regards.
>
>
>
> 刘焕宇
>
> 技术发展部（TDD）
>
> 东软集团股份有限公司
> 沈阳浑南新区新秀街2号 东软软件园 A2-105A
> Mobile：18602471510
> Email：liuhuanyu@neusoft.com
>
> ------------------------------------------------------------
> ---------------------------------------
> Confidentiality Notice: The information contained in this e-mail and any
> accompanying attachment(s)
> is intended only for the use of the intended recipient and may be
> confidential and/or privileged of
> Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader
> of this communication is
> not the intended recipient, unauthorized use, forwarding, printing,
> storing, disclosure or copying
> is strictly prohibited, and may be unlawful.If you have received this
> communication in error,please
> immediately notify the sender by return e-mail, and delete the original
> message and all copies from
> your system. Thank you.
> ------------------------------------------------------------
> ---------------------------------------
>
>
> ------------------------------------------------------------
> ---------------------------------------
> Confidentiality Notice: The information contained in this e-mail and any
> accompanying attachment(s)
> is intended only for the use of the intended recipient and may be
> confidential and/or privileged of
> Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader
> of this communication is
> not the intended recipient, unauthorized use, forwarding, printing,
> storing, disclosure or copying
> is strictly prohibited, and may be unlawful.If you have received this
> communication in error,please
> immediately notify the sender by return e-mail, and delete the original
> message and all copies from
> your system. Thank you.
> ------------------------------------------------------------
> ---------------------------------------
>
回复: SQL got unexpected result
Posted by "liuhuanyu@neusoft.com" <li...@neusoft.com>.
--I get the expect result when I use the where condition.
SQL1:
select a.*,b.aae019 from
(SELECT akc193,bkc231,akb020,akc190, aae072
FROm kc21_24_60_ka06 a
where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
group by akc193,bkc231,akb020,akc190,aae072) a
join
(SELECT akb020, akc190, aae072, SUm(aae019) aae019
FROm kc22_test
group by akb020, akc190, aae072) b
on a.akb020 = b.akb020
AND b.akc190 = a.akc190
AND b.aae072 = a.aae072;
result:
R05 01
咳嗽
2189
MZ492539
MB492541
289.6
--when I remove the where condition,the result is null,that's not my expection.
SQL2:
select a.*,b.aae019 from
(SELECT akc193,bkc231,akb020,akc190, aae072
FROm kc21_24_60_ka06 a
--where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
group by akc193,bkc231,akb020,akc190,aae072) a
join
(SELECT akb020, akc190, aae072, SUm(aae019) aae019
FROm kc22_test
group by akb020, akc190, aae072) b
on a.akb020 = b.akb020
AND b.akc190 = a.akc190
AND b.aae072 = a.aae072;
result:
Results(0)
刘焕宇
技术发展部（TDD）
东软集团股份有限公司
沈阳浑南新区新秀街2号 东软软件园 A2-105A
Mobile：18602471510
Email：liuhuanyu@neusoft.com
发件人： liuhuanyu@neusoft.com
发送时间： 2016-08-19 10:12
收件人： dev
主题： SQL got unexpected result
Hi,I found there's a bug when I execute SQL in Insight window:
--I get the expect result when I use the where condition.
SQL1:
select a.*,b.aae019 from
(SELECT akc193,bkc231,akb020,akc190, aae072
FROm kc21_24_60_ka06 a
where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
group by akc193,bkc231,akb020,akc190,aae072) a
join
(SELECT akb020, akc190, aae072, SUm(aae019) aae019
FROm kc22_test
group by akb020, akc190, aae072) b
on a.akb020 = b.akb020
AND b.akc190 = a.akc190
AND b.aae072 = a.aae072;
result:
--when I remove the where condition,the result is null,that's not my expection.
SQL2:
select a.*,b.aae019 from
(SELECT akc193,bkc231,akb020,akc190, aae072
FROm kc21_24_60_ka06 a
--where akb020='2189' and akc190 = 'MZ492539' and aae072 = 'MB492541'
group by akc193,bkc231,akb020,akc190,aae072) a
join
(SELECT akb020, akc190, aae072, SUm(aae019) aae019
FROm kc22_test
group by akb020, akc190, aae072) b
on a.akb020 = b.akb020
AND b.akc190 = a.akc190
AND b.aae072 = a.aae072;
result:
who can tell me why this happened?
best regards.
刘焕宇
技术发展部（TDD）
东软集团股份有限公司
沈阳浑南新区新秀街2号 东软软件园 A2-105A
Mobile：18602471510
Email：liuhuanyu@neusoft.com
---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is
not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is
not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------