MSSQL Failing deletes with subquery + schema
SA currently generates a query like this:
DELETE FROM tsr.vulnres WHERE tsr.vulnres.tool != ? AND tsr.vulnres.targetid IN (SELECT tsr.target.id \nFROM tsr.target AS target_1, tsr.testing AS testing_1 \nWHERE tsr.target.testingid = tsr.testing.id AND tsr.testing.tsrid = ?)
In the subquery, the tables are aliased, but the columns don't use the aliased name.
A likely fix for this is in MSSQLCompiler.visit_column, to check if it's in a subquery inside a delete/update. However, is_subquery doesn't work at the moment, so that will need to be fixed first.
For SQL 2005 a workaround is to disable all the table aliasing.
Comments (6)
-
repo owner -
reporter The desired SQL is this:
DELETE FROM tsr.vulnres WHERE tsr.vulnres.tool != ? AND tsr.vulnres.targetid IN (SELECT tsr.target.id \nFROM tsr.target AS target_1, tsr.testing AS testing_1 \nWHERE target_1.testingid = testing_1.id AND testing_1.tsrid = ?)
There are a few other options that would work, but that seems the closest to what's generated already.
-
repo owner to get is_subquery() working inside of delete/update/insert, this should work:
Index: lib/sqlalchemy/sql/compiler.py =================================================================== --- lib/sqlalchemy/sql/compiler.py (revision 5145) +++ lib/sqlalchemy/sql/compiler.py (working copy) @@ -475,7 +475,7 @@ stack_entry = {'select':select} prev_entry = self.stack and self.stack[-1](-1) or None - if asfrom or (prev_entry and 'select' in prev_entry): + if asfrom or (prev_entry and 'from' in prev_entry): stack_entry['is_subquery']('is_subquery') = True stack_entry['iswrapper']('iswrapper') = iswrapper if not iswrapper and prev_entry and 'iswrapper' in prev_entry:
then MSSQL visit_column() would have to use the label name for the column when is_subquery is detected (and maybe also self.isdelete=True ?). To get the label name look at the logic within compiler.label_select_column().
-
repo owner - changed milestone to 0.5.xx
also is 0.5 OK for this since I dont want to destabilize 0.4 ?
-
This issue was related to MSSQL aliasing the schemas. Not entirely sure why aliasing is required in this situation but looks like without it there are lots of issues that crop up. Fixed in 260c201f656ce3afe35f9ae069cdf46593d4dffb.
-
repo owner - removed milestone
Removing milestone: 0.5.0 (automated comment)
- Log in to comment
whats the desired SQL here? I can see that it has to do with schema-qualified table names needing to be alised. But, only in subqueries ?
anyway, the way that MS-SQL is creating the "alias" for tables is a little weak - within visit_select(), clause adaption should be used to return an entirely new select() statement with the proper aliasing built in. The general technique is described below: