[mssql] Pull Request Database Issue

[Cellebyte]

• Gitea version (or commit ref): 20c54f8
• Git version: Docker Container
• Operating system: Docker Container
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant
• Log gist:

Description

[...outers/repo/issue.go:835 ViewIssue()] [E] GetReviewersByPullID: mssql: Column 'user.lower_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Create a pull request.
If i want to look at my pull requests i get the following issue 500 ServerError.
1.6.0 does not have this issue.

[Cellebyte]

This bug occurs in the current master branch.

[chdxD1]

I have written the following query which works in SQL Server Management Studio. It uses a subquery and seems to return all results needed for the GetReviewersByPullID function to work. However I do not know how to write subqueries with xorm. I do not know whether this query works in MySQL or PostgreSQL.

SELECT [user].*, review.type, review.review_updated_unix FROM (SELECT review.id, review.type, review.reviewer_id, max(review.updated_unix) as review_updated_unix FROM review WHERE review.issue_id=4 AND (review.type = 1 OR review.type = 3) GROUP BY review.id, review.type, review.reviewer_id) as review INNER JOIN [user] ON review.reviewer_id = [user].id ORDER BY review_updated_unix DESC;

[lunny]

@chdxD1 You can do that using xorm

if db == mssql {
x.SQL(sql).Find(&reviews)
}

Could you send a PR?

[Cellebyte]

@lunny , @chdxD1 and me tried to implement it with the xorm.builder because it was looking like to have subquery functionality, but the version used by gitea is not the current one. So we have give up after a short time.

if db == mssql { x.SQL(sql).Find(&reviews) }

@lunny can you give an example how the sql variable should look like?

[lunny]

@Cellebyte @chdxD1 please confirm #5502 fixed this.