Repository: go-gorm/gorm
Pull request #6806 (2024-01-26 12:53:17)

let limit and offset use bind parameter
<!--
Make sure these boxes checked before submitting your pull request.

For significant changes, please open an issue to make an agreement on an implementation design/plan first before starting it.
-->

- [ ] Do only one thing
- [ ] Non breaking API changes
- [ ] Tested

### What did this pull request do?


 two  changes for solving the limt and offset problem which is  possible to cause  continuously increasing of  prepared_stmt_count of mysql , max_prepared_stmt_count  value range  is 0 - 1048576 and  default 16382.

### User Case Description

the caller used  while true ugly code to call our api , our api has bugs for no checking if the limit and offset is below the  total row number  . limit and offset was going far away from the row count . a number of sql would be  prepared in mysql ,much more than  max_prepared_stmt_count  .
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 1048576 )"




jinzhu: Hi @jasonchuan 

thank you for your pr, can you please fix those failed tests?
jasonchuan: > Hi @jasonchuan
> 
> thank you for your pr, can you please fix those failed tests?



> Hi @jasonchuan
> 
> thank you for your pr, can you please fix those failed tests?

ok  I'll fix those later!
jasonchuan: @jinzhu got  the job done , please approve the postgress commit (https://github.com/go-gorm/postgres/pull/239) first.  because the stmt.Vars is polluted  by  gorm.io/driver/postgres@v1.5.4/migrator.go:598 ,we must get the vars ok for bindvar method of postgress driver  first.



Patch:
diff --git a/clause/limit.go b/clause/limit.go
index abda005..3edde43 100644
--- a/clause/limit.go
+++ b/clause/limit.go
@@ -1,7 +1,5 @@
 package clause
 
-import "strconv"
-
 // Limit limit clause
 type Limit struct {
 	Limit  *int
@@ -17,14 +15,14 @@ func (limit Limit) Name() string {
 func (limit Limit) Build(builder Builder) {
 	if limit.Limit != nil && *limit.Limit >= 0 {
 		builder.WriteString("LIMIT ")
-		builder.WriteString(strconv.Itoa(*limit.Limit))
+		builder.AddVar(builder, *limit.Limit)
 	}
 	if limit.Offset > 0 {
 		if limit.Limit != nil && *limit.Limit >= 0 {
 			builder.WriteByte(' ')
 		}
 		builder.WriteString("OFFSET ")
-		builder.WriteString(strconv.Itoa(limit.Offset))
+		builder.AddVar(builder, limit.Offset)
 	}
 }
 


Test patch:
diff --git a/clause/limit_test.go b/clause/limit_test.go
index a9fd4e2..96a7e7e 100644
--- a/clause/limit_test.go
+++ b/clause/limit_test.go
@@ -22,43 +22,53 @@ func TestLimit(t *testing.T) {
 				Limit:  &limit10,
 				Offset: 20,
 			}},
-			"SELECT * FROM `users` LIMIT 10 OFFSET 20", nil,
+			"SELECT * FROM `users` LIMIT ? OFFSET ?",
+			[]interface{}{limit10, 20},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Limit: &limit0}},
-			"SELECT * FROM `users` LIMIT 0", nil,
+			"SELECT * FROM `users` LIMIT ?",
+			[]interface{}{limit0},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Limit: &limit0}, clause.Limit{Offset: 0}},
-			"SELECT * FROM `users` LIMIT 0", nil,
+			"SELECT * FROM `users` LIMIT ?",
+			[]interface{}{limit0},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Offset: 20}},
-			"SELECT * FROM `users` OFFSET 20", nil,
+			"SELECT * FROM `users` OFFSET ?",
+			[]interface{}{20},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Offset: 20}, clause.Limit{Offset: 30}},
-			"SELECT * FROM `users` OFFSET 30", nil,
+			"SELECT * FROM `users` OFFSET ?",
+			[]interface{}{30},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Offset: 20}, clause.Limit{Limit: &limit10}},
-			"SELECT * FROM `users` LIMIT 10 OFFSET 20", nil,
+			"SELECT * FROM `users` LIMIT ? OFFSET ?",
+			[]interface{}{limit10, 20},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Limit: &limit10, Offset: 20}, clause.Limit{Offset: 30}},
-			"SELECT * FROM `users` LIMIT 10 OFFSET 30", nil,
+			"SELECT * FROM `users` LIMIT ? OFFSET ?",
+			[]interface{}{limit10, 30},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Limit: &limit10, Offset: 20}, clause.Limit{Offset: 30}, clause.Limit{Offset: -10}},
-			"SELECT * FROM `users` LIMIT 10", nil,
+			"SELECT * FROM `users` LIMIT ?",
+			[]interface{}{limit10},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Limit: &limit10, Offset: 20}, clause.Limit{Offset: 30}, clause.Limit{Limit: &limitNeg10}},
-			"SELECT * FROM `users` OFFSET 30", nil,
+			"SELECT * FROM `users` OFFSET ?",
+			[]interface{}{30},
 		},
 		{
 			[]clause.Interface{clause.Select{}, clause.From{}, clause.Limit{Limit: &limit10, Offset: 20}, clause.Limit{Offset: 30}, clause.Limit{Limit: &limit50}},
-			"SELECT * FROM `users` LIMIT 50 OFFSET 30", nil,
+			"SELECT * FROM `users` LIMIT ? OFFSET ?",
+			[]interface{}{limit50, 30},
 		},
 	}
 
diff --git a/tests/connpool_test.go b/tests/connpool_test.go
index e0e1c77..21a2bad 100644
--- a/tests/connpool_test.go
+++ b/tests/connpool_test.go
@@ -102,13 +102,13 @@ func TestConnPoolWrapper(t *testing.T) {
 		expect: []string{
 			"SELECT VERSION()",
 			"INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`,`birthday`,`company_id`,`manager_id`,`active`) VALUES (?,?,?,?,?,?,?,?,?)",
-			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1",
+			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT ?",
 			"INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`,`birthday`,`company_id`,`manager_id`,`active`) VALUES (?,?,?,?,?,?,?,?,?)",
-			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1",
-			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1",
+			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT ?",
+			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT ?",
 			"INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`,`birthday`,`company_id`,`manager_id`,`active`) VALUES (?,?,?,?,?,?,?,?,?)",
-			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1",
-			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1",
+			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT ?",
+			"SELECT * FROM `users` WHERE name = ? AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT ?",
 		},
 	}