Merge lp:~lifeless/storm/with into lp:storm

[Robert Collins]

Implement WITH support - enough for Launchpads needs.

[Robert Collins (lifeless)]

http://www.postgresql.org/docs/9.0/static/queries-with.html for what this permits

[Gustavo Niemeyer (niemeyer)]

[1]

199 + result = self.store.with_(SQL("tmp as (select 20 as id)")).using(Foo
200 + ).find(Foo.title, SQL("foo.id in (select id from tmp)"));
201 + self.assertEqual(list(result), [u'Title 20'])

We'll have to think a bit more about the interface here. The proposed one seems
hard to reason about and will certainly yield confusion regarding the distinction
between with_() and using(), which are undistinguishable from a developer
standpoint.

review: Needs Fixing

[Robert Collins (lifeless)]

On Thu, Mar 10, 2011 at 3:16 AM, Gustavo Niemeyer <email address hidden> wrote:
> Review: Needs Fixing
> [1]
>
> 199     +        result = self.store.with_(SQL("tmp as (select 20 as id)")).using(Foo
> 200     +            ).find(Foo.title, SQL("foo.id in (select id from tmp)"));
> 201     +        self.assertEqual(list(result), [u'Title 20'])
>
> We'll have to think a bit more about the interface here.  The proposed one seems
> hard to reason about and will certainly yield confusion regarding the distinction
> between with_() and using(), which are undistinguishable from a developer
> standpoint.

we could rename using to from_ (another keyword :( ...) or perhaps
store.use_with and store.use_from or something like that.

If this patch will break any existing storm features, just let me know
and I'll fix that as a priority. I'm also happy to do modest changes
to make it fit better into storm.

We're going to run a fork in LP for the moment - we need this feature
to solve performance problems.

Cheers,
Rob

[Gustavo Niemeyer (niemeyer)]

[1]

> we could rename using to from_ (another keyword :( ...) or perhaps
> store.use_with and store.use_from or something like that.

Doesn't seem significantly better.

The WITH clause seems somewhat similar in essence to an alias, except
the definition of the alias is more than a simple rename. This means
that pretty much every location in Storm which could accept a table
can theoretically be defined with the help of a WITH clause.

I'm wondering if we might come up with a "With" type that could
optionally be defined name-less (similar to how Alias works), and be
used anywhere a real table can be.

> We're going to run a fork in LP for the moment - we need this feature
> to solve performance problems.

Please don't. We know what will happen next.

Launchpad has survived for years without this feature, so please
wait a bit longer and let's actually sort out the right way to do it.

You'll not want to go around patching code once we determine the way
that is going to be integrated, and will effectively be living with a
fork forever.

[Robert Collins (lifeless)]

On Fri, Mar 11, 2011 at 2:16 AM, Gustavo Niemeyer <email address hidden> wrote:
> [1]
>
>> we could rename using to from_ (another keyword :( ...) or perhaps
>> store.use_with and store.use_from or something like that.
>
> Doesn't seem significantly better.
>
> The WITH clause seems somewhat similar in essence to an alias, except
> the definition of the alias is more than a simple rename.  This means
> that pretty much every location in Storm which could accept a table
> can theoretically be defined with the help of a WITH clause.
>
> I'm wondering if we might come up with a "With" type that could
> optionally be defined name-less (similar to how Alias works), and be
> used anywhere a real table can be.

That would be beautiful - its outside my understanding of the plumbing
- the compiler state in particular - to make that work properly, but I
can certainly add a couple of (possibly) failing tests.

>> We're going to run a fork in LP for the moment - we need this feature
>> to solve performance problems.
>
> Please don't.  We know what will happen next.
>
> Launchpad has survived for years without this feature, so please
> wait a bit longer and let's actually sort out the right way to do it.

Launchpad has been terribly slow for years, for a number of reasons,
but one of the key things in fixing the performance issues has been
accepting that we need to solve the problem using the least-wrong
approach that is ready to hand, and *then* refactor to make the
solution have our ideal characteristics. Using this approach we've
unblocked years of inertia and made substantial performance gains for
Launchpad.

> You'll not want to go around patching code once we determine the way
> that is going to be integrated, and will effectively be living with a
> fork forever.

We'll have a couple of callsites at most, and it should be straight
forward to update them. Launchpad has run storm versions that vary
from trunk before and converged on trunk again.

In point of fact, the datetime change that is in trunk revision 386
will be more work for us to update to than a better API for doing
with.

-Rob

[Robert Collins (lifeless)]

This seems stalled. I'm told its not good enough, but I haven't been told what will be good enough, nor suggestions on how to get there.

I think this is an improvement over nothing, and it would be good to merge this with any *correctness* fixes needed, mark it as experimental in the API docs so folk don't depend on it, and then folk interested can iterate on making it really better.

[Thomas Herve (therve)]

Robert: can you give us the current code examples you have in Launchpad? It would help (me at least) to define what a good API would be.

I'm not particularly against the current branch, but it seems rather a small improvement over using store.execute directly. I think we can do better.

[Gustavo Niemeyer (niemeyer)]

You have been given concrete comments and suggestions, Robert. It's stalled because you
haven't pursued the review comments you got further, and simply stated "its outside my
understanding of the plumbing".

[James Henstridge (jamesh)]

Here's my thoughts on what Gustavo is suggesting:

1. The user should be able to create an object that acts similar to a table class to use the feature. Something like:

   WithTable = ???
   result = store.find(Foo, Foo.bar == WithTable.bar, WithTable.baz = 42)
   print list(result)

The WithTable object would compile similar to a normal table, but note that it had been used in the state object. The compile function for Select() could then generate the WITH clause if any such tables have been referenced by the query.

Generating the bulk of the with clause could be delegated to the WithTable's compile routine by calling it with a different context.

2. How do we create a WithTable? It would need the following information:
 * an alias for this temporary table.
 * a list of the column names. Do we also want the column types?
 * a query object that generates the temporary table.

What would be the most convenient way to collect this together? Perhaps something similar to ResultSet.get_select() would be convenient: it takes a column list we might be able to get column names from and produces a query object, so all it needs is a name.

3. Recursive queries could easily be handled internally to the WithTable in this model.

[Gustavo Niemeyer (niemeyer)]

> 1. The user should be able to create an object that acts
> similar to a table class to use the feature. Something like:
(...)
> The WithTable object would compile similar to a normal table,
> but note that it had been used in the state object. The compile
> function for Select() could then generate the WITH clause if any
> such tables have been referenced by the query.

Thanks James. That's exactly right.

In your example, WithTable could be defined as something like this:

    table = With("...definition...")
    WithTable = ClassAlias(cls, table)

also, normal classes could be defined with:

    table = With("...definition...")
    class Person(object):
        __storm_table__ = table

and could be used in finds in an equivalent way:

    table = With("...definition...")
    store.using(table).find(...)

> 2. How do we create a WithTable? It would need the following information:
(...)
> * a list of the column names. Do we also want the column types?

I don't think we need the column names, in the same way we don't have the
column names for normal tables. As long as it behaves like a table, we can
use the standard machinery to handle it.

[James Henstridge (jamesh)]

I guess I was misreading the syntax of the with_query, so column names are not required. For reference, here is the syntax:

  with_query_name [ ( column_name [, ...] ) ] AS ( select | insert | update | delete )

While the column list isn't required, it seems more robust to list the column names for cases where you would have duplicate names otherwise. I also don't think we'd make life any easier by automatically aliasing the columns to something like _1, _2, etc.

As far as the actual syntax goes, I'd think it common for users to use different variations on the query on each use (Robert can probably verify whether this is correct), so your second suggestion of tying the query to a class defined using standard class syntax seems a bit verbose. Something where you could link the query to an existing template class would seem more natural.

One other question Robert might have some input on: is it actually beneficial to use a full table class to represent the "with" table? They may not have a well defined primary key, and the table will be gone once the query completes, so it isn't clear you'd want to clutter the object cache with rows from the table. And are there many cases where you'd want to return objects from a "with" table from a Store.find() call, or are they just for use in the WHERE clause?

[Gustavo Niemeyer (niemeyer)]

> While the column list isn't required, it seems more robust to list the column names
> for cases where you would have duplicate names otherwise. I also don't think we'd
> make life any easier by automatically aliasing the columns to something like _1, _2, etc.

Sounds good.

Please note that I wasn't suggesting using aliases. We might simply not get into the
business of defining the query in detail, in a way similar to how we don't detail
how the CREATE TABLE is run with its column names.

But having a With type which is closer to the other types (Select, Update, etc) sounds
good too.

[Robert Collins (lifeless)]

@therve - uses of with_ so far:

http://bazaar.launchpad.net/~launchpad-pqm/launchpad/devel/view/head:/lib/lp/bugs/model/bugtask.py#L1925

http://bazaar.launchpad.net/~launchpad-pqm/launchpad/devel/view/head:/lib/lp/code/model/branchmergeproposal.py#L650

http://bazaar.launchpad.net/~launchpad-pqm/launchpad/devel/view/head:/lib/lp/blueprints/model/specification.py#L636

http://bazaar.launchpad.net/~launchpad-pqm/launchpad/devel/view/head:/lib/lp/translations/model/potmsgset.py#L449

re: I'm not particularly against the current branch, but it seems rather a small improvement over using store.execute directly. I think we can do better

The big advantage is that for the returned data we can use class expressions to get an entire class back: the filter constrains are easy to write as literal SQL; getting Storm to cast the result to objects requires using the higher level API, doesn't it?

As you can see, its a little manual at the moment, and I do like the idea of being able to define queries for more complex cases.

[Robert Collins (lifeless)]

@jamesh
>2. How do we create a WithTable? It would need the following information:
> * an alias for this temporary table.
> * a list of the column names. Do we also want the column types?
> * a query object that generates the temporary table.

there are two ways we've used WITH so far:
 * to create a constraint - e.g. we calculate all the source package names published in a distroseries in ~300ms, and then we can use that as a constraint - with joining or doing a subquery on it. (this is the bugtask use of with). The performance win here is that we know a-priori that determining package names ad-hoc is slow - *most* packagenames have been published in ubuntu always, so cheaper to do a bulk lookup.

 * to get an actual table - see the (complex) use in branchmergeproposal: we have two aliases to BranchRevision, and are doing a DAG difference between the two aliases to find new revisions in the source; the 'source' with clause *is* the origin for the BranchRevision objects we want to return. the key points here, for me is that we want to populate BranchRevision objects in the cache, not some temporary class.

I don't know if this impacts what is needed to create a WithTable.

I haven't had cause to define column names for the temporary tables yet - one will want to minimise the data in the temp table anyway - a fat temp table isn't going to be fast.

> As far as the actual syntax goes, I'd think it common for users to use different variations on > the query on each use (Robert can probably verify whether this is correct), so your second
> suggestion of tying the query to a class defined using standard class syntax seems a bit
> verbose. Something where you could link the query to an existing template class would seem
> more natural.

If I understand this correctly, then yes - see the branchmergeproposal case above.

> One other question Robert might have some input on: is it actually beneficial to use a full
> table class to represent the "with" table? They may not have a well defined primary key, and
> the table will be gone once the query completes, so it isn't clear you'd want to clutter the
> object cache with rows from the table. And are there many cases where you'd want to return
> objects from a "with" table from a Store.find() call, or are they just for use in the WHERE
> clause?

We've only 4 data points so far, but I think we'll want the objects from the with table maybe 10-25% of the time: a significant but not overwhelming amount. Most of the time it will be just a constraint or graph traversal that is best done in a very narrow fashion.

@gustavo
> Please note that I wasn't suggesting using aliases. We might simply not get into the
> business of defining the query in detail, in a way similar to how we don't detail
> how the CREATE TABLE is run with its column names.

It would be nice to use query objects to define a query in a with - I really like the sound of that.

I don't think we'd want the example you have of a full Person table defined on top of a WITH clause: unless it loaded into the storm cache in the same place our regular Person table does (which IIRC would raise errors?).

@all
I'm still at a loss on how to...

[Gustavo Niemeyer (niemeyer)]

> I don't think we'd want the example you have of a full Person table defined on
> top of a WITH clause: unless it loaded into the storm cache in the same place
> our regular Person table does (which IIRC would raise errors?).

My point is that whether you find this useful right now or not, it will just work,
because it's just another way to define a table.

> @all
> I'm still at a loss on how to code this up - it sounds nice but I need
> pointers: I got as far as I did with this on blind luck following the path of
> least resistance. I'm not asking for the code to be written for me, but I need
> to know where existing similar tests are that I can copy; how the compiler
> works so I can tell how to change it - or at least some hints pointing me in
> the appropriate directions...

It's hard to help you with plain "I don't get it. What do I do?" questions
after we provide you some guidance. We need more specific questions from
you taking into account what we already said, otherwise we'll end up just
saying the same thing again.

We can also have a voice call at some point to have a more concrete
implementation conversation, if you're finding hard to understand the
guidance we're providing and would find better to chat over it.

[James Henstridge (jamesh)]

@niemeyer
> Please note that I wasn't suggesting using aliases. We might simply not get
> into the business of defining the query in detail, in a way similar to how
> we don't detail how the CREATE TABLE is run with its column names.

While we don't really need to care about ordering or uniqueness of names for table definitions, I'm not sure that carries over to this feature. At the most basic level the WITH query will generate tuples of values, and we need names for those values in the outer query.

While we can rely on default naming in some cases, in others it is less clear. Take the following, for instance:

WITH company_employee AS (
  SELECT company.name, employee.name FROM company, employee WHERE company.id = employee.company_id)
SELECT company_employee.name FROM company_employee

What will company_employee.name refer to? What is the name of the other column? That's why I think it would be more robust to always give a column name list.

[Gustavo Niemeyer (niemeyer)]

@james

Agreed. If we are getting into the business of defining a With statement such as:

    With((a, b, c), ...)

Rather than

    With("<plain SQL>")

It'd be better to have explicit column names.

[Robert Collins (lifeless)]

On Wed, Mar 23, 2011 at 11:24 AM, Gustavo Niemeyer <email address hidden> wrote:
>
>> I don't think we'd want the example you have of a full Person table defined on
>> top of a WITH clause: unless it loaded into the storm cache in the same place
>> our regular Person table does (which IIRC would raise errors?).
>
> My point is that whether you find this useful right now or not, it will just work,
> because it's just another way to define a table.

Ok

>> @all
>> I'm still at a loss on how to code this up - it sounds nice but I need
>> pointers: I got as far as I did with this on blind luck following the path of
>> least resistance. I'm not asking for the code to be written for me, but I need
>> to know where existing similar tests are that I can copy; how the compiler
>> works so I can tell how to change it - or at least some hints pointing me in
>> the appropriate directions...
>
> It's hard to help you with plain "I don't get it. What do I do?" questions
> after we provide you some guidance.  We need more specific questions from
> you taking into account what we already said, otherwise we'll end up just
> saying the same thing again.

I don't mean to whinge, but AFAICT I haven't had any guidance : no
code pointers, design or structure pointers, other than 'something
like ClassAlias'. As a specific example; you don't like 'with_', but I
can't see any way to say to the compiler that a custom type should
compile to the front of the query - before the
SELECT/UPDATE/INSERT/DELETE.

> We can also have a voice call at some point to have a more concrete
> implementation conversation, if you're finding hard to understand the
> guidance we're providing and would find better to chat over it.

That could be very helpful - thanks. I'm timeshifted towards UK at the
moment while we work through a backlog of RT tickets impacting the LP
deployment environment, but I should have some overlap with you in
your afternoons, or in a week or so I'll be back to more NZ normal
time and have better overlap.

I've pushed up a sketch that may be what you meant, and toggled this
back to needs review.

Cheers,
Rob

[Gustavo Niemeyer (niemeyer)]

> I don't mean to whinge, but AFAICT I haven't had any guidance : no
> code pointers, design or structure pointers, other than 'something

There is a lot of information above which you've consistently
ignored. If you need details, ask for them please, and we'll
be very happy to assist.

> like ClassAlias'. As a specific example; you don't like 'with_', but I
> can't see any way to say to the compiler that a custom type should
> compile to the front of the query - before the
> SELECT/UPDATE/INSERT/DELETE.

James already answered that above:

"""
The WithTable object would compile similar to a normal table, but
note that it had been used in the state object. The compile
function for Select() could then generate the WITH clause if any
such tables have been referenced by the query.
"""

Note that tables are *already* compiled out of order in the query, so
it would be something along those lines:

1) Add with_tables to the State type, as James said
2) Make With() statements *return* the table name only (what is
   seen before a reference in a query, like "foo" so that
   "foo.column" works) during compilation
3) Make With() statements inject the WITH fragment within with_tables
   of the state during compilation
4) Make Select push and pop with_tables (see how auto_tables is handled
   there), and before popping with_tables, generate a WITH prefix in
   case it's non-empty, as James said.

> That could be very helpful - thanks. I'm timeshifted towards UK at the
> moment while we work through a backlog of RT tickets impacting the LP

I'm in Brazil, so -3 ATM.. feel free to book a time which is suitable for
you and within my awaken time (fine to be off hours).

> I've pushed up a sketch that may be what you meant, and toggled this
> back to needs review.

Ok, this is still not going in the right direction, but the above hints
might help you. Let me know if you have more questions.

review: Needs Fixing

Unmerged revisions

390. By Robert Collins on 2011-03-28

Add a compiler class to let select expressions be reused by with_.

389. By Robert Collins on 2011-03-15

And __contains__ needs special handling.

388. By Robert Collins on 2011-03-09

Oops, missed count() support.

387. By Robert Collins on 2011-03-09

Add limited WITH support to storm.

=== modified file 'storm/expr.py'
--- storm/expr.py	2011-03-15 07:54:00 +0000
+++ storm/expr.py	2011-03-28 00:10:52 +0000
@@ -636,12 +636,13 @@
 
 class Select(Expr):
     __slots__ = ("columns", "where", "tables", "default_tables", "order_by",
-                 "group_by", "limit", "offset", "distinct", "having")
+                 "group_by", "limit", "offset", "distinct", "having", "with_")
 
     def __init__(self, columns, where=Undef,
                  tables=Undef, default_tables=Undef,
                  order_by=Undef, group_by=Undef,
-                 limit=Undef, offset=Undef, distinct=False, having=Undef):
+                 limit=Undef, offset=Undef, distinct=False, having=Undef,
+                 with_=Undef):
         self.columns = columns
         self.where = where
         self.tables = tables
@@ -652,10 +653,15 @@
         self.offset = offset
         self.distinct = distinct
         self.having = having
+        self.with_ = with_
 
 @compile.when(Select)
 def compile_select(compile, select, state):
-    tokens = ["SELECT "]
+    tokens = []
+    if select.with_ is not Undef:
+        tokens.append("WITH ")
+        tokens.append(compile(select.with_, state))
+    tokens.append("SELECT ")
     if select.distinct:
         tokens.append("DISTINCT ")
     state.push("auto_tables", [])
@@ -1481,6 +1487,27 @@
 
 
 # --------------------------------------------------------------------
+# WITH clause
+
+from storm.expr import compile, Expr, SQL
+
+class With(Expr):
+    """Compiles to a simple (name AS expr) used in constructing WITH clauses."""
+
+    def __init__(self, name, select):
+        self.name = name
+        self.select = select
+
[e-mail](With)
+def compile_with(compile, with_expr, state):
+    tokens = []
+    tokens.append(compile(with_expr.name, state, token=True))
+    tokens.append(" AS ")
+    tokens.append(compile(with_expr.select, state))
+    return "".join(tokens)
+
+
+# --------------------------------------------------------------------
 # Set operator precedences.
 
 compile.set_precedence(10, Select, Insert, Update, Delete)

=== modified file 'storm/store.py'
--- storm/store.py	2011-01-05 10:19:49 +0000
+++ storm/store.py	2011-03-28 00:10:52 +0000
@@ -232,6 +232,25 @@
         """
         return self._table_set(self, tables)
 
+    def with_(self, clause):
+        """Specify a with clause for use with the query.
+
+        For instance::
+
+            store.with_(SQL("tmpname as (SELECT ...)")).find(Person,
+                SQL("Person.id in (SELECT id from tmpname)"))
+
+        will compile to
+
+             WITH tmpname as (SELECT ...)
+             SELECT Person.id FROM Person WHERE
+             PERSON.id in (SELECT id from tmpname);
+
+        @return: A L{WithSet}, which has C{find} and C{using} methods
+            similar to L{Store.find} and L{Store.using}.
+        """
+        return self._with_set(self, clause)
+
     def add(self, obj):
         """Add the given object to the store.
 
@@ -367,7 +386,6 @@
         # to test it without whitebox.
         self._order.clear()
 
-
     def _mark_autoreload(self, obj=None, invalidate=False):
         if obj is None:
             obj_infos = self._iter_alive()
@@ -908,12 +926,13 @@
     """
 
     def __init__(self, store, find_spec,
-                 where=Undef, tables=Undef, select=Undef):
+                 where=Undef, tables=Undef, select=Undef, with_=Undef):
         self._store = store
         self._find_spec = find_spec
         self._where = where
         self._tables = tables
         self._select = select
+        self._with = with_
         self._order_by = find_spec.default_order
         self._offset = Undef
         self._limit = Undef
@@ -966,7 +985,7 @@
         return Select(columns, self._where, self._tables, default_tables,
                       self._order_by, offset=self._offset, limit=self._limit,
                       distinct=self._distinct, group_by=self._group_by,
-                      having=self._having)
+                      having=self._having, with_=self._with)
 
     def _load_objects(self, result, values):
         return self._find_spec.load_objects(self._store, result, values)
@@ -1036,7 +1055,7 @@
             if self._where is not Undef:
                 where.append(self._where)
             select = Select(1, And(*where), self._tables,
-                            default_tables)
+                            default_tables, with_=self._with)
         else:
             # Rewrite the predefined query and use it as a subquery.
             aliased_columns = [Alias(column, "_key%d" % index)
@@ -1219,7 +1238,7 @@
         if (self._select is Undef and not self._distinct and
             self._offset is Undef and self._limit is Undef):
             select = Select(aggregate_func(expr), self._where,
-                            self._tables, default_tables)
+                            self._tables, default_tables, with_=self._with)
         else:
             if expr is Undef:
                 aggregate = aggregate_func(expr)
@@ -1618,9 +1637,10 @@
     This will typically be constructed by a call to L{Store.using}.
     """
 
-    def __init__(self, store, tables):
+    def __init__(self, store, tables, with_=Undef):
         self._store = store
         self._tables = tables
+        self._with = with_
 
     def find(self, cls_spec, *args, **kwargs):
         """Perform a query on the previously specified tables.
@@ -1634,12 +1654,46 @@
             self._store.flush()
         find_spec = FindSpec(cls_spec)
         where = get_where_for_args(args, kwargs, find_spec.default_cls)
-        return self._store._result_set_factory(self._store, find_spec,
-                                               where, self._tables)
+        return self._store._result_set_factory(self._store, find_spec, where,
+            self._tables, with_=self._with)
+
+
+class WithSet(object):
+    """The representation of a with clause for a query.
+
+    This is constructed by calling L{Store.with_}.
+    """
+
+    def __init__(self, store, clause):
+        self._store = store
+        self._clause = clause
+
+    def find(self, cls_spec, *args, **kwargs):
+        """Perform a query using the with clause.
+
+        This is identical to L{Store.find} except that a with clause is
+        inserted.
+
+        @return: A L{ResultSet}.
+        """
+        if self._store._implicit_flush_block_count == 0:
+            self._store.flush()
+        find_spec = FindSpec(cls_spec)
+        where = get_where_for_args(args, kwargs, find_spec.default_cls)
+        return self._store._result_set_factory(self._store, find_spec, where,
+            with_=self._clause)
+
+    def using(self, *tables):
+        """Limit the query to some tables.
+
+        See L{Store.using}.
+        """
+        return self._store._table_set(self._store, tables, self._clause)
 
 
 Store._result_set_factory = ResultSet
 Store._table_set = TableSet
+Store._with_set = WithSet
 
 
 class FindSpec(object):

=== modified file 'test'
--- test	2009-11-09 03:26:09 +0000
+++ test	2011-03-28 00:10:52 +0000
@@ -94,7 +94,7 @@
     return testpaths
 
 def test_with_runner(runner):
-    usage = "test.py [options] [<test filename>, ...]"
+    usage = "test [options] [<test filename>, ...]"
 
     parser = optparse.OptionParser(usage=usage)
 

=== modified file 'tests/store/postgres.py'
--- tests/store/postgres.py	2009-03-04 16:56:58 +0000
+++ tests/store/postgres.py	2011-03-28 00:10:52 +0000
@@ -22,6 +22,7 @@
 import gc
 
 from storm.database import create_database
+from storm.expr import Alias, With, SQL
 from storm.properties import Enum, Int, List
 from storm.info import get_obj_info
 
@@ -207,6 +208,39 @@
         self.store.flush()
         self.assertEquals(events, [])
 
+    def test_find_with_smoke(self):
+        result = self.store.with_(SQL("tmp as (select 20 as id)")).using(Foo
+            ).find(Foo.title, SQL("foo.id in (select id from tmp)"));
+        self.assertEqual(list(result), [u'Title 20'])
+
+    def test_find_with_using_smoke(self):
+        result = self.store.with_(SQL("tmp as (select 20 as id)")).find(
+            Foo.title, SQL("foo.id in (select id from tmp)"));
+        self.assertEqual(list(result), [u'Title 20'])
+
+    def test_count_with_smoke(self):
+        result = self.store.with_(SQL("tmp as (select 20 as id)")).using(Foo
+            ).find(Foo.title, SQL("foo.id in (select id from tmp)"));
+        self.assertEqual(1, result.count())
+
+    def test_count_with_distinct_smoke(self):
+        result = self.store.with_(SQL("tmp as (select 20 as id)")).using(Foo
+            ).find(Foo.title, SQL("foo.id in (select id from tmp)"));
+        result = result.config(distinct=True)
+        self.assertEqual(1, result.count())
+
+    def test_contains_with_smoke(self):
+        result = self.store.with_(SQL("tmp as (select 20 as id)")).using(Foo
+            ).find(Foo.id, SQL("foo.id in (select id from tmp)"));
+        self.assertTrue(20 in result)
+
+    def test_with_with_expr(self):
+        expr = self.store.find(Foo.id, Foo.title.like(u'Title 20'))._get_select()
+        result= self.store.with_(With('tmp', expr)).find(Foo.id,
+            SQL("foo.id in (select id from tmp)"))
+        self.assertTrue(20 in result)
+        self.assertEqual(1, result.count())
+
 
 class PostgresEmptyResultSetTest(TestHelper, EmptyResultSetTest):