diff options
| -rw-r--r-- | doc/build/changelog/changelog_09.rst | 18 | ||||
| -rw-r--r-- | doc/build/changelog/migration_09.rst | 48 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 6 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 52 | ||||
| -rw-r--r-- | test/sql/test_operators.py | 52 |
5 files changed, 123 insertions, 53 deletions
diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst index 370692528..14688fbfd 100644 --- a/doc/build/changelog/changelog_09.rst +++ b/doc/build/changelog/changelog_09.rst @@ -15,6 +15,24 @@ :version: 0.9.0b2 .. change:: + :tags: bug, sql + :tickets: 2879 + + The precedence rules for the :meth:`.ColumnOperators.collate` operator + have been modified, such that the COLLATE operator is now of lower + precedence than the comparison operators. This has the effect that + a COLLATE applied to a comparison will not render parenthesis + around the comparison, which is not parsed by backends such as + MSSQL. The change is backwards incompatible for those setups that + were working around the issue by applying :meth:`.Operators.collate` + to an individual element of the comparison expression, + rather than the comparison expression as a whole. + + .. seelalso:: + + :ref:`migration_2879` + + .. change:: :tags: bug, orm, declarative :tickets: 2865 diff --git a/doc/build/changelog/migration_09.rst b/doc/build/changelog/migration_09.rst index e70a17fb0..cb658903a 100644 --- a/doc/build/changelog/migration_09.rst +++ b/doc/build/changelog/migration_09.rst @@ -501,7 +501,55 @@ spaces are passed through as is:: :ticket:`2873` +.. _migration_2879: +The precedence rules for COLLATE have been changed +-------------------------------------------------- + +Previously, an expression like the following:: + + print (column('x') == 'somevalue').collate("en_EN") + +would produce an expression like this:: + + -- 0.8 behavior + (x = :x_1) COLLATE en_EN + +The above is misunderstood by MSSQL and is generally not the syntax suggested +for any database. The expression will now produce the syntax illustrated +by that of most database documentation:: + + -- 0.9 behavior + x = :x_1 COLLATE en_EN + +The potentially backwards incompatible change arises if the :meth:`.collate` +operator is being applied to the right-hand column, as follows:: + + print column('x') == literal('somevalue').collate("en_EN") + +In 0.8, this produces:: + + x = :param_1 COLLATE en_EN + +However in 0.9, will now produce the more accurate, but probably not what you +want, form of:: + + x = (:param_1 COLLATE en_EN) + +The :meth:`.ColumnOperators.collate` operator now works more appropriately within an +``ORDER BY`` expression as well, as a specific precedence has been given to the +``ASC`` and ``DESC`` operators which will again ensure no parentheses are +generated:: + + >>> # 0.8 + >>> print column('x').collate('en_EN').desc() + (x COLLATE en_EN) DESC + + >>> # 0.9 + >>> print column('x').collate('en_EN').desc() + x COLLATE en_EN DESC + +:ticket:`2879` .. _migration_2850: diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 5bd4b302b..a156a9796 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -831,7 +831,11 @@ _PRECEDENCE = { and_: 3, or_: 2, comma_op: -1, - collate: 7, + + desc_op: 3, + asc_op: 3, + collate: 4, + as_: -1, exists: 0, _asbool: -10, diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 36dfa2ff1..ca24deb31 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -1219,58 +1219,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "/ values.val1 > :param_1" ) - def test_collate(self): - for expr in (select([table1.c.name.collate('latin1_german2_ci')]), - select([collate(table1.c.name, 'latin1_german2_ci')])): - self.assert_compile( - expr, "SELECT mytable.name COLLATE latin1_german2_ci " - "AS anon_1 FROM mytable") - - assert table1.c.name.collate('latin1_german2_ci').type is \ - table1.c.name.type - - expr = select([table1.c.name.collate('latin1_german2_ci').\ - label('k1')]).order_by('k1') - self.assert_compile(expr, - "SELECT mytable.name " - "COLLATE latin1_german2_ci AS k1 FROM mytable ORDER BY k1") - - expr = select([collate('foo', 'latin1_german2_ci').label('k1')]) - self.assert_compile(expr, - "SELECT :param_1 COLLATE latin1_german2_ci AS k1") - - expr = select([table1.c.name.collate('latin1_german2_ci').like('%x%')]) - self.assert_compile(expr, - "SELECT mytable.name COLLATE latin1_german2_ci " - "LIKE :param_1 AS anon_1 FROM mytable") - - expr = select([table1.c.name.like(collate('%x%', - 'latin1_german2_ci'))]) - self.assert_compile(expr, - "SELECT mytable.name " - "LIKE :param_1 COLLATE latin1_german2_ci AS anon_1 " - "FROM mytable") - - expr = select([table1.c.name.collate('col1').like( - collate('%x%', 'col2'))]) - self.assert_compile(expr, - "SELECT mytable.name COLLATE col1 " - "LIKE :param_1 COLLATE col2 AS anon_1 " - "FROM mytable") - - expr = select([func.concat('a', 'b').\ - collate('latin1_german2_ci').label('x')]) - self.assert_compile(expr, - "SELECT concat(:param_1, :param_2) " - "COLLATE latin1_german2_ci AS x") - - - expr = select([table1.c.name]).\ - order_by(table1.c.name.collate('latin1_german2_ci')) - self.assert_compile(expr, - "SELECT mytable.name FROM mytable ORDER BY " - "mytable.name COLLATE latin1_german2_ci") - def test_percent_chars(self): t = table("table%name", column("percent%"), diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 0124d85fa..670d088d2 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -673,6 +673,58 @@ class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): self.table2.c.field).is_(None)), "SELECT op.field FROM op WHERE (op.field MATCH op.field) IS NULL") + 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)" + ) + + 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" + ) + + 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" + ) + + def test_operator_precedence_collate_4(self): + self.assert_compile( + and_( + (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" + ) + + def test_operator_precedence_collate_5(self): + self.assert_compile( + 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" + ) + + def test_operator_precedence_collate_6(self): + self.assert_compile( + 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" + ) + + def test_operator_precedence_collate_7(self): + self.assert_compile( + 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" + ) + def test_commutative_operators(self): self.assert_compile( literal("a") + literal("b") * literal("c"), |