Order by duplicated in some selects (only for MSSQL:adodbapi)

[Former user]

The problem doesn't occurs using pymsqldb, but, pymssqldb has many problems with unicode :( When Lazy=True the problem doesn't occurs too !

[Former user]

The testcase

[Former user]

Monkey Patch for fix the syntoms (doesn't fix the problem)

[Former user]

(original author: ram) Can you please simplify this example to use only the SQL layer?

It's a little unclear what is going on with objects that are loading other objects, backrefs, etc.

[Former user]

Sorry, I can't simplify it, 'cause it's occurs only through ORM.

Maybe the correct component for this ticket was ORM. :(

[Mike Bayer]

its a pretty simple mapping example, just a single many-to-many relationship, so this is definitely an MS-SQL issue. to simplify you might try constructing a SELECT that looks like the SELECT which the ORM is constructing...use "order_by=table.default_order_by()" to simulate the usage of the "oid" column, which id guess is where the dialect is getting confused (it would also imply that adding an explicit order_by might fix the symptom).

[paj]

This problem isn't particularly MSSQL specific. SQLAlchemy generates the duplicate ORDER BY column for all databases. It's just MSSQL is the only database that complains about it.

I will have a look through the code to see if I can find what's doing this. Could be fun!

[paj]

Ok, I understand this somewhat better now. We have T1, T2 with a many-many relationship, which is lazy T1 -> T2 and eager T2 -> T1. The problem occurs when a lazy relationship is loaded. The lazy loader adds its default "order by", which is the default for the join table. The eager loader then adds its default, which is the same. So, we end up with a duplicate column in "order by" and MSSQL doesn't like this.

An interesting problem. Both the loaders are doing the right thing. If the eager relationship was to a different table, we would expect both loaders to add the order by. And as I understand the architecture, each loader doesn't directly know the other loaders involved in the query.

My feeling is not to touch the loaders, but instead to make other code drop the duplicate "order by"s. One option is to modify _SelectBaseMixin.order_by; another would be to do this in MSSQL specific code.

[paj]

Suggested patch

[paj]

I have just attached a patch that fixes this for MSSQL. It also fixes another MSSQL ORDER BY oddity - that ORDER BY isn't allowed in subqueries, unless there is a LIMIT.

[Mike Bayer]

no, the lazy loader is broken in this case. we shouldnt have to patch MS-SQL for this kind of thing.

[Mike Bayer]

changeset:2410 fixes the ORM side so that the particular "aliasing" of the secondary table does not occur, and it does not get merged with the eager loader's order by condition.

However I see that you also have an extra bit of logic in your patch dealing with order by inside of a subquery, you might want to preserve that logic somewhere (possibly including the ansisql.py change you made).

its not safe to remove the dupes the way youre doing it since you arent preserving the order of the clauses. but even with ordering preserved, I dont think we should make the assumption as to which duplicate clause instance we should remove, since removing the first dupe and not the second has totally different results. its better that having duplicate order by's gets interpreted by the database (in ms-sql's case as an error).

so i think this particular issue is fixed, its an ORM issue and i think if duplicate order bys do go in for some reason, it should be reported as an error by ms-sql.