From 40af03f8879412051518df8aadd8886c6c33aac0 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 12 Oct 2012 13:20:56 -0400
Subject: [PATCH] - add some UPDATE..FROM tests and also some new reqs

---
 lib/sqlalchemy/testing/requirements.py | 21 +++++++++++
 test/orm/test_update_delete.py         | 50 +++++++++++++++++++++++---
 test/requirements.py                   | 16 +++++++++
 3 files changed, 83 insertions(+), 4 deletions(-)

diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 9681750b21..ae285afa4b 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -183,3 +183,24 @@ class SuiteRequirements(Requirements):
         """target database can persist/return an empty string."""
 
         return exclusions.open()
+
+
+    @property
+    def update_from(self):
+        """Target must support UPDATE..FROM syntax"""
+        return exclusions.closed()
+
+    @property
+    def update_where_target_in_subquery(self):
+        """Target must support UPDATE where the same table is present in a
+        subquery in the WHERE clause.
+
+        This is an ANSI-standard syntax that apparently MySQL can't handle,
+        such as:
+
+        UPDATE documents SET flag=1 WHERE documents.title IN
+            (SELECT max(documents.title) AS title
+                FROM documents GROUP BY documents.user_id
+            )
+        """
+        return exclusions.open()
diff --git a/test/orm/test_update_delete.py b/test/orm/test_update_delete.py
index 3d4d2559ca..4da07458e1 100644
--- a/test/orm/test_update_delete.py
+++ b/test/orm/test_update_delete.py
@@ -1,7 +1,7 @@
 from sqlalchemy.testing import eq_, assert_raises, assert_raises_message
 from sqlalchemy.testing import fixtures
 from sqlalchemy import Integer, String, ForeignKey, or_, and_, exc, \
-    select, func, Boolean
+    select, func, Boolean, case
 from sqlalchemy.orm import mapper, relationship, backref, Session, \
     joinedload, aliased
 from sqlalchemy import testing
@@ -541,7 +541,7 @@ class UpdateDeleteFromTest(fixtures.MappedTest):
               Column('id', Integer, primary_key=True),
               Column('user_id', None, ForeignKey('users.id')),
               Column('title', String(32)),
-              Column('flag', Boolean, default=False)
+              Column('flag', Boolean)
         )
 
     @classmethod
@@ -597,6 +597,50 @@ class UpdateDeleteFromTest(fixtures.MappedTest):
         s.query(Document).filter(Document.title == subq.c.title).\
                 update({'flag': True}, synchronize_session=False)
 
+        eq_(
+            set(s.query(Document.id, Document.flag)),
+            set([
+                    (1, True), (2, None),
+                    (3, None), (4, True),
+                    (5, True), (6, None),
+                ])
+        )
+
+    @testing.requires.update_where_target_in_subquery
+    def test_update_using_in(self):
+        Document = self.classes.Document
+        s = Session()
+
+        subq = s.query(func.max(Document.title).label('title')).\
+                group_by(Document.user_id).subquery()
+
+        s.query(Document).filter(Document.title.in_(subq)).\
+                update({'flag': True}, synchronize_session=False)
+
+        eq_(
+            set(s.query(Document.id, Document.flag)),
+            set([
+                    (1, True), (2, None),
+                    (3, None), (4, True),
+                    (5, True), (6, None),
+                ])
+        )
+
+    @testing.requires.update_where_target_in_subquery
+    @testing.requires.standalone_binds
+    def test_update_using_case(self):
+        Document = self.classes.Document
+        s = Session()
+
+
+        subq = s.query(func.max(Document.title).label('title')).\
+                group_by(Document.user_id).subquery()
+
+        # this would work with Firebird if you do literal_column('1')
+        # instead
+        case_stmt = case([(Document.title.in_(subq), True)], else_=False)
+        s.query(Document).update({'flag': case_stmt}, synchronize_session=False)
+
         eq_(
             set(s.query(Document.id, Document.flag)),
             set([
@@ -606,8 +650,6 @@ class UpdateDeleteFromTest(fixtures.MappedTest):
                 ])
         )
 
-
-
 class ExpressionUpdateTest(fixtures.MappedTest):
     @classmethod
     def define_tables(cls, metadata):
diff --git a/test/requirements.py b/test/requirements.py
index 3e9f5ee0f5..ca245eb307 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -183,6 +183,22 @@ class DefaultRequirements(SuiteRequirements):
                 "Backend does not support UPDATE..FROM")
 
 
+    @property
+    def update_where_target_in_subquery(self):
+        """Target must support UPDATE where the same table is present in a
+        subquery in the WHERE clause.
+
+        This is an ANSI-standard syntax that apparently MySQL can't handle,
+        such as:
+
+        UPDATE documents SET flag=1 WHERE documents.title IN
+            (SELECT max(documents.title) AS title
+                FROM documents GROUP BY documents.user_id
+            )
+        """
+        return fails_if('mysql', 'MySQL error 1093 "Cant specify target table '
+                                        'for update in FROM clause"')
+
     @property
     def savepoints(self):
         """Target database must support savepoints."""
-- 
2.47.3