Treat collation names as identifiers

The expression used for COLLATE as rendered by the column-level
:func:`.expression.collate` and :meth:`.ColumnOperators.collate` is now
quoted as an identifier when the name is case sensitive, e.g. has
uppercase characters.  Note that this does not impact type-level
collation, which is already quoted.

Change-Id: I83d5d9cd1e66a4f20b96303bb84c5f360d5d6a1a
Fixes: #3785

2 files changed, 25 insertions, 8 deletions

diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 0e0a8b29c..7c3ce1389 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -1389,19 +1389,19 @@ class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     def test_operator_precedence_collate_1(self):
         self.assert_compile(
             self.table1.c.name == literal('foo').collate('utf-8'),
-            "mytable.name = (:param_1 COLLATE utf-8)"
+            'mytable.name = (:param_1 COLLATE "utf-8")'
         )
 
     def test_operator_precedence_collate_2(self):
         self.assert_compile(
             (self.table1.c.name == literal('foo')).collate('utf-8'),
-            "mytable.name = :param_1 COLLATE utf-8"
+            'mytable.name = :param_1 COLLATE "utf-8"'
         )
 
     def test_operator_precedence_collate_3(self):
         self.assert_compile(
             self.table1.c.name.collate('utf-8') == 'foo',
-            "(mytable.name COLLATE utf-8) = :param_1"
+            '(mytable.name COLLATE "utf-8") = :param_1'
         )
 
     def test_operator_precedence_collate_4(self):
@@ -1410,8 +1410,8 @@ class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
                 (self.table1.c.name == literal('foo')).collate('utf-8'),
                 (self.table2.c.field == literal('bar')).collate('utf-8'),
             ),
-            "mytable.name = :param_1 COLLATE utf-8 "
-            "AND op.field = :param_2 COLLATE utf-8"
+            'mytable.name = :param_1 COLLATE "utf-8" '
+            'AND op.field = :param_2 COLLATE "utf-8"'
         )
 
     def test_operator_precedence_collate_5(self):
@@ -1419,7 +1419,7 @@ class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             select([self.table1.c.name]).order_by(
                 self.table1.c.name.collate('utf-8').desc()),
             "SELECT mytable.name FROM mytable "
-            "ORDER BY mytable.name COLLATE utf-8 DESC"
+            'ORDER BY mytable.name COLLATE "utf-8" DESC'
         )
 
     def test_operator_precedence_collate_6(self):
@@ -1427,7 +1427,7 @@ class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             select([self.table1.c.name]).order_by(
                 self.table1.c.name.collate('utf-8').desc().nullslast()),
             "SELECT mytable.name FROM mytable "
-            "ORDER BY mytable.name COLLATE utf-8 DESC NULLS LAST"
+            'ORDER BY mytable.name COLLATE "utf-8" DESC NULLS LAST'
         )
 
     def test_operator_precedence_collate_7(self):
@@ -1435,7 +1435,7 @@ class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
             select([self.table1.c.name]).order_by(
                 self.table1.c.name.collate('utf-8').asc()),
             "SELECT mytable.name FROM mytable "
-            "ORDER BY mytable.name COLLATE utf-8 ASC"
+            'ORDER BY mytable.name COLLATE "utf-8" ASC'
         )
 
     def test_commutative_operators(self):
diff --git a/test/sql/test_quote.py b/test/sql/test_quote.py
index 94f9d62ac..a436dde67 100644
--- a/test/sql/test_quote.py
+++ b/test/sql/test_quote.py
@@ -454,6 +454,23 @@ class QuoteTest(fixtures.TestBase, AssertsCompiledSQL):
             'SELECT t1.col1 AS "ShouldQuote" FROM t1 ORDER BY "ShouldQuote"'
         )
 
+    def test_collate(self):
+        self.assert_compile(
+            column('foo').collate('utf8'),
+            "foo COLLATE utf8"
+        )
+
+        self.assert_compile(
+            column('foo').collate('fr_FR'),
+            'foo COLLATE "fr_FR"'
+        )
+
+        self.assert_compile(
+            column('foo').collate('utf8_GERMAN_ci'),
+            'foo COLLATE `utf8_GERMAN_ci`',
+            dialect="mysql"
+        )
+
     def test_join(self):
         # Lower case names, should not quote
         metadata = MetaData()