#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