[Bug] Query incompatibility occurs in the upgraded version

[sunny19930321]

Search before asking

• I had searched in the issues and found no similar issues.

Version

1.2.7 -> 2.0.9

What's Wrong?

an exception occurred HAVING COUNT(DISTINCT)

ERROR 1105 (HY000): errCode = 2, detailMessage = multi_distinct_count can't support multi distinct.
2024-05-07 14:31:10,954 WARN (mysql-nio-pool-778|81152) [StmtExecutor.executeByLegacy():838] execute Exception. stmt[530703, cf1e56c1fb7542c0-9f9c1e21b247b6bf]
org.apache.doris.common.AnalysisException: errCode = 2, detailMessage = multi_distinct_count can't support multi distinct.
at org.apache.doris.analysis.SelectStmt.createMultiDistinctAggSMap(SelectStmt.java:1695) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.SelectStmt.analyzeAggregation(SelectStmt.java:1436) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.SelectStmt.analyze(SelectStmt.java:692) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.InlineViewRef.analyze(InlineViewRef.java:198) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.FromClause.analyze(FromClause.java:146) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.SelectStmt.analyze(SelectStmt.java:505) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.InlineViewRef.analyze(InlineViewRef.java:198) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.FromClause.analyze(FromClause.java:146) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.SelectStmt.analyze(SelectStmt.java:505) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.NativeInsertStmt.analyzeSubquery(NativeInsertStmt.java:563) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.analysis.NativeInsertStmt.analyze(NativeInsertStmt.java:323) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.StmtExecutor.analyzeAndGenerateQueryPlan(StmtExecutor.java:1190) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.StmtExecutor.analyze(StmtExecutor.java:1040) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.StmtExecutor.executeByLegacy(StmtExecutor.java:731) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:503) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.StmtExecutor.execute(StmtExecutor.java:471) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:443) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.ConnectProcessor.dispatch(ConnectProcessor.java:592) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.qe.ConnectProcessor.processOnce(ConnectProcessor.java:858) ~[doris-fe.jar:1.2-SNAPSHOT]
at org.apache.doris.mysql.ReadListener.lambda$handleEvent$0(ReadListener.java:52) ~[doris-fe.jar:1.2-SNAPSHOT]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_272]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_272]
at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_272]

What You Expected?

compatibility with older version queries

How to Reproduce?

INSERT INTO ads_bi_sale_session_conversation_enabled_tenant_d
select m1.tenant_id,
       m1.corp_name,
       m1.industry,
       m1.sub_industry,
       m1.enabled,
       (case when m1.gmt_create is not null then m1.gmt_create else m1.create_time end)   as create_time,
       (case when m1.end_time_xj is not null then m1.end_time_xj else m1.end_time_pz end) as end_time,
       m1.max_use,
       m1.user_total,
       m1.session_normal_accounts,
       m1.session_historical_accounts,
       m1.plugin_type,
       m1.secret_type,
       m1.sale,
       m1.operate
from (SELECT t10.tenant_id,
             t2.gmt_create,
             t2.end_time_xj,
             t2.max_use,
             t3.create_time,
             t3.end_time_pz,
             t3.enabled,
             t2.plugin_type,
             t3.secret_type,
             t4.corp_name,
             t4.industry,
             t4.sub_industry,
             t4.user_total,
             t4.sale,
             t4.operate,
             t5.session_normal_accounts,
             t5.session_historical_accounts

      FROM (
               SELECT tenant_id,
                      COUNT(*) AS count
               FROM (SELECT tripartite_id AS tenant_id
                   FROM ims_wework_setting_d
                   WHERE tripartite_id IS NOT NULL
                   AND create_time < end_time
                   UNION ALL
                   SELECT tenant_id
                   FROM qw_base_tenant_info_plugin_d
                   WHERE plugin_code = 'sa'
                   AND end_time > gmt_create
                   AND enabled = 1) AS combined
               GROUP BY tenant_id) AS t10
               LEFT JOIN (
          SELECT tenant_id,
                 gmt_create,
                 end_time AS end_time_xj,
                 '1'      as plugin_type,
                 max_use
          FROM qw_base_tenant_info_plugin_d
          WHERE plugin_code = 'sa'
            AND end_time > gmt_create
            AND enabled = 1) AS t2 ON t10.tenant_id = t2.tenant_id
               LEFT JOIN (
          SELECT tripartite_id,
                 create_time,
                 end_time AS end_time_pz,
                 '1'      as secret_type,
                 enabled
          FROM ims_wework_setting_d
          WHERE tripartite_id IS NOT NULL
            AND create_time < end_time) AS t3 ON t10.tenant_id = t3.tripartite_id
               left join
               ads_bi_mbr_tenant_corp_table_d
               AS t4 ON t10.tenant_id = t4.tenant_id
               left join (
          SELECT tenant_id
               , COUNT(DISTINCT
                       (CASE WHEN session_status = 0 THEN user_id END)) AS session_normal_accounts
               , COUNT(DISTINCT (CASE
                                     WHEN session_status = 0 OR session_status = 2
                                         THEN user_id END))             AS session_historical_accounts
          FROM qw_user_d
          WHERE deleted = 0
          GROUP BY tenant_id
          HAVING COUNT(DISTINCT (CASE
                                     WHEN session_status = 0 OR session_status = 2
                                         THEN user_id END)) > 0) AS t5 ON t10.tenant_id = t5.tenant_id) m1
where m1.tenant_id like 'w%' and m1.tenant_id not like 'wwx%';

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct