Incorrect SQL in reg_entry_dependents() from cregistry/entry.c

[mmpestorich (Mike M Pestorich)]

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;

​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:

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 (cregistry/entry.c Line1232​) should instead be:

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.

[jmroot (Joshua Root)]

You updated the sqlite in /usr? 10.6 only ships with 3.6.12.

Anyway, if sqlite's behaviour changed in recent versions, this will probably affect 10.7. Just need to check that your modified version still works all the way back to whatever version 10.4 has.

[mmpestorich (Mike M Pestorich)]

Woops. Sorry I wasn't clear. My MacBook is running OSX 10.6.5, MacPorts 1.9.2 and SQLite 3.6.23.1 (the Apple installed version for 10.6.5) and runs correctly without the patch above. The other machine is a Linux box that I have been using MacPorts on for a couple years now with my own portfiles. I have done a few minor things to keep it compiling and running correctly on Linux (though as of 1.9.2 not much if anything needs to be done anymore to have it successfully compile). On that Linux box I was previously running MacPorts 1.9.0 and SQLite 3.6.x using the flat file registry without any issues. Recently, I upgraded it to MacPorts 1.9.2 and SQLite 3.7.3 (and then converted the flat file registry to the newer SQLite one). After that, I ran into this problem.

I understand that most on here don't really care much about the state of MacPorts on a Linux system, but it seemed to me that in this case, this was be a bug against a newer version of SQLite and had nothing to do with BSD vs Linux incompatabilities. Anyway, thought it might be helpful.

[jmroot (Joshua Root)]

r76827