odc icon indicating copy to clipboard operation
odc copied to clipboard

Published 20 hours ago • verified publisher icon oceanbase

[Bug]: failed to find unique constraint in table's ddl

Open yhilmare opened this issue 10 months ago • 2 comments

ODC version

4.2.4

OB version

OceanBase 3.2.4.7 (r107010022023122913-1eba23192ff3365951f818121471f56a49b29a7f) (Built Dec 29 2023 13:55:55)

What happened?

  1. create a table with an unique constraint:
CREATE TABLE "TP_TRANS_ORDER" (
  "EASYPAY_ORDER_NO" VARCHAR2(64) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690630" NOT NULL ENABLE,
  "MCHT_ORDER_NO" VARCHAR2(64) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690648" NOT NULL ENABLE,
  "TP_ORDER_NO" VARCHAR2(64) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690653" NOT NULL ENABLE,
  "PP_ORDER_NO" VARCHAR2(64),
  "CEP_ORDER_NO" VARCHAR2(64),
  "PLAT_ORDER_NO" VARCHAR2(64),
  "THIRD_ORDER_NO" VARCHAR2(64),
  "MERGE_TYPE" VARCHAR2(16) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701684347466943" NOT NULL ENABLE,
  "MERGE_ORDER_NO" VARCHAR2(64),
  "PLAT_ID" VARCHAR2(32),
  "AGENT_MCHT_CODE" VARCHAR2(32) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690683" NOT NULL ENABLE,
  "MCHT_CODE" VARCHAR2(32) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690690" NOT NULL ENABLE,
  "MCHT_NAME" VARCHAR2(128) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690697" NOT NULL ENABLE,
  "TERM_CODE" VARCHAR2(16),
  "STORE_NAME" VARCHAR2(128),
  "TRANS_MCHT_CODE" VARCHAR2(32),
  "BUSINESS_SCENE_CODE" VARCHAR2(16) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690707" NOT NULL ENABLE,
  "PAY_ABILITY_CODE" VARCHAR2(16) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690711" NOT NULL ENABLE,
  "PAY_METHOD_CODE" VARCHAR2(16) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690714" NOT NULL ENABLE,
  "TRANS_STATUS" VARCHAR2(32) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690718" NOT NULL ENABLE,
  "TRANS_STATUS_DESC" VARCHAR2(128) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690722" NOT NULL ENABLE,
  "TRANS_AMOUNT" NUMBER(19) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690725" NOT NULL ENABLE,
  "CURRENCY" CHAR(3) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690730" NOT NULL ENABLE,
  "APPID" VARCHAR2(64),
  "OPENID" VARCHAR2(64),
  "FRONT_NOTIFY_URL" VARCHAR2(256),
  "BACK_NOTIFY_URL" VARCHAR2(256),
  "SEPA_BACK_NOTIFY_URL" VARCHAR2(256),
  "PLAT_STLM_AMOUNT" NUMBER(19),
  "PLAT_STLM_DATETIME" DATE,
  "PAY_AMOUNT" NUMBER(19),
  "HANDLING_FEE" NUMBER(19),
  "IS_DELAY_STLM" CHAR(1),
  "IS_D0" CHAR(1),
  "IS_SEPA" CHAR(1),
  "ORI_IS_D0" CHAR(1),
  "REFUND_AMOUNT_SUM" NUMBER(19) DEFAULT 0 CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690766" NOT NULL ENABLE,
  "STLM_AMOUNT_SUM" NUMBER(19) DEFAULT 0 CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690826" NOT NULL ENABLE,
  "SEPARATE_AMOUNT_SUM" NUMBER(19) DEFAULT 0 CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690844" NOT NULL ENABLE,
  "BUSINESS_DATETIME" DATE CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690859" NOT NULL ENABLE,
  "MCHT_DATETIME" DATE CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690869" NOT NULL ENABLE,
  "MCHT_DATE" DATE CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690873" NOT NULL ENABLE,
  "TRANS_DATETIME" DATE CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690877" NOT NULL ENABLE,
  "CODE" VARCHAR2(6),
  "MSG" VARCHAR2(256),
  "CHANNEL_RET_CODE" VARCHAR2(64),
  "CHANNEL_RET_MSG" VARCHAR2(256),
  "TIME_OUT_SECONDS" VARCHAR2(16),
  "CUSTOM_INFO" VARCHAR2(128),
  "IS_NOTIFY_SP_PAY_SUCC" CHAR(1),
  "IS_NOTIFY_MNP_PAY_SUCC" CHAR(1),
  "IS_NOTIFY_AFP_PAY_SUCC" CHAR(1),
  "IS_NOTIFY_RMP_PAY_SUCC" CHAR(1),
  "IS_NOTIFY_FP" CHAR(1),
  "IS_NOTIFY_SAP" CHAR(1),
  "IS_NOTIFY_SOP" CHAR(1),
  "IS_SETTLED" CHAR(1),
  "SETTLE_FEE_AMOUNT" NUMBER(19),
  "DF_FEE_AMOUNT" NUMBER(19),
  "ATTACH_FEE_AMOUNT" NUMBER(19),
  "TOB_AMOUNT" NUMBER(19),
  "IS_NOTIFY_FP_SETTLED" CHAR(1),
  "IS_NOTIFY_SOP_SETTLED" CHAR(1),
  "IS_NOTIFY_BMP_SETTLED" CHAR(1),
  "ATTENTION_PLATFORM" VARCHAR2(3),
  "ASSET_INFO" VARCHAR2(4096),
  "PAY_URL" VARCHAR2(2048),
  "SIMULATE_FLAG" VARCHAR2(16) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690936" NOT NULL ENABLE,
  "IS_DELETED" CHAR(1) DEFAULT 0 CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690941" NOT NULL ENABLE,
  "CREATE_DATETIME" DATE CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690982" NOT NULL ENABLE,
  "UPDATE_DATETIME" DATE CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690987" NOT NULL ENABLE,
  "REVISION" NUMBER(10) CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1701225192690992" NOT NULL ENABLE,
  "AGENT_STLM_MODE" CHAR(1),
  "CASH_ACCOUNT_REFUND_AMOUNT_SUM" NUMBER(19) DEFAULT 0 CONSTRAINT "TP_TRANS_ORDER_OBNOTNULL_1708568164800201" NOT NULL ENABLE,
  CONSTRAINT "PK_TP_TRANS_ORDER" PRIMARY KEY ("TP_ORDER_NO", "MCHT_CODE", "MCHT_DATE")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range(MCHT_DATE) subpartition by hash(MCHT_CODE) subpartition template (
subpartition "P0",
subpartition "P1",
subpartition "P2",
subpartition "P3",
subpartition "P4",
subpartition "P5")
(partition "P_202310" values less than (TO_DATE(' 2023-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "P_202311" values less than (TO_DATE(' 2023-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "P_202312" values less than (TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "P_202401" values less than (TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "P_202402" values less than (TO_DATE(' 2024-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "P_202403" values less than (TO_DATE(' 2024-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "P_202404" values less than (TO_DATE(' 2024-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "P_202405" values less than (TO_DATE(' 2024-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

CREATE INDEX "INDEX_ORDER_TABLE_MERGE_ORDER_NO" on "TP_TRANS_ORDER" (
 "MERGE_ORDER_NO",
 "MCHT_CODE",
 "MCHT_DATE"
) LOCAL ;

CREATE INDEX "INDEX_ORDER_TABLE_CEP_ORDER_NO" on "TP_TRANS_ORDER" (
 "CEP_ORDER_NO",
 "MCHT_CODE",
 "MCHT_DATE"
) LOCAL ;

CREATE INDEX "INDEX_ORDER_TABLE_THIRD_ORDER_NO" on "TP_TRANS_ORDER" (
 "THIRD_ORDER_NO",
 "MCHT_CODE",
 "MCHT_DATE"
) LOCAL ;

CREATE INDEX "INDEX_ORDER_TABLE_MCHT_DATETIME" on "TP_TRANS_ORDER" (
 "MCHT_DATETIME",
 "MCHT_CODE",
 "MCHT_DATE"
) LOCAL ;

CREATE INDEX "INDEX_ORDER_TABLE_BUSINESS_DATETIME" on "TP_TRANS_ORDER" (
 "BUSINESS_DATETIME"
) LOCAL ;

CREATE INDEX "INDEX_TPTRANSORDER_BIGDATA_QUERY" on "TP_TRANS_ORDER" (
 "UPDATE_DATETIME"
) LOCAL ;

CREATE INDEX "IDX_LINSHI_BIGDATA02" on "TP_TRANS_ORDER" (
 "BUSINESS_DATETIME",
 "MCHT_DATE",
 "AGENT_MCHT_CODE",
 "EASYPAY_ORDER_NO"
) LOCAL ;


CREATE UNIQUE INDEX INDEX_ORDER_TABLE_MCHT_ORDER_NO ON TP_TRANS_ORDER(MCHT_ORDER_NO,MCHT_CODE,MCHT_DATE) local;
  1. I can find the unique constraint in 'Indexes' panel but can not find it in table's ddl: lQLPJxz4xxkll83NBHLNBDawfr75QuL5m4UGCzLUiu0gAA_1078_1138

What did you expect to happen?

I can find this unique constraint in table's ddl~

How can we reproduce it (as minimally and precisely as possible)?

follow 'what happened'

Anything else we need to know?

No response

yhilmare avatar Apr 16 '24 13:04 yhilmare

further info:

  1. execute the index query sql select INDEX_NAME, VISIBILITY, STATUS from ALL_INDEXES where OWNER = 'SHANLU' and table_name='TP_TRANS_ORDER' 5D0040C7-0B9E-4BE9-9B39-53B9899CA21D

as you can see, the target unique index has been existed at line 3

  1. execute SELECT dbms_metadata.get_ddl('INDEX', 'INDEX_ORDER_TABLE_MCHT_ORDER_NO', 'SHANLU') as DDL from dual: 49E684B7-36E4-4E4C-B7CE-5FAA35DE198B

as you can see, we can get the ddl of this unique index.

  1. execute show create table TP_TRANS_ORDER: lQLPJw8eqFz81u3NAwzNA2iw2beG_NTeFZIGCzegvW9vAA_872_780

as you can see, the unique index is not existed in table's ddl~

yhilmare avatar Apr 16 '24 13:04 yhilmare

截屏2024-04-16 21 18 42

odc will not append create index... for unique index cause that we assume that the unique index has been existed in table's ddl~

yhilmare avatar Apr 16 '24 13:04 yhilmare

Image

pass

sl01388797 avatar Jun 11 '24 10:06 sl01388797