dsp vocabulary/picker does not find unpublished packages

[Curtis Hovey]

On qastaging, where the DSP vocabulary is enabled, I expect to search for charms/mysql and get an exact match when choosing a package affected by a bug. The picker says there are no matches.

The problem is obvious looking at the implementation, the SQL joins to DistributionSourcePackageCache to get rich package information, but unpublished packages will never appear in the table. This is a palm-in-face moment for me because 1. I insisted we extend DSP to do searches because official packages may never be published, yet 2, I wrote the SQL query that joins to a table predicated on publishing. The query/scoring of results should use SourcePackageName.name instead of dspc.name because the former is guaranteed to exist.

[Launchpad QA Bot (lpqabot)]

Fixed in stable r14711 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/14711>.

[Curtis Hovey (sinzui)]

The change does not work; charms/mysql has no results. The query got slower, some searches like 'ubuntu/gedit' never work on qastaging, but they reliably did yesterday. This is behind a feature flag only enabled on qastaging so I can safely work on this.

I am dismayed to see two near identical and expensive queries in OOPS-191b0f55deb4f3d3f35a25be045ce338 which I believe are caused by the CountableIterator call in the method. I wonder how the the duplication can be cached

1. 587.0 1 SQL-main-slave
WITH SearchableDSP AS
  ( SELECT dsp.id, dsps.name, dsps.binpkgnames, rank
   FROM DistributionSourcePackage dsp
   JOIN
     ( SELECT DISTINCT ON (spn.id) spn.id, spn.name, dspc.binpkgnames, CASE WHEN spn.name = u$STRING THEN $INT WHEN dspc.binpkgnames SIMILAR TO $STRING || u$STRING || $STRING THEN $INT WHEN spn.name SIMILAR TO $STRING || u$STRING || $STRING THEN $INT WHEN dspc.binpkgnames SIMILAR TO $STRING || u$STRING || $STRING THEN $INT ELSE $INT END AS rank
      FROM SourcePackageName spn
      LEFT JOIN DistributionSourcePackageCache dspc ON dspc.sourcepackagename = spn.id
      LEFT JOIN Archive a ON dspc.archive = a.id
      AND a.purpose IN ($INT, $INT)
      WHERE spn.name LIKE $STRING || u$STRING || $STRING
        OR dspc.binpkgnames LIKE $STRING || u$STRING || $STRING LIMIT $INT ) dsps ON dsp.sourcepackagename = dsps.id
   WHERE dsp.distribution = $INT
   ORDER BY rank DESC )
SELECT COUNT(*)
FROM SearchableDSP,
     DistributionSourcePackage
WHERE (DistributionSourcePackage.id = SearchableDSP.id)

2. 557.0 1 SQL-main-slave
WITH SearchableDSP AS
  ( SELECT dsp.id, dsps.name, dsps.binpkgnames, rank
   FROM DistributionSourcePackage dsp
   JOIN
     ( SELECT DISTINCT ON (spn.id) spn.id, spn.name, dspc.binpkgnames, CASE WHEN spn.name = u$STRING THEN $INT WHEN dspc.binpkgnames SIMILAR TO $STRING || u$STRING || $STRING THEN $INT WHEN spn.name SIMILAR TO $STRING || u$STRING || $STRING THEN $INT WHEN dspc.binpkgnames SIMILAR TO $STRING || u$STRING || $STRING THEN $INT ELSE $INT END AS rank
      FROM SourcePackageName spn
      LEFT JOIN DistributionSourcePackageCache dspc ON dspc.sourcepackagename = spn.id
      LEFT JOIN Archive a ON dspc.archive = a.id
      AND a.purpose IN ($INT, $INT)
      WHERE spn.name LIKE $STRING || u$STRING || $STRING
        OR dspc.binpkgnames LIKE $STRING || u$STRING || $STRING LIMIT $INT ) dsps ON dsp.sourcepackagename = dsps.id
   WHERE dsp.distribution = $INT
   ORDER BY rank DESC )
SELECT DistributionSourcePackage.bug_count,
       DistributionSourcePackage.bug_reported_acknowledgement,
       DistributionSourcePackage.bug_reporting_guidelines,
       DistributionSourcePackage.distribution,
       DistributionSourcePackage.enable_bugfiling_duplicate_search,
       DistributionSourcePackage.id,
       DistributionSourcePackage.is_upstream_link_allowed,
       DistributionSourcePackage.max_bug_heat,
       DistributionSourcePackage.po_message_count,
       DistributionSourcePackage.sourcepackagename,
       DistributionSourcePackage.total_bug_heat,
       binpkgnames
FROM SearchableDSP,
     DistributionSourcePackage
WHERE (DistributionSourcePackage.id = SearchableDSP.id)LIMIT $INT
OFFSET $INT