已关闭
[Bug]: interval_tbl用例执行prepare transaction报错Specified branch does not exists #338
jiangyannan创建于 8月19日关闭于 24 天前
8月19日 添加了label：bug
8月19日 关联了看板：openGauss 7.0.0-LTS
8月19日 添加了label：sig/StorageEngine
opengauss_bot
8月19日 评论：
8月19日 评论：
This issue requires an assignee. Since you haven't specified one, we've assigned TestManager as the default assignee for this issue.
opengauss_bot
8月19日 评论：
8月19日 评论：
Welcome To openGauss Community
Hey @jyn88 , thanks for your contribution to the community.
Bot Usage Manual
I'm the Bot here serving you. You can find the instructions on how to interact with me at Here . That means you can comment below every pull request or issue to trigger Bot Commands.
Contact Guide
If you have any questions, please contact the SIG: StorageEngine ,
and any of the maintainers: @CarrotGo, @chendong76, @chenxiaobin19, @congzhou2603, @dodders, @hwworkholic, @jemappellehc, @muyulinzhong, @quemingjian, @shenzheng4, @shirley_zhengx, @superlchf, @totaj, @wlff234, @wofanzheng, @ywzq1161327784 ,
and any of the committers: @CityYard, @libiao2024, @lijieac, @theothersideofsea, @wang_bowen, @weithu, @xiong_xjun, @yuanyazhi, @zhangzq131 .
8月19日 将 TestManager 设为负责人
8月19日 修改了issue 的描述
8月21日 将 congzhou2603 设为负责人
8月21日 移除了负责人 TestManager
27 天前 修改了issue 的描述
zhoucong<Okidoki>
27 天前 评论：
27 天前 评论：
create user NEBULA IDENTIFIED by 'Huawei@123';
drop table NEBULA.ACID_XA_INVAL_DML_TBL_000;
CREATE TABLE NEBULA.ACID_XA_INVAL_DML_TBL_000(C_ID INT,
C_D_ID BIGINT NOT NULL,
C_W_ID TINYINT UNSIGNED NOT NULL,
C_FIRST VARCHAR(64) NOT NULL,
C_MIDDLE CHAR(2),
C_LAST VARCHAR(64) NOT NULL,
C_STREET_1 VARCHAR(20) NOT NULL,
C_STREET_2 VARCHAR(8000),
C_CITY VARCHAR(20) NOT NULL,
C_STATE CHAR(2) NOT NULL,
C_ZIP CHAR(9) NOT NULL,
C_PHONE CHAR(16) NOT NULL,
C_SINCE TIMESTAMP,
C_CREDIT CHAR(2) NOT NULL,
C_CREDIT_LIM NUMERIC(12,2),
C_DISCOUNT NUMERIC(4,4),
C_BALANCE NUMERIC(12,2),
C_YTD_PAYMENT REAL NOT NULL,
C_PAYMENT_CNT NUMBER NOT NULL,
C_DELIVERY_CNT BOOL NOT NULL,
C_END DATE NOT NULL,
C_VCHAR VARCHAR(1000),
C_DATA LONG,
C_TEXT VARCHAR(8000),
C_CLOB VARCHAR(8000),
primary key (c_id,c_d_id,c_w_id));
insert into NEBULA.ACID_XA_INVAL_DML_TBL_000 select 0,0,0,'AA'||'is0cmvls','OE','AA'||'BAR0BARBAR','bkili'||'0'||'fcxcle'||'0',lpad('pmbwo012345vhvpaj',150,'YHIKREhight'),'dyf'||'0'||'rya'||'0','uq',4800||'0',940||'0'||205||'0','2017-12-31 10:51:47','GC',50000.0,0.4361328,-10.0,10.0,1,true,'2017-12-31 10:51:47','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSF'||'0','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSF1','1234354587643123455213445656723123424554566776763221132454566768767433242323445453565654542323','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSF1CLOB';
CREATE or replace procedure nebula_dml_interval_func_001(startall int,endall int) as
i INT;
BEGIN
FOR i IN startall..endall LOOP
insert into NEBULA.ACID_XA_INVAL_DML_TBL_000 select c_id+i,c_d_id+i*10000000000,c_w_id+i,'AA'||'iscmvls',c_middle,'AA'||'BAR'||i||'ddBARBAR',c_street_1,lpad('pmbwo0'||i||'2345vhvpaj',150,'YHIKREhight'),c_city,c_state,c_zip,c_phone,c_since+i,c_credit,c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_end+i,c_vchar,c_data,c_text,c_clob from NEBULA.ACID_XA_INVAL_DML_TBL_000 where c_id=0;commit;
END LOOP;
END;
/
--select nebula_dml_interval_func_001(1,10000);
call nebula_dml_interval_func_001(1,1000);
delete from NEBULA.ACID_XA_INVAL_DML_TBL_000 where c_id=0;
rollback;
select count(*) from NEBULA.ACID_XA_INVAL_DML_TBL_000;
zhoucong<Okidoki>
27 天前 评论：
27 天前 评论：
drop table NEBULA.ACID_XA_INT_INVAL_DML_TBL_035;
CREATE TABLE NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_ID INT,C_D_ID BIGINT NOT NULL,C_W_ID TINYINT UNSIGNED NOT NULL,C_FIRST VARCHAR(64) NOT NULL,C_MIDDLE CHAR(2),C_LAST VARCHAR(64) NOT NULL,C_STREET_1 VARCHAR(20) NOT NULL,C_STREET_2 VARCHAR(8000),C_CITY VARCHAR(20) NOT NULL,C_STATE CHAR(2) NOT NULL,C_ZIP CHAR(9) NOT NULL,C_PHONE CHAR(16) NOT NULL,C_SINCE TIMESTAMP,C_CREDIT CHAR(2) NOT NULL,C_CREDIT_LIM NUMERIC(12,2),C_DISCOUNT NUMERIC(4,4),C_BALANCE NUMERIC(12,2),C_YTD_PAYMENT REAL NOT NULL,C_PAYMENT_CNT NUMBER NOT NULL,C_DELIVERY_CNT BOOL NOT NULL,C_END DATE NOT NULL,C_VCHAR VARCHAR(1000),C_DATA LONG,C_TEXT BLOB,C_CLOB CLOB)PARTITION BY RANGE(C_ID)INTERVAL(1000)(PARTITION PART_1 VALUES LESS THAN (201),PARTITION PART_2 VALUES LESS THAN (401),PARTITION PART_3 VALUES LESS THAN (601),PARTITION PART_4 VALUES LESS THAN (801),PARTITION PART_5 VALUES LESS THAN (1001),PARTITION PART_6 VALUES LESS THAN (1201),PARTITION PART_7 VALUES LESS THAN (1401),PARTITION PART_8 VALUES LESS THAN (1601),PARTITION PART_9 VALUES LESS THAN (1801));
--I.单字段普通索引是分区键，单字段普通索引不是分区键，多字段普通索引包含分区键，多字段普通索引不包含分区键（全局索引）
CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_1 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_ID);
CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_2 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_END);
CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_3 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_ID,C_FIRST,C_SINCE,C_END);
CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_4 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_D_ID,C_LAST);
CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_0 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_VCHAR,C_FIRST);
insert into NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 select * from NEBULA.ACID_XA_INVAL_DML_TBL_000;
--I2.在事务中，Update最后一条数据,长度变长
commit;
--begin transaction;
update NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 set c_first=c_first||'aa',c_vchar=lpad('abcdefghigklmnopqrstuvwxyz',300,'aaaaaaaaaaa'),c_street_2=lpad('abcdefghigklmnopqrstuvwxyz',3000,'bbbbbbbbbbbbbbbstreet2'),c_last=lpad('c_last64',20,'bbbbbbb'),c_data=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa'),c_text=lpad('1243245324554354325',300,'1232343'),c_clob=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa') where c_id=1000001;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id <5000 group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_end <'2018-08-01 10:51:47' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id >5000 and c_first like 'AA%' and c_since >'2018-08-01 10:51:47' and c_end >'2018-08-01 10:51:47' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_d_id <5000 and (c_last like 'c%' or c_last like 'AA%')group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' and (c_last like 'c%' or c_last like 'AA%') and (c_vchar like 'c_varchar%' or c_vchar like 'QVLDETANRBR%') group by c_first order by 1;
savepoint aa;
update NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 set c_first=c_first||'aa',c_vchar=lpad('abcdefghigklmnopqrstuvwxyz',300,'aaaaaaaaaaa'),c_street_2=lpad('abcdefghigklmnopqrstuvwxyz',3000,'bbbbbbbbbbbbbbbstreet2'),c_last=lpad('c_last64',20,'bbbbbbb'),c_data=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa'),c_text=lpad('1243245324554354325',300,'1232343'),c_clob=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa') where c_id=1000001;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id <5000 group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_end <'2018-08-01 10:51:47' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id >5000 and c_first like 'AA%' and c_since >'2018-08-01 10:51:47' and c_end >'2018-08-01 10:51:47' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_d_id <5000 and (c_last like 'c%' or c_last like 'AA%')group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' and (c_last like 'c%' or c_last like 'AA%') and (c_vchar like 'c_varchar%' or c_vchar like 'QVLDETANRBR%') group by c_first order by 1;
prepare transaction '35.AE000AEABC00ABCD.000001';
commit prepared '35.AE000AEABC00ABCD.000001';
zhoucong<Okidoki>
27 天前 评论：
27 天前 评论：
对标数据库的测试结果：和分区表是否走全局索引有关，强制走全表扫描，失败，创建并强制使用全局索引，成功。
SQL> commit;
explain update /*+ full(xa_interval_miss_t) */
xa_interval_miss_t
set v = lpad('b', 300, 'b')
where id = 1000001;
update /*+ full(xa_interval_miss_t) */
xa_interval_miss_t
set v = lpad('b', 300, 'b')
where id = 1000001;
prepare transaction '35.AE000AEABC00ABCD.000201';
commit prepared '35.AE000AEABC00ABCD.000201';
rollback;
Succeed.
SQL> SQL> 2 3 4
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Description | Owner | Name | Rows | Cost | Bytes | Remark |
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | | | |
| 1 | TABLE ACCESS FULL Filter:N/A | SYS | XA_INTERVAL_MISS_T | | | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
1 - filter: ID = 1000001
9 rows fetched.
SQL> SQL> 2 3 4
0 rows affected.
SQL> SQL>
GS-01502, Specified branch does not exists
SQL>
GS-01514, Commit global xa transaction in shared storage cluster failed, error code: 13018, error message = global xa res 35.AE000AEABC00ABCD.000201 does not exist
SQL>
Succeed.
SQL> create index xa_interval_miss_idx on xa_interval_miss_t(id);
commit;
explain update /*+ index(xa_interval_miss_t xa_interval_miss_idx) */
xa_interval_miss_t
set v = lpad('b', 300, 'b')
where id = 1000001;
update /*+ index(xa_interval_miss_t xa_interval_miss_idx) */
xa_interval_miss_t
set v = lpad('b', 300, 'b')
where id = 1000001;
prepare transaction '35.AE000AEABC00ABCD.000202';
commit prepared '35.AE000AEABC00ABCD.000202';
Succeed.
SQL>
Succeed.
SQL> SQL> 2 3 4
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
| Id | Description | Owner | Name | Rows | Cost | Bytes | Remark |
----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:N/A | SYS | XA_INTERVAL_MISS_T | | | | |
| 2 | INDEX RANGE SCAN | SYS | XA_INTERVAL_MISS_IDX | | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
1 - filter: ID = 1000001
2 - access: ID = 1000001
11 rows fetched.
SQL> SQL> 2 3 4
0 rows affected.
SQL> SQL>
Succeed.
SQL>
Succeed.
26 天前 关联了pull request：fix: 修复空 interval 分区 DML 无法 prepare transaction
zhoucong<Okidoki>
25 天前 评论：
25 天前 评论：
自验证
SQL> alter system set use_bison_parser = false;
Succeed.
SQL>
SQL>
SQL> set autocommit off;
OFF
partition p1 values less than (201),
partition p2 values less than (401),
partition p3 values less than (601),
partition p4 values less than (801),
partition p5 values less than (1001),
partition p6 values less than (1201),
partition p7 values less than (1401),
partition p8 values less than (1601),
partition p9 values less than (1801)
);
insert into xa_interval_miss_t values(1, 'a');
insert into xa_interval_miss_t values(1000, 'a');
commit;
update xa_interval_miss_t
set v = lpad('b', 300, 'b')
SQL>
SQL> drop table if exists xa_interval_miss_t;
select count(*) from xa_interval_miss_t where id = 1000001;
Succeed.
savepoint aa;
update xa_interval_miss_t
SQL>
SQL> create table xa_interval_miss_t(
2 id int,
3 v varchar(1000)
4 )
5 partition by range(id)
6 interval(1000)
7 (
8 partition p1 values less than (201),
9 partition p2 values less than (401),
10 partition p3 values less than (601),
11 partition p4 values less than (801),
12 partition p5 values less than (1001),
13 partition p6 values less than (1201),
14 partition p7 values less than (1401),
15 partition p8 values less than (1601),
16 partition p9 values less than (1801)
17 );
Succeed.
SQL>
SQL> insert into xa_interval_miss_t values(1, 'a');
1 rows affected.
SQL> insert into xa_interval_miss_t values(1000, 'a');
1 rows affected.
SQL> commit;
Succeed.
SQL>
SQL> update xa_interval_miss_t
2 set v = lpad('b', 300, 'b')
3 where id = 1000001;
0 rows affected.
SQL>
SQL> select count(*) from xa_interval_miss_t where id = 1000001;
COUNT(*)
--------------------
0
1 rows fetched.
SQL>
SQL> savepoint aa;
Succeed.
SQL>
SQL> update xa_interval_miss_t
2 set v = lpad('c', 300, 'c')
3 where id = 1000001;
0 rows affected.
SQL>
SQL> prepare transaction '35.AE000AEABC00ABCD.000011';
Succeed.
SQL> commit prepared '35.AE000AEABC00ABCD.000011';
Succeed.
SQL>
SQL> create user NEBULA IDENTIFIED by 'Huawei@123';
drop table NEBULA.ACID_XA_INVAL_DML_TBL_000;
CREATE TABLE NEBULA.ACID_XA_INVAL_DML_TBL_000(C_ID INT,
C_D_ID BIGINT NOT NULL,
C_W_ID TINYINT UNSIGNED NOT NULL,
C_FIRST VARCHAR(64) NOT NULL,
C_MIDDLE CHAR(2),
C_LAST VARCHAR(64) NOT NULL,
C_STREET_1 VARCHAR(20) NOT NULL,
C_STREET_2 VARCHAR(8000),
C_CITY VARCHAR(20) NOT NULL,
C_STATE CHAR(2) NOT NULL,
C_ZIP CHAR(9) NOT NULL,
C_PHONE CHAR(16) NOT NULL,
C_SINCE TIMESTAMP,
C_CREDIT CHAR(2) NOT NULL,
C_CREDIT_LIM NUMERIC(12,2),
C_DISCOUNT NUMERIC(4,4),
C_BALANCE NUMERIC(12,2),
C_YTD_PAYMENT REAL NOT NULL,
C_PAYMENT_CNT NUMBER NOT NULL,
C_DELIVERY_CNT BOOL NOT NULL,
C_END DATE NOT NULL,
C_VCHAR VARCHAR(1000),
C_DATA LONG,
C_TEXT VARCHAR(8000),
C_CLOB VARCHAR(8000),
primary key (c_id,c_d_id,c_w_id));
insert into NEBULA.ACID_XA_INVAL_DML_TBL_000 select 0,0,0,'AA'||'is0cmvls','OE','AA'||'BAR0BARBAR','bkili'||'0'||'fcxcle'||'0',lpad('pmbwo012345vhvpaj',150,'YHIKREhight'),'dyf'||'0'||'rya'||'0','uq',4800||'0',940||'0'||205||'0','2017-12-31 10:51:47','GC',50000.0,0.4361328,-10.0,10.0,1,true,'2017-12-31 10:51:47','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSF'||'0','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSF1','1234354587643123455213445656723123424554566776763221132454566768767433242323445453565654542323','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSF1CLOB';
CREATE or replace procedure nebula_dml_interval_func_001(startall int,endall int) as
i INT;
BEGIN
FOR i IN startall..endall LOOP
insert into NEBULA.ACID_XA_INVAL_DML_TBL_000 select c_id+i,c_d_id+i*10000000000,c_w_id+i,'AA'||'iscmvls',c_middle,'AA'||'BAR'||i||'ddBARBAR',c_street_1,lpad('pmbwo0'||i||'2345vhvpaj',150,'YHIKREhight'),c_city,c_state,c_zip,c_phone,c_since+i,c_credit,c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_end+i,c_vchar,c_data,c_text,c_clob from NEBULA.ACID_XA_INVAL_DML_TBL_000 where c_id=0;commit;
END LOOP;
END;
/
--select nebula_dml_interval_func_001(1,10000);
call nebula_dml_interval_func_001(1,1000);
delete from NEBULA.ACID_XA_INVAL_DML_TBL_000 where c_id=0;
rollback;
select count(*) from NEBULA.ACID_XA_INVAL_DML_TBL_000;
^[[201~Succeed.
SQL> drop table NEBULA.ACID_XA_INVAL_DML_TBL_000;
OG-00843, The table or view NEBULA.ACID_XA_INVAL_DML_TBL_000 does not exist.
SQL> CREATE TABLE NEBULA.ACID_XA_INVAL_DML_TBL_000(C_ID INT,
2 C_D_ID BIGINT NOT NULL,
3 C_W_ID TINYINT UNSIGNED NOT NULL,
4 C_FIRST VARCHAR(64) NOT NULL,
5 C_MIDDLE CHAR(2),
6 C_LAST VARCHAR(64) NOT NULL,
7 C_STREET_1 VARCHAR(20) NOT NULL,
8 C_STREET_2 VARCHAR(8000),
9 C_CITY VARCHAR(20) NOT NULL,
10 C_STATE CHAR(2) NOT NULL,
11 C_ZIP CHAR(9) NOT NULL,
12 C_PHONE CHAR(16) NOT NULL,
13 C_SINCE TIMESTAMP,
14 C_CREDIT CHAR(2) NOT NULL,
15 C_CREDIT_LIM NUMERIC(12,2),
16 C_DISCOUNT NUMERIC(4,4),
17 C_BALANCE NUMERIC(12,2),
18 C_YTD_PAYMENT REAL NOT NULL,
19 C_PAYMENT_CNT NUMBER NOT NULL,
20 C_DELIVERY_CNT BOOL NOT NULL,
21 C_END DATE NOT NULL,
22 C_VCHAR VARCHAR(1000),
23 C_DATA LONG,
24 C_TEXT VARCHAR(8000),
25 C_CLOB VARCHAR(8000),
26 primary key (c_id,c_d_id,c_w_id));
Succeed.
SQL> insert into NEBULA.ACID_XA_INVAL_DML_TBL_000 select 0,0,0,'AA'||'is0cmvls','OE','AA'||'BAR0BARBAR','bkili'||'0'||'fcxcle'||'0',lpad('pmbwo012345vhvpaj',150,'YHIKREhight'),'dyf'||'0'||'rya'||'0','uq',4800||'0',940||'0'||205||'0','2017-12-31 10:51:47','GC',50000.0,0.4361328,-10.0,10.0,1,true,'2017-12-31 10:51:47','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECC348493214893542NPFZCSYKXXYSCDSF'||'0','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSF1','1234354587643123455213445656723123424554566776763221132454566768767433242323445453565654542323','QVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSF1CLOB';
1 rows affected.
SQL> CREATE or replace procedure nebula_dml_interval_func_001(startall int,endall int) as
2 i INT;
3 BEGIN
4 FOR i IN startall..endall LOOP
5 insert into NEBULA.ACID_XA_INVAL_DML_TBL_000 select c_id+i,c_d_id+i*10000000000,c_w_id+i,'AA'||'iscmvls',c_middle,'AA'||'BAR'||i||'ddBARBAR',c_street_1,lpad('pmbwo0'||i||'2345vhvpaj',150,'YHIKREhight'),c_city,c_state,c_zip,c_phone,c_since+i,c_credit,c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_end+i,c_vchar,c_data,c_text,c_clob from NEBULA.ACID_XA_INVAL_DML_TBL_000 where c_id=0;commit;
6 END LOOP;
7 END;
8 /
Succeed.
SQL> --select nebula_dml_interval_func_001(1,10000);
SQL> call nebula_dml_interval_func_001(1,1000);
PL/SQL procedure successfully completed.
SQL> delete from NEBULA.ACID_XA_INVAL_DML_TBL_000 where c_id=0;
1 rows affected.
SQL> rollback;
Succeed.
SQL> select count(*) from NEBULA.ACID_XA_INVAL_DML_TBL_000;
COUNT(*)
--------------------
1001
1 rows fetched.
SQL>
SQL> drop table NEBULA.ACID_XA_INT_INVAL_DML_TBL_035;
OG-00843, The table or view NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 does not exist.
--I.单字段普通索引是分区键，单字段普通索引不是分区键，多字段普通索引包含分区键，多字段普通索引不包含分区键（全局索引）
SQL> CREATE TABLE NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_ID INT,C_D_ID BIGINT NOT NULL,C_W_ID TINYINT UNSIGNED NOT NULL,C_FIRST VARCHAR(64) NOT NULL,C_MIDDLE CHAR(2),C_LAST VARCHAR(64) NOT NULL,C_STREET_1 VARCHAR(20) NOT NULL,C_STREET_2 VARCHAR(8000),C_CITY VARCHAR(20) NOT NULL,C_STATE CHAR(2) NOT NULL,C_ZIP CHAR(9) NOT NULL,C_PHONE CHAR(16) NOT NULL,C_SINCE TIMESTAMP,C_CREDIT CHAR(2) NOT NULL,C_CREDIT_LIM NUMERIC(12,2),C_DISCOUNT NUMERIC(4,4),C_BALANCE NUMERIC(12,2),C_YTD_PAYMENT REAL NOT NULL,C_PAYMENT_CNT NUMBER NOT NULL,C_DELIVERY_CNT BOOL NOT NULL,C_END DATE NOT NULL,C_VCHAR VARCHAR(1000),C_DATA LONG,C_TEXT BLOB,C_CLOB CLOB)PARTITION BY RANGE(C_ID)INTERVAL(1000)(PARTITION PART_1 VALUES LESS THAN (201),PARTITION PART_2 VALUES LESS THAN (401),PARTITION PART_3 VALUES LESS THAN (601),PARTITION PART_4 VALUES LESS THAN (801),PARTITION PART_5 VALUES LESS THAN (1001),PARTITION PART_6 VALUES LESS THAN (1201),PARTITION PART_7 VALUES LESS THAN (1401),PARTITION PART_8 VALUES LESS THAN (1601),PARTITION PART_9 VALUES LESS THAN (1801));
commit;
--begin transaction;
update NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 set c_first=c_first||'aa',c_vchar=lpad('abcdefghigklmnopqrstuvwxyz',300,'aaaaaaaaaaa'),c_street_2=lpad('abcdefghigklmnopqrstuvwxyz',3000,'bbbbbbbbbbbbbbbstreet2'),c_last=lpad('c_last64',20,'bbbbbbb'),c_data=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa'),c_text=lpad('1243245324554354325',300,'1232343'),c_clob=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa') where c_id=1000001;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id <5000 group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_end <'2018-08-01 10:51:47' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id >5000 and c_first like 'AA%' and c_since >'2018-08-01 10:51:47' and c_end >'2018-08-01 10:51:47' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_d_id <5000 and (c_last like 'c%' or c_last like 'AA%')group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' and (c_last like 'c%' or c_last like 'AA%') and (c_vchar like 'c_varchar%' or c_vchar like 'QVLDETANRBR%') group by c_first order by 1;
savepoint aa;
update NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 set c_first=c_first||'aa',c_vchar=lpad('abcdefghigklmnopqrstuvwxyz',300,'aaaaaaaaaaa'),c_street_2=lpad('abcdefghigklmnopqrstuvwxyz',3000,'bbbbbbbbbbbbbbbstreet2'),c_last=lpad('c_last64',20,'bbbbbbb'),c_data=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa'),c_text=lpad('1243245324554354325',300,'1232343'),c_clob=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa') where c_id=1000001;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id <5000 group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_end <'2018-08-01 10:51:47' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id >5000 and c_first like 'AA%' and c_since >'2018-08-01 10:51:47' and c_end >'2018-08-01 10:51:47' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_d_id <5000 and (c_last like 'c%' or c_last like 'AA%')group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' group by c_first order by 1;
select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' and (c_last like 'c%' or c_last like 'AA%') and (c_vchar like 'c_varchar%' or c_vchar like 'QVLDETANRBR%') group by c_first order by 1;
prepare transaction '35.AE000AEABC00ABCD.000001';
commit prepared '35.AE000AE
Succeed.
SQL> --I.单字段普通索引是分区键，单字段普通索引不是分区键，多字段普通索引包含分区键，多字段普通索引不包含分区键（全局索引）
SQL> CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_1 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_ID);
Succeed.
SQL> CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_2 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_END);
Succeed.
SQL> CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_3 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_ID,C_FIRST,C_SINCE,C_END);
Succeed.
SQL> CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_4 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_D_ID,C_LAST);
Succeed.
SQL> CREATE INDEX NEBULA.ACID_XA_INT_INVAL_DML_IDX_035_0 ON NEBULA.ACID_XA_INT_INVAL_DML_TBL_035(C_VCHAR,C_FIRST);
Succeed.
SQL> insert into NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 select * from NEBULA.ACID_XA_INVAL_DML_TBL_000;
1001 rows affected.
SQL> --I2.在事务中，Update最后一条数据,长度变长
SQL> commit;
Succeed.
SQL> --begin transaction;
SQL> update NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 set c_first=c_first||'aa',c_vchar=lpad('abcdefghigklmnopqrstuvwxyz',300,'aaaaaaaaaaa'),c_street_2=lpad('abcdefghigklmnopqrstuvwxyz',3000,'bbbbbbbbbbbbbbbstreet2'),c_last=lpad('c_last64',20,'bbbbbbb'),c_data=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa'),c_text=lpad('1243245324554354325',300,'1232343'),c_clob=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa') where c_id=1000001;
0 rows affected.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 1000
2 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id <5000 group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 1000
2 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_end <'2018-08-01 10:51:47' group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 212
2 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id >5000 and c_first like 'AA%' and c_since >'2018-08-01 10:51:47' and c_end >'2018-08-01 10:51:47' group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
0 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_d_id <5000 and (c_last like 'c%' or c_last like 'AA%')group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
1 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 1000
2 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' and (c_last like 'c%' or c_last like 'AA%') and (c_vchar like 'c_varchar%' or c_vchar like 'QVLDETANRBR%') group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 1000
2 rows fetched.
SQL> savepoint aa;
Succeed.
SQL> update NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 set c_first=c_first||'aa',c_vchar=lpad('abcdefghigklmnopqrstuvwxyz',300,'aaaaaaaaaaa'),c_street_2=lpad('abcdefghigklmnopqrstuvwxyz',3000,'bbbbbbbbbbbbbbbstreet2'),c_last=lpad('c_last64',20,'bbbbbbb'),c_data=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa'),c_text=lpad('1243245324554354325',300,'1232343'),c_clob=lpad('sbfacwjdafgjyjhfpyxcpmnutcjxrbfgxxbm',300,'aaaaa') where c_id=1000001;
0 rows affected.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 1000
2 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id <5000 group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 1000
2 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_end <'2018-08-01 10:51:47' group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 212
2 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_id >5000 and c_first like 'AA%' and c_since >'2018-08-01 10:51:47' and c_end >'2018-08-01 10:51:47' group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
0 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_d_id <5000 and (c_last like 'c%' or c_last like 'AA%')group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
1 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 1000
2 rows fetched.
SQL> select distinct c_first,count(*) from NEBULA.ACID_XA_INT_INVAL_DML_TBL_035 where c_first like 'AA%' and (c_last like 'c%' or c_last like 'AA%') and (c_vchar like 'c_varchar%' or c_vchar like 'QVLDETANRBR%') group by c_first order by 1;
C_FIRST COUNT(*)
---------------------------------------------------------------- --------------------
AAis0cmvls 1
AAiscmvls 1000
2 rows fetched.
SQL> prepare transaction '35.AE000AEABC00ABCD.000001';
Succeed.
SQL> commit prepared '35.AE000AEABC00ABCD.000001';
Succeed.
SQL>
25 天前 issue状态由 待办的 改变为 待回归
liuzhen001
24 天前 评论：
24 天前 评论：
回归版本：7.0.0 LTS B020
回归结论 ：回归通过
回归过程：
24 天前 issue状态由 待回归 改变为 已验收
24 天前 关闭了 issue
测试类型
SQL功能
测试版本
7.0.0LTS
问题描述
当update访问不存在的interval分区并触发自动建分区，但最终更新0行，prepare transaction会误判没有当前本地事务，只能attach已挂起XA分支
操作系统和硬件信息
cat /etc/system-release
openEuler release 22.03 (LTS-SP4)
测试环境
oGRAC双节点
被测功能
预置条件
set autocommit off;
操作步骤
前置用例：tc_dss_interval_xa_storge_acid_dml_000.sql
用例：oracle/interval_tbl/XATransaction/tc_dss_interval_xa_int_sto_acid_dml_035
预期输出
通过
实际输出
日志信息
无
提单组织
内部开发
测试代码