I couldn't find a project with multiple subprojects on this install otherwise I would have linked you to an example

fix is one line long :3

haha nice. I just posted this comment on our downstream task tracking this.

For context, we issue a query that looks like this:

SELECT * FROM task JOIN edge ...

If we know that each task has zero or one corresponding rows in the edge table, we do not need a GROUP BY clause.

However, if some tasks have two or more corresponding rows in the edge table, we might get results back like this:

| T123 | Foo | Edge AAA |
| T124 | Bar | Edge BBB |
| T124 | Bar | Edge CCC |

Here, T124 relates to both edges BBB and CCC, so we get two rows back.

When this may happen we add a GROUP BY task.phid clause to the query.

Normally, queries like this don't require a GROUP BY:

SELECT * FROM task JOIN edge ON t.phid = e.src AND e.type = X AND e.dst = Y

But queries like this do:

SELECT * FROM task JOIN edge ON t.phid = e.src AND e.type = X AND e.dst IN (Y, Z)

We incorrectly considered queries using the "Ancestor Operator" to be part of the first group; they are part of the second group. Since they were categorized wrong, we didn't include GROUP BY and got duplicate tasks back.

Later, this cascaded into confusion at the application level after MySQL said "there are 5 results" but the Query only returned 4 unique results.

We could do things like:

• Always add GROUP BY (but: this might be bad luck).
• Throw if raw results and unique results differ (but: I think this isn't always an error, strictly, and there's no formal/general method for identifying unique results, and no formal/general place where they get reduced to only unique results).

So D18012 is not the most general fix we might imagine, but the more categorical design errors here rarely have much impact and I think everyone's probably happier here with a one-line-fix than "rebuild all queries in all applications to understand and detect result uniqueness errors".

And thanks for doing the legwork to pull a reproduction case out of this.