takezoe/gitbucket — pull request #46 by tomykaira (2013-07-21 10:41:00+00:00)
Title: Select issues satisfying all specified labels

This fixes #29.

Old sql is
```sql
select x2."USER_NAME", x2."REPOSITORY_NAME", x2."ISSUE_ID", x2."OPENED_USER_NAME", x2."MILESTONE_ID", x2."ASSIGNED_USER_NAME", x2."TITLE", x2."CONTENT", x2."CLOSED", x2."REGISTERED_DATE", x2."UPDATED_DATE" from "ISSUE" x2
where (((x2."USER_NAME" = ?) and (x2."REPOSITORY_NAME" = ?)) and (x2."CLOSED" = ?)) and
exists(select x3."USER_NAME", x3."REPOSITORY_NAME", x3."ISSUE_ID", x3."LABEL_ID" from "ISSUE_LABEL" x3
where (((x3."USER_NAME" = x2."USER_NAME") and (x3."REPOSITORY_NAME" = x2."REPOSITORY_NAME")) and (x3."ISSUE_ID" = x2."ISSUE_ID"))
and (x3."LABEL_ID" in (select x4."LABEL_ID" from "LABEL" x4 where ((x4."USER_NAME" = x2."USER_NAME") and (x4."REPOSITORY_NAME" = x2."REPOSITORY_NAME")) and (x4."LABEL_NAME" in (?, ?)))))
```

New sql is

```sql
select x2."USER_NAME", x2."REPOSITORY_NAME", x2."ISSUE_ID", x2."OPENED_USER_NAME", x2."MILESTONE_ID", x2."ASSIGNED_USER_NAME", x2."TITLE", x2."CONTENT", x2."CLOSED", x2."REGISTERED_DATE", x2."UPDATED_DATE" from "ISSUE" x2
where ((((x2."USER_NAME" = ?) and (x2."REPOSITORY_NAME" = ?)) and (x2."CLOSED" = ?)) and
exists(select x3."USER_NAME", x3."REPOSITORY_NAME", x3."ISSUE_ID", x3."LABEL_ID" from "ISSUE_LABEL" x3
where (((x3."USER_NAME" = x2."USER_NAME") and (x3."REPOSITORY_NAME" = x2."REPOSITORY_NAME")) and (x3."ISSUE_ID" = x2."ISSUE_ID")) and (x3."LABEL_ID" in (select x4."LABEL_ID" from "LABEL" x4 where ((x4."USER_NAME" = x2."USER_NAME") and (x4."REPOSITORY_NAME" = x2."REPOSITORY_NAME")) and (x4."LABEL_NAME" = 'enhancement'))))) and
exists(select x5."USER_NAME", x5."REPOSITORY_NAME", x5."ISSUE_ID", x5."LABEL_ID" from "ISSUE_LABEL" x5
where (((x5."USER_NAME" = x2."USER_NAME") and (x5."REPOSITORY_NAME" = x2."REPOSITORY_NAME")) and (x5."ISSUE_ID" = x2."ISSUE_ID")) and (x5."LABEL_ID" in (select x6."LABEL_ID" from "LABEL" x6 where ((x6."USER_NAME" = x2."USER_NAME") and (x6."REPOSITORY_NAME" = x2."REPOSITORY_NAME")) and (x6."LABEL_NAME" = 'bug'))))
```

This repeats similar select sub-queries, so slow.
Another idea is to select all labels with `INNER JOIN`, then filter-out not satisfying issues in Scala.