From e447582b8575eaf165f02864a4b0b977930b3a52 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 1 Dec 2017 13:22:23 -0500 Subject: [PATCH] Fix regexp for expanding IN Fixed bug in new "expanding bind parameter" feature whereby if multiple params were used in one statement, the regular expression would not match the parameter name correctly. Change-Id: Ifaf7d627aac4ead2a13c8dddccb5c515253d88e6 Fixes: #4140 --- doc/build/changelog/unreleased_12/4140.rst | 7 +++ lib/sqlalchemy/dialects/oracle/cx_oracle.py | 5 ++ lib/sqlalchemy/engine/default.py | 2 +- test/dialect/oracle/test_compiler.py | 10 ++++ test/requirements.py | 7 +++ test/sql/test_query.py | 60 +++++++++++++++++++++ 6 files changed, 90 insertions(+), 1 deletion(-) create mode 100644 doc/build/changelog/unreleased_12/4140.rst diff --git a/doc/build/changelog/unreleased_12/4140.rst b/doc/build/changelog/unreleased_12/4140.rst new file mode 100644 index 0000000000..2b29b2523a --- /dev/null +++ b/doc/build/changelog/unreleased_12/4140.rst @@ -0,0 +1,7 @@ +.. change:: + :tags: bug, sql + :tickets: 4140 + + Fixed bug in new "expanding bind parameter" feature whereby if multiple + params were used in one statement, the regular expression would not + match the parameter name correctly. \ No newline at end of file diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 56a0425c83..68ecce5194 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -376,6 +376,11 @@ class OracleCompiler_cx_oracle(OracleCompiler): quote = getattr(name, 'quote', None) if quote is True or quote is not False and \ self.preparer._bindparam_requires_quotes(name): + if kw.get('expanding', False): + raise exc.CompileError( + "Can't use expanding feature with parameter name " + "%r on Oracle; it requires quoting which is not supported " + "in this context." % name) quoted_name = '"%s"' % name self._quoted_bind_names[name] = quoted_name return OracleCompiler.bindparam_string(self, quoted_name, **kw) diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index 4b9aa9493e..36344fc38b 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -779,7 +779,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext): return replacement_expressions.pop(m.group(1)) self.statement = re.sub( - r"\[EXPANDING_(.+)\]", + r"\[EXPANDING_(\S+)\]", process_expanding, self.statement ) diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py index fc310f8f25..3e1ffebb3c 100644 --- a/test/dialect/oracle/test_compiler.py +++ b/test/dialect/oracle/test_compiler.py @@ -85,6 +85,16 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): t.update().values(plain=5), 'UPDATE s SET "plain"=:"plain"' ) + def test_bindparam_quote_raise_on_expanding(self): + assert_raises_message( + exc.CompileError, + "Can't use expanding feature with parameter name 'uid' on " + "Oracle; it requires quoting which is not supported in this " + "context", + bindparam("uid", expanding=True).compile, + dialect=cx_oracle.dialect() + ) + def test_cte(self): part = table( 'part', diff --git a/test/requirements.py b/test/requirements.py index dac9494004..39a78dfa55 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -144,6 +144,13 @@ class DefaultRequirements(SuiteRequirements): """ return skip_if(["firebird", "mssql+mxodbc"], "not supported by driver") + @property + def no_quoting_special_bind_names(self): + """Target database will quote bound paramter names, doesn't support + EXPANDING""" + + return skip_if(["oracle"]) + @property def identity(self): """Target database must support GENERATED AS IDENTITY or a facsimile. diff --git a/test/sql/test_query.py b/test/sql/test_query.py index afb1137488..74efbf9a47 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -471,6 +471,66 @@ class QueryTest(fixtures.TestBase): ), [{"uname": ['fred']}, {"uname": ['ed']}] ) + @testing.requires.no_quoting_special_bind_names + def test_expanding_in_special_chars(self): + testing.db.execute( + users.insert(), + [ + dict(user_id=7, user_name='jack'), + dict(user_id=8, user_name='fred'), + ] + ) + + with testing.db.connect() as conn: + stmt = select([users]).where( + users.c.user_name.in_(bindparam('u35', expanding=True)) + ).where( + users.c.user_id == bindparam("u46") + ).order_by(users.c.user_id) + + eq_( + conn.execute( + stmt, {"u35": ['jack', 'fred'], "u46": 7}).fetchall(), + [(7, 'jack')] + ) + + stmt = select([users]).where( + users.c.user_name.in_(bindparam('u.35', expanding=True)) + ).where( + users.c.user_id == bindparam("u.46") + ).order_by(users.c.user_id) + + eq_( + conn.execute( + stmt, {"u.35": ['jack', 'fred'], "u.46": 7}).fetchall(), + [(7, 'jack')] + ) + + def test_expanding_in_multiple(self): + testing.db.execute( + users.insert(), + [ + dict(user_id=7, user_name='jack'), + dict(user_id=8, user_name='fred'), + dict(user_id=9, user_name='ed') + ] + ) + + with testing.db.connect() as conn: + stmt = select([users]).where( + users.c.user_name.in_(bindparam('uname', expanding=True)) + ).where( + users.c.user_id.in_(bindparam('userid', expanding=True)) + ).order_by(users.c.user_id) + + eq_( + conn.execute( + stmt, + {"uname": ['jack', 'fred', 'ed'], "userid": [8, 9]} + ).fetchall(), + [(8, 'fred'), (9, 'ed')] + ) + @testing.requires.tuple_in def test_expanding_in_composite(self): testing.db.execute( -- 2.47.3