From f3b6f4f8da5223fae0a1dd948d4266b2e49e317c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 14 Mar 2017 12:00:56 -0400 Subject: Add "empty in" strategies; default to "static" The longstanding behavior of the :meth:`.Operators.in_` and :meth:`.Operators.not_in_` operators emitting a warning when the right-hand condition is an empty sequence has been revised; a new flag :paramref:`.create_engine.empty_in_strategy` allows an empty "IN" expression to generate a simple boolean expression, or to invoke the previous behavior of dis-equating the expression to itself, with or without a warning. The default behavior is now to emit the simple boolean expression, allowing an empty IN to be evaulated without any performance penalty. Change-Id: I65cc37f2d7cf65a59bf217136c42fee446929352 Fixes: #3907 --- test/sql/test_compiler.py | 8 ++++-- test/sql/test_operators.py | 60 +++++++++++++++++++++++++++++++------- test/sql/test_query.py | 72 ++++++++++++++++++++++++++++++++++------------ 3 files changed, 108 insertions(+), 32 deletions(-) (limited to 'test') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index e65db2a36..8b19b8931 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -1386,7 +1386,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): dialect=mysql.dialect() ) - @testing.emits_warning('.*empty sequence.*') def test_render_binds_as_literal(self): """test a compiler that renders binds inline into SQL in the columns clause.""" @@ -1423,7 +1422,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( select([literal("foo").in_([])]), - "SELECT 'foo' != 'foo' AS anon_1", + "SELECT 1 != 1 AS anon_1", dialect=dialect ) @@ -1440,13 +1439,16 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): dialect=dialect ) + empty_in_dialect = default.DefaultDialect(empty_in_strategy='dynamic') + empty_in_dialect.statement_compiler = Compiler + assert_raises_message( exc.CompileError, "Bind parameter 'foo' without a " "renderable value not allowed here.", bindparam("foo").in_( []).compile, - dialect=dialect) + dialect=empty_in_dialect) def test_literal(self): diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 0bdebab58..0e0a8b29c 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -1638,6 +1638,11 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL): column('othername', String) ) + def _dialect(self, empty_in_strategy="static"): + return default.DefaultDialect( + empty_in_strategy=empty_in_strategy + ) + def test_in_1(self): self.assert_compile(self.table1.c.myid.in_(['a']), "mytable.myid IN (:myid_1)") @@ -1751,11 +1756,6 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL): "FROM myothertable)" ) - @testing.emits_warning('.*empty sequence.*') - def test_in_23(self): - self.assert_compile(self.table1.c.myid.in_([]), - "mytable.myid != mytable.myid") - def test_in_24(self): self.assert_compile( select([self.table1.c.myid.in_(select([self.table2.c.otherid]))]), @@ -1812,15 +1812,53 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL): "mytable.myid IN (NULL)" ) - @testing.emits_warning('.*empty sequence.*') - def test_in_29(self): + def test_empty_in_dynamic_1(self): + self.assert_compile(self.table1.c.myid.in_([]), + "mytable.myid != mytable.myid", + dialect=self._dialect("dynamic")) + + def test_empty_in_dynamic_2(self): + self.assert_compile(self.table1.c.myid.notin_([]), + "mytable.myid = mytable.myid", + dialect=self._dialect("dynamic")) + + def test_empty_in_dynamic_3(self): + self.assert_compile(~self.table1.c.myid.in_([]), + "mytable.myid = mytable.myid", + dialect=self._dialect("dynamic")) + + def test_empty_in_dynamic_warn_1(self): + with testing.expect_warnings( + "The IN-predicate was invoked with an empty sequence."): + self.assert_compile(self.table1.c.myid.in_([]), + "mytable.myid != mytable.myid", + dialect=self._dialect("dynamic_warn")) + + def test_empty_in_dynamic_warn_2(self): + with testing.expect_warnings( + "The IN-predicate was invoked with an empty sequence."): + self.assert_compile(self.table1.c.myid.notin_([]), + "mytable.myid = mytable.myid", + dialect=self._dialect("dynamic_warn")) + + def test_empty_in_dynamic_warn_3(self): + with testing.expect_warnings( + "The IN-predicate was invoked with an empty sequence."): + self.assert_compile(~self.table1.c.myid.in_([]), + "mytable.myid = mytable.myid", + dialect=self._dialect("dynamic_warn")) + + def test_empty_in_static_1(self): + self.assert_compile(self.table1.c.myid.in_([]), + "1 != 1") + + def test_empty_in_static_2(self): self.assert_compile(self.table1.c.myid.notin_([]), - "mytable.myid = mytable.myid") + "1 = 1") - @testing.emits_warning('.*empty sequence.*') - def test_in_30(self): + def test_empty_in_static_3(self): self.assert_compile(~self.table1.c.myid.in_([]), - "mytable.myid = mytable.myid") + "1 = 1") class MathOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL): diff --git a/test/sql/test_query.py b/test/sql/test_query.py index bc9a176f1..d90cb0476 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -420,8 +420,7 @@ class QueryTest(fixtures.TestBase): s = users.select(not_(users.c.user_name.in_([]))) r = s.execute().fetchall() - # All usernames with a value are outside an empty set - assert len(r) == 2 + assert len(r) == 3 s = users.select(users.c.user_name.in_(['jack', 'fred'])) r = s.execute().fetchall() @@ -432,7 +431,6 @@ class QueryTest(fixtures.TestBase): # Null values are not outside any set assert len(r) == 0 - @testing.emits_warning('.*empty sequence.*') @testing.fails_on('firebird', "uses sql-92 rules") @testing.fails_on('sybase', "uses sql-92 rules") @testing.fails_if( @@ -456,7 +454,7 @@ class QueryTest(fixtures.TestBase): r = s.execute(search_key='john').fetchall() assert len(r) == 3 r = s.execute(search_key=None).fetchall() - assert len(r) == 0 + assert len(r) == 3 @testing.emits_warning('.*empty sequence.*') def test_literal_in(self): @@ -470,28 +468,66 @@ class QueryTest(fixtures.TestBase): r = s.execute().fetchall() assert len(r) == 3 - @testing.emits_warning('.*empty sequence.*') @testing.requires.boolean_col_expressions - def test_in_filtering_advanced(self): + def test_empty_in_filtering_static(self): """test the behavior of the in_() function when comparing against an empty collection, specifically that a proper boolean value is generated. """ - users.insert().execute(user_id=7, user_name='jack') - users.insert().execute(user_id=8, user_name='fred') - users.insert().execute(user_id=9, user_name=None) + with testing.db.connect() as conn: + conn.execute( + users.insert(), + [ + {'user_id': 7, 'user_name': 'jack'}, + {'user_id': 8, 'user_name': 'ed'}, + {'user_id': 9, 'user_name': None} + ] + ) - s = users.select(users.c.user_name.in_([]) == True) # noqa - r = s.execute().fetchall() - assert len(r) == 0 - s = users.select(users.c.user_name.in_([]) == False) # noqa - r = s.execute().fetchall() - assert len(r) == 2 - s = users.select(users.c.user_name.in_([]) == None) # noqa - r = s.execute().fetchall() - assert len(r) == 1 + s = users.select(users.c.user_name.in_([]) == True) # noqa + r = conn.execute(s).fetchall() + assert len(r) == 0 + s = users.select(users.c.user_name.in_([]) == False) # noqa + r = conn.execute(s).fetchall() + assert len(r) == 3 + s = users.select(users.c.user_name.in_([]) == None) # noqa + r = conn.execute(s).fetchall() + assert len(r) == 0 + + @testing.requires.boolean_col_expressions + def test_empty_in_filtering_dynamic(self): + """test the behavior of the in_() function when + comparing against an empty collection, specifically + that a proper boolean value is generated. + + """ + + engine = engines.testing_engine( + options={"empty_in_strategy": "dynamic"}) + + with engine.connect() as conn: + users.create(engine, checkfirst=True) + + conn.execute( + users.insert(), + [ + {'user_id': 7, 'user_name': 'jack'}, + {'user_id': 8, 'user_name': 'ed'}, + {'user_id': 9, 'user_name': None} + ] + ) + + s = users.select(users.c.user_name.in_([]) == True) # noqa + r = conn.execute(s).fetchall() + assert len(r) == 0 + s = users.select(users.c.user_name.in_([]) == False) # noqa + r = conn.execute(s).fetchall() + assert len(r) == 2 + s = users.select(users.c.user_name.in_([]) == None) # noqa + r = conn.execute(s).fetchall() + assert len(r) == 1 class RequiredBindTest(fixtures.TablesTest): -- cgit v1.2.1