[Bug] mpp20749 failed due to unexpected query plan changed

congxuebin commented 2 weeks ago

Cloudberry Database version

Cloudberry Database 1.0.0+9b9dd58 build commit:9b9dd5856d002c43efc16e861d05514d08951a0

What happened

/code/cbdb_testrepo_src /code /code/cbdb_testrepo_src/test_framework/tinc.py discover partitioning test_partitioning_bfv.test_GPSQL2656 ... 471.26 ms ... ok test_partitioning_bfv.test_mpp20749_planner ... 29332.83 ms ... ok test_partitioning_bfv.test_mpp20749_orca ... 43967.91 ms ... FAIL

Access plan unexpected changed for this query, please see the attachment I uploaded to see more detailed access plan information.

EXPLAIN SELECT                                           
      CAST ('Q' AS VARCHAR (1)) AS mkt,          /*greenplum needs this cast*/
       CAST ('SBESO' AS VARCHAR (10))
          AS trns_type,
       TO_CHAR (fact.trd_dt, 'DD-Mon-YYYY') AS event_dt, /*greenplum needs this cast*/
       fact.odr_updt_tm AS event_start_tm,
       fact.issue_sym_id AS sym,
       fact.msg_seq_nb AS seq_num,
       fact.odr_rfrnc_nb AS odr_rfrnc_nb,
       fact.msg_type_cd AS msg_type,                         
       TO_CHAR (fact.entry_dt, 'DD-Mon-YYYY') AS entry_dt, 
       TO_CHAR (fact.odr_entry_dt, 'DD-Mon-YYYY')
          AS odr_entry_dt,
       fact.odr_entry_tm AS odr_entry_tm,
       fact.msg_ctgry_cd AS msg_category,
       fact.trans_cd AS trans_cd,
       fact.odr_st AS odr_st,
       fact.odr_ctgry_cd AS odr_ctgry_cd,
       fact.oe_mp_id AS oe_mpid,
       fact.side_cd AS side_cd,
       fact.shrt_sale_cd AS shrt_sale_cd,
       fact.entrd_pr AS entered_pr,
       fact.mkt_odr_fl AS mkt_odr_fl,
       fact.odr_entry_qt AS odr_entry_qt,
       fact.odr_qt AS odr_qt,
       fact.oe_cpcty_cd AS oe_capacity,
       fact.atrbl_odr_fl AS attributable,
       fact.tif_cd AS tif_cd,
       fact.user_odr_id AS usr_odr_id,
       TO_CHAR (fact.odr_rank_dt, 'DD-Mon-YYYY')
          AS odr_rank_dt,
       fact.odr_rank_tm AS odr_rank_tm,
       fact.exctn_rfrnc_nb AS exctn_rfrnc_nb,
       fact.exctn_qt AS exctn_qty,
       fact.cncl_qt AS cncl_qty,
       fact.peg_type_cd AS peg_type,
       fact.peg_offst_pr AS peg_offset_pr,
       fact.peg_cap_pr AS peg_cap_pr,
       fact.dcrny_odr_fl AS dcrny_odr_fl,
       fact.dcrny_offst_pr AS dcrny_offset_pr,
       fact.clsg_cross_elgbl_fl AS clsng_cross_elgbl,
       fact.opng_cross_elgbl_fl AS opng_cross_elgbl,
       fact.rtng_fl AS routing_fl,
       fact.oe_prmry_mp_id AS oe_prmry_mpid,
       fact.oe_prmry_mpid_nb AS oe_prmry_mpid__,
       fact.odr_rsrv_size_qt AS rsrv_sz,
       fact.rndd_odr_pr AS rnd_prc,
       fact.ads_smntg_odr_id AS rec_id,
       fact.core_actn_cd AS core_actn_cd,
       fact.core_link_id AS core_link_id,
       fact.core_seq_nb AS core_seq_num,
       fact.core_updt_tm AS core_updt_tm,
       fact.cross_fl AS cross_fl,
       fact.host_updt_tm AS host_updt_tm,
       fact.iso_fl AS iso_fl,
       fact.rtng_seq_nb AS rtng_seq_num
FROM /*CR 111848 Greenplum 3.3 needs the specific OR condition in order to trigger correct index usage */
     /*keep_fL is derived here since using the odr_entry_dt directly in the join condition seems to invalidate proper index selection in Greenplum 3.3*/
     (SELECT *
      FROM (SELECT smntg_odr.*,
                   CASE
                      WHEN smntg_odr.odr_entry_dt =
                              ola.odr_entry_dt
                      THEN
                         1
                      ELSE
                         0
                   END
                      AS keep_fl
            FROM smntg_odr,
                 (SELECT DISTINCT
                         m.odr_rfrnc_nb,
                         odr_entry_dt
                  FROM wiat_ola_tree t,
                       rtng_mtch m
                  WHERE m.rtng_order_roe_id =
                           t.child_id
                        AND t.ola_tree_id =
                              101153
                        AND m.dstnt_cd IN
                                 ('XQ',
                                  'U'))
                 ola
            WHERE (smntg_odr.odr_rfrnc_nb =
                      'Gp3.3Fix'
                   OR smntg_odr.odr_rfrnc_nb =
                        ola.odr_rfrnc_nb))
           a
      WHERE keep_fl =
               1)
     fact
WHERE                                                            /*SPLITDATE*/
      (                                  /*_DS_NAME is : ads2Gp33DataSource */
       fact.trd_dt <
          '01-Jun-2009')
ORDER BY fact.trd_dt ASC, fact.odr_updt_tm ASC, fact.msg_seq_nb ASC
;

What you think should happen instead

No response

How to reproduce

/code/cbdb_testrepo_src /code /code/cbdb_testrepo_src/test_framework/tinc.py discover partitioning

Operating System

centos7

Anything else

No response

Are you willing to submit PR?

[ ] Yes, I am willing to submit a PR!

Code of Conduct

[X] I agree to follow this project's Code of Conduct.

congxuebin commented 2 weeks ago

Test outputs and expected results are in the following attachment. test_partitioning_bfv.test_mpp20749_orca.zip

congxuebin commented 2 weeks ago

Another case failed with similar symptom. PartitionDDLTests.test_mpp7734.zip

congxuebin commented 1 week ago

As confirmed with Zijie, these are as expected, thus I updated test cases accordingly.

apache / cloudberry