Fixed #27149 -- Added Subquery and Exists database expressions.

Thanks Josh Smeaton for Oracle fixes.

3 files changed, 164 insertions, 6 deletions

diff --git a/django/db/backends/oracle/compiler.py b/django/db/backends/oracle/compiler.py
index 3ae567669f..9aa4acc0fe 100644
--- a/django/db/backends/oracle/compiler.py
+++ b/django/db/backends/oracle/compiler.py
@@ -31,10 +31,17 @@ class SQLCompiler(compiler.SQLCompiler):
             high_where = ''
             if self.query.high_mark is not None:
                 high_where = 'WHERE ROWNUM <= %d' % (self.query.high_mark,)
-            sql = (
-                'SELECT * FROM (SELECT "_SUB".*, ROWNUM AS "_RN" FROM (%s) '
-                '"_SUB" %s) WHERE "_RN" > %d' % (sql, high_where, self.query.low_mark)
-            )
+
+            if self.query.low_mark:
+                sql = (
+                    'SELECT * FROM (SELECT "_SUB".*, ROWNUM AS "_RN" FROM (%s) '
+                    '"_SUB" %s) WHERE "_RN" > %d' % (sql, high_where, self.query.low_mark)
+                )
+            else:
+                # Simplify the query to support subqueries if there's no offset.
+                sql = (
+                    'SELECT * FROM (SELECT "_SUB".* FROM (%s) "_SUB" %s)' % (sql, high_where)
+                )
 
         return sql, params
 
diff --git a/django/db/models/__init__.py b/django/db/models/__init__.py
index 225436231c..8ab11b098a 100644
--- a/django/db/models/__init__.py
+++ b/django/db/models/__init__.py
@@ -6,7 +6,8 @@ from django.db.models.deletion import (
     CASCADE, DO_NOTHING, PROTECT, SET, SET_DEFAULT, SET_NULL, ProtectedError,
 )
 from django.db.models.expressions import (
-    Case, Expression, ExpressionWrapper, F, Func, Value, When,
+    Case, Exists, Expression, ExpressionWrapper, F, Func, OuterRef, Subquery,
+    Value, When,
 )
 from django.db.models.fields import *  # NOQA
 from django.db.models.fields import __all__ as fields_all
@@ -62,7 +63,8 @@ __all__ += [
     'ObjectDoesNotExist', 'signals',
     'CASCADE', 'DO_NOTHING', 'PROTECT', 'SET', 'SET_DEFAULT', 'SET_NULL',
     'ProtectedError',
-    'Case', 'Expression', 'ExpressionWrapper', 'F', 'Func', 'Value', 'When',
+    'Case', 'Exists', 'Expression', 'ExpressionWrapper', 'F', 'Func',
+    'OuterRef', 'Subquery', 'Value', 'When',
     'FileField', 'ImageField', 'OrderWrt', 'Lookup', 'Transform', 'Manager',
     'Prefetch', 'Q', 'QuerySet', 'prefetch_related_objects', 'DEFERRED', 'Model',
     'ForeignKey', 'ForeignObject', 'OneToOneField', 'ManyToManyField',
diff --git a/django/db/models/expressions.py b/django/db/models/expressions.py
index 1ff4cd7735..36c2b969db 100644
--- a/django/db/models/expressions.py
+++ b/django/db/models/expressions.py
@@ -477,6 +477,33 @@ class F(Combinable):
         return OrderBy(self, descending=True, **kwargs)
 
 
+class ResolvedOuterRef(F):
+    """
+    An object that contains a reference to an outer query.
+
+    In this case, the reference to the outer query has been resolved because
+    the inner query has been used as a subquery.
+    """
+    def as_sql(self, *args, **kwargs):
+        raise ValueError(
+            'This queryset contains a reference to an outer query and may '
+            'only be used in a subquery.'
+        )
+
+    def _prepare(self, output_field=None):
+        return self
+
+
+class OuterRef(F):
+    def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
+        if isinstance(self.name, self.__class__):
+            return self.name
+        return ResolvedOuterRef(self.name)
+
+    def _prepare(self, output_field=None):
+        return self
+
+
 class Func(Expression):
     """
     An SQL function call.
@@ -873,6 +900,128 @@ class Case(Expression):
         return sql, sql_params
 
 
+class Subquery(Expression):
+    """
+    An explicit subquery. It may contain OuterRef() references to the outer
+    query which will be resolved when it is applied to that query.
+    """
+    template = '(%(subquery)s)'
+
+    def __init__(self, queryset, output_field=None, **extra):
+        self.queryset = queryset
+        self.extra = extra
+        if output_field is None and len(self.queryset.query.select) == 1:
+            output_field = self.queryset.query.select[0].field
+        super(Subquery, self).__init__(output_field)
+
+    def copy(self):
+        clone = super(Subquery, self).copy()
+        clone.queryset = clone.queryset.all()
+        return clone
+
+    def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
+        clone = self.copy()
+        clone.is_summary = summarize
+        clone.queryset.query.bump_prefix(query)
+
+        # Need to recursively resolve these.
+        def resolve_all(child):
+            if hasattr(child, 'children'):
+                [resolve_all(_child) for _child in child.children]
+            if hasattr(child, 'rhs'):
+                child.rhs = resolve(child.rhs)
+
+        def resolve(child):
+            if hasattr(child, 'resolve_expression'):
+                return child.resolve_expression(
+                    query=query, allow_joins=allow_joins, reuse=reuse,
+                    summarize=summarize, for_save=for_save,
+                )
+            return child
+
+        resolve_all(clone.queryset.query.where)
+
+        for key, value in clone.queryset.query.annotations.items():
+            if isinstance(value, Subquery):
+                clone.queryset.query.annotations[key] = resolve(value)
+
+        return clone
+
+    def get_source_expressions(self):
+        return [
+            x for x in [
+                getattr(expr, 'lhs', None)
+                for expr in self.queryset.query.where.children
+            ] if x
+        ]
+
+    def relabeled_clone(self, change_map):
+        clone = self.copy()
+        clone.queryset.query = clone.queryset.query.relabeled_clone(change_map)
+        clone.queryset.query.external_aliases.update(
+            alias for alias in change_map.values()
+            if alias not in clone.queryset.query.tables
+        )
+        return clone
+
+    def as_sql(self, compiler, connection, template=None, **extra_context):
+        connection.ops.check_expression_support(self)
+        template_params = self.extra.copy()
+        template_params.update(extra_context)
+        template_params['subquery'], sql_params = self.queryset.query.get_compiler(connection=connection).as_sql()
+
+        template = template or template_params.get('template', self.template)
+        sql = template % template_params
+        sql = connection.ops.unification_cast_sql(self.output_field) % sql
+        return sql, sql_params
+
+    def _prepare(self, output_field):
+        # This method will only be called if this instance is the "rhs" in an
+        # expression: the wrapping () must be removed (as the expression that
+        # contains this will provide them). SQLite evaluates ((subquery))
+        # differently than the other databases.
+        if self.template == '(%(subquery)s)':
+            clone = self.copy()
+            clone.template = '%(subquery)s'
+            return clone
+        return self
+
+
+class Exists(Subquery):
+    template = 'EXISTS(%(subquery)s)'
+
+    def __init__(self, *args, **kwargs):
+        self.negated = kwargs.pop('negated', False)
+        super(Exists, self).__init__(*args, **kwargs)
+
+    def __invert__(self):
+        return type(self)(self.queryset, self.output_field, negated=(not self.negated), **self.extra)
+
+    @property
+    def output_field(self):
+        return fields.BooleanField()
+
+    def resolve_expression(self, query=None, **kwargs):
+        # As a performance optimization, remove ordering since EXISTS doesn't
+        # care about it, just whether or not a row matches.
+        self.queryset = self.queryset.order_by()
+        return super(Exists, self).resolve_expression(query, **kwargs)
+
+    def as_sql(self, compiler, connection, template=None, **extra_context):
+        sql, params = super(Exists, self).as_sql(compiler, connection, template, **extra_context)
+        if self.negated:
+            sql = 'NOT {}'.format(sql)
+        return sql, params
+
+    def as_oracle(self, compiler, connection, template=None, **extra_context):
+        # Oracle doesn't allow EXISTS() in the SELECT list, so wrap it with a
+        # CASE WHEN expression. Change the template since the When expression
+        # requires a left hand side (column) to compare against.
+        sql, params = self.as_sql(compiler, connection, template, **extra_context)
+        sql = 'CASE WHEN {} THEN 1 ELSE 0 END'.format(sql)
+        return sql, params
+
+
 class OrderBy(BaseExpression):
     template = '%(expression)s %(ordering)s'