758057 - API search shouldn't return add-ons with compat overrides when compat mode is set to "normal"

Status: RESOLVED FIXED

(addons.mozilla.org Graveyard :: API, defect)

Description

[krupa raj[:krupa]]

Attachment: screenshot

steps to reproduce:
1. Load https://addons-dev.allizom.org/en-US/firefox/api/1.5/search/quick%20trans/all/30/Darwin/15.0a1/normal?src=firefox
2. Notice that 'Quick Translator' is one of the search results

expected behavior:
API search doesn't return add-ons with compat overrides when compat mode is set to "normal".

observed behavior:
As per https://addons-dev.allizom.org/en-US/firefox/addon/quick-translator/?src=search, this add-on has compat override set up. Still it is listed in API search results.

Comment 1

[Rob Hudson [:robhudson]]

The problem is shown in the following 2 SQL queries. 

mysql> select * from incompatible_versions where version_id in (SELECT id FROM versions WHERE addon_id=58606);
+----+---------------------+---------------------+------------+--------+-----------------+-----------------+---------------------+---------------------+
| id | created             | modified            | version_id | app_id | min_app_version | max_app_version | min_app_version_int | max_app_version_int |
+----+---------------------+---------------------+------------+--------+-----------------+-----------------+---------------------+---------------------+
| 65 | 2012-05-25 15:53:19 | 2012-05-25 15:53:19 |    1245718 |      1 | 10.0a1          | *               |      10000000001100 |      99000000200100 |
+----+---------------------+---------------------+------------+--------+-----------------+-----------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select id,version from versions where addon_id=58606;
+---------+---------+
| id      | version |
+---------+---------+
|   90536 | 0.2.0.1 |
|   91326 | 0.2.1   |
|   95023 | 0.3     |
|   99825 | 0.3.1   |
|  107966 | 0.4     |
|  108521 | 0.4.5   |
|  115110 | 0.5     |
|  116614 | 0.6b2   |
|  116706 | 0.6b3   |
|  116743 | 0.6     |
|  117356 | 0.7b2   |
|  117465 | 0.7     |
|  117538 | 0.7.1   |
|  117610 | 0.7.2   |
|  117758 | 0.7.3   |
|  122852 | 0.7.5   |
|  124792 | 0.8     |
|  124943 | 0.8.1   |
| 1245718 | 0.9     |
| 1326023 | 1.0     |
+---------+---------+
20 rows in set (0.00 sec)


`incompatible_versions` is my denormalized table of what's in the compat override tables and is used in a JOIN elsewhere. You can't JOIN on a range like "0 - 0.9" so this was my solution.

It seems that incompatible_versions isn't getting populated correctly.

So I'll look at why that may be. I did create a management command to rebuild this table but running it still didn't populate it for this addon correctly.

Comment 2

[Rob Hudson [:robhudson]]

And if I had selected one more column I'd have seen the problem right away...

mysql> select id,version,version_int from versions where addon_id=58606;
+---------+---------+---------------+
| id      | version | version_int   |
+---------+---------+---------------+
|   90536 | 0.2.0.1 |          NULL |
|   91326 | 0.2.1   |          NULL |
|   95023 | 0.3     |          NULL |
|   99825 | 0.3.1   |          NULL |
|  107966 | 0.4     |          NULL |
|  108521 | 0.4.5   |          NULL |
|  115110 | 0.5     |          NULL |
|  116614 | 0.6b2   |          NULL |
|  116706 | 0.6b3   |          NULL |
|  116743 | 0.6     |          NULL |
|  117356 | 0.7b2   |          NULL |
|  117465 | 0.7     |          NULL |
|  117538 | 0.7.1   |          NULL |
|  117610 | 0.7.2   |          NULL |
|  117758 | 0.7.3   |          NULL |
|  122852 | 0.7.5   |          NULL |
|  124792 | 0.8     |          NULL |
|  124943 | 0.8.1   |          NULL |
| 1245718 | 0.9     |   90000200100 |
| 1326023 | 1.0     | 1000000200100 |
+---------+---------+---------------+
20 rows in set (0.00 sec)

I'm using version_int for the version comparison, but it's NULL for many version which means it wouldn't be a range match if 0 <= NULL <= 0.9.

Comment 3

[Rob Hudson [:robhudson]]

https://github.com/mozilla/zamboni/commit/3ae43a6

This switches from trying to use add-on versions for range comparisons, to using version IDs for the range.

Note: For compat overrides to work, they will need to exist in the database, unless you use "0" for the min value, and "*" for the max value.