diff --git a/search/ast2select.py b/search/ast2select.py
new file mode 100644
index 0000000..a6e5f17
--- /dev/null
+++ b/search/ast2select.py
@@ -0,0 +1,957 @@
+# Copyright 2016 The Chromium Authors. All rights reserved.
+# Use of this source code is governed by a BSD-style
+# license that can be found in the LICENSE file or at
+# https://developers.google.com/open-source/licenses/bsd
+
+"""Convert a user's issue search AST into SQL clauses.
+
+The main query is done on the Issues table.
+ + Some simple conditions are implemented as WHERE conditions on the Issue
+   table rows.  These are generated by the _Compare() function.
+ + More complex conditions are implemented via a "LEFT JOIN ... ON ..." clause
+   plus a check in the WHERE clause to select only rows where the join's ON
+   condition was satisfied.  These are generated by appending a clause to
+   the left_joins list plus calling _CompareAlreadyJoined().  Each such left
+   join defines a unique alias to keep it separate from other conditions.
+
+The functions that generate SQL snippets need to insert table names, column
+names, alias names, and value placeholders into the generated string.  These
+functions use the string format() method and the "{varname}" syntax to avoid
+confusion with the "%s" syntax used for SQL value placeholders.
+"""
+from __future__ import print_function
+from __future__ import division
+from __future__ import absolute_import
+
+import logging
+
+from framework import sql
+from proto import ast_pb2
+from proto import tracker_pb2
+from search import query2ast
+from services import tracker_fulltext
+
+
+NATIVE_SEARCHABLE_FIELDS = {
+    'id': 'local_id',
+    'is_spam': 'is_spam',
+    'stars': 'star_count',
+    'attachments': 'attachment_count',
+    'opened': 'opened',
+    'closed': 'closed',
+    'modified': 'modified',
+    'ownermodified': 'owner_modified',
+    'statusmodified': 'status_modified',
+    'componentmodified': 'component_modified',
+    }
+
+
+def BuildSQLQuery(query_ast, snapshot_mode=False):
+  """Translate the user's query into an SQL query.
+
+  Args:
+    query_ast: user query abstract syntax tree parsed by query2ast.py.
+
+  Returns:
+    A pair of lists (left_joins, where) to use when building the SQL SELECT
+    statement.  Each of them is a list of (str, [val, ...]) pairs.
+  """
+  left_joins = []
+  where = []
+  unsupported_conds = []
+  # OR-queries are broken down into multiple simpler queries before they
+  # are sent to the backends, so we should never see an "OR"..
+  assert len(query_ast.conjunctions) == 1, 'OR-query should have been split'
+  conj = query_ast.conjunctions[0]
+
+  for cond_num, cond in enumerate(conj.conds):
+    cond_left_joins, cond_where, unsupported = _ProcessCond(cond_num, cond,
+        snapshot_mode)
+    left_joins.extend(cond_left_joins)
+    where.extend(cond_where)
+    unsupported_conds.extend(unsupported)
+
+  return left_joins, where, unsupported_conds
+
+
+def _ProcessBlockedOnIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a blockedon_id=issue_id cond to SQL."""
+  return _ProcessRelatedIDCond(cond, alias, 'blockedon',
+      snapshot_mode=snapshot_mode)
+
+
+def _ProcessBlockingIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a blocking_id:1,2 cond to SQL."""
+  return _ProcessRelatedIDCond(cond, alias, 'blockedon', reverse_relation=True,
+      snapshot_mode=snapshot_mode)
+
+
+def _ProcessMergedIntoIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a mergedinto:1,2 cond to SQL."""
+  return _ProcessRelatedIDCond(cond, alias, 'mergedinto',
+      snapshot_mode=snapshot_mode)
+
+
+def _ProcessRelatedIDCond(cond, alias, kind, reverse_relation=False,
+                          snapshot_mode=False):
+  """Convert either blocking_id, blockedon_id, or mergedinto_id cond to SQL.
+
+  Normally, we query for issue_id values where the dst_issue_id matches the
+  IDs specified in the cond.  However, when reverse_relation is True, we
+  query for dst_issue_id values where issue_id matches.  This is done for
+  blockedon_id.
+  """
+  if snapshot_mode:
+    return [], [], [cond]
+
+  matching_issue_col = 'issue_id' if reverse_relation else 'dst_issue_id'
+  ret_issue_col = 'dst_issue_id' if reverse_relation else 'issue_id'
+  ext_kind = 'blocking' if reverse_relation else kind
+  left_join = []
+  where = []
+
+  issue_ids = cond.int_values
+  ext_issue_ids = cond.str_values
+  # Filter has:blockedon and has:blocking.
+  if (not issue_ids) and (not ext_issue_ids):
+    kind_cond_str, kind_cond_args = _Compare(
+      alias, ast_pb2.QueryOp.EQ, tracker_pb2.FieldTypes.STR_TYPE, 'kind',
+      [kind])
+    left_join_str = (
+        'IssueRelation AS {alias} ON Issue.id = {alias}.{ret_issue_col} AND '
+         '{kind_cond}').format(
+             alias=alias, ret_issue_col=ret_issue_col, kind_cond=kind_cond_str)
+    left_join_args = kind_cond_args
+    left_join.append((left_join_str, left_join_args))
+    kind_cond_str, kind_cond_args = _Compare(
+      'DIR', ast_pb2.QueryOp.EQ, tracker_pb2.FieldTypes.STR_TYPE, 'kind',
+      [ext_kind])
+    ext_left_join_str = ('DanglingIssueRelation AS DIR ON '
+        'Issue.id = DIR.issue_id AND {kind_cond}').format(
+            kind_cond=kind_cond_str)
+    left_join.append((ext_left_join_str, kind_cond_args))
+    where_str, where_args = _CompareAlreadyJoined(alias,
+      cond.op, ret_issue_col)
+    ext_where_str, ext_where_args = _CompareAlreadyJoined('DIR',
+      cond.op, 'issue_id')
+    where.append(('({where} OR {ext_where})'.format(
+      where=where_str, ext_where=ext_where_str),
+      where_args + ext_where_args))
+  # Filter kind using provided issue ids.
+  if issue_ids:
+    kind_cond_str, kind_cond_args = _Compare(
+      alias, ast_pb2.QueryOp.EQ, tracker_pb2.FieldTypes.STR_TYPE, 'kind',
+      [kind])
+    left_join_str = (
+        'IssueRelation AS {alias} ON Issue.id = {alias}.{ret_issue_col} AND '
+         '{kind_cond}').format(
+             alias=alias, ret_issue_col=ret_issue_col, kind_cond=kind_cond_str)
+    left_join_args = kind_cond_args
+    related_cond_str, related_cond_args = _Compare(
+        alias, ast_pb2.QueryOp.EQ, tracker_pb2.FieldTypes.INT_TYPE,
+        matching_issue_col, issue_ids)
+    left_join_str += ' AND {related_cond}'.format(related_cond=related_cond_str)
+    left_join_args += related_cond_args
+
+    left_join.append((left_join_str, left_join_args))
+    where.append(_CompareAlreadyJoined(alias, cond.op, ret_issue_col))
+  # Filter kind using provided external issue ids.
+  if ext_issue_ids:
+    kind_cond_str, kind_cond_args = _Compare(
+      'DIR', ast_pb2.QueryOp.EQ, tracker_pb2.FieldTypes.STR_TYPE, 'kind',
+      [ext_kind])
+    ext_left_join_str = ('DanglingIssueRelation AS DIR ON '
+        'Issue.id = DIR.issue_id AND {kind_cond}').format(
+            kind_cond=kind_cond_str)
+    related_cond_str, related_cond_args = _Compare(
+        'DIR', ast_pb2.QueryOp.EQ, tracker_pb2.FieldTypes.INT_TYPE,
+        'ext_issue_identifier', ext_issue_ids)
+    ext_left_join_str += ' AND {related_cond}'.format(
+        related_cond=related_cond_str)
+    kind_cond_args += related_cond_args
+
+    left_join.append((ext_left_join_str, kind_cond_args))
+    where.append(_CompareAlreadyJoined('DIR', cond.op, 'issue_id'))
+  return left_join, where, []
+
+
+def _GetFieldTypeAndValues(cond):
+  """Returns the field type and values to use from the condition.
+
+  This function should be used when we do not know what values are present on
+  the condition. Eg: cond.int_values could be set if ast2ast.py preprocessing is
+  first done. If that preprocessing is not done then str_values could be set
+  instead.
+  If both int values and str values exist on the condition then the int values
+  are returned.
+  """
+  if cond.int_values:
+    return tracker_pb2.FieldTypes.INT_TYPE, cond.int_values
+  else:
+    return tracker_pb2.FieldTypes.STR_TYPE, cond.str_values
+
+
+def _ProcessOwnerCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert an owner:substring cond to SQL."""
+  if snapshot_mode:
+    left_joins = [(
+        'User AS {alias} ON '
+        'IssueSnapshot.owner_id = {alias}.user_id'.format(alias=alias),
+        [])]
+  else:
+    left_joins = [(
+        'User AS {alias} ON (Issue.owner_id = {alias}.user_id '
+        'OR Issue.derived_owner_id = {alias}.user_id)'.format(alias=alias),
+        [])]
+  where = [_Compare(alias, cond.op, tracker_pb2.FieldTypes.STR_TYPE, 'email',
+                    cond.str_values)]
+
+  return left_joins, where, []
+
+
+def _ProcessOwnerIDCond(cond, _alias, _spare_alias, snapshot_mode):
+  """Convert an owner_id=user_id cond to SQL."""
+  if snapshot_mode:
+    field_type, field_values = _GetFieldTypeAndValues(cond)
+    explicit_str, explicit_args = _Compare(
+        'IssueSnapshot', cond.op, field_type, 'owner_id', field_values)
+    where = [(explicit_str, explicit_args)]
+  else:
+    field_type, field_values = _GetFieldTypeAndValues(cond)
+    explicit_str, explicit_args = _Compare(
+        'Issue', cond.op, field_type, 'owner_id', field_values)
+    derived_str, derived_args = _Compare(
+        'Issue', cond.op, field_type, 'derived_owner_id', field_values)
+    if cond.op in (ast_pb2.QueryOp.NE, ast_pb2.QueryOp.NOT_TEXT_HAS):
+      where = [(explicit_str, explicit_args), (derived_str, derived_args)]
+    else:
+      if cond.op == ast_pb2.QueryOp.IS_NOT_DEFINED:
+        op = ' AND '
+      else:
+        op = ' OR '
+      where = [
+          ('(' + explicit_str + op + derived_str + ')',
+           explicit_args + derived_args)]
+
+  return [], where, []
+
+
+def _ProcessOwnerLastVisitCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert an ownerlastvisit<timestamp cond to SQL."""
+  # TODO(jeffcarp): It is possible to support this on snapshots.
+  if snapshot_mode:
+    return [], [], [cond]
+
+  left_joins = [(
+      'User AS {alias} '
+      'ON (Issue.owner_id = {alias}.user_id OR '
+      'Issue.derived_owner_id = {alias}.user_id)'.format(alias=alias),
+      [])]
+  where = [_Compare(alias, cond.op, tracker_pb2.FieldTypes.INT_TYPE,
+                    'last_visit_timestamp', cond.int_values)]
+  return left_joins, where, []
+
+
+def _ProcessIsOwnerBouncing(cond, alias, _spare_alias, snapshot_mode):
+  """Convert an is:ownerbouncing cond to SQL."""
+  if snapshot_mode:
+    return [], [], [cond]
+
+  left_joins = [(
+      'User AS {alias} '
+      'ON (Issue.owner_id = {alias}.user_id OR '
+      'Issue.derived_owner_id = {alias}.user_id)'.format(alias=alias),
+      [])]
+  if cond.op == ast_pb2.QueryOp.EQ:
+    op = ast_pb2.QueryOp.IS_DEFINED
+  else:
+    op = ast_pb2.QueryOp.IS_NOT_DEFINED
+
+  where = [_Compare(alias, op, tracker_pb2.FieldTypes.INT_TYPE,
+                    'email_bounce_timestamp', [])]
+  return left_joins, where, []
+
+
+def _ProcessReporterCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a reporter:substring cond to SQL."""
+  if snapshot_mode:
+    left_joins = [(
+        'User AS {alias} ON IssueSnapshot.reporter_id = {alias}.user_id'.format(
+            alias=alias), [])]
+  else:
+    left_joins = [(
+        'User AS {alias} ON Issue.reporter_id = {alias}.user_id'.format(
+            alias=alias), [])]
+  where = [_Compare(alias, cond.op, tracker_pb2.FieldTypes.STR_TYPE, 'email',
+                    cond.str_values)]
+
+  return left_joins, where, []
+
+
+def _ProcessReporterIDCond(cond, _alias, _spare_alias, snapshot_mode):
+  """Convert a reporter_ID=user_id cond to SQL."""
+  field_type, field_values = _GetFieldTypeAndValues(cond)
+
+  if snapshot_mode:
+    where = [_Compare(
+        'IssueSnapshot', cond.op, field_type, 'reporter_id', field_values)]
+  else:
+    where = [_Compare(
+        'Issue', cond.op, field_type, 'reporter_id', field_values)]
+  return [], where, []
+
+
+def _ProcessCcCond(cond, alias, user_alias, snapshot_mode):
+  """Convert a cc:substring cond to SQL."""
+  email_cond_str, email_cond_args = _Compare(
+      user_alias, ast_pb2.QueryOp.TEXT_HAS, tracker_pb2.FieldTypes.STR_TYPE,
+      'email', cond.str_values)
+
+  if snapshot_mode:
+    left_joins = [(
+        '(IssueSnapshot2Cc AS {alias} JOIN User AS {user_alias} '
+        'ON {alias}.cc_id = {user_alias}.user_id AND {email_cond}) '
+        'ON IssueSnapshot.id = {alias}.issuesnapshot_id'.format(
+            alias=alias, user_alias=user_alias, email_cond=email_cond_str),
+        email_cond_args)]
+  else:
+    # Note: email_cond_str will have parens, if needed.
+    left_joins = [(
+        '(Issue2Cc AS {alias} JOIN User AS {user_alias} '
+        'ON {alias}.cc_id = {user_alias}.user_id AND {email_cond}) '
+        'ON Issue.id = {alias}.issue_id AND '
+        'Issue.shard = {alias}.issue_shard'.format(
+            alias=alias, user_alias=user_alias, email_cond=email_cond_str),
+        email_cond_args)]
+  where = [_CompareAlreadyJoined(user_alias, cond.op, 'email')]
+
+  return left_joins, where, []
+
+
+def _ProcessCcIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a cc_id=user_id cond to SQL."""
+  if snapshot_mode:
+    join_str = (
+        'IssueSnapshot2Cc AS {alias} '
+        'ON IssueSnapshot.id = {alias}.issuesnapshot_id'.format(alias=alias))
+  else:
+    join_str = (
+        'Issue2Cc AS {alias} ON Issue.id = {alias}.issue_id AND '
+        'Issue.shard = {alias}.issue_shard'.format(
+            alias=alias))
+  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
+    left_joins = [(join_str, [])]
+  else:
+    field_type, field_values = _GetFieldTypeAndValues(cond)
+    cond_str, cond_args = _Compare(
+        alias, ast_pb2.QueryOp.EQ, field_type, 'cc_id', field_values)
+    left_joins = [(join_str + ' AND ' + cond_str, cond_args)]
+
+  where = [_CompareAlreadyJoined(alias, cond.op, 'cc_id')]
+  return left_joins, where, []
+
+
+def _ProcessStarredByCond(cond, alias, user_alias, snapshot_mode):
+  """Convert a starredby:substring cond to SQL."""
+  if snapshot_mode:
+    return [], [], [cond]
+
+  email_cond_str, email_cond_args = _Compare(
+      user_alias, cond.op, tracker_pb2.FieldTypes.STR_TYPE, 'email',
+      cond.str_values)
+  # Note: email_cond_str will have parens, if needed.
+  left_joins = [(
+      '(IssueStar AS {alias} JOIN User AS {user_alias} '
+      'ON {alias}.user_id = {user_alias}.user_id AND {email_cond}) '
+      'ON Issue.id = {alias}.issue_id'.format(
+          alias=alias, user_alias=user_alias, email_cond=email_cond_str),
+      email_cond_args)]
+  where = [_CompareAlreadyJoined(user_alias, cond.op, 'email')]
+
+  return left_joins, where, []
+
+
+def _ProcessStarredByIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a starredby_id=user_id cond to SQL."""
+  if snapshot_mode:
+    return [], [], [cond]
+
+  join_str = 'IssueStar AS {alias} ON Issue.id = {alias}.issue_id'.format(
+      alias=alias)
+  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
+    left_joins = [(join_str, [])]
+  else:
+    field_type, field_values = _GetFieldTypeAndValues(cond)
+    cond_str, cond_args = _Compare(
+        alias, ast_pb2.QueryOp.EQ, field_type, 'user_id', field_values)
+    left_joins = [(join_str + ' AND ' + cond_str, cond_args)]
+
+  where = [_CompareAlreadyJoined(alias, cond.op, 'user_id')]
+  return left_joins, where, []
+
+
+def _ProcessCommentByCond(cond, alias, user_alias, snapshot_mode):
+  """Convert a commentby:substring cond to SQL."""
+  if snapshot_mode:
+    return [], [], [cond]
+
+  email_cond_str, email_cond_args = _Compare(
+      user_alias, ast_pb2.QueryOp.TEXT_HAS, tracker_pb2.FieldTypes.STR_TYPE,
+      'email', cond.str_values)
+  # Note: email_cond_str will have parens, if needed.
+  left_joins = [(
+      '(Comment AS {alias} JOIN User AS {user_alias} '
+      'ON {alias}.commenter_id = {user_alias}.user_id AND {email_cond}) '
+      'ON Issue.id = {alias}.issue_id AND '
+      '{alias}.deleted_by IS NULL'.format(
+          alias=alias, user_alias=user_alias, email_cond=email_cond_str),
+      email_cond_args)]
+  where = [_CompareAlreadyJoined(user_alias, cond.op, 'email')]
+
+  return left_joins, where, []
+
+
+def _ProcessCommentByIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a commentby_id=user_id cond to SQL."""
+  if snapshot_mode:
+    return [], [], [cond]
+
+  field_type, field_values = _GetFieldTypeAndValues(cond)
+  commenter_cond_str, commenter_cond_args = _Compare(
+      alias, ast_pb2.QueryOp.EQ, field_type, 'commenter_id', field_values)
+  left_joins = [(
+      'Comment AS {alias} ON Issue.id = {alias}.issue_id AND '
+      '{commenter_cond} AND '
+      '{alias}.deleted_by IS NULL'.format(
+          alias=alias, commenter_cond=commenter_cond_str),
+      commenter_cond_args)]
+  where = [_CompareAlreadyJoined(alias, cond.op, 'commenter_id')]
+
+  return left_joins, where, []
+
+
+def _ProcessStatusIDCond(cond, _alias, _spare_alias, snapshot_mode):
+  """Convert a status_id=ID cond to SQL."""
+  field_type, field_values = _GetFieldTypeAndValues(cond)
+  if snapshot_mode:
+    explicit_str, explicit_args = _Compare(
+        'IssueSnapshot', cond.op, field_type, 'status_id', field_values)
+    where = [(explicit_str, explicit_args)]
+  else:
+    explicit_str, explicit_args = _Compare(
+        'Issue', cond.op, field_type, 'status_id', field_values)
+    derived_str, derived_args = _Compare(
+        'Issue', cond.op, field_type, 'derived_status_id', field_values)
+    if cond.op in (ast_pb2.QueryOp.IS_NOT_DEFINED, ast_pb2.QueryOp.NE):
+      where = [(explicit_str, explicit_args), (derived_str, derived_args)]
+    else:
+      where = [
+          ('(' + explicit_str + ' OR ' + derived_str + ')',
+           explicit_args + derived_args)]
+
+  return [], where, []
+
+
+def _ProcessSummaryCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a summary="exact string" cond to SQL."""
+  left_joins = []
+  where = []
+  field_type, field_values = _GetFieldTypeAndValues(cond)
+  if snapshot_mode:
+    return [], [], [cond]
+  elif cond.op in (ast_pb2.QueryOp.EQ, ast_pb2.QueryOp.NE,
+                   ast_pb2.QueryOp.GT, ast_pb2.QueryOp.LT,
+                   ast_pb2.QueryOp.GE, ast_pb2.QueryOp.LE,
+                   ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
+    summary_cond_str, summary_cond_args = _Compare(
+        alias, cond.op, field_type, 'summary', field_values)
+    left_joins = [(
+        'IssueSummary AS {alias} ON Issue.id = {alias}.issue_id AND '
+        '{summary_cond}'.format(
+          alias=alias, summary_cond=summary_cond_str),
+        summary_cond_args)]
+    where = [_CompareAlreadyJoined(alias, ast_pb2.QueryOp.EQ, 'issue_id')]
+
+  return left_joins, where, []
+
+
+def _ProcessLabelIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a label_id=ID cond to SQL."""
+  if snapshot_mode:
+    join_str = (
+        'IssueSnapshot2Label AS {alias} '
+        'ON IssueSnapshot.id = {alias}.issuesnapshot_id'.format(alias=alias))
+  else:
+    join_str = (
+        'Issue2Label AS {alias} ON Issue.id = {alias}.issue_id AND '
+        'Issue.shard = {alias}.issue_shard'.format(alias=alias))
+
+  field_type, field_values = _GetFieldTypeAndValues(cond)
+  if not field_values and cond.op == ast_pb2.QueryOp.NE:
+    return [], [], []
+  cond_str, cond_args = _Compare(
+      alias, ast_pb2.QueryOp.EQ, field_type, 'label_id', field_values)
+  left_joins = [(join_str + ' AND ' + cond_str, cond_args)]
+  where = [_CompareAlreadyJoined(alias, cond.op, 'label_id')]
+  return left_joins, where, []
+
+
+def _ProcessComponentIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert a component_id=ID cond to SQL."""
+  # This is a built-in field, so it shadows any other fields w/ the same name.
+  if snapshot_mode:
+    join_str = (
+        'IssueSnapshot2Component AS {alias} '
+        'ON IssueSnapshot.id = {alias}.issuesnapshot_id'.format(alias=alias))
+  else:
+    join_str = (
+        'Issue2Component AS {alias} ON Issue.id = {alias}.issue_id AND '
+        'Issue.shard = {alias}.issue_shard'.format(alias=alias))
+  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
+    left_joins = [(join_str, [])]
+  else:
+    field_type, field_values = _GetFieldTypeAndValues(cond)
+    cond_str, cond_args = _Compare(
+        alias, ast_pb2.QueryOp.EQ, field_type, 'component_id', field_values)
+    left_joins = [(join_str + ' AND ' + cond_str, cond_args)]
+
+  where = [_CompareAlreadyJoined(alias, cond.op, 'component_id')]
+  return left_joins, where, []
+
+
+# TODO(jojang): monorail:3819, check for cond.phase_name and process
+# appropriately so users can search 'Canary.UXReview-status:Approved'
+def _ProcessApprovalFieldCond(cond, alias, user_alias, snapshot_mode):
+  """Convert a custom approval field cond to SQL."""
+  if snapshot_mode:
+    return [], [], [cond]
+
+  approval_fd = cond.field_defs[0]
+  left_joins = []
+
+  join_str_tmpl = (
+    '{tbl_name} AS {alias} ON Issue.id = {alias}.issue_id AND '
+    '{alias}.approval_id = %s')
+
+  join_args = [approval_fd.field_id]
+
+  val_type, values = _GetFieldTypeAndValues(cond)
+  if val_type is tracker_pb2.FieldTypes.STR_TYPE:
+    values = [val.lower() for val in values]
+  # TODO(jojwang):monorail:3809, check if there is a cond.key_suffx.
+  # status, approver should always have a value, so 'has:UXReview-approver'
+  # should return the same issues as 'has:UXReview'.
+  # There will not always be values approval.setter_id and approval.set_on
+  # and the current code would not process 'has:UXReview-by' correctly.
+  if cond.op in (
+      ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
+    join_str = join_str_tmpl.format(
+        tbl_name='Issue2ApprovalValue', alias=alias)
+    left_joins = [(join_str, join_args)]
+  else:
+    op = cond.op
+    if op == ast_pb2.QueryOp.NE:
+      op = ast_pb2.QueryOp.EQ  # Negation is done in WHERE clause.
+    elif op == ast_pb2.QueryOp.NOT_TEXT_HAS:
+      op = ast_pb2.QueryOp.TEXT_HAS
+
+    if (not cond.key_suffix) or cond.key_suffix == query2ast.STATUS_SUFFIX:
+      tbl_str = 'Issue2ApprovalValue'
+      cond_str, cond_args = _Compare(
+          alias, op, val_type, 'status', values)
+    elif cond.key_suffix == query2ast.SET_ON_SUFFIX:
+      tbl_str = 'Issue2ApprovalValue'
+      cond_str, cond_args = _Compare(
+          alias, op, val_type, 'set_on', values)
+    elif cond.key_suffix in [
+        query2ast.APPROVER_SUFFIX, query2ast.SET_BY_SUFFIX]:
+      if cond.key_suffix == query2ast.SET_BY_SUFFIX:
+        tbl_str = 'Issue2ApprovalValue'
+        col_name = 'setter_id'
+      else:
+        tbl_str = 'IssueApproval2Approver'
+        col_name = 'approver_id'
+
+      if val_type == tracker_pb2.FieldTypes.INT_TYPE:
+        cond_str, cond_args = _Compare(
+            alias, op, val_type, col_name, values)
+      else:
+        email_cond_str, email_cond_args = _Compare(
+            user_alias, op, val_type, 'email', values)
+        left_joins.append((
+          'User AS {user_alias} ON {email_cond}'.format(
+              user_alias=user_alias, email_cond=email_cond_str),
+          email_cond_args))
+
+        cond_str = '{alias}.{col_name} = {user_alias}.user_id'.format(
+            alias=alias, col_name=col_name, user_alias=user_alias)
+        cond_args = []
+    if cond_str or cond_args:
+      join_str = join_str_tmpl.format(tbl_name=tbl_str, alias=alias)
+      join_str += ' AND ' + cond_str
+      join_args.extend(cond_args)
+    left_joins.append((join_str, join_args))
+
+  where = [_CompareAlreadyJoined(alias, cond.op, 'approval_id')]
+  return left_joins, where, []
+
+
+def _ProcessCustomFieldCond(
+    cond, alias, user_alias, phase_alias, snapshot_mode):
+  """Convert a custom field cond to SQL."""
+  if snapshot_mode:
+    return [], [], [cond]
+
+  # TODO(jrobbins): handle ambiguous field names that map to multiple
+  # field definitions, especially for cross-project search.
+  field_def = cond.field_defs[0]
+  field_type = field_def.field_type
+  left_joins = []
+
+  join_str = (
+      'Issue2FieldValue AS {alias} ON Issue.id = {alias}.issue_id AND '
+      'Issue.shard = {alias}.issue_shard AND '
+      '{alias}.field_id = %s'.format(alias=alias))
+  join_args = [field_def.field_id]
+
+  if cond.op not in (
+      ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
+    op = cond.op
+    if op == ast_pb2.QueryOp.NE:
+      op = ast_pb2.QueryOp.EQ  # Negation is done in WHERE clause.
+    if field_type == tracker_pb2.FieldTypes.INT_TYPE:
+      cond_str, cond_args = _Compare(
+          alias, op, field_type, 'int_value', cond.int_values)
+    elif field_type == tracker_pb2.FieldTypes.STR_TYPE:
+      cond_str, cond_args = _Compare(
+          alias, op, field_type, 'str_value', cond.str_values)
+    elif field_type == tracker_pb2.FieldTypes.USER_TYPE:
+      if cond.int_values:
+        cond_str, cond_args = _Compare(
+            alias, op, field_type, 'user_id', cond.int_values)
+      else:
+        email_cond_str, email_cond_args = _Compare(
+            user_alias, op, field_type, 'email', cond.str_values)
+        left_joins.append((
+            'User AS {user_alias} ON {email_cond}'.format(
+                user_alias=user_alias, email_cond=email_cond_str),
+            email_cond_args))
+        cond_str = '{alias}.user_id = {user_alias}.user_id'.format(
+            alias=alias, user_alias=user_alias)
+        cond_args = []
+    elif field_type == tracker_pb2.FieldTypes.URL_TYPE:
+      cond_str, cond_args = _Compare(
+          alias, op, field_type, 'url_value', cond.str_values)
+    if field_type == tracker_pb2.FieldTypes.DATE_TYPE:
+      cond_str, cond_args = _Compare(
+          alias, op, field_type, 'date_value', cond.int_values)
+    if cond_str or cond_args:
+      join_str += ' AND ' + cond_str
+      join_args.extend(cond_args)
+
+  if cond.phase_name:
+    phase_cond_str, phase_cond_args = _Compare(
+        phase_alias, ast_pb2.QueryOp.EQ, tracker_pb2.FieldTypes.STR_TYPE,
+        'name', [cond.phase_name])
+    left_joins.append((
+        'IssuePhaseDef AS {phase_alias} ON {phase_cond}'.format(
+            phase_alias=phase_alias, phase_cond=phase_cond_str),
+        phase_cond_args))
+    cond_str = '{alias}.phase_id = {phase_alias}.id'.format(
+        alias=alias, phase_alias=phase_alias)
+    cond_args = []
+    join_str += ' AND ' + cond_str
+    join_args.extend(cond_args)
+
+  left_joins.append((join_str, join_args))
+  where = [_CompareAlreadyJoined(alias, cond.op, 'field_id')]
+  return left_joins, where, []
+
+
+def _ProcessAttachmentCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert has:attachment and -has:attachment cond to SQL."""
+  if snapshot_mode:
+    return [], [], [cond]
+
+  if cond.op in (ast_pb2.QueryOp.IS_DEFINED, ast_pb2.QueryOp.IS_NOT_DEFINED):
+    left_joins = []
+    where = [_Compare('Issue', cond.op, tracker_pb2.FieldTypes.INT_TYPE,
+                      'attachment_count', cond.int_values)]
+  else:
+    field_def = cond.field_defs[0]
+    field_type = field_def.field_type
+    left_joins = [
+      ('Attachment AS {alias} ON Issue.id = {alias}.issue_id AND '
+       '{alias}.deleted = %s'.format(alias=alias),
+       [False])]
+    where = [_Compare(alias, cond.op, field_type, 'filename', cond.str_values)]
+
+  return left_joins, where, []
+
+
+def _ProcessHotlistIDCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert hotlist_id=IDS cond to SQL."""
+  if snapshot_mode:
+    join_str = (
+      'IssueSnapshot2Hotlist AS {alias} '
+      'ON IssueSnapshot.id = {alias}.issuesnapshot_id'.format(alias=alias))
+  else:
+    join_str = (
+      'Hotlist2Issue AS {alias} ON Issue.id = {alias}.issue_id'.format(
+          alias=alias))
+
+  field_type, field_values = _GetFieldTypeAndValues(cond)
+  if not field_values and cond.op == ast_pb2.QueryOp.NE:
+    return [], [], []
+  cond_str, cond_args = _Compare(
+      alias, ast_pb2.QueryOp.EQ, field_type, 'hotlist_id', field_values)
+  left_joins = [(join_str + ' AND ' + cond_str, cond_args)]
+  where = [_CompareAlreadyJoined(alias, cond.op, 'hotlist_id')]
+
+  return left_joins, where, []
+
+
+def _ProcessHotlistCond(cond, alias, _spare_alias, snapshot_mode):
+  """Convert hotlist=user:hotlist-name to SQL"""
+  # hotlist conditions that reach this function definitely have invalid
+  # user_name/id/email. This validity was determined in
+  # ast2ast._PreprocessHotlistCond. Any possible user identification is ignored.
+  hotlist_substrings = []
+  for val in cond.str_values:
+    substring = val.split(':')[-1]
+    if substring:
+      hotlist_substrings.append(substring)
+  hotlist_cond_str, hotlist_cond_args = _Compare(
+      alias, ast_pb2.QueryOp.TEXT_HAS, tracker_pb2.FieldTypes.STR_TYPE,
+      'name', hotlist_substrings)
+  if snapshot_mode:
+    left_joins = [(
+        '(IssueSnapshot2Hotlist JOIN Hotlist AS {alias} '
+        'ON IssueSnapshot2Hotlist.hotlist_id = {alias}.id AND {hotlist_cond}) '
+        'ON IssueSnapshot.id = IssueSnapshot2Hotlist.issuesnapshot_id'.format(
+            alias=alias, hotlist_cond=hotlist_cond_str), hotlist_cond_args)]
+  else:
+    left_joins = [(
+        '(Hotlist2Issue JOIN Hotlist AS {alias} '
+        'ON Hotlist2Issue.hotlist_id = {alias}.id AND {hotlist_cond}) '
+        'ON Issue.id = Hotlist2Issue.issue_id'.format(
+            alias=alias, hotlist_cond=hotlist_cond_str), hotlist_cond_args)]
+  where = [_CompareAlreadyJoined(alias, cond.op, 'name')]
+
+  return left_joins, where, []
+
+
+def _ProcessPhaseCond(cond, alias, phase_alias, _snapshot_mode):
+  """Convert gate:<phase_name> to SQL."""
+
+  op = cond.op
+  if cond.op == ast_pb2.QueryOp.NE:
+    op = ast_pb2.QueryOp.EQ
+  elif cond.op == ast_pb2.QueryOp.NOT_TEXT_HAS:
+    op = ast_pb2.QueryOp.TEXT_HAS
+
+  cond_str, cond_args = _Compare(
+      phase_alias, op, tracker_pb2.FieldTypes.STR_TYPE,
+      'name', cond.str_values)
+  left_joins = [(
+      '(Issue2ApprovalValue AS {alias} JOIN IssuePhaseDef AS {phase_alias} '
+      'ON {alias}.phase_id = {phase_alias}.id AND {name_cond}) '
+      'ON Issue.id = {alias}.issue_id'.format(
+          alias=alias, phase_alias=phase_alias, name_cond=cond_str),
+      cond_args)]
+  where = [_CompareAlreadyJoined(phase_alias, cond.op, 'name')]
+
+  return left_joins, where, []
+
+
+_PROCESSORS = {
+    'owner': _ProcessOwnerCond,
+    'owner_id': _ProcessOwnerIDCond,
+    'ownerlastvisit': _ProcessOwnerLastVisitCond,
+    'ownerbouncing': _ProcessIsOwnerBouncing,
+    'reporter': _ProcessReporterCond,
+    'reporter_id': _ProcessReporterIDCond,
+    'cc': _ProcessCcCond,
+    'cc_id': _ProcessCcIDCond,
+    'starredby': _ProcessStarredByCond,
+    'starredby_id': _ProcessStarredByIDCond,
+    'commentby': _ProcessCommentByCond,
+    'commentby_id': _ProcessCommentByIDCond,
+    'status_id': _ProcessStatusIDCond,
+    'summary': _ProcessSummaryCond,
+    'label_id': _ProcessLabelIDCond,
+    'component_id': _ProcessComponentIDCond,
+    'blockedon_id': _ProcessBlockedOnIDCond,
+    'blocking_id': _ProcessBlockingIDCond,
+    'mergedinto_id': _ProcessMergedIntoIDCond,
+    'attachment': _ProcessAttachmentCond,
+    'hotlist_id': _ProcessHotlistIDCond,
+    'hotlist': _ProcessHotlistCond,
+    }
+
+
+def _ProcessCond(cond_num, cond, snapshot_mode):
+  """Translate one term of the user's search into an SQL query.
+
+  Args:
+    cond_num: integer cond number used to make distinct local variable names.
+    cond: user query cond parsed by query2ast.py.
+
+  Returns:
+    A pair of lists (left_joins, where) to use when building the SQL SELECT
+    statement.  Each of them is a list of (str, [val, ...]) pairs.
+  """
+  alias = 'Cond%d' % cond_num
+  spare_alias = 'Spare%d' % cond_num
+  # Note: a condition like [x=y] has field_name "x", there may be multiple
+  # field definitions that match "x", but they will all have field_name "x".
+  field_def = cond.field_defs[0]
+  assert all(field_def.field_name == fd.field_name for fd in cond.field_defs)
+
+  if field_def.field_name in NATIVE_SEARCHABLE_FIELDS:
+    # TODO(jeffcarp): Support local_id search here.
+    if snapshot_mode:
+      return [], [], [cond]
+    else:
+      col = NATIVE_SEARCHABLE_FIELDS[field_def.field_name]
+      where = [_Compare(
+          'Issue', cond.op, field_def.field_type, col,
+          cond.str_values or cond.int_values)]
+      return [], where, []
+
+  elif field_def.field_name in _PROCESSORS:
+    proc = _PROCESSORS[field_def.field_name]
+    return proc(cond, alias, spare_alias, snapshot_mode)
+
+  #  Any phase conditions use the sql.SHORTHAND['phase_cond'], which expects a
+  # 'Phase' alias. 'phase_cond' cannot expect a 'Spare' alias because
+  # _ProcessCustomFieldCond also creates a phase_cond string where it uses the
+  # 'Phase' alias because it needs the 'Spare' alias for other conditions.
+  elif field_def.field_name == 'gate':
+    phase_alias = 'Phase%d' % cond_num
+    return _ProcessPhaseCond(cond, alias, phase_alias, snapshot_mode)
+
+  elif field_def.field_id:  # it is a search on a custom field
+    phase_alias = 'Phase%d' % cond_num
+    if field_def.field_type == tracker_pb2.FieldTypes.APPROVAL_TYPE:
+      return _ProcessApprovalFieldCond(cond, alias, spare_alias, snapshot_mode)
+    return _ProcessCustomFieldCond(
+        cond, alias, spare_alias, phase_alias, snapshot_mode)
+
+  elif (cond.op in (ast_pb2.QueryOp.TEXT_HAS, ast_pb2.QueryOp.NOT_TEXT_HAS) and
+        (field_def.field_name in tracker_fulltext.ISSUE_FULLTEXT_FIELDS or
+         field_def.field_name == 'any_field')):
+    if snapshot_mode:
+      return [], [], [cond]
+    # This case handled by full-text search.
+
+  else:
+    logging.error('untranslated search cond %r', cond)
+
+  return [], [], []
+
+
+def _Compare(alias, op, val_type, col, vals):
+  """Return an SQL comparison for the given values. For use in WHERE or ON.
+
+  Args:
+    alias: String name of the table or alias defined in a JOIN clause.
+    op: One of the operators defined in ast_pb2.py.
+    val_type: One of the value types defined in ast_pb2.py.
+    col: string column name to compare to vals.
+    vals: list of values that the user is searching for.
+
+  Returns:
+    (cond_str, cond_args) where cond_str is a SQL condition that may contain
+    some %s placeholders, and cond_args is the list of values that fill those
+    placeholders.  If the condition string contains any AND or OR operators,
+    the whole expression is put inside parens.
+
+  Raises:
+    NoPossibleResults: The user's query is impossible to ever satisfy, e.g.,
+        it requires matching an empty set of labels.
+  """
+  vals_ph = sql.PlaceHolders(vals)
+  if col in ['label', 'status', 'email', 'name']:
+    alias_col = 'LOWER(%s.%s)' % (alias, col)
+  else:
+    alias_col = '%s.%s' % (alias, col)
+
+  def Fmt(cond_str):
+    return cond_str.format(alias_col=alias_col, vals_ph=vals_ph)
+
+  no_value = (0 if val_type in [tracker_pb2.FieldTypes.DATE_TYPE,
+                                tracker_pb2.FieldTypes.INT_TYPE] else '')
+  if op == ast_pb2.QueryOp.IS_DEFINED:
+    return Fmt('({alias_col} IS NOT NULL AND {alias_col} != %s)'), [no_value]
+  if op == ast_pb2.QueryOp.IS_NOT_DEFINED:
+    return Fmt('({alias_col} IS NULL OR {alias_col} = %s)'), [no_value]
+
+  if val_type in [tracker_pb2.FieldTypes.DATE_TYPE,
+                  tracker_pb2.FieldTypes.INT_TYPE]:
+    if op == ast_pb2.QueryOp.TEXT_HAS:
+      op = ast_pb2.QueryOp.EQ
+    if op == ast_pb2.QueryOp.NOT_TEXT_HAS:
+      op = ast_pb2.QueryOp.NE
+
+  if op == ast_pb2.QueryOp.EQ:
+    if not vals:
+      raise NoPossibleResults('Column %s has no possible value' % alias_col)
+    elif len(vals) == 1:
+      cond_str = Fmt('{alias_col} = %s')
+    else:
+      cond_str = Fmt('{alias_col} IN ({vals_ph})')
+    return cond_str, vals
+
+  if op == ast_pb2.QueryOp.NE:
+    if not vals:
+      return 'TRUE', []  # a no-op that matches every row.
+    elif len(vals) == 1:
+      comp = Fmt('{alias_col} != %s')
+    else:
+      comp = Fmt('{alias_col} NOT IN ({vals_ph})')
+    return '(%s IS NULL OR %s)' % (alias_col, comp), vals
+
+  wild_vals = ['%%%s%%' % val for val in vals]
+  if op == ast_pb2.QueryOp.TEXT_HAS:
+    cond_str = ' OR '.join(Fmt('{alias_col} LIKE %s') for v in vals)
+    return ('(%s)' % cond_str), wild_vals
+  if op == ast_pb2.QueryOp.NOT_TEXT_HAS:
+    cond_str = (Fmt('{alias_col} IS NULL OR ') +
+                ' AND '.join(Fmt('{alias_col} NOT LIKE %s') for v in vals))
+    return ('(%s)' % cond_str), wild_vals
+
+
+  # Note: These operators do not support quick-OR
+  val = vals[0]
+
+  if op == ast_pb2.QueryOp.GT:
+    return Fmt('{alias_col} > %s'), [val]
+  if op == ast_pb2.QueryOp.LT:
+    return Fmt('{alias_col} < %s'), [val]
+  if op == ast_pb2.QueryOp.GE:
+    return Fmt('{alias_col} >= %s'), [val]
+  if op == ast_pb2.QueryOp.LE:
+    return Fmt('{alias_col} <= %s'), [val]
+
+  logging.error('unknown op: %r', op)
+
+
+def _CompareAlreadyJoined(alias, op, col):
+  """Return a WHERE clause comparison that checks that a join succeeded."""
+  def Fmt(cond_str):
+    return cond_str.format(alias_col='%s.%s' % (alias, col))
+
+  if op in (ast_pb2.QueryOp.NE, ast_pb2.QueryOp.NOT_TEXT_HAS,
+            ast_pb2.QueryOp.IS_NOT_DEFINED):
+    return Fmt('{alias_col} IS NULL'), []
+  else:
+    return Fmt('{alias_col} IS NOT NULL'), []
+
+
+class Error(Exception):
+  """Base class for errors from this module."""
+
+
+class NoPossibleResults(Error):
+  """The query could never match any rows from the database, so don't try.."""