[Bug Report] 在我链接代理15306端口进行insert into插入时，两种语法似乎不太兼容，在3306上没有任何区别

Overview of the Issue

INSERT INTO study_subject ( name, parent_id, ancestors, del_flag ) SELECT '测试1', 0, IFNULL( MAX( concat( ancestors, ',', 0 )), 0 ), '0' FROM study_subject WHERE id = 0 我再链接15306代理端口的时候，上面这个可以正常插入，下面这个报错，我应该调整哪里呢 INSERT INTO study_subject ( NAME, parent_id, ancestors, del_flag ) VALUE ( '测试', 0, IF ( TRUE, 0, ( SELECT concat( t1.ancestors, ',', 0 ) FROM study_subject t1 WHERE t1.id = 0 )), '0')

Reproduction Steps

/* Navicat Premium Data Transfer

Source Server : 97代理 Source Server Type : MySQL Source Server Version : 80030 (8.0.30) Source Host : 172.16.1.97:8085 Source Schema : community-bd

Target Server Type : MySQL Target Server Version : 80030 (8.0.30) File Encoding : 65001

Date: 10/05/2024 18:06:46 */

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for study_subject

DROP TABLE IF EXISTS study_subject; CREATE TABLE study_subject ( id int NOT NULL AUTO_INCREMENT, name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, parent_id int NOT NULL COMMENT '父ID', ancestors varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '祖级列表', del_flag char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '逻辑删除 0：正常 1：删除', PRIMARY KEY (id) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学习类型' ROW_FORMAT = COMPACT;

-- Records of study_subject

INSERT INTO study_subject VALUES (1, '文档', 0, '0', '0'); INSERT INTO study_subject VALUES (2, '视频', 0, '0', '0'); INSERT INTO study_subject VALUES (3, '文档下级', 1, '0,1', '0'); INSERT INTO study_subject VALUES (4, '视频下级', 2, '0,2', '0'); INSERT INTO study_subject VALUES (5, '测试', 0, '0', '0'); INSERT INTO study_subject VALUES (6, '测试1', 0, '0', '0');

SET FOREIGN_KEY_CHECKS = 1;

Binary Version

2024-05-10T17:18:21.404039487+08:00 I0510 09:18:21.403790       9 servenv.go:131] Version: 16.0.0 (Git revision b8637a7e85bea42628b40ed961d59cdc69bf3cd1 branch 'HEAD') built on Wed Mar 27 00:57:19 UTC 2024 by root@buildkitsandbox using go1.20.2 linux/amd64

Operating System and Environment details

[root@node82 ~]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

[root@node82 ~]# uname -sr
Linux 3.10.0-1160.105.1.el7.x86_64
[root@node82 ~]# uname -m
x86_64
[root@node82 ~]#

Log Fragments

No response

嗨, @wanghaiyang5241

首先，你的SQL中，有个关键词错误，应该是VALUES而不是VALUE:

INSERT INTO study_subject ( NAME, parent_id, ancestors, del_flag )
VALUES
(
'测试',
0,
IF
(
TRUE,
0,
( SELECT concat( t1.ancestors, ',', 0 ) FROM study_subject t1 WHERE t1.id = 0 )),
'0');

其次，当开启读写分离的时候，如果SQL中带有SubQuery，会导致WeScale解析SQL的时候报错，这是我们的bug。目前你有2个解决方案：

修改配置，关闭读写分离: read_write_splitting_policy=disable
修改SQL，去掉子查询

我们接下来会立刻修复这个bug，不过这需要发布新的版本，等我们发布后，你就可以使用现在这样的SQL了。

Hi, @wanghaiyang5241

Firstly, there is a keyword error in your SQL. It should be VALUES instead of VALUE:

INSERT INTO study_subject ( NAME, parent_id, ancestors, del_flag )
VALUES
(
'Test',
0,
IF
(
TRUE,
0,
( SELECT concat( t1.ancestors, ',', 0 ) FROM study_subject t1 WHERE t1.id = 0 )),
'0');

Secondly, when read-write splitting is enabled, if the SQL contains a SubQuery, it will cause WeScale to report an error while parsing the SQL. This is a bug on our side. Currently, you have two solutions:

Modify the configuration to disable read-write splitting: read_write_splitting_policy=disable
Modify the SQL to remove the subquery

We will be fixing this bug immediately, but it requires the release of a new version. Once we release it, you will be able to use the SQL as it is now.

wesql / wescale