Re: [OSSTEST PATCH 14/14] duration_estimator: Move duration query loop into database

[George Dunlap <George.Dunlap@xxxxxxxxxx>]

> On Jul 31, 2020, at 11:39 AM, Ian Jackson <ian.jackson@xxxxxxxxxx> wrote:
> 
> George Dunlap writes ("Re: [OSSTEST PATCH 14/14] duration_estimator: Move 
> duration query loop into database"):
>>> On Jul 21, 2020, at 7:42 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
> ...
>>> Example queries before (from the debugging output):
>>> 
>>> Query A part I:
>>> 
>>>           SELECT f.flight AS flight,
>>>                  j.job AS job,
>>>                  f.started AS started,
>>>                  j.status AS status
>>>                    FROM flights f
>>>                    JOIN jobs j USING (flight)
>>>                    JOIN runvars r
>>>                            ON  f.flight=r.flight
>>>                           AND  r.name=?
>>>                   WHERE  j.job=r.job
>> 
>> Did these last two get mixed up?  My limited experience w/ JOIN ON
>> and WHERE would lead me to expect we’re joining on
>> `f.flight=r.flight and r.job = j.job`, and having `r.name = ?` as
>> part of the WHERE clause.  I see it’s the same in the combined query
>> as well.
> 
> Well spotted.  However, actually, this makes no difference: with an
> inner join, ON clauses are the same as WHERE clauses.  It does seem
> stylistically poor though, so I will add a commit to change it.

Yeah, in my tiny amount of experience with SQLite, putting this sort of 
restriction in WHERE rather than ON didn’t seem to make a practical difference; 
no doubt the query planner is smart enough to DTRT.  But switching them should 
make it slightly easier for humans to parse, so is probably worth doing while 
you’re here, if you have a few spare cycles.

Thanks,
 -George