bug: wrong result after having count(1)

[FANNG1]

Search before asking

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

Version

xxx

What's Wrong?

sql1 result:

51793322
51793322

after adding having count(1) > 1 , the result of sql2 are empty.

sql1

SELECT photo_id
FROM
  (SELECT photo_id AS photo_id,
          app_id AS ksmp_app_id
   FROM xxx
   WHERE dt = '2022-10-12'
     AND status <> 2
    and photo_id = '51793322'
     AND (photo_id NOT IN (0,
                           1,
                           2,
                           3)
          OR (app_id <> 'xxx'
              AND production_source = 8))
   GROUP BY photo_id,
            app_id) AS a
GROUP BY photo_id
ORDER BY photo_id
LIMIT 100
;

sql2

SELECT photo_id
FROM
  (SELECT photo_id AS photo_id,
          app_id AS ksmp_app_id
   FROM xxx
   WHERE dt = '2022-10-12'
     AND status <> 2
    and photo_id = '51793322'
     AND (photo_id NOT IN (0,
                           1,
                           2,
                           3)
          OR (app_id <> 'xxx'
              AND production_source = 8))
   GROUP BY photo_id,
            app_id) AS a
GROUP BY photo_id
having count(1) > 1
ORDER BY photo_id
LIMIT 100
;

How to Reproduce?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[FANNG1]

maybe same problem with #8290 ， data is distributed by photo_id and app_id , we should't push down the filter in fragment 1. cc @sundy-li @leiysky

Fragment 0:
  DataExchange: Shuffle
  Exchange Sink: fragment id: [1]
    Aggregate(Partial): group items: [10, 4], aggregate functions: []
      Filter: [=($1, 2022-10-12), <>($4, 2), or(not(in($2, (0, 1, 2, 3))), and(<>($0, ks683421245751343703), =($3, 8)))]
        TableScan: [ks_origin_plat_db.s_plc_entry_info]

Fragment 1:
  DataExchange: Merge
  Exchange Sink: fragment id: [2]
    Filter: [>($0, 1)]
      Aggregate(Final): group items: [10], aggregate functions: [count()]
        Aggregate(Partial): group items: [10], aggregate functions: [count()]
          Aggregate(Final): group items: [10, 4], aggregate functions: []
            Exchange Source: fragment id: [0]

Fragment 2:
  Limit: [100], Offset: [0]
    Sort: [10 ASC], Limit: [100]
      Exchange Source: fragment id: [1]