From a23264e1dc43b1250b9b5de541ff27bd49a2b2c1 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Wed, 10 Sep 2014 11:33:49 +0300 Subject: tests for FILTER (WHERE ...) --- test/sql/test_compiler.py | 64 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 64 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index d47b58f1f..6e730ad50 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2190,6 +2190,70 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable" ) + def test_aggregate_filter(self): + self.assert_compile( + func.count(1).filter(), + "count(:param_1)" + ) + self.assert_compile( + func.count(1).filter( + table1.c.name != None + ), + "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)" + ) + self.assert_compile( + func.count(1).filter( + table1.c.name == None, + table1.c.myid > 0 + ), + "count(:param_1) FILTER (WHERE mytable.name IS NULL AND " + "mytable.myid > :myid_1)" + ) + + self.assert_compile( + select([func.count(1).filter( + table1.c.description != None + ).label('foo')]), + "SELECT count(:param_1) FILTER (WHERE mytable.description " + "IS NOT NULL) AS foo FROM mytable" + ) + + # test from_obj generation. + # from func: + self.assert_compile( + select([ + func.max(table1.c.name).filter( + literal_column('description') != None + ) + ]), + "SELECT max(mytable.name) FILTER (WHERE description " + "IS NOT NULL) AS anon_1 FROM mytable" + ) + # from criterion: + self.assert_compile( + select([ + func.count(1).filter( + table1.c.name == 'name' + ) + ]), + "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) " + "AS anon_1 FROM mytable" + ) + + # test chaining: + self.assert_compile( + select([ + func.count(1).filter( + table1.c.name == 'name' + ).filter( + table1.c.description == 'description' + ) + ]), + "SELECT count(:param_1) FILTER (WHERE " + "mytable.name = :name_1 AND mytable.description = :description_1) " + "AS anon_1 FROM mytable" + ) + def test_date_between(self): import datetime table = Table('dt', metadata, -- cgit v1.2.1 From a3cd517c95e5f341c711c41474de14db97bb7778 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Wed, 10 Sep 2014 12:07:38 +0300 Subject: add ClauseTest for aggregatefilter --- test/sql/test_generative.py | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py index 013ba8082..1b67ab68c 100644 --- a/test/sql/test_generative.py +++ b/test/sql/test_generative.py @@ -539,6 +539,11 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): expr2 = CloningVisitor().traverse(expr) assert str(expr) == str(expr2) + def test_aggregatefilter(self): + expr = func.count(1).filter(t1.c.col1 > 1) + expr2 = CloningVisitor().traverse(expr) + assert str(expr) == str(expr2) + def test_adapt_union(self): u = union( t1.select().where(t1.c.col1 == 4), -- cgit v1.2.1 From ab1c25266dd49f087b5fff316b6ba6fb610b1d35 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Thu, 11 Sep 2014 15:29:33 +0300 Subject: renamed aggregatefilter to funcfilter, since it is that --- test/sql/test_compiler.py | 2 +- test/sql/test_generative.py | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 6e730ad50..7bba29563 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2190,7 +2190,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable" ) - def test_aggregate_filter(self): + def test_funcfilter(self): self.assert_compile( func.count(1).filter(), "count(:param_1)" diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py index 1b67ab68c..6044cecb0 100644 --- a/test/sql/test_generative.py +++ b/test/sql/test_generative.py @@ -539,7 +539,7 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): expr2 = CloningVisitor().traverse(expr) assert str(expr) == str(expr2) - def test_aggregatefilter(self): + def test_funcfilter(self): expr = func.count(1).filter(t1.c.col1 > 1) expr2 = CloningVisitor().traverse(expr) assert str(expr) == str(expr2) -- cgit v1.2.1 From 52a095ba6675f5f5807a1dc655b4ae32b9999f27 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Thu, 11 Sep 2014 15:39:56 +0300 Subject: allow windowing filtered functions --- test/sql/test_compiler.py | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 7bba29563..fc33db184 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2254,6 +2254,33 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "AS anon_1 FROM mytable" ) + # test filtered windowing: + self.assert_compile( + select([ + func.rank().filter( + table1.c.name > 'foo' + ).over( + order_by=table1.c.name + ) + ]), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable" + ) + + self.assert_compile( + select([ + func.rank().filter( + table1.c.name > 'foo' + ).over( + order_by=table1.c.name, + partition_by=['description'] + ) + ]), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (PARTITION BY mytable.description ORDER BY mytable.name) " + "AS anon_1 FROM mytable" + ) + def test_date_between(self): import datetime table = Table('dt', metadata, -- cgit v1.2.1