Repository: geldata/gel
Pull request: SQL resolver
Closes #4540 

Also a few fixes to `pgsql.ast` and `pgsql.parser`

Problem statement:
SQL name resolution
For the upcoming SQL support, we essentially need to implement a basic SQL name resolver. We will not support all of the SQL, but we need following features:

(using the Movies schema)
```sql
-- map Movie to our ObjectType
-- map Movie.title to the correct property
SELECT Movie.title FROM Movie;

-- figure out that property title comes from ObjectType Movie
SELECT title FROM Movie;

-- case insentive by default, lowercase when strictly-typed
SELECT tItLe, `relase_year` FROM `movie`;

-- table aliases
SELECT m.title FROM Movie AS m;

-- resolve <link>_id
SELECT title FROM Movie JOIN Genre ON Movie.genre_id = Genre.id

-- resolve <parent_type>_id
SELECT c.title, m.release_year FROM Content c JOIN Movie m ON m.content_id = c.id

-- infer subquery types
SELECT title, t.name FROM (SELECT *, title as name FROM Movie) t JOIN Genre ON ...
```

## Inheritance

We have to decide what to do when selecting from a parent object. Should rows of child tables be included?

@1st1 says no, because SQL is low level and we should expose only this low-level data.

@msullivan says that we should at least provide some syntax to allow access to the inheriting views we already have so the SQL interface is not such a pain in the ass to use.

I think that we should avoid having two different syntaxes (or any kind of table name suffixes), because this adds much more surface for bugs and confusion. 

While it would be convenient to expose inheriting views, the exposed database would not be normalized so I agree with Yury to stick only to base tables.

## Names of multi-multi link tables

```
type Movie {
    multi link actors -> Person {
      property character_name -> str;
    };
}
type Person {
    link filmography := .<actors[is Content];
}
```

These tables do not have a name in EdgeQL, but need one in SQL. I suggest `movie_actors_person_filmography`:
- object name where link is defined,
- link name on that object
- object name where link is referenced,
- link name on that object.

Hints from issue discussion:
For many-to-many links, I don't think we need to include anything but `Movie` and `actors` in the table name. That fully identifies the link. The `filmography` computed backlink is helpful for writing edgeql queries, but is not fundamental.

So I think it should be named `Movie_actors`, or `"Movie.actors"` if we are willing to require quoting.
I also tend towards thinking that rows of child tables *should* be included by default. We can use the `ONLY` keyword to suppress that.

(Though it might also be worth doing a bit of investigation about any tools we expect to be automatically generating these SQL queries, and what their capabilities are.)
For single links, my inclination is to just name the column the same thing as the link, instead of adding `_id` to the end?
Yeah, I'd avoid adding suffixes and prefixes to names unless absolutely necessary.
Also, regardless of whether we include subtypes, we don't need the `-- resolve <parent_type>_id` thing. All of our tables *include* all the properties from parent types
@msullivan 
> Also, regardless of whether we include subtypes, we don't need the -- resolve <parent_type>_id thing. All of our tables include all the properties from parent types

If we decide to include subtypes, you may want to select all Content but to also have properties from Movies (for those objects that are a Movie). You can't do that with UNION, because rows would be duplicated and columns would not match.

If we decide not to include subtypes, you also need a way to get all Content subtypes instances.

The more I think about it, the more I lean toward exposing the normalized database. No inheritance, no showing the same values in two different tables.

This will also inflict the appreciation for all the work EdgeDB is doing over the low-level SQL database.

---

> For many-to-many links ...

Ok, agree `Movie_actors` is probably best.
Like we discussed offline, we need to duplicate pointers from parent objects in the children type--that is how we store it in edgedb, with each object in one table.

Implementation work doesn't need to wait on a final decision on inheritance; it shouldn't be hard to change

Commit message: return rel (current table) from functions instead of keeping in context

Patch to review:
diff --git a/edb/pgsql/ast.py b/edb/pgsql/ast.py
index 0b2c62798ae..662a3ecb571 100644
--- a/edb/pgsql/ast.py
+++ b/edb/pgsql/ast.py
@@ -183,7 +183,11 @@ def get_path_outputs(self, flavor: str) -> typing.Dict[
 
 
 class BaseRangeVar(ImmutableBaseExpr):
-    """Range variable, used in FROM clauses."""
+    """
+    Range variable, used in FROM clauses.
+    
+    This can be though as a specific instance of a table within a query.
+    """
 
     __ast_meta__ = {'schema_object_id', 'tag'}
 
@@ -209,12 +213,16 @@ def __repr__(self) -> str:
 
 
 class BaseRelation(EdgeQLPathInfo, BaseExpr):
+    """
+    A relation-valued (table-valued) expression.
+    """
+
     name: typing.Optional[str] = None
     nullable: typing.Optional[bool] = None  # Whether the result can be NULL.
 
 
 class Relation(BaseRelation):
-    """Regular relation."""
+    """A reference to a table or a view."""
 
     catalogname: typing.Optional[str] = None
     schemaname: typing.Optional[str] = None
@@ -409,7 +417,7 @@ class ResTarget(ImmutableBaseExpr):
     # Column name (optional)
     name: typing.Optional[str] = None
     # subscripts, field names and '*'
-    indirection: typing.Optional[list] = None
+    indirection: typing.Optional[typing.List[IndirectionOp]] = None
     # value expression to compute
     val: BaseExpr
 
@@ -574,9 +582,7 @@ class DeleteStmt(DMLQuery):
 class SelectStmt(Query):
 
     # List of DISTINCT ON expressions, empty list for DISTINCT ALL
-    distinct_clause: typing.Optional[list] = None
-    # The target list
-    target_list: typing.List[ResTarget] = ast.field(factory=list)
+    distinct_clause: typing.Optional[typing.List[OutputVar]] = None
     # The FROM clause
     from_clause: typing.List[BaseRangeVar] = ast.field(factory=list)
     # The WHERE clause
@@ -711,9 +717,9 @@ class FuncCall(ImmutableBaseExpr):
     # List of arguments
     args: typing.List[BaseExpr]
     # ORDER BY
-    agg_order: typing.List[SortBy]
+    agg_order: typing.Optional[typing.List[SortBy]]
     # FILTER clause
-    agg_filter: BaseExpr
+    agg_filter: typing.Optional[BaseExpr]
     # Argument list is '*'
     agg_star: bool
     # Arguments were labeled DISTINCT
diff --git a/edb/pgsql/common.py b/edb/pgsql/common.py
index 46e41ded068..11f9cd944f2 100644
--- a/edb/pgsql/common.py
+++ b/edb/pgsql/common.py
@@ -25,6 +25,7 @@
 import base64
 import re
 from typing import *
+import uuid
 
 from edb.common import uuidgen
 from edb.schema import abc as s_abc
@@ -96,7 +97,7 @@ def needs_quoting(string: str) -> bool:
     )
 
 
-def qname(*parts: str | pgast.Star):
+def qname(*parts: str | pgast.Star) -> str:
     assert len(parts) <= 3, parts
     return '.'.join([quote_ident(q) for q in parts])
 
@@ -176,7 +177,7 @@ def edgedb_name_to_pg_name(name: str, prefix_length: int = 0) -> str:
     return _edgedb_name_to_pg_name(name, prefix_length)
 
 
-def convert_name(name, suffix='', catenate=True):
+def convert_name(name: s_name.QualName, suffix='', catenate=True):
     schema = get_module_backend_name(name.get_module_name())
     if suffix:
         sname = f'{name.name}_{suffix}'
@@ -233,7 +234,13 @@ def get_aspect_suffix(aspect):
         return aspect
 
 
-def get_objtype_backend_name(id, module_name, *, catenate=True, aspect=None):
+def get_objtype_backend_name(
+    id: uuid.UUID,
+    module_name: str,
+    *,
+    catenate: bool = True,
+    aspect: str = None,
+):
     if aspect is None:
         aspect = 'table'
     if aspect not in {'table', 'inhview'} and not re.match(
diff --git a/edb/pgsql/parser/ast_builder.py b/edb/pgsql/parser/ast_builder.py
index 104594f6b05..fa8c0adaf2d 100644
--- a/edb/pgsql/parser/ast_builder.py
+++ b/edb/pgsql/parser/ast_builder.py
@@ -158,7 +158,7 @@ def _build_select_stmt(n: Node, c: Context) -> pgast.SelectStmt:
         if op == "NONE":
             op = None
     return pgast.SelectStmt(
-        distinct_clause=_maybe_list(n, c, "distinct_clause", _build_any),
+        distinct_clause=_maybe(n, c, "distinctClause", _build_distinct),
         target_list=_maybe_list(n, c, "targetList", _build_res_target) or [],
         from_clause=_maybe_list(n, c, "fromClause", _build_base_range_var)
         or [],
@@ -230,6 +230,7 @@ def _build_base_expr(node: Node, c: Context) -> pgast.BaseExpr:
         {
             "ResTarget": _build_res_target,
             "FuncCall": _build_func_call,
+            "CoalesceExpr": _build_coalesce,
             "List": _build_implicit_row,
             "A_Expr": _build_a_expr,
             "A_ArrayExpr": _build_array_expr,
@@ -248,6 +249,14 @@ def _build_base_expr(node: Node, c: Context) -> pgast.BaseExpr:
     )
 
 
+def _build_distinct(nodes: List[Node], c: Context) -> List[pgast.Base]:
+    # For some reason, plain DISTINCT is parsed as [{}]
+    # In our AST this is represented by [pgast.Star()]
+    if len(nodes) == 1 and len(nodes[0]) == 0:
+        return [pgast.Star()]
+    return [_build_base_expr(n, c) for n in nodes]
+
+
 def _build_indirection_op(n: Node, c: Context) -> pgast.IndirectionOp:
     return _enum(
         n,
@@ -480,6 +489,8 @@ def _build_alias(n: Node, c: Context) -> pgast.Alias:
 def _build_base_relation(n: Node, c: Context) -> pgast.BaseRelation:
     return pgast.Relation(
         name=_maybe(n, c, "relname", _build_str),
+        catalogname=_maybe(n, c, "catalogname", _build_str),
+        schemaname=_maybe(n, c, "schemaname", _build_str),
         context=_build_context(n, c),
     )
 
@@ -542,6 +553,12 @@ def _build_func_call(n: Node, c: Context) -> pgast.FuncCall:
     )
 
 
+def _build_coalesce(n: Node, c: Context) -> pgast.CoalesceExpr:
+    return pgast.CoalesceExpr(
+        args=_list(n, c, "args", _build_base_expr),
+    )
+
+
 def _build_index_or_slice(n: Node, c: Context) -> pgast.Slice | pgast.Index:
     if n['is_slice']:
         return pgast.Slice(
diff --git a/edb/pgsql/resolver/__init__.py b/edb/pgsql/resolver/__init__.py
new file mode 100644
index 00000000000..40968eefce1
--- /dev/null
+++ b/edb/pgsql/resolver/__init__.py
@@ -0,0 +1,40 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2008-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+from __future__ import annotations
+
+from edb.pgsql import ast as pgast
+from edb.schema import schema as s_schema
+
+from . import dispatch
+from . import context
+from . import expr # NOQA
+from . import relation # NOQA
+
+
+def resolve(
+    query: pgast.Base,
+    schema: s_schema.Schema,
+) -> pgast.Base:
+    ctx = context.ResolverContextLevel(
+        None, context.ContextSwitchMode.EMPTY, schema=schema
+    )
+
+    _ = context.ResolverContext(initial=ctx)
+
+    return dispatch.resolve(query, ctx=ctx)
diff --git a/edb/pgsql/resolver/context.py b/edb/pgsql/resolver/context.py
new file mode 100644
index 00000000000..d587fb8f897
--- /dev/null
+++ b/edb/pgsql/resolver/context.py
@@ -0,0 +1,166 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2008-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+from __future__ import annotations
+
+from copy import deepcopy
+from typing import *
+import enum
+
+from edb.common import compiler
+from edb.schema import schema as s_schema
+
+
+class Scope:
+    """
+    Information about that objects are visible at a specific point in an
+    SQL query.
+
+    Scope is modified during resolving of a query, when new tables are
+    discovered in FROM or JOIN or new columns declared in SELECT's projection.
+
+    After a query is done resolving, resulting relations are extracted from its
+    scope and inserted into parent scope.
+    """
+
+    # RangeVars (table instances) in this query
+    tables: List[Table]
+
+    # Common Table Expressions 
+    ctes: List[CTE]
+
+    def __init__(self):
+        self.tables = []
+        self.ctes = []
+
+
+class Table:
+
+    # Public SQL
+    name: Optional[str] = None
+    alias: Optional[str] = None
+
+    columns: List[Column]
+
+    # Internal SQL
+    reference_as: Optional[str] = None
+
+    def __init__(self):
+        self.columns = []
+
+    def __str__(self) -> str:
+        columns = ', '.join(str(c) for c in self.columns)
+        alias = f'{self.alias} = ' if self.alias else ''
+        return f'{alias}{self.name or "<unnamed>"}({columns})'
+
+
+class CTE:
+    name: Optional[str] = None
+    columns: List[Column]
+
+    def __init__(self):
+        self.columns = []
+
+
+class Column:
+    # Public SQL
+    name: Optional[str] = None
+
+    # Internal SQL
+    reference_as: Optional[str] = None
+
+    def __init__(
+        self, name: Optional[str] = None, reference_as: Optional[str] = None
+    ):
+        self.name = name
+        self.reference_as = reference_as
+
+    def __str__(self) -> str:
+        return self.name or '<unnamed>'
+
+
+class NameGenerator:
+    next_rel_index = 0
+
+    def generate_relation(self) -> str:
+        name = f'_rel_{self.next_rel_index}'
+        self.next_rel_index += 1
+        return name
+
+
+class ContextSwitchMode(enum.Enum):
+    EMPTY = enum.auto()
+    ISOLATED = enum.auto()
+
+
+class ResolverContextLevel(compiler.ContextLevel):
+    schema: s_schema.Schema
+    names: compiler.AliasGenerator
+
+    scope: Scope
+    """Visible names in scope"""
+
+    include_inherited: bool
+    """
+    True iff relation currently resolving should also include instances of
+    child objects.
+    """
+
+    def __init__(
+        self,
+        prevlevel: Optional[ResolverContextLevel],
+        mode: ContextSwitchMode,
+        *,
+        schema: Optional[s_schema.Schema] = None,
+    ) -> None:
+        if prevlevel is None:
+            assert schema is not None
+
+            self.schema = schema
+            self.scope = Scope()
+            self.include_inherited = True
+            self.names = compiler.AliasGenerator()
+
+        else:
+            self.schema = prevlevel.schema
+            self.names = prevlevel.names
+
+            self.include_inherited = True
+
+            if mode == ContextSwitchMode.EMPTY:
+                self.scope = Scope()
+                self.scope.ctes = prevlevel.scope.ctes
+            elif mode == ContextSwitchMode.ISOLATED:
+                self.scope = deepcopy(prevlevel.scope)
+
+    def empty(
+        self,
+    ) -> compiler.CompilerContextManager[ResolverContextLevel]:
+        """Create a new empty context"""
+        return self.new(ContextSwitchMode.EMPTY)
+
+    def isolated(
+        self,
+    ) -> compiler.CompilerContextManager[ResolverContextLevel]:
+        """Clone current context, prevent changes from leaking to parent"""
+        return self.new(ContextSwitchMode.ISOLATED)
+
+
+class ResolverContext(compiler.CompilerContext[ResolverContextLevel]):
+    ContextLevelClass = ResolverContextLevel
+    default_mode = ContextSwitchMode.EMPTY
diff --git a/edb/pgsql/resolver/dispatch.py b/edb/pgsql/resolver/dispatch.py
new file mode 100644
index 00000000000..caac48dfcf9
--- /dev/null
+++ b/edb/pgsql/resolver/dispatch.py
@@ -0,0 +1,88 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2008-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+
+from __future__ import annotations
+
+import functools
+import typing
+
+from edb.pgsql import ast as pgast
+
+from . import context
+
+Base_T = typing.TypeVar('Base_T', bound=pgast.Base)
+BaseRelation_T = typing.TypeVar('BaseRelation_T', bound=pgast.BaseRelation)
+
+
+@functools.singledispatch
+def _resolve(
+    ir: pgast.Base, *, ctx: context.ResolverContextLevel
+) -> pgast.Base:
+    raise ValueError(f'no SQL resolve handler for {ir.__class__}')
+
+
+def resolve(ir: Base_T, *, ctx: context.ResolverContextLevel) -> Base_T:
+    res = _resolve(ir, ctx=ctx)
+    return typing.cast(Base_T, res)
+
+
+def resolve_opt(
+    ir: typing.Optional[Base_T], *, ctx: context.ResolverContextLevel
+) -> typing.Optional[Base_T]:
+    if not ir:
+        return None
+    return resolve(ir, ctx=ctx)
+
+
+def resolve_list(
+    exprs: typing.Sequence[Base_T], *, ctx: context.ResolverContextLevel
+) -> typing.List[Base_T]:
+    return [resolve(e, ctx=ctx) for e in exprs]
+
+
+def resolve_opt_list(
+    exprs: typing.Optional[typing.List[Base_T]],
+    *,
+    ctx: context.ResolverContextLevel,
+) -> typing.Optional[typing.List[Base_T]]:
+    if not exprs:
+        return None
+    return resolve_list(exprs, ctx=ctx)
+
+
+@functools.singledispatch
+def _resolve_relation(
+    ir: pgast.BaseRelation, *, ctx: context.ResolverContextLevel
+) -> typing.Tuple[pgast.BaseRelation, context.Table]:
+    raise ValueError(f'no SQL resolve handler for {ir.__class__}')
+
+
+def resolve_relation(
+    ir: BaseRelation_T, *, ctx: context.ResolverContextLevel
+) -> typing.Tuple[BaseRelation_T, context.Table]:
+    res, tab = _resolve_relation(ir, ctx=ctx)
+    return typing.cast(BaseRelation_T, res), tab
+
+
+@_resolve.register
+def _resolve_BaseRelation(
+    rel: pgast.BaseRelation, *, ctx: context.ResolverContextLevel
+) -> pgast.BaseRelation:
+    rel, _ = resolve_relation(rel, ctx=ctx)
+    return rel
\ No newline at end of file
diff --git a/edb/pgsql/resolver/expr.py b/edb/pgsql/resolver/expr.py
new file mode 100644
index 00000000000..00b214cb9ab
--- /dev/null
+++ b/edb/pgsql/resolver/expr.py
@@ -0,0 +1,338 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2008-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+"""SQL resolver that compiles public SQL to internal SQL which is executable
+in our internal Postgres instance."""
+
+from typing import *
+
+from edb import errors
+
+from edb.pgsql import ast as pgast
+
+from . import dispatch
+from . import context
+
+Context = context.ResolverContextLevel
+
+# this function cannot go though dispatch,
+# because it may return multiple nodes, due to * notation
+def resolve_ResTarget(
+    res_target: pgast.ResTarget, *, ctx: Context
+) -> Tuple[Sequence[pgast.ResTarget], Sequence[context.Column]]:
+
+    alias = res_target.name
+
+    # if just name has been selected, use it as the alias
+    if not alias and isinstance(res_target.val, pgast.ColumnRef):
+        name = res_target.val.name
+        last_name_part = name[len(name) - 1]
+        if isinstance(last_name_part, str):
+            alias = last_name_part
+
+    # special case for ColumnRef for handing wildcards
+    if not alias and isinstance(res_target.val, pgast.ColumnRef):
+        col_res = _lookup_column(res_target.val, ctx)
+
+        res = []
+        columns = []
+        for table, column in col_res:
+            columns.append(column)
+
+            assert table.reference_as
+            assert column.reference_as
+            res.append(
+                pgast.ResTarget(
+                    val=pgast.ColumnRef(
+                        name=(table.reference_as, column.reference_as)
+                    ),
+                    name=column.name,
+                )
+            )
+        return (res, columns)
+
+    # base case
+    val = dispatch.resolve(res_target.val, ctx=ctx)
+
+    col = context.Column(name=alias, reference_as=alias)
+    return (pgast.ResTarget(val=val, name=alias),), (col,)
+
+
+@dispatch._resolve.register
+def resolve_ColumnRef(
+    column_ref: pgast.ColumnRef, *, ctx: Context
+) -> pgast.ColumnRef:
+    res = _lookup_column(column_ref, ctx)
+    if len(res) != 1:
+        raise errors.QueryError(
+            f'bad use of `*` column name', context=column_ref.context
+        )
+    table, column = res[0]
+    assert table.reference_as
+    assert column.reference_as
+
+    return pgast.ColumnRef(name=(table.reference_as, column.reference_as))
+
+
+def _lookup_column(
+    column_ref: pgast.ColumnRef,
+    ctx: Context,
+) -> Sequence[Tuple[context.Table, context.Column]]:
+    matched_columns: List[Tuple[context.Table, context.Column]] = []
+
+    name = column_ref.name
+    col_name: str | pgast.Star
+
+    if len(name) == 1:
+        # look for the column in all tables
+        col_name = name[0]
+
+        if isinstance(col_name, pgast.Star):
+            return [(t, c) for t in ctx.scope.tables for c in t.columns]
+        else:
+            for table in ctx.scope.tables:
+                matched_columns.extend(_lookup_in_table(col_name, table))
+
+    elif len(name) == 2:
+        # look for the column in the specific table
+        tab_name = name[0]
+        col_name = name[1]
+
+        try:
+            table = _lookup_table(cast(str, tab_name), ctx)
+        except errors.QueryError as e:
+            e.set_source_context(column_ref.context)
+            raise
+
+        if isinstance(col_name, pgast.Star):
+            return [(table, c) for c in table.columns]
+        else:
+            matched_columns.extend(_lookup_in_table(col_name, table))
+
+    if not matched_columns:
+        raise errors.QueryError(
+            f'cannot find column `{col_name}`', context=column_ref.context
+        )
+
+    elif len(matched_columns) > 1:
+        potential_tables = ', '.join(
+            [t.name or '' for t, _ in matched_columns]
+        )
+        raise errors.QueryError(
+            f'ambiguous column `{col_name}` could belong to '
+            f'following tables: {potential_tables}',
+            context=column_ref.context,
+        )
+
+    return (matched_columns[0],)
+
+
+def _lookup_in_table(
+    col_name: str, table: context.Table
+) -> Iterator[Tuple[context.Table, context.Column]]:
+    for column in table.columns:
+        if column.name == col_name:
+            yield (table, column)
+
+
+def _lookup_table(tab_name: str, ctx: Context) -> context.Table:
+    matched_tables = []
+    for t in ctx.scope.tables:
+        if t.name == tab_name or t.alias == tab_name:
+            matched_tables.append(t)
+
+    if not matched_tables:
+        raise errors.QueryError(f'cannot find table `{tab_name}`')
+    elif len(matched_tables) > 1:
+        raise errors.QueryError(f'ambiguous table `{tab_name}`')
+
+    table = matched_tables[0]
+    return table
+
+
+@dispatch._resolve.register
+def resolve_SubLink(
+    sub_link: pgast.SubLink,
+    *,
+    ctx: Context,
+) -> pgast.SubLink:
+    with ctx.empty() as subctx:
+        expr = dispatch.resolve(sub_link.expr, ctx=subctx)
+
+    return pgast.SubLink(
+        type=sub_link.type,
+        expr=expr,
+        test_expr=dispatch.resolve_opt(sub_link.test_expr, ctx=ctx),
+    )
+
+
+@dispatch._resolve.register
+def resolve_Expr(expr: pgast.Expr, *, ctx: Context) -> pgast.Expr:
+    return pgast.Expr(
+        kind=expr.kind,
+        name=expr.name,
+        lexpr=dispatch.resolve(expr.lexpr, ctx=ctx) if expr.lexpr else None,
+        rexpr=dispatch.resolve(expr.rexpr, ctx=ctx) if expr.rexpr else None,
+    )
+
+
+@dispatch._resolve.register
+def resolve_TypeCast(
+    expr: pgast.TypeCast,
+    *,
+    ctx: Context,
+) -> pgast.TypeCast:
+    return pgast.TypeCast(
+        arg=dispatch.resolve(expr.arg, ctx=ctx),
+        type_name=expr.type_name,
+    )
+
+
+@dispatch._resolve.register
+def resolve_BaseConstant(
+    expr: pgast.BaseConstant,
+    *,
+    ctx: Context,
+) -> pgast.BaseConstant:
+    return expr
+
+
+@dispatch._resolve.register
+def resolve_CaseExpr(
+    expr: pgast.CaseExpr,
+    *,
+    ctx: Context,
+) -> pgast.CaseExpr:
+    return pgast.CaseExpr(
+        arg=dispatch.resolve_opt(expr.arg, ctx=ctx),
+        args=dispatch.resolve_list(expr.args, ctx=ctx),
+        defresult=dispatch.resolve_opt(expr.defresult, ctx=ctx),
+    )
+
+
+@dispatch._resolve.register
+def resolve_CaseWhen(
+    expr: pgast.CaseWhen,
+    *,
+    ctx: Context,
+) -> pgast.CaseWhen:
+    return pgast.CaseWhen(
+        expr=dispatch.resolve(expr.expr, ctx=ctx),
+        result=dispatch.resolve(expr.result, ctx=ctx),
+    )
+
+
+@dispatch._resolve.register
+def resolve_SortBy(
+    expr: pgast.SortBy,
+    *,
+    ctx: Context,
+) -> pgast.SortBy:
+    return pgast.SortBy(
+        node=dispatch.resolve(expr.node, ctx=ctx),
+        dir=expr.dir,
+        nulls=expr.nulls,
+    )
+
+
+@dispatch._resolve.register
+def resolve_FuncCall(
+    expr: pgast.FuncCall,
+    *,
+    ctx: Context,
+) -> pgast.FuncCall:
+    # TODO: which functions do we want to expose on the outside?
+    return pgast.FuncCall(
+        name=expr.name,
+        args=dispatch.resolve_list(expr.args, ctx=ctx),
+        agg_order=dispatch.resolve_opt_list(expr.agg_order, ctx=ctx),
+        agg_filter=dispatch.resolve_opt(expr.agg_filter, ctx=ctx),
+        agg_star=expr.agg_star,
+        agg_distinct=expr.agg_distinct,
+        over=dispatch.resolve_opt(expr.over, ctx=ctx),
+        with_ordinality=expr.with_ordinality,
+    )
+
+
+@dispatch._resolve.register
+def resolve_WindowDef(
+    expr: pgast.WindowDef,
+    *,
+    ctx: Context,
+) -> pgast.WindowDef:
+    return pgast.WindowDef(
+        partition_clause=dispatch.resolve_opt_list(
+            expr.partition_clause, ctx=ctx
+        ),
+        order_clause=dispatch.resolve_opt_list(expr.order_clause, ctx=ctx),
+        start_offset=dispatch.resolve_opt(expr.start_offset, ctx=ctx),
+        end_offset=dispatch.resolve_opt(expr.end_offset, ctx=ctx),
+    )
+
+
+@dispatch._resolve.register
+def resolve_CoalesceExpr(
+    expr: pgast.CoalesceExpr,
+    *,
+    ctx: Context,
+) -> pgast.CoalesceExpr:
+    return pgast.CoalesceExpr(args=dispatch.resolve_list(expr.args, ctx=ctx))
+
+
+@dispatch._resolve.register
+def resolve_NullTest(
+    expr: pgast.NullTest,
+    *,
+    ctx: Context,
+) -> pgast.NullTest:
+    return pgast.NullTest(
+        arg=dispatch.resolve(expr.arg, ctx=ctx), negated=expr.negated
+    )
+
+
+@dispatch._resolve.register
+def resolve_BooleanTest(
+    expr: pgast.BooleanTest,
+    *,
+    ctx: Context,
+) -> pgast.BooleanTest:
+    return pgast.BooleanTest(
+        arg=dispatch.resolve(expr.arg, ctx=ctx),
+        negated=expr.negated,
+        is_true=expr.is_true,
+    )
+
+
+@dispatch._resolve.register
+def resolve_ImplicitRowExpr(
+    expr: pgast.ImplicitRowExpr,
+    *,
+    ctx: Context,
+) -> pgast.ImplicitRowExpr:
+    return pgast.ImplicitRowExpr(
+        args=dispatch.resolve_list(expr.args, ctx=ctx),
+    )
+
+
+@dispatch._resolve.register
+def resolve_ParamRef(
+    expr: pgast.ParamRef,
+    *,
+    ctx: Context,
+) -> pgast.ParamRef:
+    return pgast.ParamRef(number=expr.number)
diff --git a/edb/pgsql/resolver/range_var.py b/edb/pgsql/resolver/range_var.py
new file mode 100644
index 00000000000..dd977decb0d
--- /dev/null
+++ b/edb/pgsql/resolver/range_var.py
@@ -0,0 +1,226 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2008-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+"""SQL resolver that compiles public SQL to internal SQL which is executable
+in our internal Postgres instance."""
+
+import functools
+from typing import *
+
+from edb import errors
+
+from edb.pgsql import ast as pgast
+from edb.pgsql.compiler import astutils as pgastutils
+
+from . import dispatch
+from . import context
+
+Context = context.ResolverContextLevel
+
+
+def resolve_BaseRangeVar(
+    range_var: pgast.BaseRangeVar, *, ctx: Context
+) -> Tuple[pgast.BaseRangeVar, Sequence[context.Table]]:
+    # handle join that returns multiple tables and does not use alias
+    if isinstance(range_var, pgast.JoinExpr):
+        return _resolve_JoinExpr(range_var, ctx=ctx)
+
+    # generate internal alias
+    internal_alias = ctx.names.get('relation')
+    alias = pgast.Alias(aliasname=internal_alias)
+
+    # general case
+    node, table = _resolve_range_var(range_var, alias, ctx=ctx)
+
+    # infer public name and internal alias
+    table.alias = range_var.alias.aliasname
+    table.reference_as = internal_alias
+
+    return node, (table,)
+
+
+@functools.singledispatch
+def _resolve_range_var(
+    ir: pgast.BaseRangeVar,
+    alias: pgast.Alias,
+    *,
+    ctx: context.ResolverContextLevel,
+) -> Tuple[pgast.BaseRangeVar, context.Table]:
+    raise ValueError(f'no SQL resolve handler for {ir.__class__}')
+
+
+@_resolve_range_var.register
+def _resolve_RelRangeVar(
+    range_var: pgast.RelRangeVar,
+    alias: pgast.Alias,
+    *,
+    ctx: Context,
+) -> Tuple[pgast.BaseRangeVar, context.Table]:
+    ctx.include_inherited = range_var.include_inherited
+
+    relation: Union[pgast.BaseRelation, pgast.CommonTableExpr]
+    if isinstance(range_var.relation, pgast.BaseRelation):
+        relation, table = dispatch.resolve_relation(range_var.relation, ctx=ctx)
+    else:
+        relation, cte = resolve_CommonTableExpr(range_var.relation, ctx=ctx)
+        table = context.Table()
+        table.name = cte.name
+        table.columns = cte.columns
+        table.reference_as = cte.name
+
+    return (
+        pgast.RelRangeVar(
+            relation=relation,
+            alias=alias,
+        ),
+        table
+    )
+
+
+@_resolve_range_var.register
+def _resolve_RangeSubselect(
+    range_var: pgast.RangeSubselect,
+    alias: pgast.Alias,
+    *,
+    ctx: Context,
+) -> Tuple[pgast.BaseRangeVar, context.Table]:
+    with ctx.empty() as subctx:
+        subquery, subtable = dispatch.resolve_relation(range_var.subquery, ctx=subctx)
+
+        result = context.Table()
+        result.name = range_var.alias.aliasname
+        result.reference_as = alias.aliasname
+        result.columns = [
+            context.Column(name=col.name, reference_as=col.name)
+            for col in subtable.columns
+        ]
+
+    return (
+        pgast.RangeSubselect(
+            subquery=subquery,
+            alias=alias,
+        ),
+        result,
+    )
+
+
+@_resolve_range_var.register
+def _resolve_RangeFunction(
+    range_var: pgast.RangeFunction,
+    alias: pgast.Alias,
+    *,
+    ctx: Context,
+) -> Tuple[pgast.BaseRangeVar, context.Table]:
+    raise errors.UnsupportedFeatureError(
+        'range functions are not supported', context=range_var.context
+    )
+
+
+def _resolve_JoinExpr(
+    range_var: pgast.JoinExpr,
+    *,
+    ctx: Context,
+) -> Tuple[pgast.BaseRangeVar, Sequence[context.Table]]:
+    
+    def resolve_arg(
+        arg: pgast.BaseExpr,
+    ) -> Tuple[pgast.BaseExpr, Sequence[context.Table]]:
+        with ctx.empty() as subctx:
+            if isinstance(arg, pgast.BaseRelation):
+                arg, table = dispatch.resolve_relation(arg, ctx=subctx)
+                return arg, (table,)
+            elif isinstance(arg, pgast.BaseRangeVar):
+                return resolve_BaseRangeVar(arg, ctx=subctx)
+            else:
+                raise ValueError()
+
+    larg, l_tables = resolve_arg(range_var.larg)
+    rarg, r_tables = resolve_arg(range_var.rarg)
+
+    tables = list(l_tables) + list(r_tables)
+    ctx.scope.tables.extend(tables)
+
+    quals: Optional[pgast.BaseExpr] = None
+    if range_var.quals:
+        quals = dispatch.resolve(range_var.quals, ctx=ctx)
+
+    if range_var.using_clause:
+        for c in range_var.using_clause:
+            with ctx.empty() as subctx:
+                subctx.scope.tables = list(l_tables)
+                l_expr = dispatch.resolve(c, ctx=subctx)
+            with ctx.empty() as subctx:
+                subctx.scope.tables = list(r_tables)
+                r_expr = dispatch.resolve(c, ctx=subctx)
+            quals = pgastutils.extend_binop(
+                quals,
+                pgast.Expr(
+                    kind=pgast.ExprKind.OP,
+                    name='=',
+                    lexpr=l_expr,
+                    rexpr=r_expr,
+                ),
+            )
+
+    node = pgast.JoinExpr(
+        type=range_var.type,
+        larg=larg,
+        rarg=rarg,
+        quals=quals,
+    )
+    return (node, tables)
+
+
+def resolve_CommonTableExpr(
+    cte: pgast.CommonTableExpr, *, ctx: Context
+) -> Tuple[pgast.CommonTableExpr, context.CTE]:
+    with ctx.isolated() as subctx:
+
+        query, table = dispatch.resolve_relation(cte.query, ctx=subctx)
+
+        result = context.CTE()
+        result.name = cte.name
+        result.columns = []
+
+        cols_query = table.columns
+        cols_names = cte.aliascolnames
+        if cols_names and len(cols_query) != len(cols_names):
+            raise errors.QueryError(
+                f'CTE alias for `{cte.name}` contains {len(cols_names)} '
+                f'columns, but the query resolves to `{len(cols_query)}` '
+                f'columns',
+                context=cte.context,
+            )
+        for index, col in enumerate(cols_query):
+            res_col = context.Column()
+            if cols_names:
+                res_col.name = cols_names[index]
+            else:
+                res_col.name = col.name
+            res_col.reference_as = col.name
+            result.columns.append(res_col)
+
+    node = pgast.CommonTableExpr(
+        name=cte.name,
+        aliascolnames=None,
+        query=query,
+        recursive=cte.recursive,
+        materialized=cte.materialized,
+    )
+    return node, result
+
diff --git a/edb/pgsql/resolver/relation.py b/edb/pgsql/resolver/relation.py
new file mode 100644
index 00000000000..f7db5d75a79
--- /dev/null
+++ b/edb/pgsql/resolver/relation.py
@@ -0,0 +1,222 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2008-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+"""SQL resolver that compiles public SQL to internal SQL which is executable
+in our internal Postgres instance."""
+
+from typing import *
+
+from edb import errors
+from edb.edgeql import qltypes
+
+from edb.pgsql import ast as pgast
+from edb.pgsql import common as pgcommon
+
+from edb.schema import objtypes as s_objtypes
+from edb.schema import links as s_links
+
+from . import dispatch
+from . import context
+from . import range_var
+from . import expr
+
+Context = context.ResolverContextLevel
+
+
+@dispatch._resolve_relation.register
+def resolve_SelectStmt(
+    stmt: pgast.SelectStmt, *, ctx: Context
+) -> Tuple[pgast.SelectStmt, context.Table]:
+
+    # UNION
+    if stmt.larg or stmt.rarg:
+        assert stmt.larg and stmt.rarg
+
+        with ctx.isolated() as subctx:
+            larg, ltable = dispatch.resolve_relation(stmt.larg, ctx=subctx)
+
+        with ctx.isolated() as subctx:
+            rarg, rtable = dispatch.resolve_relation(stmt.rarg, ctx=subctx)
+
+        # valudate equal columns from both sided
+        if len(ltable.columns) != len(rtable.columns):
+            raise errors.QueryError(
+                f'{stmt.op} requires equal number of columns in both sides',
+                context=stmt.context,
+            )
+
+        relation = pgast.SelectStmt(
+            larg=larg,
+            rarg=rarg,
+            op=stmt.op,
+            all=stmt.all,
+        )
+        return (relation, ltable)
+
+    # CTEs
+    ctes = None
+    if stmt.ctes:
+        ctes = []
+        for cte in stmt.ctes:
+            cte, tab = range_var.resolve_CommonTableExpr(cte, ctx=ctx)
+            ctes.append(cte)
+            ctx.scope.ctes.append(tab)
+
+    # FROM
+    from_clause: List[pgast.BaseRangeVar] = []
+    for clause in stmt.from_clause:
+
+        with ctx.empty() as subctx:
+            node, tables = range_var.resolve_BaseRangeVar(clause, ctx=subctx)
+            from_clause.append(node)
+
+            # pull result relation of inner scope into own scope
+            ctx.scope.tables.extend(tables)
+
+    where = dispatch.resolve_opt(stmt.where_clause, ctx=ctx)
+
+    table = context.Table()
+    target_list: List[pgast.ResTarget] = []
+    for t in stmt.target_list:
+        targets, columns = expr.resolve_ResTarget(t, ctx=ctx)
+        target_list.extend(targets)
+        table.columns.extend(columns)
+
+    distinct_clause = None
+    if stmt.distinct_clause:
+        distinct_clause = [
+            (c if isinstance(c, pgast.Star) else dispatch.resolve(c, ctx=ctx))
+            for c in stmt.distinct_clause
+        ]
+
+    sort_clause = dispatch.resolve_opt_list(stmt.sort_clause, ctx=ctx)
+    limit_offset = dispatch.resolve_opt(stmt.limit_offset, ctx=ctx)
+    limit_count = dispatch.resolve_opt(stmt.limit_count, ctx=ctx)
+
+    res = pgast.SelectStmt(
+        distinct_clause=distinct_clause,
+        from_clause=from_clause,
+        target_list=target_list,
+        where_clause=where,
+        sort_clause=sort_clause,
+        limit_offset=limit_offset,
+        limit_count=limit_count,
+        ctes=ctes,
+    )
+    return (
+        res,
+        table,
+    )
+
+
+@dispatch._resolve_relation.register
+def resolve_DMLQuery(
+    query: pgast.DMLQuery, *, ctx: Context
+) -> Tuple[pgast.DMLQuery, context.Table]:
+    raise errors.UnsupportedFeatureError(
+        'DML queries (INSERT/UPDATE/DELETE) are not supported',
+        context=query.context,
+    )
+
+
+@dispatch._resolve_relation.register
+def resolve_relation(
+    relation: pgast.Relation, *, ctx: Context
+) -> Tuple[pgast.Relation, context.Table]:
+    assert relation.name
+    schema_name = relation.schemaname or 'public'
+
+    # try a CTE
+    cte = next((t for t in ctx.scope.ctes if t.name == relation.name), None)
+    if cte:
+        table = context.Table()
+        table.name = cte.name
+        table.columns = cte.columns.copy()
+        return pgast.Relation(name=cte.name, schemaname=None), table
+
+    # lookup the object in schema
+    obj: Optional[s_objtypes.ObjectType] = None
+    if schema_name == 'public':
+        object_type_name = relation.name[0].upper() + relation.name[1:]
+
+        obj = ctx.schema.get(  # type: ignore
+            object_type_name,
+            None,
+            module_aliases={None: 'default'},
+            type=s_objtypes.ObjectType,
+        )
+
+    if not obj:
+        raise errors.QueryError(
+            f'unknown table `{relation.schemaname}.{relation.name}`',
+            context=relation.context,
+        )
+
+    # extract table name
+    table = context.Table()
+    table.name = obj.get_shortname(ctx.schema).name.lower()
+
+    # extract table columns
+    pointers = obj.get_pointers(ctx.schema).objects(ctx.schema)
+
+    columns: List[context.Column] = []
+    for p in pointers:
+        if p.is_protected_pointer(ctx.schema):
+            continue
+        if p.get_cardinality(ctx.schema) != qltypes.SchemaCardinality.One:
+            continue
+
+        if p.is_property(ctx.schema):
+            col = context.Column()
+            col.name = p.get_shortname(ctx.schema).name
+
+            if p.is_id_pointer(ctx.schema):
+                col.reference_as = 'id'
+            else:
+                _, dbname = pgcommon.get_backend_name(
+                    ctx.schema, p, catenate=False
+                )
+                col.reference_as = dbname
+
+            columns.append(col)
+
+        if isinstance(p, s_links.Link):
+            col = context.Column()
+            col.name = p.get_shortname(ctx.schema).name + '_id'
+
+            _, dbname = pgcommon.get_backend_name(
+                ctx.schema, p, catenate=False
+            )
+            col.reference_as = dbname
+
+            columns.append(col)
+
+    # sort by name but put `id` first
+    columns.sort(key=lambda c: () if c.name == 'id' else (c.name or '',))
+
+    table.columns.extend(columns)
+
+    # compile
+    aspect = 'inhview' if ctx.include_inherited else 'table'
+
+    schemaname, dbname = pgcommon.get_backend_name(
+        ctx.schema, obj, aspect=aspect, catenate=False
+    )
+
+    return pgast.Relation(name=dbname, schemaname=schemaname), table
+
diff --git a/edb/server/compiler/compiler.py b/edb/server/compiler/compiler.py
index dd8f22c5470..2b66118d072 100644
--- a/edb/server/compiler/compiler.py
+++ b/edb/server/compiler/compiler.py
@@ -680,10 +680,19 @@ def _compile_ql_debug_sql(
         # (eventually) rewriting <STRING> and returning it
         assert self._is_dev_instance()
 
-        # TODO: Translate the SQL source code here
-        # current_tx = ctx.state.current_tx()
-        # schema = current_tx.get_schema(self._std_schema)
-        sql_source = ql.source + ';'
+        current_tx = ctx.state.current_tx()
+        schema = current_tx.get_schema(self._std_schema)
+
+        from edb.pgsql import parser as pg_parser
+        from edb.pgsql import resolver as pg_resolver
+        from edb.pgsql import codegen as pg_codegen
+
+        stmts = pg_parser.parse(ql.source)
+        sql_source = ''
+        for stmt in stmts:
+            resolved = pg_resolver.resolve(stmt, schema)
+            source = pg_codegen.generate_source(resolved)
+            sql_source += source + ';'
 
         # Compile the result as a query that just returns the string
         res_ql = edgeql.parse(f'SELECT {qlquote.quote_literal(sql_source)}')
diff --git a/edb/testbase/server.py b/edb/testbase/server.py
index eae7125c68d..4ce5bb1650a 100644
--- a/edb/testbase/server.py
+++ b/edb/testbase/server.py
@@ -1260,6 +1260,25 @@ async def squery(self, query):
         ''')
         return await self.scon.fetch(rewritten)
 
+    async def squery_values(self, query):
+        res = await self.squery(query)
+        return [list(r.values()) for r in res]
+
+    def assert_shape(self, res, rows, cols, column_names=None):
+        """
+        Fail if query result does not confront the specified shape, defined in
+        terms of:
+        - number of rows,
+        - number of columns (not checked if there are not rows)
+        - column names.
+        """
+
+        self.assertEqual(len(res), rows)
+        if rows > 0:
+            self.assertEqual(len(res[0]), cols)
+        if column_names:
+            self.assertListEqual(column_names, list(res[0].keys()))
+
 
 class DumpCompatTestCaseMeta(TestCaseMeta):
 
diff --git a/tests/test_sql.py b/tests/schemas/movies.esdl
similarity index 56%
rename from tests/test_sql.py
rename to tests/schemas/movies.esdl
index ebe9aa333d5..8495442c55f 100644
--- a/tests/test_sql.py
+++ b/tests/schemas/movies.esdl
@@ -1,7 +1,7 @@
 #
 # This source file is part of the EdgeDB open source project.
 #
-# Copyright 2016-present MagicStack Inc. and the EdgeDB authors.
+# Copyright 2020-present MagicStack Inc. and the EdgeDB authors.
 #
 # Licensed under the Apache License, Version 2.0 (the "License");
 # you may not use this file except in compliance with the License.
@@ -17,21 +17,26 @@
 #
 
 
-import os.path
+type Person {
+    required property first_name -> str;
+    property last_name -> str;
+}
 
-from edb.testbase import server as tb
+type Genre {
+    required property name -> str;
+}
 
+type Content {
+    required property title -> str;
+    link genre -> Genre;
+}
 
-class TestSQL(tb.SQLQueryTestCase):
+type Movie extending Content {
+    property release_year -> int64;
+    multi link actors -> Person;
+    link director -> Person;
+}
 
-    SCHEMA = os.path.join(os.path.dirname(__file__), 'schemas',
-                          'issues.esdl')
-
-    async def test_sql_01(self):
-        res = await self.squery('''
-            select 1, 2, 3
-        ''')
-        self.assertEqual(
-            res,
-            [(1, 2, 3)],
-        )
+type Book extending Content {
+    required property pages -> int16;
+}
\ No newline at end of file
diff --git a/tests/schemas/movies_setup.edgeql b/tests/schemas/movies_setup.edgeql
new file mode 100644
index 00000000000..d9ef37d4098
--- /dev/null
+++ b/tests/schemas/movies_setup.edgeql
@@ -0,0 +1,60 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2016-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+insert Genre { name:= 'Fiction' };
+insert Genre { name:= 'Drama' };
+
+insert Person { first_name:= 'Tom', last_name:= 'Hanks' };
+insert Person { first_name:= 'Robin' };
+insert Person { first_name:= 'Steven', last_name:= 'Spielberg' };
+
+insert Movie {
+    title := 'Forrest Gump',
+    release_year := 1994,
+    actors := (select Person 
+        filter .first_name = 'Tom' or .first_name = 'Robin'
+    ),
+    genre := (select Genre filter .name = 'Drama' limit 1),
+};
+
+insert Movie {
+    title := 'Saving Private Ryan',
+    release_year := 1998,
+    actors := (select Person filter .first_name = 'Tom'),
+    director := (select Person
+        filter .last_name = 'Spielberg' limit 1
+    ),
+    genre := (select Genre filter .name = 'Drama' limit 1),
+};
+
+insert Book {
+    title:='Hunger Games',
+    pages := 374,
+    genre:= (select Genre filter .name = 'Fiction' limit 1)
+};
+
+insert Book {
+    title:='Chronicles of Narnia',
+    pages := 206,
+    genre:= (select Genre filter .name = 'Fiction' limit 1)
+};
+
+insert Content {
+    title := 'Halo 3',
+    genre := (select Genre filter .name = 'Fiction' limit 1)
+};
\ No newline at end of file
diff --git a/tests/test_pgsql_parse.py b/tests/test_sql_parse.py
similarity index 99%
rename from tests/test_pgsql_parse.py
rename to tests/test_sql_parse.py
index e866cde543f..fb0410bdb92 100644
--- a/tests/test_pgsql_parse.py
+++ b/tests/test_sql_parse.py
@@ -231,8 +231,6 @@ def test_pgsql_parse_select_23(self):
     def test_pgsql_parse_select_24(self):
         """
         SELECT * FROM table_one, public.table_one
-% OK %
-        SELECT * FROM table_one, table_one
         """
 
     def test_pgsql_parse_select_25(self):
diff --git a/tests/test_sql_query.py b/tests/test_sql_query.py
new file mode 100644
index 00000000000..1e6dc63a753
--- /dev/null
+++ b/tests/test_sql_query.py
@@ -0,0 +1,333 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2016-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+import os.path
+
+from edb.testbase import server as tb
+
+
+class TestSQL(tb.SQLQueryTestCase):
+
+    SCHEMA = os.path.join(os.path.dirname(__file__), 'schemas', 'movies.esdl')
+
+    SETUP = os.path.join(
+        os.path.dirname(__file__), 'schemas', 'movies_setup.edgeql'
+    )
+
+    async def test_sql_query_00(self):
+        # basic
+        res = await self.squery_values(
+            '''
+            select title from Movie order by title
+            '''
+        )
+        self.assertEqual(res, [['Forrest Gump'], ['Saving Private Ryan']])
+
+    async def test_sql_query_01(self):
+        # table alias
+        res = await self.squery(
+            '''
+            select mve.title, mve.release_year, director_id FROM Movie as mve
+            '''
+        )
+        self.assert_shape(res, 2, 3)
+
+    async def test_sql_query_02(self):
+        # select from parent type
+        res = await self.squery(
+            '''
+            select * FROM Content
+            '''
+        )
+        self.assert_shape(res, 5, 3, ['id', 'genre_id', 'title'])
+
+    async def test_sql_query_03(self):
+        # select from parent type only
+        res = await self.squery(
+            '''
+            select * FROM ONLY Content -- should have only one result
+            '''
+        )
+        self.assert_shape(res, 1, 3, ['id', 'genre_id', 'title'])
+
+    async def test_sql_query_04(self):
+        # multiple FROMs
+        res = await self.squery(
+            '''
+            select mve.title, Person.first_name
+            FROM Movie mve, Person WHERE mve.director_id = person.id
+            '''
+        )
+        self.assert_shape(res, 1, 2, ['title', 'first_name'])
+
+    async def test_sql_query_05(self):
+        # case insensitive
+        res = await self.squery(
+            '''
+            SeLeCt mve.title as tiT, perSon.first_name
+            FROM Movie mve, Person
+            '''
+        )
+        self.assert_shape(res, 6, 2, ['tit', 'first_name'])
+
+    async def test_sql_query_06(self):
+        # sub relations
+        res = await self.squery(
+            '''
+            select id, title, prS.first_name
+            FROM Movie mve, (select first_name FROM Person) prs
+            '''
+        )
+        self.assert_shape(res, 6, 3, ['id', 'title', 'first_name'])
+
+    async def test_sql_query_07(self):
+        # quoted case sensitive
+        res = await self.squery(
+            '''
+            SELECT tItLe, release_year "RL year" FROM "movie" ORDER BY titLe;
+            '''
+        )
+        self.assert_shape(res, 2, 2, ['title', 'RL year'])
+
+    async def test_sql_query_08(self):
+        # JOIN
+        res = await self.squery(
+            '''
+            SELECT movie.id, genre.id
+            FROM Movie JOIN Genre ON Movie.genre_id = Genre.id
+            '''
+        )
+        self.assert_shape(res, 2, 2, ['id', 'id'])
+
+    async def test_sql_query_09(self):
+        # resolve columns without table names
+        res = await self.squery(
+            '''
+            SELECT movie.id, title, name
+            FROM Movie JOIN Genre ON Movie.genre_id = Genre.id
+            '''
+        )
+        self.assert_shape(res, 2, 3, ['id', 'title', 'name'])
+
+    async def test_sql_query_10(self):
+        # wildcard select
+        res = await self.squery(
+            '''
+            SELECT m.* FROM Movie m
+            '''
+        )
+        self.assert_shape(
+            res,
+            2,
+            5,
+            ['id', 'director_id', 'genre_id', 'release_year', 'title'],
+        )
+
+    async def test_sql_query_11(self):
+        # multiple wildcard select
+        res = await self.squery(
+            '''
+            SELECT * FROM Movie JOIN Genre g ON Movie.genre_id = Genre.id
+            '''
+        )
+        self.assert_shape(res, 2, 7)
+
+    async def test_sql_query_12(self):
+        # JOIN USING
+        res = await self.squery(
+            '''
+            SELECT * FROM Movie
+            JOIN (SELECT id as genre_id, name FROM Genre) g USING (genre_id)
+            '''
+        )
+        self.assert_shape(res, 2, 7)
+
+    async def test_sql_query_13(self):
+        # CTE
+        res = await self.squery(
+            '''
+            WITH g AS (SELECT id as genre_id, name FROM Genre)
+            SELECT * FROM Movie JOIN g USING (genre_id)
+            '''
+        )
+        self.assert_shape(res, 2, 7)
+
+    async def test_sql_query_14(self):
+        # CASE
+        res = await self.squery_values(
+            '''
+            SELECT title, CASE WHEN title='Forrest Gump' THEN 'forest'
+            WHEN title='Saving Private Ryan' THEN 'the war film'
+            ELSE 'unknown' END AS nick_name FROM Movie
+            '''
+        )
+        self.assertEqual(
+            res,
+            [
+                ['Forrest Gump', 'forest'],
+                ['Saving Private Ryan', 'the war film'],
+            ],
+        )
+
+    async def test_sql_query_15(self):
+        # UNION
+        res = await self.squery(
+            '''
+            SELECT id, title FROM Movie UNION select id, title FROM Book
+            '''
+        )
+        self.assert_shape(res, 4, 2)
+
+    async def test_sql_query_16(self):
+        # casting
+        res = await self.squery(
+            '''
+            SELECT 1::bigint, 'accbf276-705b-11e7-b8e4-0242ac120002'::UUID
+            '''
+        )
+        self.assert_shape(res, 1, 2)
+
+    async def test_sql_query_17(self):
+        # ORDER BY
+        res = await self.squery_values(
+            '''
+            SELECT first_name, last_name
+            FROM Person ORDER BY last_name DESC NULLS FIRST
+            '''
+        )
+        self.assertEqual(
+            res, [['Robin', None], ['Steven', 'Spielberg'], ['Tom', 'Hanks']]
+        )
+
+        res = await self.squery_values(
+            '''
+            SELECT first_name, last_name
+            FROM Person ORDER BY last_name DESC NULLS LAST
+            '''
+        )
+        self.assertEqual(
+            res, [['Steven', 'Spielberg'], ['Tom', 'Hanks'], ['Robin', None]]
+        )
+
+    async def test_sql_query_18(self):
+        # LIMIT & OFFSET
+        res = await self.squery_values(
+            '''
+            SELECT title FROM Content ORDER BY title OFFSET 1 LIMIT 2
+            '''
+        )
+        self.assertEqual(res, [['Forrest Gump'], ['Halo 3']])
+
+    async def test_sql_query_19(self):
+        # DISTINCT
+        res = await self.squery_values(
+            '''
+            SELECT DISTINCT name
+            FROM Content c JOIN Genre g ON (c.genre_id = g.id)
+            ORDER BY name
+            '''
+        )
+        self.assertEqual(res, [['Drama'], ['Fiction']])
+
+        res = await self.squery_values(
+            '''
+            SELECT DISTINCT ON (name) name, title
+            FROM Content c JOIN Genre g ON (c.genre_id = g.id)
+            ORDER BY name, title
+            '''
+        )
+        self.assertEqual(
+            res,
+            [['Drama', 'Forrest Gump'], ['Fiction', 'Chronicles of Narnia']],
+        )
+
+    async def test_sql_query_20(self):
+        # WHERE
+        res = await self.squery_values(
+            '''
+            SELECT first_name FROM Person
+            WHERE last_name IS NOT NULL AND LENGTH(first_name) < 4
+            '''
+        )
+        self.assertEqual(res, [['Tom']])
+
+    async def test_sql_query_21(self):
+        # window functions
+        res = await self.squery_values(
+            '''
+            WITH content AS (
+                SELECT c.id, c.title, pages
+                FROM Content c LEFT JOIN Book USING(id)
+            ),
+            content2 AS (
+                SELECT id, COALESCE(pages, 0) as pages FROM content
+            )
+            SELECT pages, sum(pages) OVER (ORDER BY pages)
+            FROM content2 ORDER BY pages DESC
+            '''
+        )
+        self.assertEqual(
+            res,
+            [[374, 580], [206, 206], [0, 0], [0, 0], [0, 0]],
+        )
+
+    async def test_sql_query_22(self):
+        # IS NULL/true
+        res = await self.squery(
+            '''
+            SELECT id FROM Person WHERE last_name IS NULL
+            '''
+        )
+        self.assert_shape(res, 1, 1)
+
+        res = await self.squery(
+            '''
+            SELECT id FROM Person WHERE (last_name = 'Hanks') IS NOT TRUE
+            '''
+        )
+        self.assert_shape(res, 2, 1)
+
+    async def test_sql_query_23(self):
+        # ImplicitRow
+        res = await self.squery(
+            '''
+            SELECT id FROM person
+            WHERE (first_name, last_name) IN (
+                ('Tom', 'Hanks'), ('Steven', 'Spielberg')
+            )
+            '''
+        )
+        self.assert_shape(res, 2, 1)
+
+    async def test_sql_query_24(self):
+        # SubLink
+        res = await self.squery_values(
+            '''
+            SELECT title FROM Movie WHERE id IN (
+                SELECT id FROM Movie ORDER BY title LIMIT 1
+            )
+            '''
+        )
+        self.assertEqual(res, [['Forrest Gump']])
+
+        res = await self.squery_values(
+            '''
+            SELECT (SELECT title FROM Movie ORDER BY title LIMIT 1)
+            '''
+        )
+        self.assertEqual(res, [['Forrest Gump']])


Code review comments:
diff_hunk: @@ -0,0 +1,222 @@
+#
+# This source file is part of the EdgeDB open source project.
+#
+# Copyright 2008-present MagicStack Inc. and the EdgeDB authors.
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#     http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+"""SQL resolver that compiles public SQL to internal SQL which is executable
+in our internal Postgres instance."""
+
+from typing import *
+
+from edb import errors
+from edb.edgeql import qltypes
+
+from edb.pgsql import ast as pgast
+from edb.pgsql import common as pgcommon
+
+from edb.schema import objtypes as s_objtypes
+from edb.schema import links as s_links
+
+from . import dispatch
+from . import context
+from . import range_var
+from . import expr
+
+Context = context.ResolverContextLevel
+
+
+@dispatch._resolve_relation.register
+def resolve_SelectStmt(
+    stmt: pgast.SelectStmt, *, ctx: Context
+) -> Tuple[pgast.SelectStmt, context.Table]:
+
+    # UNION
+    if stmt.larg or stmt.rarg:
+        assert stmt.larg and stmt.rarg
+
+        with ctx.isolated() as subctx:
+            larg, ltable = dispatch.resolve_relation(stmt.larg, ctx=subctx)
+
+        with ctx.isolated() as subctx:
+            rarg, rtable = dispatch.resolve_relation(stmt.rarg, ctx=subctx)
+
+        # valudate equal columns from both sided
path: edb/pgsql/resolver/relation.py
text: @user1:
s/valudate/validate/

@user1:
and s/sided/sides/