升级scql版本后，原来可以执行的sql现在不能执行了

[EtanWatson]

Issue Type

Running

Have you searched for existing issues?

Yes

OS Platform and Distribution

centos 7.8

SCQL Version

0.9.3.b1

What happend and What you expected to happen.

SELECT 
    X.ANONYMIZED_ID,
    X.CUSTOMER_CODE,
    X.NEW_INSTALL_CNT,
    X.CANCEL_SERVICE_CNT,
    X.BASIC_SERVICE_CNT,
    X.BROADBAND_CNT,
    D.UPGRADE_CNT,
    D.DOWNGRADE_CNT 
FROM (
    SELECT 
        A.ANONYMIZED_ID,
        C.CUSTOMER_CODE,
        C.NEW_INSTALL_CNT,
        C.CANCEL_SERVICE_CNT,
        C.BASIC_SERVICE_CNT,
        C.BROADBAND_CNT 
    FROM 
        T_CUSTOMER_MASTER A 
    LEFT JOIN (
        SELECT 
            T2.CUSTOMER_CODE,
            T2.BASIC_SERVICE_CNT,
            T2.BROADBAND_CNT,
            T2.NEW_INSTALL_CNT,
            T1.CANCEL_SERVICE_CNT 
        FROM (
            SELECT 
                CUSTOMER_CODE,
                SUM(CASE 
                    WHEN SERVICE_TYPE IN ('10', '16', '17', '33', '50') 
                    AND ACTIVE_FLAG = '0' THEN 1 ELSE 0 
                END) BASIC_SERVICE_CNT,
                SUM(CASE 
                    WHEN SERVICE_TYPE IN ('40') 
                    AND ACTIVE_FLAG = '0' THEN 1 ELSE 0 
                END) BROADBAND_CNT,
                SUM(CASE 
                    WHEN SERVICE_TYPE IN ('10', '16', '17', '33', '50') 
                    AND STR_TO_DATE(ACTIVE_DATE, '%Y%m%d%H%M%S') > SUBDATE(STR_TO_DATE(ACTIVE_DATE, '%Y%m%d%H%M%S'), INTERVAL 1 YEAR) 
                    THEN 1 ELSE 0 
                END) NEW_INSTALL_CNT 
            FROM 
                T_SERVICE_RECORDS 
            WHERE 
                ACTIVE_DATE IS NOT NULL 
                AND ACTIVE_DATE != ''
            GROUP BY 
                CUSTOMER_CODE 
        ) T2 
        LEFT JOIN (
            SELECT 
                CUSTOMER_CODE,
                SUM(CASE 
                    WHEN SERVICE_TYPE IN ('10', '16', '17', '33', '50') 
                    AND STR_TO_DATE(CLOSE_DATE, '%Y%m%d%H%M%S') > SUBDATE(STR_TO_DATE(CLOSE_DATE, '%Y%m%d%H%M%S'), INTERVAL 1 YEAR) 
                    THEN 1 ELSE 0 
                END) CANCEL_SERVICE_CNT 
            FROM 
                T_SERVICE_RECORDS 
            WHERE 
                CLOSE_DATE IS NOT NULL 
                AND CLOSE_DATE != ''
            GROUP BY 
                CUSTOMER_CODE 
        ) T1 
        ON T2.CUSTOMER_CODE = T1.CUSTOMER_CODE 
    ) C 
    ON A.CUSTOMER_CODE = C.CUSTOMER_CODE 
) X 
LEFT JOIN (
    SELECT 
        CUSTOMER_CODE,
        SUM(CASE 
            WHEN TRANSACTION_TYPE IN ('236', '136') 
            AND STR_TO_DATE(REQUEST_DATE, '%Y%m%d%H%M%S') > SUBDATE(STR_TO_DATE(REQUEST_DATE, '%Y%m%d%H%M%S'), INTERVAL 1 YEAR) 
            THEN 1 ELSE 0 
        END) UPGRADE_CNT,
        SUM(CASE 
            WHEN TRANSACTION_TYPE IN ('226', '126') 
            AND STR_TO_DATE(REQUEST_DATE, '%Y%m%d%H%M%S') > SUBDATE(STR_TO_DATE(REQUEST_DATE, '%Y%m%d%H%M%S'), INTERVAL 1 YEAR) 
            THEN 1 ELSE 0 
        END) DOWNGRADE_CNT 
    FROM 
        T_TRANSACTION_HISTORY 
    WHERE 
        TRANSACTION_STATUS = '9'
        AND PROCESSING_FLAG = '0'
        AND CANCELLATION_FLAG = '0'
    GROUP BY 
        CUSTOMER_CODE 
) D 
ON X.CUSTOMER_CODE = D.CUSTOMER_CODE 这个是我当前sql，之前版本是0.9.1.b1下是可以执行的，但是升版本以后报错

Configuration used to run SCQL.

SELECT 
    X.ANONYMIZED_ID,
    X.CUSTOMER_CODE,
    X.NEW_INSTALL_CNT,
    X.CANCEL_SERVICE_CNT,
    X.BASIC_SERVICE_CNT,
    X.BROADBAND_CNT,
    D.UPGRADE_CNT,
    D.DOWNGRADE_CNT 
FROM (
    SELECT 
        A.ANONYMIZED_ID,
        C.CUSTOMER_CODE,
        C.NEW_INSTALL_CNT,
        C.CANCEL_SERVICE_CNT,
        C.BASIC_SERVICE_CNT,
        C.BROADBAND_CNT 
    FROM 
        T_CUSTOMER_MASTER A 
    LEFT JOIN (
        SELECT 
            T2.CUSTOMER_CODE,
            T2.BASIC_SERVICE_CNT,
            T2.BROADBAND_CNT,
            T2.NEW_INSTALL_CNT,
            T1.CANCEL_SERVICE_CNT 
        FROM (
            SELECT 
                CUSTOMER_CODE,
                SUM(CASE 
                    WHEN SERVICE_TYPE IN ('10', '16', '17', '33', '50') 
                    AND ACTIVE_FLAG = '0' THEN 1 ELSE 0 
                END) BASIC_SERVICE_CNT,
                SUM(CASE 
                    WHEN SERVICE_TYPE IN ('40') 
                    AND ACTIVE_FLAG = '0' THEN 1 ELSE 0 
                END) BROADBAND_CNT,
                SUM(CASE 
                    WHEN SERVICE_TYPE IN ('10', '16', '17', '33', '50') 
                    AND STR_TO_DATE(ACTIVE_DATE, '%Y%m%d%H%M%S') > SUBDATE(STR_TO_DATE(ACTIVE_DATE, '%Y%m%d%H%M%S'), INTERVAL 1 YEAR) 
                    THEN 1 ELSE 0 
                END) NEW_INSTALL_CNT 
            FROM 
                T_SERVICE_RECORDS 
            WHERE 
                ACTIVE_DATE IS NOT NULL 
                AND ACTIVE_DATE != ''
            GROUP BY 
                CUSTOMER_CODE 
        ) T2 
        LEFT JOIN (
            SELECT 
                CUSTOMER_CODE,
                SUM(CASE 
                    WHEN SERVICE_TYPE IN ('10', '16', '17', '33', '50') 
                    AND STR_TO_DATE(CLOSE_DATE, '%Y%m%d%H%M%S') > SUBDATE(STR_TO_DATE(CLOSE_DATE, '%Y%m%d%H%M%S'), INTERVAL 1 YEAR) 
                    THEN 1 ELSE 0 
                END) CANCEL_SERVICE_CNT 
            FROM 
                T_SERVICE_RECORDS 
            WHERE 
                CLOSE_DATE IS NOT NULL 
                AND CLOSE_DATE != ''
            GROUP BY 
                CUSTOMER_CODE 
        ) T1 
        ON T2.CUSTOMER_CODE = T1.CUSTOMER_CODE 
    ) C 
    ON A.CUSTOMER_CODE = C.CUSTOMER_CODE 
) X 
LEFT JOIN (
    SELECT 
        CUSTOMER_CODE,
        SUM(CASE 
            WHEN TRANSACTION_TYPE IN ('236', '136') 
            AND STR_TO_DATE(REQUEST_DATE, '%Y%m%d%H%M%S') > SUBDATE(STR_TO_DATE(REQUEST_DATE, '%Y%m%d%H%M%S'), INTERVAL 1 YEAR) 
            THEN 1 ELSE 0 
        END) UPGRADE_CNT,
        SUM(CASE 
            WHEN TRANSACTION_TYPE IN ('226', '126') 
            AND STR_TO_DATE(REQUEST_DATE, '%Y%m%d%H%M%S') > SUBDATE(STR_TO_DATE(REQUEST_DATE, '%Y%m%d%H%M%S'), INTERVAL 1 YEAR) 
            THEN 1 ELSE 0 
        END) DOWNGRADE_CNT 
    FROM 
        T_TRANSACTION_HISTORY 
    WHERE 
        TRANSACTION_STATUS = '9'
        AND PROCESSING_FLAG = '0'
        AND CANCELLATION_FLAG = '0'
    GROUP BY 
        CUSTOMER_CODE 
) D 
ON X.CUSTOMER_CODE = D.CUSTOMER_CODE

SCQL log output.

WARN[0500] content-type (text/plain; charset=utf-8) is not one of application/json and application/x-protobuf 
[fetch]err: Code: 320, message:RunExecutionPlan run jobs(92b2784f-559a-11f0-9d7e-0242ac160002) failed, catch std::exception=[Enforce fail at engine/datasource/csvdb_adaptor.cc:194] !duck_result->HasError(). send query to DuckDB failed, msg=Binder Error: Referenced table "C" not found!
Candidate tables: "D", "A"
LINE 1: ...evt_cb_trade_his"."CUSTOMER_CODE ") as "D" on "C"."NEW_INSTALL_CNT"="D"."CUSTOMER_CODE";

[jingshi-ant]

建议对比下升级前后broker（scdb）日志里的digraph，看看是否有明显变化。
可能是CCL授权的变化导致执行流程有变化

[EtanWatson]

建议对比下升级前后broker（scdb）日志里的digraph，看看是否有明显变化。 可能是CCL授权的变化导致执行流程有变化

老师，这种的可以通过REVOKE ccl后重新赋权解决吗？还是需要重新再scdb上删除表建表

[jingshi-ant]

可以试一下revoke ccl

[EtanWatson]

可以试一下revoke ccl

我试了一下，这种不生效，我是直接修改镜像版本将scql从0.9.1b1升级到的0.9.3b1，是不是有哪些兼容的地方导致的

[EtanWatson]

我发现生成的子查询的图里面和我写的sql对不上，这种的一般怎么导致的

[jingshi-ant]

有点奇怪，数据源类型有变更吗？ csv数据源应该一直不支持子查询里有两张单方表（mysql可以）

[EtanWatson]

里有两张单

一直是csv数据源，数据源一直没变过，而且任务一直定时调度执行，但是我升级版本以后，就不行了

[jingshi-ant]

对比升级前，digraph哪些东西变化了呢？子查询和原始query不一样正常，子查询只是获取需要的单方数据

[EtanWatson]

对比升级前，digraph哪些东西变化了呢？子查询和原始query不一样正常，子查询只是获取需要的单方数据

老师，下面是对比主要的差距，想同的sql在不同的版本的差距，不知道是否有影响,左边是高版本0.9.3b1，右边是低版本0.9.1b1

日志0.9.3b1.txt
日志0.9.1b1.txt

[jingshi-ant]

简单看了下执行图，任务本身只有runsql+publish不复杂，主要是sql一大坨，可以把sql format 后对比一下区别。

[jingshi-ant]

或者，你的文件传错了？

[EtanWatson]

或者，你的文件传错了？

老师，有一个奇怪的问题，就是我这边提交的是a.x，但是在图里面就变成c.x了 ，在低版本中就不会这样转，之前的日志好像传错了

日志0.9.3b1.txt
日志0.9.1b1.txt

[tyrone-yu]

或者，你的文件传错了？

老师，有一个奇怪的问题，就是我这边提交的是a.x，但是在图里面就变成c.x了 ，在低版本中就不会这样转，之前的日志好像传错了
日志0.9.3b1.txt 日志0.9.1b1.txt

日志里面对应的 query 感觉跟你 issue 里面的 query 对不上，0.9.3 这个日志对应执行的 query 是哪个啊？

[EtanWatson]

或者，你的文件传错了？

老师，有一个奇怪的问题，就是我这边提交的是a.x，但是在图里面就变成c.x了 ，在低版本中就不会这样转，之前的日志好像传错了
日志0.9.3b1.txt 日志0.9.1b1.txt

日志里面对应的 query 感觉跟你 issue 里面的 query 对不上，0.9.3 这个日志对应执行的 query 是哪个啊？

issue最开始的那个是另外一个sql，现在我们升级以后，有好几个sql都是这个问题，现在传的就是另外一个sql

SELECT a.SVC_NUM AS SVC_NUM, a.etl_time, a.cz_xq_id, a.cz_xq, a.cover, a.cz_area_name, a.cz_county_name, a.cz_zone_name, IF((d.account_avg3 <= 30) OR d.account_avg3 IS NULL, '1', IF(d.account_avg3 <= 100, '2', '3')) AS account_avg3, IF(a.is_out_flux = '1', '是', '否') AS is_out_flux, IF((a.visit_dura <= 21) OR a.visit_dura IS NULL, '1', '2') AS visit_dura FROM( SELECT a.svc_num AS SVC_NUM, a.etl_time, a.cz_xq_id, a.cz_xq, a.cover, a.cz_area_name, a.cz_county_name, a.cz_zone_name, c.is_out_flux, c.visit_dura FROM zbsjfwvfxe_dwd_m_user_kyrh_tar AS a LEFT JOIN( SELECT device_number, is_out_flux, visit_dura FROM zbsjfwvfxe_esd_m_cus_cb_sing_flux_mb WHERE month_id = '202505') AS c ON a.svc_num = c.device_number) AS a LEFT JOIN( SELECT device_number, account_avg3 FROM zbsjfwvfxe_esd_m_cus_cb_use_mb WHERE month_id = '202505') AS d ON a.SVC_NUM = d.device_number; 

执行的是这个sql，上面的日志文件也是这个sql

[EtanWatson]

这个日志和上面的那个图片日志还有文件日志里面是一致的，明明sql是a，但是解析后就变成c了，但是c本来就找不到~