Gorm Insert sql with MySQL Geometry Type in gorm client throws error，but MySQL native client success run

GORM Playground Link

https://github.com/go-gorm/playground/pull/501

https://github.com/go-gorm/playground/pull/1

Description

I have a MySQL table： CREATE TABLE location ( id int unsigned NOT NULL AUTO_INCREMENT, ip_start int unsigned NOT NULL DEFAULT '0', ip_end int unsigned NOT NULL DEFAULT '0', ip_range linestring NOT NULL SRID 3857, PRIMARY KEY (id), SPATIAL KEY si_geo_ip_range (ip_range) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

And I have a insert sql:
INSERT INTO location (ip_start,ip_end,ip_range) VALUES (8060929,8060930,ST_GeomFromText('LINESTRING(8060929 -1, 8060930 1)', 3857))

this sql success running at MySQL, run result show: > Affected rows: 1。

so, I run gorm code:

`err = db.Table("location").Create(map[string]interface{}{ "ip_start": 8060929, "ip_end": 8060930, "ip_range": clause.Expr{ SQL: "ST_GeomFromText('LINESTRING(? -1, ? 1)', 3857)", Vars: []interface{}{8060929, 8060930}, }, }).Error

if err != nil { panic(err) } `

console show: sql: expected 2 arguments, got 4 [167.608ms] [rows:0] INSERT INTO location (ip_end,ip_range,ip_start) VALUES (8060930,ST_GeomFromText('LINESTRING(8060929 -1, 8060930 1)', 3857),8060929) panic: sql: expected 2 arguments, got 4

However, the SQL obtained from the console runs successfully in native MySQL

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: Albert-Zuo

MySQL Version 8.0.28

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: a631807682

This has nothing to do with gorm, you can't actually execute a statement like this

PREPARE stmt2 FROM "INSERT INTO `location` (`ip_end`,`ip_range`,`ip_start`) VALUES (?,ST_GeomFromText('LINESTRING(? -1, ? 1)', 3857),?)"

SET @a = 8060929;
SET @b = 8060930;
-- Incorrect arguments 
EXECUTE stmt2 USING @a, @b , @a, @b;

You can change interpolateparams config or use scanner to support it.

https://stackoverflow.com/questions/60520863/working-with-spatial-data-with-gorm-and-mysql https://github.com/twpayne/go-geom

Comment From: Albert-Zuo

This has nothing to do with gorm, you can't actually execute a statement like this

``sql PREPARE stmt2 FROM "INSERT INTOlocation(ip_end,ip_range,ip_start`) VALUES (?,ST_GeomFromText('LINESTRING(? -1, ? 1)', 3857),?)"

SET @a = 8060929; SET @b = 8060930; -- Incorrect arguments EXECUTE stmt2 USING @a, @b , @a, @b; ```

You can change interpolateparams config or use scanner to support it.

https://stackoverflow.com/questions/60520863/working-with-spatial-data-with-gorm-and-mysql https://github.com/twpayne/go-geom

thanks,your reply. but this result is absurd. I used the Create function to get the correct SQL, but I couldn't use it. Isn't this a very incorrect result?

So I think this is a bug.

Comment From: a631807682

This is mysql's implementation, when you create a prepare statement and ask mysql how many parameters it needs, it will tell you 2.

Comment From: Albert-Zuo

This is mysql's implementation, when you create a prepare statement and ask mysql how many parameters it needs, it will tell you 2.

You're right，thanks