Repository: kayak/pypika (python)

Issue:
Allow filtering by non existant table
When creating a subquery, often I'd like to reference the parent table id.
However this validation code https://github.com/kayak/pypika/blob/master/pypika/queries.py#L718 prevents that.

For a contrived example

```
Teacher, School = Tables("teacher", "school")
Query.from_(School).select(
 Query.from_(Teacher).select(Teacher.name).where(
  Teacher.current_school_id==School.id
  ).limit(1).as_('a_teacher')
)
```

Here Teacher is referencing the parent 'School' table and the validation would fail.

My use case is generally including a JSONB array of tags or similar from the result of a subquery in the parent.

I have worked around this by adding the table, filtering the subquery, then removing the table. Kind of like a decorate / action / undecorate pattern.

Options I can think of for solving this:
1. A flag that would disable this particular validation check (but still allow others);
2. An API for a validations framework where validations can be added and removed;
3. A way of telling a subquery about a known table, that would pass this validation and have no effect on output SQL otherwise; and
4. Continue the decorate / action /undecorate pattern in our code.

I'm happy to develop a PR if any of these, or another option excites you.

Pull request:
Removes exception raising validate_term
Fixes #227 to allow correlated subqueries.

Removing table validation was not enough. Because most subqueries which reference a parent table would result in an ambiguous query and need the full namespace when referring to the fields. (e.g. imaging joining on "id" without the full table namespace)

The only mechanism for invoking the full namespace previously was an entry in the `from_` field or `joins` field and neither fit this.

So I retained the validation, and used it to determine when to switch table namespaces on.

An alternative I considered was to always use table namespaces - for every query, but I figured that is outside the scope of this PR.

Patch:
diff --git a/README.rst b/README.rst
index 91ef64f..b5af7e1 100644
--- a/README.rst
+++ b/README.rst
@@ -414,6 +414,31 @@ Example of a join using `USING`
     SELECT "history".* FROM "history" JOIN "customers" USING "customer_id" WHERE "customers"."id"=5
 
 
+Example of a correlated subquery in the `SELECT`
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code-block:: python
+    history, customers = Tables('history', 'customers')
+    last_purchase_at = Query.from_(history).select(
+        history.purchase_at
+    ).where(history.customer_id==customers.customer_id).orderby(
+        history.purchase_at, order=Order.desc
+    ).limit(1)
+    q = Query.from_(customers).select(
+        customers.id, last_purchase_at._as('last_purchase_at')
+    )
+.. code-block:: sql
+
+    SELECT
+      "id",
+      (SELECT "history"."purchase_at"
+       FROM "history"
+       WHERE "history"."customer_id" = "customers"."customer_id"
+       ORDER BY "history"."purchase_at" DESC
+       LIMIT 1) "last_purchase_at"
+    FROM "customers"
+
+
 Unions
 """"""
 
diff --git a/pypika/queries.py b/pypika/queries.py
index d6e8df0..2242ac5 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -388,6 +388,7 @@ class QueryBuilder(Selectable, Term):
         self._select_into = False
 
         self._subquery_count = 0
+        self._foreign_table = False
 
         self.quote_char = quote_char
         self.dialect = dialect
@@ -516,7 +517,8 @@ class QueryBuilder(Selectable, Term):
 
     @builder
     def prewhere(self, criterion):
-        self._validate_term(criterion)
+        if not self._validate_table(criterion):
+            self._foreign_table = True
 
         if self._prewheres:
             self._prewheres &= criterion
@@ -528,7 +530,8 @@ class QueryBuilder(Selectable, Term):
         if isinstance(criterion, EmptyCriterion):
             return
 
-        self._validate_term(criterion)
+        if not self._validate_table(criterion):
+            self._foreign_table = True
 
         if self._wheres:
             self._wheres &= criterion
@@ -537,8 +540,6 @@ class QueryBuilder(Selectable, Term):
 
     @builder
     def having(self, criterion):
-        self._validate_term(criterion)
-
         if self._havings:
             self._havings &= criterion
         else:
@@ -552,7 +553,6 @@ class QueryBuilder(Selectable, Term):
             elif isinstance(term, int):
                 term = Field(str(term), table=self._from[0]).wrap_constant(term)
 
-            self._validate_term(term)
             self._groupbys.append(term)
 
     @builder
@@ -664,8 +664,6 @@ class QueryBuilder(Selectable, Term):
             # Do not add select terms after a star is selected
             return
 
-        self._validate_term(term)
-
         if term.table in self._select_star_tables:
             # Do not add select terms for table after a table star is selected
             return
@@ -679,7 +677,6 @@ class QueryBuilder(Selectable, Term):
         self._selects.append(term)
 
     def _select_other(self, function):
-        self._validate_term(function)
         self._selects.append(function)
 
     def fields(self):
@@ -704,7 +701,11 @@ class QueryBuilder(Selectable, Term):
 
         self._joins.append(join)
 
-    def _validate_term(self, term):
+    def _validate_table(self, term):
+        """
+        Returns False if the term references a table not already part of the
+        FROM clause or JOINS and True otherwise.
+        """
         base_tables = self._from + [self._update_table]
 
         for field in term.fields():
@@ -714,9 +715,8 @@ class QueryBuilder(Selectable, Term):
                   and not table_in_base_tables \
                   and not table_in_joins \
                   and field.table != self._update_table:
-                raise JoinException('Table [%s] missing from query.  '
-                                    'Table must be first joined before any of '
-                                    'its fields can be used' % field.table)
+                return False
+        return True
 
     def _tag_subquery(self, subquery):
         subquery.alias = 'sq%d' % self._subquery_count
@@ -756,8 +756,14 @@ class QueryBuilder(Selectable, Term):
         has_joins = bool(self._joins)
         has_multiple_from_clauses = 1 < len(self._from)
         has_subquery_from_clause = 0 < len(self._from) and isinstance(self._from[0], QueryBuilder)
+        has_reference_to_foreign_table = self._foreign_table
 
-        kwargs['with_namespace'] = any((has_joins, has_multiple_from_clauses, has_subquery_from_clause))
+        kwargs['with_namespace'] = any((
+            has_joins,
+            has_multiple_from_clauses,
+            has_subquery_from_clause,
+            has_reference_to_foreign_table
+        ))
 
         if self._update_table:
             querystring = self._update_sql(**kwargs)


Test patch:
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index 4b4ff1a..466c016 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -276,23 +276,34 @@ class JoinBehaviorTests(unittest.TestCase):
         self.assertEqual('SELECT "foo" FROM "abc"', str(query1))
 
     def test_select_field_from_missing_table(self):
-        with self.assertRaises(JoinException):
-            Query.from_(self.table_abc).select(self.table_efg.foo)
+        """
+        Previously we validated the select field was a member of a table
+        in the query (e.g. FROM clause or a JOIN). However this made
+        correlated subqueries awkward to write (subqueries which refer
+        to their parent table. So the validation was removed.
 
-        with self.assertRaises(JoinException):
-            Query.from_(self.table_abc).where(self.table_efg.foo == 0)
+        None of the following raise an error. Previously raised JoinException
+        """
+        Query.from_(self.table_abc).select(self.table_efg.foo)
 
-        with self.assertRaises(JoinException):
-            Query.from_(self.table_abc).where(fn.Sum(self.table_efg.foo) == 0)
+        Query.from_(self.table_abc).where(self.table_efg.foo == 0)
 
-        with self.assertRaises(JoinException):
-            Query.from_(self.table_abc).select(fn.Sum(self.table_abc.bar * 2) + fn.Sum(self.table_efg.foo * 2))
+        Query.from_(self.table_abc).where(fn.Sum(self.table_efg.foo) == 0)
 
-        with self.assertRaises(JoinException):
-            Query.from_(self.table_abc).groupby(self.table_efg.foo)
+        Query.from_(self.table_abc).select(fn.Sum(self.table_abc.bar * 2) + fn.Sum(self.table_efg.foo * 2))
 
-        with self.assertRaises(JoinException):
-            Query.from_(self.table_abc).groupby(self.table_abc.foo).having(self.table_efg.bar)
+        Query.from_(self.table_abc).groupby(self.table_efg.foo)
+
+        Query.from_(self.table_abc).groupby(self.table_abc.foo).having(self.table_efg.bar)
+
+        subquery = Query.from_(self.table_efg).select(
+            self.table_efg.id
+        ).where(self.table_efg.abc_id == self.table_abc.id)
+        query = Query.from_(self.table_abc).select(subquery.as_('efg_id').limit(1))
+        self.assertEqual(
+            'SELECT (SELECT "efg"."id" FROM "efg" WHERE "efg"."abc_id"="abc"."id" LIMIT 1) "efg_id" FROM "abc"',
+            str(query)
+        )
 
     def test_ignore_table_references(self):
         query = Query.from_(Table('abc')).select(Table('abc').foo)