GetUserFollowers generates wrong sql on MS-SQL #12072

New Issue

GiteaMirror · 2025-11-02T09:56:51-06:00

GiteaMirror commented

2025-11-02 09:56:51 -06:00

Originally created by @philippbeckmann on GitHub (Nov 23, 2023).

Description

A malformed SQL query is produced when calling GetUserFollowers from the users activity page (when switching to a page no > 1) on an installation with a MS-SQL server.

The query looks like this:
SELECT TOP 20 [user].* FROM [user] LEFT JOIN [follow] ON [user].id = follow.user_id WHERE ( follow.follow_id = 1 ) AND ( [user].type = 0 ) AND ( user.id NOT IN (SELECT TOP 20 user.id FROM [user] LEFT JOIN [follow] ON [user].id = follow.user_id WHERE ( follow.follow_id = 1 ) AND ( [user].type = 0 )) )

Please note the missing square brackets on the table name 'user', which collides with the integrated function "USER()".

Gitea Version

1.20.5

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

https://gist.github.com/philippbeckmann/edd06e884d1e9147d5d0f90be508abf4

Screenshots

No response

Git Version

2.40.1.windows.1

Operating System

Windows Server 2019

How are you running Gitea?

Official .exe installed as a windows service.

Database

MSSQL

Originally created by @philippbeckmann on GitHub (Nov 23, 2023). ### Description A malformed SQL query is produced when calling GetUserFollowers from the users activity page (when switching to a page no > 1) on an installation with a MS-SQL server. The query looks like this: `SELECT TOP 20 [user].* FROM [user] LEFT JOIN [follow] ON [user].id = follow.user_id WHERE ( follow.follow_id = 1 ) AND ( [user].type = 0 ) AND ( user.id NOT IN (SELECT TOP 20 user.id FROM [user] LEFT JOIN [follow] ON [user].id = follow.user_id WHERE ( follow.follow_id = 1 ) AND ( [user].type = 0 )) ) ` Please note the missing square brackets on the table name 'user', which collides with the integrated function "USER()". ### Gitea Version 1.20.5 ### Can you reproduce the bug on the Gitea demo site? No ### Log Gist https://gist.github.com/philippbeckmann/edd06e884d1e9147d5d0f90be508abf4 ### Screenshots _No response_ ### Git Version 2.40.1.windows.1 ### Operating System Windows Server 2019 ### How are you running Gitea? Official .exe installed as a windows service. ### Database MSSQL

GiteaMirror added the type/bug label 2025-11-02 09:56:51 -06:00

GiteaMirror closed this issue

2025-11-02 09:56:51 -06:00

GiteaMirror commented

2025-11-02 09:56:52 -06:00

@delvh commented on GitHub (Nov 23, 2023):

I think this has been fixed in 1.21.0:
a2314ca9c5/models/user/user.go (L1209)
sounds like the responsible line, and I can see the escape here.

@delvh commented on GitHub (Nov 23, 2023): I think this has been fixed in 1.21.0: https://github.com/go-gitea/gitea/blob/a2314ca9c5e17db140e66c7031cbfb97e04e41e5/models/user/user.go#L1209 sounds like the responsible line, and I can see the escape here.

GiteaMirror commented

2025-11-02 09:56:52 -06:00

@delvh commented on GitHub (Nov 23, 2023):

Although it is interesting that this code snippet hasn't been touched in a year and has been that way since 1.18…
Weird, what's happening here?

@delvh commented on GitHub (Nov 23, 2023): Although it is interesting that this code snippet hasn't been touched in a year and has been that way since 1.18… Weird, what's happening here?

GiteaMirror commented

2025-11-02 09:56:52 -06:00

@wxiaoguang commented on GitHub (Nov 23, 2023):

Maybe it's caused by a XORM bug.

@wxiaoguang commented on GitHub (Nov 23, 2023): Maybe it's caused by a XORM bug.

GiteaMirror commented

2025-11-02 09:56:52 -06:00

@lng2020 commented on GitHub (Nov 23, 2023):

That's weird. Where does the not in come from?

@lng2020 commented on GitHub (Nov 23, 2023): That's weird. Where does the `not in` come from?

GiteaMirror commented

2025-11-02 09:56:53 -06:00

@yp05327 commented on GitHub (Nov 24, 2023):

This SQL is so strange.

These two queries are almost same, and will not get any results.
As you want to find users whose
follow.follow_id=1 and user.type=0
but the user id should not equal users whose
follow.follow_id=1 and user.type=0

@yp05327 commented on GitHub (Nov 24, 2023): This SQL is so strange. ![image](https://github.com/go-gitea/gitea/assets/18380374/f311a112-6ff5-4ecf-9270-1a63ccf80bc4) These two queries are almost same, and will not get any results. As you want to find users whose `follow.follow_id=1 and user.type=0` but the user id should not equal users whose `follow.follow_id=1 and user.type=0`

GiteaMirror commented

2025-11-02 09:56:53 -06:00

@delvh commented on GitHub (Nov 24, 2023):

So, if I see that correctly:
Somehow, you managed to confuse everyone… 😁

@delvh commented on GitHub (Nov 24, 2023): So, if I see that correctly: Somehow, you managed to confuse **everyone**… :grin:

GiteaMirror commented

2025-11-02 09:56:53 -06:00

@KazzmanK commented on GitHub (Nov 25, 2023):

There is a chance that TOP 20 will produce different rows for subquery and main query and it may produce some data.
In case of TOP N query should have ORDER BY clause

@KazzmanK commented on GitHub (Nov 25, 2023): There is a chance that `TOP 20` will produce different rows for subquery and main query and it _may_ produce some data. In case of `TOP N` query should have `ORDER BY` clause

GiteaMirror commented

2025-11-02 09:56:53 -06:00

@philippbeckmann commented on GitHub (Feb 1, 2024):

We're now on 1.21.4 and the issue disappeared. So this can be closed.

@philippbeckmann commented on GitHub (Feb 1, 2024): We're now on 1.21.4 and the issue disappeared. So this can be closed.

GiteaMirror commented

2025-11-02 09:56:54 -06:00

@github-actions[bot] commented on GitHub (Feb 29, 2024):

Automatically locked because of our CONTRIBUTING guidelines

@github-actions[bot] commented on GitHub (Feb 29, 2024): Automatically locked because of our [CONTRIBUTING guidelines](https://github.com/go-gitea/gitea/blob/main/CONTRIBUTING.md#issue-locking)

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#12072