Bad SQL corrupts search results

Bernard emailed me, and Tim Stoddard posted in the forum about search not finding what it should find. I quickly agreed; it was wrong. Fortunately, it was a small fix.

See also my follow-up post where I fixed the broken data.

The problem

The problem was searching for ports such as bird didn’t include that port in the results. I saw no errors in the logs, so I started searching in the code.

The cause

Turning on debug dumps the SQL queries to the webpage. Here is the original query:

  SELECT count(*)
    from ports LEFT OUTER JOIN ports_vulnerable on ports_vulnerable.port_id = ports.id JOIN commit_log CL on ports.last_commit_id = CL.id JOIN repo R on CL.repo_id = R.id , categories, element  
	WHERE ports.category_id  = categories.id
      and ports.element_id   = element.id  AND      lower(element.name) = lower(E'bird')

It returns zero rows, which is correct for the query, but not what we want.

Whenever I have a problem such as this, I reduce it to simple queries, then build it back up again.

SELECT count(*)
  FROM ports, categories, element  
 WHERE ports.category_id   = categories.id
   AND ports.element_id    = element.id  
   AND lower(element.name) = lower(E'bird');

 count 
-------
     1
(1 row)

This is a simple query on three tables, looking for something named bird.

Now, let’s add in the other clauses. While here, let’s add in some table aliases to make the query shorter and easier to read:

SELECT count(*)
  FROM ports P LEFT OUTER JOIN ports_vulnerable PV on PV.port_id = P.id, categories C, element E
 WHERE P.category_id   = C.id
   AND P.element_id    = E.id  
   AND lower(E.name) = lower(E'bird');

 count 
-------
     1
(1 row)

That still works. Let’s add in another clause:

SELECT count(*)
  FROM ports P LEFT OUTER JOIN ports_vulnerable PV on PV.port_id = P.id JOIN commit_log CL on P.last_commit_id = CL.id, categories C, element E
 WHERE P.category_id   = C.id
   AND P.element_id    = E.id  
   AND lower(E.name) = lower(E'bird');
 count 
-------
     0
(1 row)

There’s our problem. What is that last_commit_id value:

 SELECT P.last_commit_id
  FROM ports P LEFT OUTER JOIN ports_vulnerable PV on PV.port_id = P.id, categories C, element E
 WHERE P.category_id   = C.id
   AND P.element_id    = E.id  
   AND lower(E.name) = lower(E'bird');
 last_commit_id 
----------------
         504097
(1 row)

That is the entry which does not exist in the commit_log table, as demonstrated by this query:

# select * from commit_log where id = 504097;
 id | message_id | message_date | message_subject | date_added | commit_date | committer | description | system_id | encoding_losses | svn_revision | repo_id 
----+------------+--------------+-----------------+------------+-------------+-----------+-------------+-----------+-----------------+--------------+---------
(0 rows)

I think I know how this occurred. That field is updated each time a commit occurs which involves that port. That commit was manually deleted, and then manually processed again. There is no relational integrity to correct that field should the commit be deleted.

The commit was this one, which had a huge number entries under Directory Properties:. I have since changed the code. I did not write about that issue, but I did post about it in the forum. To rerun the commit, I deleted the commit via SQL and then re-injected the commit email into the processing queue.

Some fixes

To cater for this situation, should it arise again, I’m going to try a LEFT OUTER JOIN, and expand the query back to the original clauses:

SELECT count(*)
  FROM ports P LEFT OUTER JOIN ports_vulnerable PV ON PV.port_id       = P.id 
               LEFT OUTER JOIN commit_log       CL ON P.last_commit_id = CL.id 
               LEFT OUTER JOIN repo             R  ON CL.repo_id       = R.id, 
       categories C, element E
 WHERE P.category_id   = C.id
   AND P.element_id    = E.id  
   AND lower(E.name) = lower(E'bird');
 count 
-------
     1
(1 row)

That’s better. While here, I aliased all the tables. I think I’ve corrected the search problem. In another post, I’ll fix the underlying relational integrity issue. That fix will include: