diff options
| -rw-r--r-- | CHANGES | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/ansisql.py | 19 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql.py | 21 | ||||
| -rw-r--r-- | test/sql/query.py | 1 | ||||
| -rw-r--r-- | test/sql/select.py | 30 |
5 files changed, 55 insertions, 22 deletions
@@ -31,6 +31,12 @@ full statement being compiled. this means the same statement will produce the same string across application restarts and allowing DB query plan caching to work better. + - the "mini" column labels generated when using subqueries, which + are to work around glitchy SQLite behavior that doesnt understand + "foo.id" as equivalent to "id", are now only generated in the case + that those named columns are selected from (part of [ticket:513]) + - MS-SQL better detects when a query is a subquery and knows not to + generate ORDER BY phrases for those [ticket:513] - preliminary support for unicode table names, column names and SQL statements added, for databases which can support them. - fix for fetchmany() "size" argument being positional in most diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py index 03053b998..c3170aa79 100644 --- a/lib/sqlalchemy/ansisql.py +++ b/lib/sqlalchemy/ansisql.py @@ -355,9 +355,7 @@ class ANSICompiler(sql.Compiled): group_by = self.get_str(cs.group_by_clause) if group_by: text += " GROUP BY " + group_by - order_by = self.get_str(cs.order_by_clause) - if order_by: - text += " ORDER BY " + order_by + text += self.order_by_clause(cs) text += self.visit_select_postclauses(cs) if cs.parens: self.strings[cs] = "(" + text + ")" @@ -460,7 +458,7 @@ class ANSICompiler(sql.Compiled): inner_columns[self.get_str(co)] = co # TODO: figure this out, a ColumnClause with a select as a parent # is different from any other kind of parent - elif select.is_subquery and isinstance(co, sql._ColumnClause) and not co.is_literal and co.table is not None and not isinstance(co.table, sql.Select): + elif select.is_selected_from and isinstance(co, sql._ColumnClause) and not co.is_literal and co.table is not None and not isinstance(co.table, sql.Select): # SQLite doesnt like selecting from a subquery where the column # names look like table.colname, so add a label synonomous with # the column name @@ -529,12 +527,8 @@ class ANSICompiler(sql.Compiled): if t: text += " \nHAVING " + t - order_by = self.get_str(select.order_by_clause) - if order_by: - text += " ORDER BY " + order_by - + text += self.order_by_clause(select) text += self.visit_select_postclauses(select) - text += self.for_update_clause(select) if getattr(select, 'parens', False): @@ -556,6 +550,13 @@ class ANSICompiler(sql.Compiled): return (select.limit or select.offset) and self.limit_clause(select) or "" + def order_by_clause(self, select): + order_by = self.get_str(select.order_by_clause) + if order_by: + return " ORDER BY " + order_by + else: + return "" + def for_update_clause(self, select): if select.for_update: return " FOR UPDATE" diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 4959c4e21..2388ecc3e 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -2263,6 +2263,9 @@ class Select(_SelectBaseMixin, FromClause): # indicates if this select statement is a subquery inside another query self.is_subquery = False + # indicates if this select statement is in the from clause of another query + self.is_selected_from = False + # indicates if this select statement is a subquery as a criterion # inside of a WHERE clause self.is_where = False @@ -2272,7 +2275,10 @@ class Select(_SelectBaseMixin, FromClause): self.__correlated = {} self.__correlator = Select._CorrelatedVisitor(self, False) self.__wherecorrelator = Select._CorrelatedVisitor(self, True) + self.__fromvisitor = Select._FromVisitor(self) + self.order_by_clause = self.group_by_clause = None + if columns is not None: for c in columns: self.append_column(c) @@ -2327,6 +2333,17 @@ class Select(_SelectBaseMixin, FromClause): return [select.correlate(x) for x in self.select._Select__froms] + class _FromVisitor(NoColumnVisitor): + def __init__(self, select): + NoColumnVisitor.__init__(self) + self.select = select + + def visit_select(self, select): + if select is self.select: + return + select.is_selected_from = True + select.is_subquery = True + def append_column(self, column): if _is_literal(column): column = literal_column(str(column), table=self) @@ -2369,6 +2386,7 @@ class Select(_SelectBaseMixin, FromClause): def _process_froms(self, elem, asfrom): for f in elem._get_from_objects(): + self.__fromvisitor.traverse(f) self.__froms.add(f) if asfrom: self.__froms.add(elem) @@ -2432,8 +2450,7 @@ class Select(_SelectBaseMixin, FromClause): def get_children(self, column_collections=True, **kwargs): return (column_collections and list(self.columns) or []) + \ list(self.froms) + \ - [x for x in (self.whereclause, self.having) if x is not None] + \ - [self.order_by_clause, self.group_by_clause] + [x for x in (self.whereclause, self.having, self.order_by_clause, self.group_by_clause) if x is not None] def accept_visitor(self, visitor): visitor.visit_select(self) diff --git a/test/sql/query.py b/test/sql/query.py index 08c766a0d..928112c24 100644 --- a/test/sql/query.py +++ b/test/sql/query.py @@ -376,6 +376,7 @@ class QueryTest(PersistTest): finally: shadowed.drop(checkfirst=True) + class CompoundTest(PersistTest): """test compound statements like UNION, INTERSECT, particularly their ability to nest on different databases.""" diff --git a/test/sql/select.py b/test/sql/select.py index 7406d6c4f..56584e708 100644 --- a/test/sql/select.py +++ b/test/sql/select.py @@ -1,7 +1,7 @@ from testbase import PersistTest import testbase from sqlalchemy import * -from sqlalchemy.databases import sqlite, postgres, mysql, oracle, firebird +from sqlalchemy.databases import sqlite, postgres, mysql, oracle, firebird, mssql import unittest, re @@ -123,6 +123,14 @@ sq2.sq_myothertable_otherid, sq2.sq_myothertable_othername FROM \ sq.mytable_description AS sq_mytable_description, sq.myothertable_otherid AS sq_myothertable_otherid, \ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") AS sq) AS sq2") + def testmssql_noorderbyinsubquery(self): + """test that the ms-sql dialect removes ORDER BY clauses from subqueries""" + dialect = mssql.dialect() + q = select([table1.c.myid], order_by=[table1.c.myid]).alias('foo') + crit = q.c.myid == table1.c.myid + self.runtest(select(['*'], crit), """SELECT * FROM (SELECT mytable.myid AS myid FROM mytable ORDER BY mytable.myid) AS foo, mytable WHERE foo.myid = mytable.myid""", dialect=sqlite.dialect()) + self.runtest(select(['*'], crit), """SELECT * FROM (SELECT mytable.myid AS myid FROM mytable) AS foo, mytable WHERE foo.myid = mytable.myid""", dialect=mssql.dialect()) + def testdontovercorrelate(self): self.runtest(select([table1], from_obj=[table1, table1.select()]), """SELECT mytable.myid, mytable.name, mytable.description FROM mytable, (SELECT mytable.myid AS myid, mytable.name AS name, mytable.description AS description FROM mytable)""") @@ -137,7 +145,7 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A self.runtest( table1.select(table1.c.myid == select([table2.c.otherid], table1.c.name == table2.c.othername)), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = (SELECT myothertable.otherid AS otherid FROM myothertable WHERE mytable.name = myothertable.othername)" + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = (SELECT myothertable.otherid FROM myothertable WHERE mytable.name = myothertable.othername)" ) self.runtest( @@ -167,20 +175,20 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A def testorderbysubquery(self): self.runtest( table1.select(order_by=[select([table2.c.otherid], table1.c.myid==table2.c.otherid)]), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable ORDER BY (SELECT myothertable.otherid AS otherid FROM myothertable WHERE mytable.myid = myothertable.otherid)" + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable ORDER BY (SELECT myothertable.otherid FROM myothertable WHERE mytable.myid = myothertable.otherid)" ) self.runtest( table1.select(order_by=[desc(select([table2.c.otherid], table1.c.myid==table2.c.otherid))]), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable ORDER BY (SELECT myothertable.otherid AS otherid FROM myothertable WHERE mytable.myid = myothertable.otherid) DESC" + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable ORDER BY (SELECT myothertable.otherid FROM myothertable WHERE mytable.myid = myothertable.otherid) DESC" ) def testcolumnsubquery(self): s = select([table1.c.myid], scalar=True, correlate=False) - self.runtest(select([table1, s]), "SELECT mytable.myid, mytable.name, mytable.description, (SELECT mytable.myid AS myid FROM mytable) FROM mytable") + self.runtest(select([table1, s]), "SELECT mytable.myid, mytable.name, mytable.description, (SELECT mytable.myid FROM mytable) FROM mytable") s = select([table1.c.myid], scalar=True) - self.runtest(select([table2, s]), "SELECT myothertable.otherid, myothertable.othername, (SELECT mytable.myid AS myid FROM mytable) FROM myothertable") + self.runtest(select([table2, s]), "SELECT myothertable.otherid, myothertable.othername, (SELECT mytable.myid FROM mytable) FROM myothertable") zips = table('zips', @@ -201,7 +209,7 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A order_by = ['dist', places.c.nm] ) - self.runtest(q,"SELECT places.id, places.nm, zips.zipcode, latlondist((SELECT zips.latitude AS latitude FROM zips WHERE zips.zipcode = :zips_zipcode_1), (SELECT zips.longitude AS longitude FROM zips WHERE zips.zipcode = :zips_zipcode_2)) AS dist FROM places, zips WHERE zips.zipcode = :zips_zipcode ORDER BY dist, places.nm") + self.runtest(q,"SELECT places.id, places.nm, zips.zipcode, latlondist((SELECT zips.latitude FROM zips WHERE zips.zipcode = :zips_zipcode_1), (SELECT zips.longitude FROM zips WHERE zips.zipcode = :zips_zipcode_2)) AS dist FROM places, zips WHERE zips.zipcode = :zips_zipcode ORDER BY dist, places.nm") zalias = zips.alias('main_zip') qlat = select([zips.c.latitude], zips.c.zipcode == zalias.c.zipcode, scalar=True) @@ -209,7 +217,7 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A q = select([places.c.id, places.c.nm, zalias.c.zipcode, func.latlondist(qlat, qlng).label('dist')], order_by = ['dist', places.c.nm] ) - self.runtest(q, "SELECT places.id, places.nm, main_zip.zipcode, latlondist((SELECT zips.latitude AS latitude FROM zips WHERE zips.zipcode = main_zip.zipcode), (SELECT zips.longitude AS longitude FROM zips WHERE zips.zipcode = main_zip.zipcode)) AS dist FROM places, zips AS main_zip ORDER BY dist, places.nm") + self.runtest(q, "SELECT places.id, places.nm, main_zip.zipcode, latlondist((SELECT zips.latitude FROM zips WHERE zips.zipcode = main_zip.zipcode), (SELECT zips.longitude FROM zips WHERE zips.zipcode = main_zip.zipcode)) AS dist FROM places, zips AS main_zip ORDER BY dist, places.nm") def testand(self): self.runtest( @@ -694,10 +702,10 @@ myothertable.othername != :myothertable_othername AND EXISTS (select yay from fo "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (:mytable_myid, :mytable_myid_1, :mytable_myid_2)") self.runtest(select([table1], table1.c.myid.in_(select([table2.c.otherid]))), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (SELECT myothertable.otherid AS otherid FROM myothertable)") + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid IN (SELECT myothertable.otherid FROM myothertable)") self.runtest(select([table1], ~table1.c.myid.in_(select([table2.c.otherid]))), - "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid NOT IN (SELECT myothertable.otherid AS otherid FROM myothertable)") + "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid NOT IN (SELECT myothertable.otherid FROM myothertable)") # test that putting a select in an IN clause does not blow away its ORDER BY clause self.runtest( @@ -707,7 +715,7 @@ myothertable.othername != :myothertable_othername AND EXISTS (select yay from fo ), from_obj=[table1.join(table2, table1.c.myid==table2.c.otherid)], order_by=[table1.c.myid] ), - "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername FROM mytable JOIN myothertable ON mytable.myid = myothertable.otherid WHERE myothertable.otherid IN (SELECT myothertable.otherid AS otherid FROM myothertable ORDER BY myothertable.othername LIMIT 10) ORDER BY mytable.myid" + "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername FROM mytable JOIN myothertable ON mytable.myid = myothertable.otherid WHERE myothertable.otherid IN (SELECT myothertable.otherid FROM myothertable ORDER BY myothertable.othername LIMIT 10) ORDER BY mytable.myid" ) |