In T195796, we discovered that the ORES extension currently stores scores for three of the four draftquality classes ('spam', 'vandalism', 'ok' -- but not 'attack'). The queries to derive the 'attack' score from the other three would be prohibitively unperformant. This task is about making it so that the 'attack' score is also stored in the mediawiki database.

Here is the conversation from T195796:

In T195796#4408315, @SBisson wrote:

Filtering on draftquality may not be as straightforward as we'd like, especially for attacks (class 0).

The ORES extension stores 3 lines for every revision scores on draftquality. It ignores class 0 explicitly. Its value can be deducted from the other lines by substracting the sum of their probabilities from 1 (1 - ( p(OK) + p(SPAM) + p(VANDALISM) ) ). If none of the other class has oresc_is_predicted === 1 then class 0 logically has it.

Here is an example of a revision that is probably an attack.

mysql:research@s3-analytics-slave [enwiki]> select * from ores_classification where oresc_model=33 and oresc_rev=845715298;
+-----------+-----------+-------------+-------------+-------------------+--------------------+
| oresc_id  | oresc_rev | oresc_model | oresc_class | oresc_probability | oresc_is_predicted |
+-----------+-----------+-------------+-------------+-------------------+--------------------+
| 232150135 | 845715298 |          33 |           1 |             0.060 |                  0 |
| 232150136 | 845715298 |          33 |           2 |             0.258 |                  0 |
| 232150137 | 845715298 |          33 |           3 |             0.312 |                  0 |
+-----------+-----------+-------------+-------------+-------------------+--------------------+
3 rows in set (0.00 sec)

Revisions that are attacks can be found with a query like this:

mysql:research@s3-analytics-slave [enwiki]> select oresc_rev, count(oresc_is_predicted) as c, sum(oresc_is_predicted) as s from ores_classification where oresc_model=33 group by oresc_rev having c=3 and s=0 limit 4;
+-----------+---+------+
| oresc_rev | c | s    |
+-----------+---+------+
| 819044721 | 3 |    0 |
| 819086300 | 3 |    0 |
| 819201556 | 3 |    0 |
| 819241003 | 3 |    0 |
+-----------+---+------+
4 rows in set (3.58 sec)

Storing class 0 for draftquality would take up more space but make filtering much easier.

In T195796#4410122, @Catrope wrote:

That sum query is probably not gonna be great performance-wise. You could also do this with something like LEFT JOIN ores_classification ON oresc_model=33 AND oresc_rev=rev_id AND oresc_is_predicted=1 WHERE oresc_probability IS NULL, it's possible that that would be more performant, but it would also flag all unscored revisions as attacks.

I agree that we should ask the ORES team to store class 0 for draftquality.