MS-SQL: Incorrect syntax near the keyword 'COLLATE'.

[Former user]

(original reporter: krysros) SQLAlchemy produce incorrect SQL in this case:

rows = DBSession.query(Categories.CategoryID, Categories.Category).\
                 filter(collate(Categories.Category.like(letter_), 'SQL_Latin1_General_CP1_CI_AI')).\
                 order_by(Categories.Category)

Result:

SELECT [Categories](Categories).[CategoryID](CategoryID) AS [Categories_CategoryID](Categories_CategoryID), [Categories](Categories).[Category](Category) AS [Categories_Category](Categories_Category)
FROM [Categories](Categories)
WHERE ([Categories](Categories).[Category](Category) LIKE ?)
COLLATE SQL_Latin1_General_CP1_CI_AI
ORDER BY [Categories](Categories).[Category](Category)

Throw:

ProgrammingError: (ProgrammingError) ('42000', "[42000](42000) [Microsoft](Microsoft)[Server Native Client 11.0](SQL)[Server](SQL)Incorrect syntax near the keyword 'COLLATE'. (156) (SQLExecDirectW)") 'SELECT [Categories](Categories).[CategoryID](CategoryID) AS [Categories_CategoryID](Categories_CategoryID), [Categories](Categories).[Category](Category) AS [Categories_Category](Categories_Category) \nFROM [Categories](Categories) \nWHERE ([Categories](Categories).[Category](Category) LIKE ?) COLLATE SQL_Latin1_General_CP1_CI_AI ORDER BY [Categories](Categories).[Category](Category)' ('a%',)

Code without parentheses around WHERE or with COLLATE inside the parentheses works as expected:

rows = DBSession.execute("""
    SELECT [Categories](Categories).[CategoryID](CategoryID) AS [Categories_CategoryID](Categories_CategoryID), [Categories](Categories).[Category](Category) AS [Categories_Category](Categories_Category)
    FROM [Categories](Categories)
    WHERE [Categories](Categories).[Category](Category) LIKE :letter
    COLLATE SQL_Latin1_General_CP1_CI_AI
    ORDER BY [Categories](Categories).[Category](Category)
    """, {'letter': letter_}).fetchall()

rows = DBSession.execute("""
    SELECT [Categories](Categories).[CategoryID](CategoryID) AS [Categories_CategoryID](Categories_CategoryID), [Categories](Categories).[Category](Category) AS [Categories_Category](Categories_Category)
    FROM [Categories](Categories)
    WHERE ([Categories](Categories).[Category](Category) LIKE :letter COLLATE SQL_Latin1_General_CP1_CI_AI)
    ORDER BY [Categories](Categories).[Category](Category)
    """, {'letter': letter_}).fetchall()

Windows 7, Python 3.3.3, Pyramid 1.4.5, SQLAlchemy 0.9.0b1.

[Mike Bayer]

Well we'd need to consider if a global change to the precedence of COLLATE is warranted, you can get that now like:

from sqlalchemy.sql import operators
operators._PRECEDENCE[operators.collate](operators.collate) = 4

a patch with some unit test changes follows, I'd need to research how COLLATE is applied on other backends to see if this change is universally appropriate:

diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index 2ccc878..9220cf1 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -821,7 +821,7 @@ _PRECEDENCE = {
     and_: 3,
     or_: 2,
     comma_op: -1,
-    collate: 7,
+    collate: 4,
     as_: -1,
     exists: 0,
     _smallest: _smallest,
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index f35c6a7..1773d9b 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1371,21 +1371,27 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):

         expr = select([table1.c.name.collate('latin1_german2_ci').like('%x%')](table1.c.name.collate('latin1_german2_ci').like('%x%')))
         self.assert_compile(expr,
-                            "SELECT mytable.name COLLATE latin1_german2_ci "
+                            "SELECT (mytable.name COLLATE latin1_german2_ci) "
                             "LIKE :param_1 AS anon_1 FROM mytable")

         expr = select([                                'latin1_german2_ci'))](table1.c.name.like(collate('%x%',
))
         self.assert_compile(expr,
                         "SELECT mytable.name "
-                        "LIKE :param_1 COLLATE latin1_german2_ci AS anon_1 "
+                        "LIKE (:param_1 COLLATE latin1_german2_ci) AS anon_1 "
                         "FROM mytable")

+        expr = select([table1.c.name](table1.c.name)).\
+            where(table1.c.name.like('%x%').collate('latin1_german2_ci'))
+        self.assert_compile(expr, "SELECT mytable.name FROM mytable WHERE "
+            "mytable.name LIKE :name_1 COLLATE latin1_german2_ci")
+
+
         expr = select([            collate('%x%', 'col2'))](table1.c.name.collate('col1').like(
))
         self.assert_compile(expr,
-                            "SELECT mytable.name COLLATE col1 "
-                            "LIKE :param_1 COLLATE col2 AS anon_1 "
+                            "SELECT (mytable.name COLLATE col1) "
+                            "LIKE (:param_1 COLLATE col2) AS anon_1 "
                             "FROM mytable")

         expr = select([func.concat('a', 'b').\

[Former user]

(original author: krysros) Thanks. This workaround works for me.

Replying to zzzeek:

Well we'd need to consider if a global change to the precedence of COLLATE is warranted, you can get that now like:

{{{ from sqlalchemy.sql import operators operators._PRECEDENCEoperators.collate = 4 }}}

a patch with some unit test changes follows, I'd need to research how COLLATE is applied on other backends to see if this change is universally appropriate:

{{{ #!diff diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 2ccc878..9220cf1 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -821,7 +821,7 @@ PRECEDENCE = { and: 3, or_: 2, comma_op: -1, - collate: 7, + collate: 4, as_: -1, exists: 0, _smallest: _smallest, diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index f35c6a7..1773d9b 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -1371,21 +1371,27 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):

     expr = select([table1.c.name.collate('latin1_german2_ci').like('%x%')](table1.c.name.collate('latin1_german2_ci').like('%x%')))
     self.assert_compile(expr,

• "SELECT mytable.name COLLATE latin1_german2_ci "

• "SELECT (mytable.name COLLATE latin1_german2_ci) " "LIKE :param_1 AS anon_1 FROM mytable")

   expr = select([                                 'latin1_german2_ci'))](table1.c.name.like(collate('%x%',
  

  )) self.assert_compile(expr, "SELECT mytable.name " - "LIKE :param_1 COLLATE latin1_german2_ci AS anon_1 " + "LIKE (:param_1 COLLATE latin1_german2_ci) AS anon_1 " "FROM mytable")

• expr = select(table1.c.name).\

• where(table1.c.name.like('%x%').collate('latin1_german2_ci'))
• self.assert_compile(expr, "SELECT mytable.name FROM mytable WHERE "
• "mytable.name LIKE :name_1 COLLATE latin1_german2_ci") + + expr = select( collate('%x%', 'col2')).like( )) self.assert_compile(expr,
• "SELECT mytable.name COLLATE col1 "
• "LIKE :param_1 COLLATE col2 AS anon_1 "
• "SELECT (mytable.name COLLATE col1) "
• "LIKE (:param_1 COLLATE col2) AS anon_1 " "FROM mytable")

   expr = select([func.concat('a', 'b').\
  

  }}}

[Mike Bayer]

it seems like COLLATE following the expression in WHERE without the parens is normal. PG's docs have some examples:

SELECT a < ('foo' COLLATE "fr_FR") FROM test1;

SELECT a < b COLLATE "de_DE" FROM test1;

SELECT a COLLATE "de_DE" < b FROM test1;

from the above it seems like COLLATE has a lower priority than a comparison operator like <.

Running these tests using first 4 then 7 for priority:

from sqlalchemy.sql import column

print (column('x') > column('y')).collate('q')

print (column('x') > column('y').collate('q'))

print (column('x').collate('q') > column('y'))

print (column('x') > column('y')).collate('q') & (column('z') < column('j')).collate('p')

output using 4:

x > y COLLATE q
x > (y COLLATE q)
(x COLLATE q) > y
x > y COLLATE q AND z < j COLLATE p

output using 7

(x > y) COLLATE q
x > y COLLATE q
x COLLATE q > y
(x > y) COLLATE q AND (z < j) COLLATE p

4 seems to be correct but I'm noticing that using the (x > y.collate(q)) syntax appears to be a workaround, and it's possible this is how we thought this would work when we first rolled out COLLATE support. It's likely people are using this form, so I think a change here would have to be a hard 0.9 thing (more things someone migrating to 0.9 needs to worry about, basically....)

[Mike Bayer]

b653fb3a23a0388814d9ab79b884d6

[Mike Bayer]

Removing milestone: 0.9.0 (automated comment)