On 7 Feb 2006, at 17:53, Alastair Houghton wrote:
On 7 Feb 2006, at 17:47, Alastair Houghton wrote:
Hi all,
Another patch; MySQL doesn't currently support correlated
subqueries in FROM clauses. This improves the error message you
get in that situation. (I didn't actually want or need a
correlated subquery, but I also spent quite some considerable time
trying to work out what was going on because MySQL threw a syntax
error rather than explaining that it didn't like correlated
subqueries in the FROM part of a SELECT.)
(Note: I have assumed that it's only possible to reach this code
with a subquery in the FROM clause or the WHERE clause... if it's
also possible to get here with a scalar or row subquery, then this
code isn't quite right.)
Oops. Just realised, this patch is in the wrong place :-) It
should be checking whether anything actually correlated, not
stopping things from working in cases where there was no
correlation :-D
OK, here's a patch that does work.
BTW, why is there a lambda expression in the froms property() call?
I would have expected
froms = property(_get_froms)
to work just as well.
Kind regards,
Alastair.
Index: lib/sqlalchemy/sql.py
===================================================================
--- lib/sqlalchemy/sql.py (revision 926)
+++ lib/sqlalchemy/sql.py (working copy)
@@ -1311,6 +1311,12 @@
fromclause.accept_visitor(self._correlator)
fromclause._process_from_dict(self._froms, True)
+ def is_correlated(self):
+ return (self._correlated is not None
+ and reduce(lambda x, y: x or y,
+ [self._correlated.has_key(f.id)
+ for f in self._froms.values()]))
+
def _get_froms(self):
return [f for f in self._froms.values() if self._correlated
is None or not self._correlated.has_key(f.id)]
froms = property(lambda s: s._get_froms())
Index: lib/sqlalchemy/databases/mysql.py
===================================================================
--- lib/sqlalchemy/databases/mysql.py (revision 926)
+++ lib/sqlalchemy/databases/mysql.py (working copy)
@@ -244,7 +244,16 @@
text += " \n LIMIT 18446744073709551615"
text += " OFFSET " + str(select.offset)
return text
+
+ def visit_select(self, select):
+ for f in select.froms:
+ if (isinstance(f, sql.Alias)
+ and isinstance(f.selectable, sql.Select)
+ and f.selectable.is_correlated()):
+ raise "MySQL does not support correlated subqueries
in the FROM clause. If it was not your intention to perform a
correlated subquery, you may need to use a table alias to avoid this
problem."
+ super(MySQLCompiler, self).visit_select(select)
+
class MySQLSchemaGenerator(ansisql.ANSISchemaGenerator):
def get_column_specification(self, column, override_pk=False,
first_pk=False):
colspec = column.name + " " + column.type.get_col_spec()
--
http://www.alastairs-place.net
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
