Execute 'WHERE xxx IN ('xxx-')' SQL statement，report '30000 - Unknown exception: Illegal embedded sign character'

[Gangplank0]

Execute 'WHERE xxx IN ('xxx-')' SQL statement, if only “-” in parameter，report '30000 - Unknown exception: Illegal embedded sign character' MySQL version: 5.7.32-log ShardingSphere-jdbc version: 5.2.0

How to solve this problem please?

[sandynz]

Hi @Gangplank0 , could you supply full error message and stack trace?

And does it work with PreparedStatement?

[Gangplank0]

I use ShardingSphere-proxy connect to the database，Execute this SQL statement, also report this exception; ShardingShpere-proxy version: 5.2.0

[sandynz]

Hi @tuichenchuxin , could you help to verify it? Looks it's SQL parse error

[tuichenchuxin]

@Gangplank0 can you offer ifp_locating_information create sql?

[Gangplank0]

CREATE TABLE ifp_locating_information ( id bigint NOT NULL COMMENT 'id', tenant_id bigint NOT NULL COMMENT '租户ID', xid varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '编号', name varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名称', data_source varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '数据来源', shipment_xid varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '运单号', vehicle_lpn varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '车牌号', device_num varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '设备编号', device_status varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '设备运行状态', device_electric varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '设备电量', device_offline_time decimal(20,6) DEFAULT NULL COMMENT '设备离线时长(m)', positioning_type varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '定位方式', positioning_time datetime DEFAULT NULL COMMENT '定位时间', positioning_remark varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '定位备注', travel_speed varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '行驶速度', travel_distance varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '行驶里程', travel_time decimal(20,6) DEFAULT NULL COMMENT '行驶时间', total_distance varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '路程总里程(km)', dashboard_distance varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '仪表盘里程数', coordinates_type varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '坐标类型', lon decimal(20,6) DEFAULT NULL COMMENT '经度', lat decimal(20,6) DEFAULT NULL COMMENT '纬度', hgt decimal(20,6) DEFAULT NULL COMMENT '海拔', agl decimal(20,6) DEFAULT NULL COMMENT '正北方向夹角', direction varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '方向', province varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '省份', city varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '城市', district varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '区县', location varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '位置', eta datetime DEFAULT NULL COMMENT '预计送达时间', city_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '城市名称', city_code varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '城市编码', city_in_out_type varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '进出城市类型,1-进 2-出', city_in_out_time datetime DEFAULT NULL COMMENT '进出城市时间', park_time decimal(20,6) DEFAULT NULL COMMENT '停车时长(m)', park_location varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '停车地址', park_begin_time datetime DEFAULT NULL COMMENT '停车开始时间', park_end_time datetime DEFAULT NULL COMMENT '停车结束时间', park_lon decimal(20,6) DEFAULT NULL COMMENT '停车经度', park_lat decimal(20,6) DEFAULT NULL COMMENT '停车纬度', object_version_number bigint NOT NULL DEFAULT '1' COMMENT '行版本号', creation_date datetime DEFAULT CURRENT_TIMESTAMP, created_by bigint DEFAULT '-1', last_updated_by bigint DEFAULT '-1', last_update_date datetime DEFAULT CURRENT_TIMESTAMP, event_type varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '事件类型', event_date datetime DEFAULT NULL COMMENT '事件时间', shipment_gid bigint DEFAULT NULL COMMENT '运单ID', driver_name varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '司机姓名', driver_phone varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '司机号码', issue_date datetime NOT NULL COMMENT '运单开单时间', PRIMARY KEY (id) USING BTREE, KEY ifp_locating_information_n2 (vehicle_lpn) USING BTREE, KEY ifp_locating_information_n3 (issue_date) USING BTREE, KEY ifp_locating_information_n1 (positioning_type,shipment_xid,positioning_time) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='定位信息表'