From ce60f93a68f312c7401802820dd17f5d91f73a2c Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Mon, 29 Jul 2024 23:52:04 +0200 Subject: [PATCH] Fixed compilation of bitwise operators on oracle and sqlite. Implemented bitwise operators for Oracle which was previously non-functional due to a non-standard syntax used by this database. Oracle's support for bitwise "or" and "xor" starts with server version 21. Additionally repaired the implementation of "xor" for SQLite. As part of this change, the dialect compliance test suite has been enhanced to include support for server-side bitwise tests; third party dialect authors should refer to new "supports_bitwise" methods in the requirements.py file to enable these tests. Fixes: #11663 Change-Id: I41040bd67992b6c89ed3592edca8965d5d59be9e --- doc/build/changelog/unreleased_20/11663.rst | 16 ++++++ lib/sqlalchemy/dialects/oracle/base.py | 25 +++++++++ lib/sqlalchemy/dialects/sqlite/base.py | 7 +++ lib/sqlalchemy/testing/requirements.py | 25 +++++++++ lib/sqlalchemy/testing/suite/test_select.py | 60 +++++++++++++++++++++ test/requirements.py | 25 +++++++++ 6 files changed, 158 insertions(+) create mode 100644 doc/build/changelog/unreleased_20/11663.rst diff --git a/doc/build/changelog/unreleased_20/11663.rst b/doc/build/changelog/unreleased_20/11663.rst new file mode 100644 index 0000000000..599cd744bf --- /dev/null +++ b/doc/build/changelog/unreleased_20/11663.rst @@ -0,0 +1,16 @@ +.. change:: + :tags: bug, oracle, sqlite + :tickets: 11663 + + Implemented bitwise operators for Oracle which was previously + non-functional due to a non-standard syntax used by this database. + Oracle's support for bitwise "or" and "xor" starts with server version 21. + Additionally repaired the implementation of "xor" for SQLite. + + As part of this change, the dialect compliance test suite has been enhanced + to include support for server-side bitwise tests; third party dialect + authors should refer to new "supports_bitwise" methods in the + requirements.py file to enable these tests. + + + diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 8e5989990e..058becf831 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -1256,6 +1256,31 @@ class OracleCompiler(compiler.SQLCompiler): def visit_aggregate_strings_func(self, fn, **kw): return "LISTAGG%s" % self.function_argspec(fn, **kw) + def _visit_bitwise(self, binary, fn_name, custom_right=None, **kw): + left = self.process(binary.left, **kw) + right = self.process( + custom_right if custom_right is not None else binary.right, **kw + ) + return f"{fn_name}({left}, {right})" + + def visit_bitwise_xor_op_binary(self, binary, operator, **kw): + return self._visit_bitwise(binary, "BITXOR", **kw) + + def visit_bitwise_or_op_binary(self, binary, operator, **kw): + return self._visit_bitwise(binary, "BITOR", **kw) + + def visit_bitwise_and_op_binary(self, binary, operator, **kw): + return self._visit_bitwise(binary, "BITAND", **kw) + + def visit_bitwise_rshift_op_binary(self, binary, operator, **kw): + raise exc.CompileError("Cannot compile bitwise_rshift in oracle") + + def visit_bitwise_lshift_op_binary(self, binary, operator, **kw): + raise exc.CompileError("Cannot compile bitwise_lshift in oracle") + + def visit_bitwise_not_op_unary_operator(self, element, operator, **kw): + raise exc.CompileError("Cannot compile bitwise_not in oracle") + class OracleDDLCompiler(compiler.DDLCompiler): def define_constraint_cascades(self, constraint): diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 8e3f7a560e..04e84a68d2 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -1528,6 +1528,13 @@ class SQLiteCompiler(compiler.SQLCompiler): return "ON CONFLICT %s DO UPDATE SET %s" % (target_text, action_text) + def visit_bitwise_xor_op_binary(self, binary, operator, **kw): + # sqlite has no xor. Use "a XOR b" = "(a | b) - (a & b)". + kw["eager_grouping"] = True + or_ = self._generate_generic_binary(binary, " | ", **kw) + and_ = self._generate_generic_binary(binary, " & ", **kw) + return f"({or_} - {and_})" + class SQLiteDDLCompiler(compiler.DDLCompiler): def get_column_specification(self, column, **kwargs): diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index ee175524fb..3b53dd943f 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -1776,3 +1776,28 @@ class SuiteRequirements(Requirements): def materialized_views_reflect_pk(self): """Target database reflect MATERIALIZED VIEWs pks.""" return exclusions.closed() + + @property + def supports_bitwise_or(self): + """Target database supports bitwise or""" + return exclusions.closed() + + @property + def supports_bitwise_and(self): + """Target database supports bitwise and""" + return exclusions.closed() + + @property + def supports_bitwise_not(self): + """Target database supports bitwise not""" + return exclusions.closed() + + @property + def supports_bitwise_xor(self): + """Target database supports bitwise xor""" + return exclusions.closed() + + @property + def supports_bitwise_shift(self): + """Target database supports bitwise left or right shift""" + return exclusions.closed() diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py index 882ca45967..d81e5a04c8 100644 --- a/lib/sqlalchemy/testing/suite/test_select.py +++ b/lib/sqlalchemy/testing/suite/test_select.py @@ -1951,3 +1951,63 @@ class WindowFunctionTest(fixtures.TablesTest): ).all() eq_(result_rows, [(i,) for i in expected]) + + +class BitwiseTest(fixtures.TablesTest): + __backend__ = True + run_inserts = run_deletes = "once" + + inserted_data = [{"a": i, "b": i + 1} for i in range(10)] + + @classmethod + def define_tables(cls, metadata): + Table("bitwise", metadata, Column("a", Integer), Column("b", Integer)) + + @classmethod + def insert_data(cls, connection): + connection.execute(cls.tables.bitwise.insert(), cls.inserted_data) + + @testing.combinations( + ( + lambda a: a.bitwise_xor(5), + [i for i in range(10) if i != 5], + testing.requires.supports_bitwise_xor, + ), + ( + lambda a: a.bitwise_or(1), + list(range(10)), + testing.requires.supports_bitwise_or, + ), + ( + lambda a: a.bitwise_and(4), + list(range(4, 8)), + testing.requires.supports_bitwise_and, + ), + ( + lambda a: (a - 2).bitwise_not(), + [0], + testing.requires.supports_bitwise_not, + ), + ( + lambda a: a.bitwise_lshift(1), + list(range(1, 10)), + testing.requires.supports_bitwise_shift, + ), + ( + lambda a: a.bitwise_rshift(2), + list(range(4, 10)), + testing.requires.supports_bitwise_shift, + ), + argnames="case, expected", + ) + def test_bitwise(self, case, expected, connection): + tbl = self.tables.bitwise + + a = tbl.c.a + + op = testing.resolve_lambda(case, a=a) + + stmt = select(tbl).where(op > 0).order_by(a) + + res = connection.execute(stmt).mappings().all() + eq_(res, [self.inserted_data[i] for i in expected]) diff --git a/test/requirements.py b/test/requirements.py index 0f6fb3f0e3..9d12652de2 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -2068,3 +2068,28 @@ class DefaultRequirements(SuiteRequirements): statement. """ return only_on(["mssql"]) + + @property + def supports_bitwise_and(self): + """Target database supports bitwise and""" + return exclusions.open() + + @property + def supports_bitwise_or(self): + """Target database supports bitwise or""" + return fails_on(["oracle<21"]) + + @property + def supports_bitwise_not(self): + """Target database supports bitwise not""" + return fails_on(["oracle", "mysql", "mariadb"]) + + @property + def supports_bitwise_xor(self): + """Target database supports bitwise xor""" + return fails_on(["oracle<21"]) + + @property + def supports_bitwise_shift(self): + """Target database supports bitwise left or right shift""" + return fails_on(["oracle"]) -- 2.47.2