Order() in scopes not compatible with Count()

[socksy]

GORM Playground Link

go-gorm/playground#816

Description

I was trying to refactor a query into using scopes for more composability. For some reason, one of the queries that should've been identical kept failing. Turns out, if you have .Order() in a scope, then if that scope is used in a Count() query at some point, then it will fail on PostgreSQL.

This happens because the Count query adds a GROUP BY clause, and in postgres when a query has both a GROUP BY and an ORDER BY, the columns in the ORDER BY must also be in the GROUP BY.

Normally this would not be a problem, since in finsher_api.go, gorm is specifically looking for this scenario, and removes the ORDER BY clause if it can't find the columns in a GROUP BY clause. This happens here: https://github.com/go-gorm/gorm/blob/master/finisher_api.go#L487

However, this uses db.Statement.Clauses["ORDER BY"] to find the ORDER BY clause. In the case of it being in a scope, this will be nil, since it'll be under .scopes and not .Clauses.

Delve Debugger Session

(dlv) n
> gorm.io/gorm.(*DB).Count() ./gorm/finisher_api.go:487 (PC: 0x1032e10e0)
   482:			}
   483:	
   484:			tx.Statement.AddClause(clause.Select{Expression: expr})
   485:		}
   486:	
=> 487:		if orderByClause, ok := db.Statement.Clauses["ORDER BY"]; ok {
   488:			if _, ok := db.Statement.Clauses["GROUP BY"]; !ok {
   489:				delete(tx.Statement.Clauses, "ORDER BY")
   490:				defer func() {
   491:					tx.Statement.Clauses["ORDER BY"] = orderByClause
   492:				}()
(dlv) n
> gorm.io/gorm.(*DB).Count() ./gorm/finisher_api.go:496 (PC: 0x1032e13b4)
   491:					tx.Statement.Clauses["ORDER BY"] = orderByClause
   492:				}()
   493:			}
   494:		}
   495:	
=> 496:		tx.Statement.Dest = count
   497:		tx = tx.callbacks.Query().Execute(tx)
   498:	
   499:		if _, ok := db.Statement.Clauses["GROUP BY"]; ok || tx.RowsAffected != 1 {
   500:			*count = tx.RowsAffected
   501:		}
(dlv) print db.Statement.Clauses
gorm.io/gorm.(*DB).Clauses {
	db.Statement.DB *gorm.io/gorm.DB = nil
}
(dlv) print db.Statement.scopes
[]func(*gorm.io/gorm.DB) *gorm.io/gorm.DB len: 1, cap: 1, [gorm.io/playground.OrderByScope]
(dlv) print db.Statement.scopes[0]
gorm.io/playground.OrderByScope
(dlv) list db.Statement.scopes[0]
Showing /Users/ben/code/gorm-playground/main_test.go:13 (PC: 0x1036102bc)
     8:	// GORM_REPO: https://github.com/go-gorm/gorm.git
     9:	// GORM_BRANCH: master
    10:	// TEST_DRIVERS: sqlite, mysql, postgres, sqlserver
    11:	
    12:	
    13:	func OrderByScope(db *gorm.DB) *gorm.DB {
    14:		return db.Order("name desc")
    15:	}
    16:	
    17:	func TestGORM(t *testing.T) {
    18:		user := User{Name: "jinzhu"}