Ok, first-look patch for the discussion at http://comments.gmane.org/gmane.comp.python.sqlobject/7819
This was originally entered in Trac but it is currently down - this SF patch should supersede Trac #291.
This also is an alternate resolution to SF Patch 1432078, "Add DISTINCT to sqlbuilder.py Select".
This includes changes to Aliases and SQLJoins to properly handle sqlrepr'ing Alias(Select()) instances (and more generally, use sqlrepr to construct the join sqlrepr). But see item #2 below.
All tests pass for me on postgres except where noted in the diff.
This patch is probably NOT ready to be applied for the following hack-ish reasons:
1. sqlbuilder.Select LIMIT/OFFSET for db-specific formats - this was previously implemented in dbconnection-specific method "_queryAddLimitOffset" (only postgres and mysql override this today), should probably instead become a SQLExpression with a custom converter that implements these differences.
2. There's a fundamental issue with the current sqlbuilder.SQLExpression, where in several instances (AliasField.tablesUsedImmediate for instance) objects that may be SQLExpression instances are indiscriminately converted to strings, ie "%s %s %s % (self.tableName self.as_string, self.alias) when self.tableName is actually a Select instance. SQLExpression.__str__ calls .__sqlrepr__(None), which means that earlier than necessary we've lost the ability to format queries for the db they eventually run on (in the exposing case for me, I have Select(... clause=X.q.boolCol==True) and True is not properly formatted for the db.
My recommendation for fixing #2 is to change these str-casts to sqlrepr(x, db), which will require tablesUsedImmediate accepting a db argument. This patch takes a lazier approach that lets me move forward, creating a module-level variable to override the None used in the __str__/__repr__ call mentioned above.