The correct index was not selected

[hawkingrei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `tbl_payment_history` (
  `ID` varchar(30) NOT NULL,
  `batch_num` varchar(30) DEFAULT NULL COMMENT 'PHBNTN',
  `batch_seq_num` int(7) DEFAULT NULL COMMENT 'PHBLON',
  `product_id` varchar(50) DEFAULT NULL COMMENT 'PHRKID',
  `acct_num` varchar(19) DEFAULT NULL COMMENT 'PHKNTN',
  `amt_posted_interest` decimal(15,2) DEFAULT NULL COMMENT 'PHBELP',
  `issuer_curr_amt` decimal(15,2) DEFAULT NULL COMMENT 'PHBELI',
  `val_date` date DEFAULT NULL COMMENT 'PHVLDT',
  `entry_dt` date DEFAULT NULL COMMENT 'PHBDAT',
  `rect_mark` varchar(1) DEFAULT NULL COMMENT 'PHKORR',
  `usr_id` varchar(100) DEFAULT NULL COMMENT 'PHUSER',
  `txn_code_grp` varchar(30) DEFAULT NULL COMMENT 'PHTCGR',
  `grp_acct_num` varchar(19) DEFAULT NULL COMMENT 'PHGACC',
  `period` varchar(6) DEFAULT NULL COMMENT 'PHPERI',
  `reversed_yn` varchar(1) DEFAULT NULL COMMENT 'PHRVST',
  `reversed_amt` decimal(15,2) DEFAULT NULL COMMENT 'PHRVAM',
  `dt_time` timestamp NULL DEFAULT NULL COMMENT 'PHDTTM',
  `pay_to_acct_interest_free` decimal(15,2) DEFAULT NULL COMMENT 'PHITFP',
  `status_code` varchar(50) DEFAULT NULL,
  `created_by` varchar(100) DEFAULT NULL,
  `created_dt` timestamp NULL DEFAULT NULL,
  `created_domain` varchar(10) DEFAULT NULL,
  `updated_by` varchar(100) DEFAULT NULL,
  `updated_dt` timestamp NULL DEFAULT NULL,
  `updated_domain` varchar(10) DEFAULT NULL,
  `db_version` bigint(20) DEFAULT NULL,
  `acct_category` varchar(10) DEFAULT NULL,
  `ipp_ind` varchar(1) DEFAULT NULL,
  `sub_seq_num` int(11) DEFAULT NULL,
  `pay_to_period` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`) /*T![clustered_index] NONCLUSTERED */,
  KEY `tbl_payment_idx_003` (`entry_dt`, `txn_code_grp`),
  KEY `tbl_payment_history_ix2` (`acct_num`, `grp_acct_num`, `acct_category`, `entry_dt`),
  KEY `tbl_payment_idx_002` (`entry_dt`, `acct_num`, `status_code`),
  KEY `tbl_payment_history_pg1` (`created_dt`),
  KEY `idx_payment_history_001` (`grp_acct_num`),
  KEY `tbl_payment_history_idx_001_1` (`batch_num`, `batch_seq_num`, `entry_dt`, `acct_category`),
  KEY `idx_payment_history_004_1` (`entry_dt`, `acct_category`, `ID`, `txn_code_grp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

2. What did you expect to see? (Required)

SELECT
  MIN(g.id) AS start_key,
  MAX(g.id) AS end_key,
  COUNT(g.id) AS page_size
FROM
  (
    SELECT
      /*+use_index(a,tbl_payment_idx_003)*/ id id,
      ROW_NUMBER() OVER (
        ORDER BY
          id
      ) AS row_num
    FROM
      tbl_payment_history a
    WHERE
      a.entry_dt = '2024-09-08 00:00:00'
  ) g
GROUP BY
  FLOOR((g.row_num - 1) / 500)
ORDER BY
  start_key;



+--------------------------------------+---------+----------+-----------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows | estCost  | task      | access object                                                                           | operator info                                                                                                                                                                                                                                          |
+--------------------------------------+---------+----------+-----------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_10                              | 10.00   | 25477.49 | root      |                                                                                         | Column#34                                                                                                                                                                                                                                              |
| └─HashAgg_13                         | 10.00   | 23680.85 | root      |                                                                                         | group by:Column#42, funcs:min(Column#39)->Column#34, funcs:max(Column#40)->Column#35, funcs:count(Column#41)->Column#36                                                                                                                                |
|   └─Projection_20                    | 10.00   | 21461.05 | root      |                                                                                         | test.tbl_cardcore_payment_posting_history.id->Column#39, test.tbl_cardcore_payment_posting_history.id->Column#40, test.tbl_cardcore_payment_posting_history.id->Column#41, floor(div(cast(minus(Column#33, 1), decimal(20,0) BINARY), 500))->Column#42 |
|     └─Window_14                      | 10.00   | 21358.25 | root      |                                                                                         | row_number()->Column#33 over(order by test.tbl_cardcore_payment_posting_history.id rows between current row and current row)                                                                                                                           |
|       └─Sort_18                      | 10.00   | 21358.25 | root      |                                                                                         | test.tbl_cardcore_payment_posting_history.id                                                                                                                                                                                                           |
|         └─IndexLookUp_17             | 10.00   | 19592.61 | root      |                                                                                         |                                                                                                                                                                                                                                                        |
|           ├─IndexRangeScan_15(Build) | 10.00   | 2494.62  | cop[tikv] | table:a, index:tbl_cardcore_payment_posting_idx_003(entry_date, transaction_code_group) | range:[2024-09-08,2024-09-08], keep order:false, stats:pseudo                                                                                                                                                                                          |
|           └─TableRowIDScan_16(Probe) | 10.00   | 4017.87  | cop[tikv] | table:a                                                                                 | keep order:false, stats:pseudo                                                                                                                                                                                                                         |
+--------------------------------------+---------+----------+-----------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3. What did you see instead (Required)

SELECT
  MIN(g.id) AS start_key,
  MAX(g.id) AS end_key,
  COUNT(g.id) AS page_size
FROM
  (
    SELECT
      id,
      ROW_NUMBER() OVER (
        ORDER BY
          id
      ) AS row_num
    FROM
      tbl_payment_history a
    WHERE
      a.entry_dt = '2024-09-08 00:00:00'
  ) g
GROUP BY
  FLOOR((g.row_num - 1) / 500)
ORDER BY
  start_key;


+-------------------------------------+----------+-----------+----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                  | estRows  | task      | access object              | operator info                                                                                                                                                                                                                                          |
+-------------------------------------+----------+-----------+----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_10                             | 10.00    | root      |                            | Column#34                                                                                                                                                                                                                                              |
| └─HashAgg_13                        | 10.00    | root      |                            | group by:Column#42, funcs:min(Column#39)->Column#34, funcs:max(Column#40)->Column#35, funcs:count(Column#41)->Column#36                                                                                                                                |
|   └─Projection_21                   | 10.00    | root      |                            | test.tbl_cardcore_payment_posting_history.id->Column#39, test.tbl_cardcore_payment_posting_history.id->Column#40, test.tbl_cardcore_payment_posting_history.id->Column#41, floor(div(cast(minus(Column#33, 1), decimal(20,0) BINARY), 500))->Column#42 |
|     └─Window_14                     | 10.00    | root      |                            | row_number()->Column#33 over(order by test.tbl_cardcore_payment_posting_history.id rows between current row and current row)                                                                                                                           |
|       └─Projection_19               | 10.00    | root      |                            | test.tbl_cardcore_payment_posting_history.id, test.tbl_cardcore_payment_posting_history.entry_date                                                                                                                                                     |
|         └─IndexLookUp_18            | 10.00    | root      |                            |                                                                                                                                                                                                                                                        |
|           ├─IndexFullScan_15(Build) | 10000.00 | cop[tikv] | table:a, index:PRIMARY(ID) | keep order:true, stats:pseudo                                                                                                                                                                                                                          |
|           └─Selection_17(Probe)     | 10.00    | cop[tikv] |                            | eq(test.tbl_cardcore_payment_posting_history.entry_date, 2024-09-08 00:00:00.000000)                                                                                                                                                                   |
|             └─TableRowIDScan_16     | 10000.00 | cop[tikv] | table:a                    | keep order:false, stats:pseudo                                                                                                                                                                                                                         |
+-------------------------------------+----------+-----------+----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4. What is your TiDB version? (Required)

[AilinKid]

Fix46177 uint64 = 46177 set fixcontrol46177=on will get the right thing

[hawkingrei]

It is the same as #46177.