[Bug] [SQL] execute sql error: Can not issue data manipulation statements with executeQuery()

hanfengcan commented 2 years ago

Search before asking

[X] I had searched in the issues and found no similar issues.

What happened

我在执行SQL任务的时候, 报错execute sql error: Can not issue data manipulation statements with executeQuery()

数据库使用了Doris

建表和select都可以正常执行

What you expected to happen

能够执行insert语句

How to reproduce

目标表

CREATE TABLE `wx_payment_details` (
  `create_time` datetime NOT NULL COMMENT "创建时间",
  `amount` decimal(12, 4) NULL COMMENT "支付金额",
  `settlement_refund_fee` decimal(12, 4) NULL COMMENT "退款金额",
  `goods_name` varchar(100) NULL COMMENT "商品名称",
  `goods_type` varchar(50) NULL COMMENT "商品类型",
  `transaction_id` varchar(100) NOT NULL DEFAULT "reserve" COMMENT "微信订单号"
) ENGINE=OLAP
DUPLICATE KEY(`create_time`)
COMMENT "微信订单记录"
DISTRIBUTED BY HASH(`create_time`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
);

源表

CREATE TABLE `wx_payment_details_load` (
  `create_time` varchar(100) NOT NULL COMMENT "",
  `appid` varchar(100) NOT NULL COMMENT "",
  `mch_id` varchar(100) NOT NULL COMMENT "",
  `mch_id_child` varchar(50) NOT NULL COMMENT "",
  `device_info` varchar(60) NULL COMMENT "",
  `transaction_id` varchar(100) NOT NULL COMMENT "",
  `out_trade_no` varchar(100) NOT NULL COMMENT "",
  `openid` varchar(100) NOT NULL COMMENT "",
  `trade_type` varchar(100) NOT NULL COMMENT "",
  `trade_state` varchar(100) NOT NULL COMMENT "",
  `bank_type` varchar(100) NOT NULL COMMENT "",
  `fee_type` varchar(100) NOT NULL COMMENT "",
  `amount` varchar(50) NOT NULL COMMENT "",
  `enterprise_discounts` varchar(100) NOT NULL COMMENT "",
  `refund_id` varchar(100) NOT NULL COMMENT "",
  `out_refund_no` varchar(100) NOT NULL COMMENT "",
  `settlement_refund_fee` varchar(50) NOT NULL COMMENT "",
  `enterprise_refund_discounts` varchar(100) NOT NULL COMMENT "",
  `refund_type` varchar(100) NULL COMMENT "",
  `refund_status` varchar(100) NULL COMMENT "",
  `goods_name` varchar(100) NOT NULL COMMENT "",
  `attach` varchar(100) NOT NULL COMMENT "",
  `service_charge` varchar(100) NOT NULL COMMENT "",
  `rate` varchar(100) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`create_time`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`create_time`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
);

将源表中的微信账单处理后写入目标表

INSERT into example_db.wx_payment_details SELECT
  CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
  0 + split_part(amount, '`', 2) as amount,
  0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
  split_part(goods_name, '`', 2) as goods_name,
  CASE goods_name
  when '`门诊缴费' then '门诊缴费'
  when '`住院缴费' then '住院缴费'
  else goods_name end as goods_type, 
  split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load;

Anything else

No response

Version

2.0.2

Are you willing to submit PR?

[ ] Yes I am willing to submit a PR!

Code of Conduct

[X] I agree to follow this project's Code of Conduct

github-actions[bot] commented 2 years ago

Search before asking

[X] I had searched in the issues and found no similar issues.

What happened

When I execute a SQL task, I get an error execute sql error: Can not issue data manipulation statements with executeQuery()

The database uses Doris

Both table creation and select can be executed normally

What you expected to happen

Ability to execute insert statements

How to reproduce

target table

CREATE TABLE `wx_payment_details` (
  `create_time` datetime NOT NULL COMMENT "create time",
  `amount` decimal(12, 4) NULL COMMENT "Payment Amount",
  `settlement_refund_fee` decimal(12, 4) NULL COMMENT "Refund Amount",
  `goods_name` varchar(100) NULL COMMENT "goods name",
  `goods_type` varchar(50) NULL COMMENT "goods type",
  `transaction_id` varchar(100) NOT NULL DEFAULT "reserve" COMMENT "WeChat order number"
) ENGINE=OLAP
DUPLICATE KEY(`create_time`)
COMMENT "WeChat order record"
DISTRIBUTED BY HASH(`create_time`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
);

source table CREATE TABLE wx_payment_details_load ( create_time varchar(100) NOT NULL COMMENT "", appid varchar(100) NOT NULL COMMENT "", mch_id varchar(100) NOT NULL COMMENT "", mch_id_child varchar(50) NOT NULL COMMENT "", device_info varchar(60) NULL COMMENT "", transaction_id varchar(100) NOT NULL COMMENT "", out_trade_no varchar(100) NOT NULL COMMENT "", openid varchar(100) NOT NULL COMMENT "", trade_type varchar(100) NOT NULL COMMENT "", trade_state varchar(100) NOT NULL COMMENT "", bank_type varchar(100) NOT NULL COMMENT "", fee_type varchar(100) NOT NULL COMMENT "", amount varchar(50) NOT NULL COMMENT "", enterprise_discounts varchar(100) NOT NULL COMMENT "", refund_id varchar(100) NOT NULL COMMENT "", out_refund_no varchar(100) NOT NULL COMMENT "", settlement_refund_fee varchar(50) NOT NULL COMMENT "", enterprise_refund_discounts varchar(100) NOT NULL COMMENT "", refund_type varchar(100) NULL COMMENT "", refund_status varchar(100) NULL COMMENT "", goods_name varchar(100) NOT NULL COMMENT "", attach varchar(100) NOT NULL COMMENT "", service_charge varchar(100) NOT NULL COMMENT "", rate varchar(100) NOT NULL COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(create_time) COMMENT "OLAP" DISTRIBUTED BY HASH(create_time) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2" );

Write the WeChat bill in the source table into the target table after processing

INSERT into example_db.wx_payment_details SELECT
  CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
  0 + split_part(amount, '`', 2) as amount,
  0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
  split_part(goods_name, '`', 2) as goods_name,
  CASE goods_name
  when '`outpatient' then 'outpatient payment'
  when '`payment between clinics' then 'payment for outpatient clinics'
  when '`hospitalization payment' then 'hospitalization payment'
  when '`hospitalization prepayment' then 'hospitalization payment'
  else goods_name end as goods_type,
  split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load;

Anything else

No response

Version

2.0.2

Are you willing to submit PR?

[ ] Yes I am willing to submit a PR!

Code of Conduct

[X] I agree to follow this project's Code of Conduct

github-actions[bot] commented 2 years ago

Thank you for your feedback, we have received your issue, Please wait patiently for a reply.

In order for us to understand your request as soon as possible, please provide detailed information、version or pictures.
If you haven't received a reply for a long time, you can join our slack and send your question to channel #troubleshooting

SbloodyS commented 2 years ago

Currently SQL node does not support semicolons. This feature will be implemented when the issue #9177 is closed. You can pay attention to it.

hanfengcan commented 2 years ago

thanks

hanfengcan commented 2 years ago

@SbloodyS Removing the semicolon also raises an error

SbloodyS commented 2 years ago

@SbloodyS Removing the semicolon also raises an error

Can you paste your configuration and error log here?

hanfengcan commented 2 years ago

[LOG-PATH]: /opt/apache-dolphinscheduler-2.0.1-bin/logs/5241061414816_1/100/275.log, [HOST]:  192.168.160.3
[INFO] 2022-04-20 10:37:30.007  - [taskAppId=TASK-5241061414816_1-100-275]:[121] - Full sql parameters: SqlParameters{type='MYSQL', datasource=3, sql='INSERT into fy_payment_db.wx_payment_details SELECT
  CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
  0 + split_part(amount, '`', 2) as amount,
  0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
  split_part(goods_name, '`', 2) as goods_name,
  CASE goods_name
  when '`门诊缴费' then '门诊缴费'
  when '`诊间支付-缴费订单' then '门诊缴费'
  when '`住院缴费' then '住院缴费'
  when '`住院缴费-住院预交' then '住院缴费'
  else goods_name end as goods_type, 
  split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load', sqlType=0, sendEmail=false, displayRows=10, limit=0, udfs='', showType='null', connParams='', groupId='0', title='', preStatements=[], postStatements=[]}
[INFO] 2022-04-20 10:37:30.014  - [taskAppId=TASK-5241061414816_1-100-275]:[122] - sql type : MYSQL, datasource : 3, sql : INSERT into fy_payment_db.wx_payment_details SELECT
  CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
  0 + split_part(amount, '`', 2) as amount,
  0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
  split_part(goods_name, '`', 2) as goods_name,
  CASE goods_name
  when '`门诊缴费' then '门诊缴费'
  when '`诊间支付-缴费订单' then '门诊缴费'
  when '`住院缴费' then '住院缴费'
  when '`住院缴费-住院预交' then '住院缴费'
  else goods_name end as goods_type, 
  split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load , localParams : [],udfs : ,showType : null,connParams : ,varPool : [] ,query max result limit  0
[INFO] 2022-04-20 10:37:30.017  - [taskAppId=TASK-5241061414816_1-100-275]:[450] - after replace sql , preparing : INSERT into fy_payment_db.wx_payment_details SELECT
  CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
  0 + split_part(amount, '`', 2) as amount,
  0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
  split_part(goods_name, '`', 2) as goods_name,
  CASE goods_name
  when '`门诊缴费' then '门诊缴费'
  when '`诊间支付-缴费订单' then '门诊缴费'
  when '`住院缴费' then '住院缴费'
  when '`住院缴费-住院预交' then '住院缴费'
  else goods_name end as goods_type, 
  split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load
[INFO] 2022-04-20 10:37:30.018  - [taskAppId=TASK-5241061414816_1-100-275]:[459] - Sql Params are replaced sql , parameters:
[INFO] 2022-04-20 10:37:30.019  - [taskAppId=TASK-5241061414816_1-100-275]:[529] - can't find udf function resource
[INFO] 2022-04-20 10:37:30.029  - [taskAppId=TASK-5241061414816_1-100-275]:[403] - prepare statement replace sql : HikariProxyPreparedStatement@1988302493 wrapping com.mysql.cj.jdbc.ClientPreparedStatement: INSERT into fy_payment_db.wx_payment_details SELECT
  CAST ( split_part(create_time, '`', 2) as datetime ) as create_time,
  0 + split_part(amount, '`', 2) as amount,
  0 - split_part(settlement_refund_fee, '`', 2) as settlement_refund_fee ,
  split_part(goods_name, '`', 2) as goods_name,
  CASE goods_name
  when '`门诊缴费' then '门诊缴费'
  when '`住院缴费' then '住院缴费'
  else goods_name end as goods_type, 
  split_part(transaction_id, '`', 2) as transaction_id
FROM fy_payment_db.wx_payment_details_load 
[ERROR] 2022-04-20 10:37:30.030  - [taskAppId=TASK-5241061414816_1-100-275]:[212] - execute sql error: Can not issue data manipulation statements with executeQuery().
[ERROR] 2022-04-20 10:37:30.031  - [taskAppId=TASK-5241061414816_1-100-275]:[163] - sql task error: java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

SbloodyS commented 2 years ago

Did you chose Non Query sql type? @hanfengcan

hanfengcan commented 2 years ago

OK, it work, thanks

netcloudtec commented 1 year ago

大佬这个问题怎么解决的？

netcloudtec commented 1 year ago

大佬这个问题怎么解决的？

apache / dolphinscheduler