[MacPorts] #27506: Incorrect SQL in reg_entry_dependents() from cregistry/entry.c

[MacPorts]

#27506: Incorrect SQL in reg_entry_dependents() from cregistry/entry.c
-------------------------------------+--------------------------------------
 Reporter:  mmpestorich@…            |       Owner:  macports-tickets@…                   
     Type:  defect                   |      Status:  new                                  
 Priority:  Normal                   |   Milestone:                                       
Component:  base                     |     Version:  1.9.2                                
 Keywords:  sqlite entry dependents  |        Port:                                       
-------------------------------------+--------------------------------------
 I recently noticed a problem when running {{{port dependents ...some
 port...}}} in that it would return only the name of the port queired
 instead of a list of its dependents. (i.e. {{{port dependents openssl}}}
 would show {{{openssl depends on openssl}}} instead of the expected
 {{{openssh depends on openssl}}} ...). The actual dependencies however
 were still being calculated correctly. {{{port uninstall ...some
 port...}}} would still complain about dependent ports being installed, but
 as in the case above would display lines of dependent ports consisting
 only the current port's name and not it's dependents.

 I had recently upgraded my SQLite version to 3.7.3 and upgraded from
 macports flat file registry to the newer sqlite registry. This made me
 think that there might the issue with how information is being pulled from
 the database. I found this;

 [http://www.sqlite.org/lang_select.html#fromclause SQLite Documentation]
 states:
 > ''For each pair of columns identified by a USING clause, the column from
 the right-hand dataset is omitted from the joined dataset. This is the
 only difference between a USING clause and its equivalent ON constraint.''

 Currently, the SQL statement in reg_entry_dependents is:
 {{{
 #!sql
 SELECT dependent.id FROM ports port
 INNER JOIN dependencies USING(name) INNER JOIN ports dependent
 USING(id) WHERE port.id=%lld ORDER BY dependent.name,
 dependent.epoch, dependent.version, dependent.revision,
 dependent.variants
 }}}

 When I ran the above statement in the sqlite3 command line utility I did
 infact get back the same incorrect result that the port command was giving
 me. When I replaced the USING clauses with ON clauses, I received the
 correct expected result. I then read the snippet of docmentation above and
 tried the modified query below. It worked as well.

 Based on that SQLite documentation and my testing, I believe
 ([source:trunk/base/src/cregistry/entry.c#L1232 cregistry/entry.c
 Line1232]) should instead be:
 {{{
 #!sql
 SELECT dependencies.id FROM ports port
 }}}

 Side Note: I have a couple of computers and this was only an issue on one
 of them. The only difference that I could tell between the two
 installations was the version of SQLite. The computer with SQLite version
 3.6.23.1 worked properly while the computer that had version 3.7.3 did
 not. MacPorts 1.9.2 is installed on both of them.

 I have attached my patch that fixed this issue for me.

-- 
Ticket URL: <https://trac.macports.org/ticket/27506>
MacPorts <http://www.macports.org/>
Ports system for Mac OS