CountIssues: pq: invalid input syntax for type smallint: "true"

[zenofile]

Description

After migrating from MariaDB to PostgreSQL using gitea dump -d postgres, Gitea produces a status code 500 upon visiting https://example.org/notifications/subscriptions.

The error reported in the log is:

...user/notification.go:256:NotificationSubscriptions() [I] [63485399] [SQL] SELECT COUNT(issue.id) AS count FROM "issue" INNER JOIN "repository" ON "issue".repo_id = "repository".id WHERE issue.id NOT IN (SELECT issue_id FROM issue_watch WHERE is_watching=$1 AND user_id=$2) AND (issue.id IN (SELECT issue_id FROM issue_watch WHERE is_watching=$3 AND user_id=$4) OR issue.id IN (SELECT issue_id FROM comment WHERE poster_id=$5) OR issue.poster_id=$6 OR issue.repo_id IN (SELECT id FROM watch WHERE mode=$7 AND user_id=$8)) [false 1 true 1 1 1 true 1] - 577.586µs
...user/notification.go:263:NotificationSubscriptions() [E] [63485399] CountIssues: pq: invalid input syntax for type smallint: "true"

Manually issuing the query produces:

 gitea=>  SELECT COUNT(issue.id) AS count FROM "issue" INNER JOIN "repository" ON "issue".repo_id = "repository".id WHERE issue.id NOT IN (SELECT issue_id FROM issue_watch WHERE is_watching=false AND user_id=1) AND (issue.id IN (SELECT issue_id FROM issue_watch WHERE is_watching=true AND user_id=1) OR issue.id IN (SELECT issue_id FROM comment WHERE poster_id=1) OR issue.poster_id=1 OR issue.repo_id IN (SELECT id FROM watch WHERE mode=true AND user_id=1));
ERROR:  operator does not exist: smallint = boolean
LINE 1: ... issue.repo_id IN (SELECT id FROM watch WHERE mode=true AND ...
                                                             ^

This seems to be the only issue I encountered after migration.

Gitea Version

1.18.0+dev-548-g6a6dc97b0

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

https://gist.github.com/zenofile/6cdef4553a55bcbebff6135a31511a6d

Screenshots

Git Version

2.36.2

Operating System

Fedora Linux 37

How are you running Gitea?

As a podman (docker) container, migrated from MariaDB to PostgreSQL.

Database

PostgreSQL

[lafriks]

You need to change smallint columns to bool

[zenofile]

You need to change smallint columns to bool

I understand that postgres does use boolean values instead of smallints, but which smallint columns are you referring to?

Taking a look at

gitea/models/repo/watch.go

Lines 19 to 38 in fd7d83a

	const (
	// WatchModeNone don't watch
	WatchModeNone WatchMode = iota // 0
	// WatchModeNormal watch repository (from other sources)
	WatchModeNormal // 1
	// WatchModeDont explicit don't auto-watch
	WatchModeDont // 2
	// WatchModeAuto watch repository (from AutoWatchOnChanges)
	WatchModeAuto // 3
	)
	// Watch is connection request for receiving repository notification.
	type Watch struct {
	ID int64 `xorm:"pk autoincr"`
	UserID int64 `xorm:"UNIQUE(watch)"`
	RepoID int64 `xorm:"UNIQUE(watch)"`
	Mode WatchMode `xorm:"SMALLINT NOT NULL DEFAULT 1"`
	CreatedUnix timeutil.TimeStamp `xorm:"INDEX created"`
	UpdatedUnix timeutil.TimeStamp `xorm:"INDEX updated"`
	}

mode does not seem to be intended as a boolean value like the query suggests.

[lafriks]

sorry you are right. It's actually bug in this line:
https://github.com/go-gitea/gitea/blob/main/models/issues/issue.go#L1495

[lafriks]

Shouldn't filter only 1 and 3? Not sure tho if 2 means to not watch? 🤔