On 8 Feb 2006, at 20:52, Michael Bayer wrote:
wow reduce() ! the one python function i havent tried yet.
:-) You'll have to slap me if I get too lisp-y.
how come we need to test for an Alias wrapping a Select? why not
just put
an "is_correlated()" method on FromClause as a non implemented
method, and
each subclass of FromClause implements a version which either descends
into its child selectable, or its list of from objects, and determines
that there is a correlation occuring...that way MySQL compilation
just has
one simple test and the SQL object graph uses the is_correlated method
recursively/polymorphically to give you the right answer.
Yes, you're right, that would be better. I thought an Alias
containing a Select
was probably the only way it could currently happen, but now I think
about it
I suppose it's quite likely that there are or will be other ways
(e.g. a union of two select statements) that it could happen.
also is this correlated FROM issue on all versions of MySQL or just
some ?
on myISAM tables or InnoDB tables as well ?
All versions so far. There are suggested workarounds in the manual,
but nothing that looks terribly straightforward to do automatically.
So the patch becomes something like this:
Index: lib/sqlalchemy/sql.py
===================================================================
--- lib/sqlalchemy/sql.py (revision 926)
+++ lib/sqlalchemy/sql.py (working copy)
@@ -543,6 +543,8 @@
def _get_from_objects(self):
# this could also be [self], at the moment it doesnt matter
to the Select object
return []
+ def is_correlated (self):
+ raise NotImplementedError(repr(self))
def default_order_by(self):
if not self.engine.default_ordering:
return None
@@ -598,7 +600,7 @@
raise NotImplementedError(repr(self))
def _proxy_column(self, column):
return column._make_proxy(self)
-
+
class BindParamClause(ClauseElement, CompareMixin):
"""represents a bind parameter. public constructor is the
bindparam() function."""
def __init__(self, key, value, shortname=None, type=None):
@@ -865,6 +867,9 @@
statement"""
return True
+ def is_correlated(self):
+ return self.left.is_correlated() or self.right.is_correlated()
+
def hash_key(self):
return "Join(%s, %s, %s, %s)" % (repr(self.left.hash_key
()), repr(self.right.hash_key()), repr(self.onclause.hash_key()), repr
(self.isouter))
@@ -885,6 +890,8 @@
self.join = join
def _exportable_columns(self):
return []
+ def is_correlated(self):
+ return self.join.is_correlated()
def _process_from_dict(self, data, asfrom):
for f in self.onclause._get_from_objects():
@@ -919,6 +926,9 @@
def _exportable_columns(self):
return self.selectable.columns
+ def is_correlated(self):
+ return self.selectable.is_correlated()
+
def hash_key(self):
return "Alias(%s, %s)" % (self.selectable.hash_key(), repr
(self.name))
@@ -1078,6 +1088,9 @@
foreign_keys = property(lambda self:self.table.foreign_keys)
original_columns = property(_orig_columns)
+ def is_correlated(self):
+ return False
+
def _exportable_columns(self):
raise NotImplementedError()
@@ -1165,6 +1178,9 @@
[util.hash_key(s) for s in self.selects] +
["%s=%s" % (k, repr(getattr(self, k))) for k in
['use_labels', 'keyword']],
",")
+ def is_correlated(self):
+ return reduce(lambda x, sel: x or sel.is_correlated(),
+ self.selects, False)
def _exportable_columns(self):
return self.selects[0].columns
def _proxy_column(self, column):
@@ -1311,6 +1327,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, f: (x or
self._correlated.has_key(f.id)
+ or f.is_correlated()),
+ self._froms.values(), False))
+
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,13 @@
text += " \n LIMIT 18446744073709551615"
text += " OFFSET " + str(select.offset)
return text
+
+ def visit_select(self, select):
+ if (select.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()
My understanding of exactly how all of sql.py fits together is still
not 100%, so it wouldn't surprise me if I've missed something. Also,
perhaps we need some new unit tests that exercise this code to make
sure that it works (I might get some time to do something tomorrow,
we'll see).
Kind regards,
Alastair.
--
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
