/repos/issue/search API is broken

[davydov-vyacheslav]

I'm trying to get list of all open PR within the system via API. When I'm trying to execute /repos/issue/search API request (even without search parameters, even without authentication -- just curl -X GET "https://try.gitea.io/api/v1/repos/issues/search") I'm retrieving the following error: Find: too many SQL variables

• Gitea version: 1.12.0+dev-3-g9269b7f62
• full request:curl -X GET "https://try.gitea.io/api/v1/repos/issues/search?state=open&type=pulls" -H "accept: application/json" -H "authorization: Basic ZGF2c0BqYXZhbml4LmNvbTpkYXZ5ZG92dnY="

P.S. I have local system of 1.11.2 version and don't have such issue.

[jolheiser]

Underlying SQL problem is related to #7750 and #9841

[stale]

This issue has been automatically marked as stale because it has not had recent activity. I am here to help clear issues left open even if solved or waiting for more insight. This issue will be closed if no further activity occurs during the next 2 weeks. If the issue is still valid just add a comment to keep it alive. Thank you for your contributions.

[davydov-vyacheslav]

unstale please

[6543]

Does it still happen?

[a1012112796]

Does it still happen?

[zeripath]

The problem is this:

	for page := 1; ; page++ {
		opts.Page = page
		repos, count, err := models.SearchRepositoryByName(opts)
		if err != nil {
			ctx.Error(http.StatusInternalServerError, "SearchRepositoryByName", err)
			return
		}

		if len(repos) == 0 {
			break
		}
		log.Trace("Processing next %d repos of %d", len(repos), count)
		for _, repo := range repos {
			switch isClosed {
			case util.OptionalBoolTrue:
				issueCount += repo.NumClosedIssues
			case util.OptionalBoolFalse:
				issueCount += repo.NumOpenIssues
			case util.OptionalBoolNone:
				issueCount += repo.NumIssues
			}
			repoIDs = append(repoIDs, repo.ID)
		}
	}

This extracts all of the repo IDs that the search options could have found - I.e. Every repo that the user has access to... (Even worse than that it actually extracts all the repos out of the db.)

then:

	if len(keyword) > 0 && len(repoIDs) > 0 {
		issueIDs, err = issue_indexer.SearchIssuesByKeyword(repoIDs, keyword)
	}

plugs them all back in to a single SQL query.

On a big site like try there are likely easily >900 repos that a user could see - SQLite limits the number of variables to 999.

We shouldn't be doing it this way

[6543]

@zeripath we could replace repoIDs with a xorm.builder query witch we can join within SearchIssuesByKeyword as we have already done elsewhere

[zeripath]

Exactly

[6543]

are you going for it?

[zeripath]

Or should I say: genau

[zeripath]

Nope I've played enough with reposearch recently - if I touch it again 🔥 may ensue

[6543]

haha - OK I'll give it a try

[zeripath]

Remember whatever you do it needs a backport...

[6543]

😓

[6543]

ok the sittuation is worse than i thought

[zeripath]

Now you see why I only commented and didn't fix ...

[6543]

@zeripath we could replace repoIDs with a xorm.builder query witch we can join within SearchIssuesByKeyword as we have already done elsewhere

this is not posible as it use the Indexer and the whole Indexer would need arewrite/refactory
so #11664 split In query up into multible one combinde by OR