回复: 【求助！】sql报错问题

[guojial...@smartbi.com.cn]

请问有没有哪位知道为什么呢？




 
发件人： guojial...@smartbi.com.cn
发送时间： 2020-09-29 12:08
收件人： user@kylin.apache.org
主题： 【求助！】sql报错问题
各位大大好，
    我有一个让我非常困惑的问题
    像下面这个sql，执行的时候会报错。
    

    select
        T59780.F1126 as F59781,
        '_Insight_Total' as F59782,
        case
            when ( sum(T59780.F1152) - sum(T59780.F1163) ) = 0 then 0
            else ( sum(case
                when T59780.F1169='1' then T59780.F1165
                else 0
            end) + sum( case
                when T59780.F1169='1' then T59780.F1167 + T59780.F1168
                else 0
            end) ) / ( sum(T59780.F1152) - sum(T59780.F1163) )
        end as F59783
    from
        (select
            T1199.COMNAME1 as F1112,
            T1198.PT as F1126,
            T1198.CHGNTPREMIUM as F1152,
            T1198.NODUTYNTPREMIUM as F1163,
            T1198.CATASTROPHEFLAG as F1169,
            T1198.YJ_SUMLOSS as F1165,
            T1198.WJ_SUMLOSS as F1167,
            T1198.WJ_SUMCLAIM as F1168
        from
            STAT_NOCAR_ADS_HIVE.ADS_PROP_STATEMENT_DF T1198
        left outer join
            STAT_NOCAR_DIM_HIVE.DIM_NOCAR_COMPANYLEVEL T1199
                on T1198.COMCODE = T1199.COMCODE
        where
            T1199.COMCODE1 IN (
                '05000000'
            )
            and T1198.PT ='2020-08-30') T59780
    group by
        T59780.F1126   

    报错信息见附件。
    
    但是当我在这个sql后面加上“limit 100000”，就可以通过jdbc正常执行了。
    
    sql如下：
    

    select
        T59780.F1126 as F59781,
        '_Insight_Total' as F59782,
        case
            when ( sum(T59780.F1152) - sum(T59780.F1163) ) = 0 then 0
            else ( sum(case
                when T59780.F1169='1' then T59780.F1165
                else 0
            end) + sum( case
                when T59780.F1169='1' then T59780.F1167 + T59780.F1168
                else 0
            end) ) / ( sum(T59780.F1152) - sum(T59780.F1163) )
        end as F59783
    from
        (select
            T1199.COMNAME1 as F1112,
            T1198.PT as F1126,
            T1198.CHGNTPREMIUM as F1152,
            T1198.NODUTYNTPREMIUM as F1163,
            T1198.CATASTROPHEFLAG as F1169,
            T1198.YJ_SUMLOSS as F1165,
            T1198.WJ_SUMLOSS as F1167,
            T1198.WJ_SUMCLAIM as F1168
        from
            STAT_NOCAR_ADS_HIVE.ADS_PROP_STATEMENT_DF T1198
        left outer join
            STAT_NOCAR_DIM_HIVE.DIM_NOCAR_COMPANYLEVEL T1199
                on T1198.COMCODE = T1199.COMCODE
        where
            T1199.COMCODE1 IN (
                '05000000'
            )
            and T1198.PT ='2020-08-30') T59780
    group by
        T59780.F1126    limit 100000

    我找了许久资料，也没明白是为什么。难道是我使用的kylin版本的bug吗？
    非常期待您的来信~感谢！



Jaylin