Seata（2.0.0）update语句表名不支持加别名-oracle数据库

5cnsuln7 于 22天前发布在 Oracle

关注(0)|答案(4)|浏览(25)

如果sql为：update mallcard.qst_mallcard_info t set t.amount = t.amount + 10 where t.card_no = ''；加了别名t，在回滚时会报标识符无效，发现Seata的for update 语句在amount字段上加了双引号，如下图

如果sql为：update mallcard.qst_mallcard_info t set amount = amount + 10 where t.card_no = '';，则可以正常回滚

Environment:
JDK version(e.g. java -version): 1.8
Seata client/server version: 2.0.0
Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
OS(e.g. uname -a): Linux
Others:

seata

来源：https://github.com/apache/incubator-seata/issues/6448

4条答案

按热度按时间

fae0ux8s1#

@XieYingFa Thank you very much for your feedback. Could you please provide the SQL for the table structure?

赞(0）回复(0）举报 22天前

bcs8qyzn2#

@slievrly
表结构如下：
-- Create table
create table MALLCARD.QST_MALLCARD_INFO
(
id VARCHAR2(36) not null,
card_no VARCHAR2(30) not null,
active_date DATE,
create_date DATE default sysdate not null,
expire_date DATE,
status NUMBER not null,
mercard_id VARCHAR2(36),
mem_id VARCHAR2(36),
amount NUMBER(18,2),
ori_amount NUMBER(18,2),
mer_id VARCHAR2(36),
freeze_amount NUMBER(18,2),
card_discount NUMBER(4,2),
ori_cardid VARCHAR2(36),
card_type NUMBER(2),
card_kind NUMBER,
nonce_str VARCHAR2(19),
card_qr_inf VARCHAR2(100),
sequence_number VARCHAR2(9),
track_info VARCHAR2(200),
industry_no VARCHAR2(3),
first_charge NUMBER(1) default 0,
is_fee NUMBER default 1,
fee_date DATE,
legal_id_num VARCHAR2(30),
legal_card_positive VARCHAR2(200),
legal_card_negative VARCHAR2(200),
auth_flag NUMBER(1) default 0,
auth_date DATE,
real_name VARCHAR2(100),
virtual_fee NUMBER(8,2) default 0,
virtual_fee_version_no NUMBER default 0,
sale_card_status NUMBER default 0,
card_type_id VARCHAR2(20),
bind_date DATE,
get_hxq_rate NUMBER(22,6) default 0,
ele_account_no VARCHAR2(30),
bind_amount NUMBER(18,2),
policy_id VARCHAR2(36),
card_upgrade NUMBER(6,2),
cd_key VARCHAR2(200),
upgrade_status NUMBER default 0,
bind_card_disable VARCHAR2(30) default 1,
transfer_flag NUMBER default 2,
frozen_date DATE,
agent_no VARCHAR2(10),
limit_type NUMBER,
card_recycle NUMBER,
limit_value NUMBER(6,2),
buy_flag NUMBER,
price_type NUMBER default 1,
wx_flag NUMBER,
frozen_reason VARCHAR2(255),
unfrozen_date DATE,
unfrozen_reason VARCHAR2(255),
frozen_update_by VARCHAR2(50),
update_date DATE,
unfrozen_update_by VARCHAR2(50),
combine_pay_flag NUMBER,
total_limit_amount NUMBER(18,2),
limit_amount NUMBER(18,2),
bind_before_is_ava NUMBER default 0,
card_category NUMBER default 1,
user_type NUMBER,
operator VARCHAR2(36),
update_expire_date DATE,
exchange_num NUMBER,
original_card_no VARCHAR2(30),
movie_coupon_superpose NUMBER default 1,
is_card_group INTEGER default 0,
purchase_company VARCHAR2(500),
card_sell_order_no VARCHAR2(500),
risk_policy_id VARCHAR2(36),
remark VARCHAR2(1000)
)
tablespace MALLCARD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

-- Create/Recreate indexes
create index IDX_NALLCARD_CARD_NO on MALLCARD.QST_MALLCARD_INFO (CARD_NO);
create index IDX_NALLCARD_MEM_ID on MALLCARD.QST_MALLCARD_INFO (MEM_ID);
create index IDX_NALLCARD_MERCARD_ID on MALLCARD.QST_MALLCARD_INFO (MERCARD_ID);
create index MALLCARD.idx_active_date on MALLCARD.QST_MALLCARD_INFO (ACTIVE_DATE);
-- Create/Recreate primary, unique and foreign key constraints
alter table MALLCARD.QST_MALLCARD_INFO
add primary key (ID)
using index ;
-- Grant/Revoke object privileges
grant select, update on MALLCARD.QST_MALLCARD_INFO to YFCUSR;

赞(0）回复(0）举报 22天前

bogh5gae3#

@slievrly
表结构如下：
-- Create table
create table MALLCARD.QST_MALLCARD_INFO
(
id VARCHAR2(36) not null,
card_no VARCHAR2(30) not null,
active_date DATE,
create_date DATE default sysdate not null,
expire_date DATE,
status NUMBER not null,
mercard_id VARCHAR2(36),
mem_id VARCHAR2(36),
amount NUMBER(18,2),
ori_amount NUMBER(18,2),
mer_id VARCHAR2(36),
freeze_amount NUMBER(18,2),
card_discount NUMBER(4,2),
ori_cardid VARCHAR2(36),
card_type NUMBER(2),
card_kind NUMBER,
nonce_str VARCHAR2(19),
card_qr_inf VARCHAR2(100),
sequence_number VARCHAR2(9),
track_info VARCHAR2(200),
industry_no VARCHAR2(3),
first_charge NUMBER(1) default 0,
is_fee NUMBER default 1,
fee_date DATE,
legal_id_num VARCHAR2(30),
legal_card_positive VARCHAR2(200),
legal_card_negative VARCHAR2(200),
auth_flag NUMBER(1) default 0,
auth_date DATE,
real_name VARCHAR2(100),
virtual_fee NUMBER(8,2) default 0,
virtual_fee_version_no NUMBER default 0,
sale_card_status NUMBER default 0,
card_type_id VARCHAR2(20),
bind_date DATE,
get_hxq_rate NUMBER(22,6) default 0,
ele_account_no VARCHAR2(30),
bind_amount NUMBER(18,2),
policy_id VARCHAR2(36),
card_upgrade NUMBER(6,2),
cd_key VARCHAR2(200),
upgrade_status NUMBER default 0,
bind_card_disable VARCHAR2(30) default 1,
transfer_flag NUMBER default 2,
frozen_date DATE,
agent_no VARCHAR2(10),
limit_type NUMBER,
card_recycle NUMBER,
limit_value NUMBER(6,2),
buy_flag NUMBER,
price_type NUMBER default 1,
wx_flag NUMBER,
frozen_reason VARCHAR2(255),
unfrozen_date DATE,
unfrozen_reason VARCHAR2(255),
frozen_update_by VARCHAR2(50),
update_date DATE,
unfrozen_update_by VARCHAR2(50),
combine_pay_flag NUMBER,
total_limit_amount NUMBER(18,2),
limit_amount NUMBER(18,2),
bind_before_is_ava NUMBER default 0,
card_category NUMBER default 1,
user_type NUMBER,
operator VARCHAR2(36),
update_expire_date DATE,
exchange_num NUMBER,
original_card_no VARCHAR2(30),
movie_coupon_superpose NUMBER default 1,
is_card_group INTEGER default 0,
purchase_company VARCHAR2(500),
card_sell_order_no VARCHAR2(500),
risk_policy_id VARCHAR2(36),
remark VARCHAR2(1000)
)
tablespace MALLCARD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

-- Create/Recreate indexes
create index IDX_NALLCARD_CARD_NO on MALLCARD.QST_MALLCARD_INFO (CARD_NO);
create index IDX_NALLCARD_MEM_ID on MALLCARD.QST_MALLCARD_INFO (MEM_ID);
create index IDX_NALLCARD_MERCARD_ID on MALLCARD.QST_MALLCARD_INFO (MERCARD_ID);
create index MALLCARD.idx_active_date on MALLCARD.QST_MALLCARD_INFO (ACTIVE_DATE);
-- Create/Recreate primary, unique and foreign key constraints
alter table MALLCARD.QST_MALLCARD_INFO
add primary key (ID)
using index ;
-- Grant/Revoke object privileges
grant select, update on MALLCARD.QST_MALLCARD_INFO to YFCUSR;