[BUG] INSERT INTO... ON DUPLICATE KEY UPDATE... 语法解析失败

[pengweizhong]

Database Type

mysql

Database Version

8.1.10

Druid Version

1.2.23

JDK Version

1.8

Error SQL

insert into
	`products` (
        `category_id`,
	`price`,
	`product_id`,
	`created_at`,
	`attributes`,
	`stock`,
	`product_name`
    )
values
    (4,
879.00,
20,
'2024-12-12',
null,
222,
'New Coffee Maker -> upsert2') as _tmp_upsert 
        on
duplicate key
update
	`category_id` = _tmp_upsert.`category_id`,
	`price` = _tmp_upsert.`price`,
	`product_id` = _tmp_upsert.`product_id`,
	`created_at` = _tmp_upsert.`created_at`,
	`attributes` = _tmp_upsert.`attributes`,
	`stock` = _tmp_upsert.`stock`,
	`product_name` = _tmp_upsert.`product_name`;
        

Testcase Code

测试代码

    @Test
    void upsertSelective() {
        Product product = sqlContext.select().allColumn().from(Product.class)
                .where(whereCondition -> whereCondition.andEqualTo(Product::getProductId, 20))
                .fetch().toOne();
        product.setProductName("New Coffee Maker -> upsert2");
        product.setCreatedAt(new Date());
        product.setAttributes(null);
        int i = sqlContext.upsertSelective(product, Collections.singletonList(Product::getAttributes));
        System.out.println(i);
    }

sql建表语句

-- 创建 Products 表
CREATE TABLE products
(
    product_id   INT PRIMARY KEY AUTO_INCREMENT COMMENT '产品 ID',                          -- 产品 ID
    product_name VARCHAR(150)   NOT NULL COMMENT '产品名称',                                -- 产品名称
    price        DECIMAL(10, 2) NOT NULL COMMENT '产品价格',                                -- 产品价格
    stock        INT            NOT NULL COMMENT '产品库存',                                -- 产品库存
    category_id  INT COMMENT '外键，关联 Categories 表',                                     -- 外键，关联 Categories 表
    attributes   JSON COMMENT '产品属性（JSON 存储颜色、尺寸等）',                             -- 产品属性（JSON）
    created_at   DATE           NOT NULL COMMENT '产品创建日期',                            -- 产品创建日期
    is_available BOOLEAN DEFAULT TRUE COMMENT '是否上架',                                   -- 是否上架
    CONSTRAINT fk_category_id FOREIGN KEY (category_id) REFERENCES categories (category_id) -- 外键约束
) COMMENT = '产品表';

INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(1, 'iPhone 14', 999.99, 50, 1, '{"color": "black", "storage": "128GB"}', '2023-01-15', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(2, 'MacBook Pro', 1999.99, 30, 1, '{"color": "silver", "storage": "256GB"}', '2023-03-10', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(3, 'Harry Potter and the Philosopher''s Stone', 12.99, 200, 2, '{"author": "J.K. Rowling", "format": "hardcover"}', '2022-09-05', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(4, 'Nike Running Shoes', 85.50, 100, 3, '{"size": "10", "color": "blue"}', '2023-06-01', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(5, 'Coffee Maker', 49.99, 150, 4, '{"type": "drip", "brand": "Breville"}', '2023-04-18', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(6, 'iPhone 14', 999.99, 50, 1, '{"color": "black", "storage": "128GB"}', '2023-01-15', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(7, 'MacBook Pro', 1999.99, 30, 1, '{"color": "silver", "storage": "256GB"}', '2023-03-10', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(8, 'Harry Potter and the Philosopher''s Stone', 12.99, 200, 2, '{"author": "J.K. Rowling", "format": "hardcover"}', '2022-09-05', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(9, 'Nike Running Shoes', 85.50, 100, 3, '{"size": "10", "color": "blue"}', '2023-06-01', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(10, 'Coffee Maker', 49.99, 150, 4, '{"type": "drip", "brand": "Breville"}', '2023-04-18', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(11, 'iPhone 14', 999.99, 50, 1, '{"color": "black", "storage": "128GB"}', '2023-01-15', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(12, 'MacBook Pro', 1999.99, 30, 1, '{"color": "silver", "storage": "256GB"}', '2023-03-10', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(13, 'Harry Potter and the Philosopher''s Stone', 12.99, 200, 2, '{"author": "J.K. Rowling", "format": "hardcover"}', '2022-09-05', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(14, 'Nike Running Shoes', 85.50, 100, 3, '{"size": "10", "color": "blue"}', '2023-06-01', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(15, 'Coffee Maker', 49.99, 150, 4, '{"type": "drip", "brand": "Breville"}', '2023-04-18', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(16, 'iPhone 14', 999.99, 50, 1, '{"color": "black", "storage": "128GB"}', '2023-01-15', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(17, 'MacBook Pro', 1999.99, 30, 1, '{"color": "silver", "storage": "256GB"}', '2023-03-10', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(18, 'Harry Potter and the Philosopher''s Stone', 12.99, 200, 2, '{"author": "J.K. Rowling", "format": "hardcover"}', '2022-09-05', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(19, 'Nike Running Shoes', 85.50, 100, 3, '{"size": "10", "color": "blue"}', '2023-06-01', 1);
INSERT INTO dynamic_sql2.products
(product_id, product_name, price, stock, category_id, `attributes`, created_at, is_available)
VALUES(20, 'Coffee Maker', 49.99, 150, 4, '{"type": "drip", "brand": "Breville"}', '2023-04-18', 1);

Stacktrace Info

2024-11-20 16:33:57 [main] DEBUG com.pengwz.dynamic.sql2.core.database.SqlDebugger - dataSource -->     Preparing: select `products`.`category_id` as categoryId, `products`.`price` as price, `products`.`product_id` as productId, `products`.`created_at` as createdAt, `products`.`attributes` as attributes, `products`.`stock` as stock, `products`.`product_name` as productName, `products`.`is_available` as isAvailable from `products` as `products` where  `products`.`product_id` = ?
2024-11-20 16:33:57 [main] DEBUG com.pengwz.dynamic.sql2.core.database.SqlDebugger - dataSource -->    Parameters: 20(Integer)
2024-11-20 16:33:58 [main] DEBUG com.pengwz.dynamic.sql2.core.database.SqlDebugger - dataSource <--         Total: 1
2024-11-20 16:33:58 [main] DEBUG com.pengwz.dynamic.sql2.core.database.SqlDebugger - dataSource -->     Preparing: insert into `products` (`category_id`, `price`, `product_id`, `created_at`, `attributes`, `stock`, `product_name`) values (?, ?, ?, ?, ?, ?, ?) as _tmp_upsert on duplicate key update `category_id` = _tmp_upsert.`category_id`, `price` = _tmp_upsert.`price`, `product_id` = _tmp_upsert.`product_id`, `created_at` = _tmp_upsert.`created_at`, `attributes` = _tmp_upsert.`attributes`, `stock` = _tmp_upsert.`stock`, `product_name` = _tmp_upsert.`product_name`
2024-11-20 16:33:58 [main] DEBUG com.pengwz.dynamic.sql2.core.database.SqlDebugger - dataSource -->    Parameters: 4(Integer), 879.00(BigDecimal), 20(Integer), Wed Nov 20 16:33:58 CST 2024(Date), null, 222(Integer), New Coffee Maker -> upsert2(String)

java.lang.IllegalStateException: java.sql.SQLException: sql injection violation, dbType mysql, , druid-version 1.2.23, syntax error: not supported.pos 146, line 1, column 143, token AS : insert into `products` (`category_id`, `price`, `product_id`, `created_at`, `attributes`, `stock`, `product_name`) values (?, ?, ?, ?, ?, ?, ?) as _tmp_upsert on duplicate key update `category_id` = _tmp_upsert.`category_id`, `price` = _tmp_upsert.`price`, `product_id` = _tmp_upsert.`product_id`, `created_at` = _tmp_upsert.`created_at`, `attributes` = _tmp_upsert.`attributes`, `stock` = _tmp_upsert.`stock`, `product_name` = _tmp_upsert.`product_name`

	at com.pengwz.dynamic.sql2.core.database.RootExecutor.executeUpdate(RootExecutor.java:102)
	at com.pengwz.dynamic.sql2.core.database.impl.MysqlSqlExecutor.upsertSelective(MysqlSqlExecutor.java:82)
	at com.pengwz.dynamic.sql2.core.database.SqlExecutionFactory.applySql(SqlExecutionFactory.java:103)
	at com.pengwz.dynamic.sql2.core.database.SqlExecutionFactory.executorSql(SqlExecutionFactory.java:63)
	at com.pengwz.dynamic.sql2.core.dml.update.EntitiesUpdater.upsertSelective(EntitiesUpdater.java:146)
	at com.pengwz.dynamic.sql2.context.DefaultSqlContext.upsertSelective(DefaultSqlContext.java:122)
	at com.pengwz.dynamic.sql2.core.dml.update.UpdateTest.upsertSelective(UpdateTest.java:115)
	at java.lang.reflect.Method.invoke(Method.java:503)
	at java.util.ArrayList.forEach(ArrayList.java:1259)
	at java.util.ArrayList.forEach(ArrayList.java:1259)
Caused by: java.sql.SQLException: sql injection violation, dbType mysql, , druid-version 1.2.23, syntax error: not supported.pos 146, line 1, column 143, token AS : insert into `products` (`category_id`, `price`, `product_id`, `created_at`, `attributes`, `stock`, `product_name`) values (?, ?, ?, ?, ?, ?, ?) as _tmp_upsert on duplicate key update `category_id` = _tmp_upsert.`category_id`, `price` = _tmp_upsert.`price`, `product_id` = _tmp_upsert.`product_id`, `created_at` = _tmp_upsert.`created_at`, `attributes` = _tmp_upsert.`attributes`, `stock` = _tmp_upsert.`stock`, `product_name` = _tmp_upsert.`product_name`
	at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:876)
	at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:318)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:547)
	at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:908)
	at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:116)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:547)
	at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:328)
	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:369)
	at com.pengwz.dynamic.sql2.core.database.RootExecutor.executeUpdate(RootExecutor.java:92)
	... 9 more
Caused by: com.alibaba.druid.sql.parser.ParserException: not supported.pos 146, line 1, column 143, token AS
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:654)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:117)
	at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:494)
	at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:446)
	at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:853)
	... 17 more

Error Info

No response