Subselect error with builder.Dialect #2284

New Issue

KN4CK3R · 2023-07-03T15:30:15Z

KN4CK3R commented

2023-07-03 15:30:15 +00:00

Not really sure if this error does only occur with subselects but that's how I found it.

inner := builder.Select("*").From("foo").Where(builder.Eq{"col1": 1})

result := make([]*Foo, 0, 10)
e.Table("bar").Where(builder.Eq{"col2": 2}).Join("INNER", inner, "foo.key = bar.key").Find(&result)

results in the following working query
SELECT * FROM "bar" INNER JOIN (SELECT * FROM foo WHERE col1=$1) "foo" ON foo.key = bar.key WHERE col2=$2 [1 2]
As you can see there are parameters $1 and $2.

If you change inner to

inner := builder.Dialect(builder.POSTGRES).Select("*").From("foo").Where(builder.Eq{"col1": 1})

the generated query is
SELECT * FROM "bar" INNER JOIN (SELECT * FROM foo WHERE col1=$1) "foo" ON foo.key = bar.key WHERE col2=$1 [1 2]
Now both parameters are named $1 and the query fails because there are more provided than needed parameters.

Not really sure if this error does only occur with subselects but that's how I found it. ```go inner := builder.Select("*").From("foo").Where(builder.Eq{"col1": 1}) result := make([]*Foo, 0, 10) e.Table("bar").Where(builder.Eq{"col2": 2}).Join("INNER", inner, "foo.key = bar.key").Find(&result) ``` results in the following working query `SELECT * FROM "bar" INNER JOIN (SELECT * FROM foo WHERE col1=$1) "foo" ON foo.key = bar.key WHERE col2=$2 [1 2]` As you can see there are parameters `$1` and `$2`. If you change `inner` to ```go inner := builder.Dialect(builder.POSTGRES).Select("*").From("foo").Where(builder.Eq{"col1": 1}) ``` the generated query is `SELECT * FROM "bar" INNER JOIN (SELECT * FROM foo WHERE col1=$1) "foo" ON foo.key = bar.key WHERE col2=$1 [1 2]` Now both parameters are named `$1` and the query fails because there are more provided than needed parameters.

lunny added the

kind

bug

label 2023-07-03 15:53:31 +00:00

lunny commented

2023-07-04 05:07:03 +00:00

Is that .Dialect(builder.POSTGRES) necessary for you?

Is that `.Dialect(builder.POSTGRES)` necessary for you?

lunny referenced a pull request that will close this issue

2023-07-04 05:12:23 +00:00

Fix builder bug #2285

KN4CK3R commented

2023-07-04 09:31:41 +00:00

I noticed that problem in https://github.com/go-gitea/gitea/pull/25613 The relevant code is https://github.com/go-gitea/gitea/pull/25613/files#diff-682f1dccd138b66040eb9312ba2052783bbf970677bdf408f6e890231568ac68R40-R54

The use of LIMIT in the inner query requires to specify the dialect which causes the problems.

I noticed that problem in https://github.com/go-gitea/gitea/pull/25613 The relevant code is https://github.com/go-gitea/gitea/pull/25613/files#diff-682f1dccd138b66040eb9312ba2052783bbf970677bdf408f6e890231568ac68R40-R54 The use of `LIMIT` in the inner query requires to specify the dialect which causes the problems.

lunny referenced a pull request that will close this issue

2023-07-11 05:21:28 +00:00

Fix join problem #2291

lunny closed this issue

2023-07-12 02:02:00 +00:00

lunny referenced this issue from a commit

2023-07-12 02:02:05 +00:00

Fix join problem (#2291)

Sign in to join this conversation.