[Bug]: fragile bvt cases in create_retention

[aptend]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main

Commit ID

5142544

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

2024-09-20 07:02:21 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:199][select * from mo_catalog.mo_retention;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:21 ERROR Executor:201 - [EXPECT RESULT]:
database_name    table_name    retention_deadline
2024-09-20 07:02:21 ERROR Executor:202 - [ACTUAL RESULT]:
database_name    table_name    retention_deadline
test01    aff01    1726815738
2024-09-20 07:02:21 ERROR RSSet:164 - The row count does not equal with each other, one is 1, the other is 2
2024-09-20 07:02:21 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:200][show tables;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:21 ERROR Executor:201 - [EXPECT RESULT]:
Tables_in_test01
pri01
2024-09-20 07:02:21 ERROR Executor:202 - [ACTUAL RESULT]:
Tables_in_test01
aff01
pri01
2024-09-20 07:02:21 ERROR Executor:[269](https://github.com/matrixorigin/matrixone/actions/runs/10954653305/job/30417042479#step:8:270) - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:202][drop table pri01;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:21 ERROR Executor:[271](https://github.com/matrixorigin/matrixone/actions/runs/10954653305/job/30417042479#step:8:272) - [EXPECT RESULT]:
null
2024-09-20 07:02:21 ERROR Executor:[272](https://github.com/matrixorigin/matrixone/actions/runs/10954653305/job/30417042479#step:8:273) - [ACTUAL RESULT]:
internal error: can not drop table 'pri01' referenced by some foreign key constraint
2024-09-20 07:02:24 ERROR RSSet:164 - The row count does not equal with each other, one is 1, the other is 2
2024-09-20 07:02:24 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:228][show tables;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:24 ERROR Executor:201 - [EXPECT RESULT]:
Tables_in_test01
back01
2024-09-20 07:02:24 ERROR Executor:202 - [ACTUAL RESULT]:
Tables_in_test01
back01
pri01
2024-09-20 07:02:31 ERROR RSSet:164 - The row count does not equal with each other, one is 0, the other is 1
2024-09-20 07:02:31 ERROR Executor:199 - [/home/runner/work/matrixone/matrixone/head/test/distributed/cases/ddl/create_retention_table.sql][row:256][show tables;] was executed failed, con[id=0, user=dump, ***
2024-09-20 07:02:31 ERROR Executor:201 - [EXPECT RESULT]:
Tables_in_test01
2024-09-20 07:02:31 ERROR Executor:202 - [ACTUAL RESULT]:
Tables_in_test01
pri01

Expected Behavior

No response

Steps to Reproduce

https://github.com/matrixorigin/matrixone/actions/runs/10954653305/job/30417042479

Additional information

No response