prompt: We are currently solving the following issue within our repository. Here is the issue text:
  --- BEGIN ISSUE ---
  Ordering by a non-orderable column (implicitly or explicitly) causes a backend crash
  ## Bug
  
  Brent [writes](https://github.com/centerofci/mathesar/pull/2253#pullrequestreview-1251718380):
  
  > At one point, you note in a comment that there's a bug whenever we have internal columns of JSON type, and no primary key in a table. This bug is still present for _any_ non-orderable column type if there is no primary key in the table (e.g., if the user attaches to a preexisting DB). It's easy to figure out whether or not a type is orderable in PostgreSQL. See the [docs page for ORDER BY](https://www.postgresql.org/docs/current/queries-order.html#id-1.5.6.9.5.10) and the [docs page for Operator Classes](https://www.postgresql.org/docs/9.5/indexes-opclass.html).
  
  Brent is referring to [this comment](https://github.com/centerofci/mathesar/blob/8c215b2efdd740da0d530b624763cf260e12ca90/db/records/operations/sort.py#L47).
  
  The error message received upon such a crash is: `could not identify an ordering operator for type json` (`json` is the type of the unorderable column in this case.
  
  ## Solution
  
  The solution is, as explained by Brent, to make sure that every column we order by (implicitly or explicitly) is orderable.
  
  ## Severity
  
  Because of this, a JSON column (it's non-orderable), can make a query/table view unrenderable, but only when the table/query does not have a primary key, which cannot happen in a Mathesar-created table. It can only happen when Mathesar is used to access a pre-existing database.
  
  --- END ISSUE ---
  
  Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
  --- BEGIN FILES ---
  Path: `db/records/operations/sort.py`
  Content:
  ```
  1 from collections import namedtuple
  2 from sqlalchemy import select
  3 from db.columns import utils as col_utils
  4 from db.records.exceptions import BadSortFormat, SortFieldNotFound
  5 
  6 
  7 def make_order_by_deterministic(relation, order_by=None):
  8     """
  9     Makes an order_by deterministic (totally ordering).
  10 
  11     Given a relation, and a `order_by` spec, that defines the ordering to be applied to the
  12     relation, returns a new order_by that is the totally ordered (deterministic) version of the
  13     input order_by.
  14 
  15     Appending primary key sort guarantees determinism, but if that fails, we revert to ordering by
  16     all columns.
  17     """
  18     if order_by is None:
  19         order_by = []
  20     order_by = _append_primary_key_sort(relation, order_by)
  21     if not order_by:
  22         order_by = _build_order_by_all_columns_clause(relation)
  23     return order_by
  24 
  25 
  26 def _append_primary_key_sort(relation, order_by):
  27     """
  28     Given an order by clause, we can guarantee a deterministic ordering
  29     overall by appending a final ordering by primary key if one exists.
  30     """
  31     pk_cols = col_utils.get_primary_key_column_collection_from_relation(relation)
  32     order_by = list(order_by)
  33     if pk_cols is not None:
  34         order_by += [
  35             {'field': col, 'direction': 'asc'}
  36             for col
  37             in set(pk_cols).intersection(relation.columns)
  38         ]
  39     return order_by
  40 
  41 
  42 def _build_order_by_all_columns_clause(relation):
  43     """
  44     To be used when we have failed to find any other ordering criteria,
  45     since ordering by all columns is inherently inefficient.
  46 
  47     Note the filtering out of internal columns. Before applying this fix, psycopg was throwing an error
  48     like "could not identify an ordering operator for type json", because we were trying to
  49     sort by an internal column like `__mathesar_group_metadata`, which has type `json`, which
  50     requires special handling to be sorted. The problem is bypassed by not attempting to sort on
  51     internal columns.
  52     """
  53     return [
  54         {'field': col, 'direction': 'asc'}
  55         for col
  56         in relation.columns
  57         if not _is_internal_column(col)
  58     ]
  59 
  60 
  61 def _is_internal_column(col):
  62     """
  63     Might not be exhaustive, take care.
  64     """
  65     return col.name == '__mathesar_group_metadata'
  66 
  67 
  68 def apply_relation_sorting(relation, sort_spec):
  69     order_by_list = [
  70         _get_sorted_column_obj_from_spec(relation, spec) for spec in sort_spec
  71     ]
  72     return select(relation).order_by(*order_by_list)
  73 
  74 
  75 def _get_sorted_column_obj_from_spec(relation, spec):
  76     try:
  77         sort_spec = _deserialize_sort_spec(spec)
  78     except (KeyError, TypeError, AssertionError):
  79         raise BadSortFormat
  80 
  81     try:
  82         column = col_utils.get_column_obj_from_relation(relation, sort_spec.field)
  83     except KeyError as e:
  84         raise SortFieldNotFound(e)
  85     except AttributeError:
  86         raise BadSortFormat
  87 
  88     try:
  89         directed_col = _build_directed_column_expr(column, sort_spec)
  90     except AttributeError:
  91         raise BadSortFormat
  92 
  93     return directed_col
  94 
  95 
  96 def _deserialize_sort_spec(spec):
  97     sort_spec = namedtuple(
  98         '_sort_spec',
  99         ['field', 'direction', 'nullsfirst', 'nullslast']
  100     )(
  101         field=spec['field'],
  102         direction=spec['direction'],
  103         nullsfirst=spec.get('nullsfirst', False),
  104         nullslast=spec.get('nullslast', False)
  105     )
  106     # Since it's not valid to have both nullsfirst and nullslast.
  107     assert not sort_spec.nullsfirst or not sort_spec.nullslast
  108     return sort_spec
  109 
  110 
  111 def _build_directed_column_expr(column, sort_spec):
  112     directed_col = getattr(column, sort_spec.direction)()
  113     if sort_spec.nullsfirst:
  114         directed_col = directed_col.nulls_first()
  115     elif sort_spec.nullslast:
  116         directed_col = directed_col.nulls_last()
  117     return directed_col
  118
  ```
  --- END FILES ---
  
  Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.
  
  Here is an example:
  
  ```diff
  diff --git a/examples/server_async.py b/examples/server_async.py
  --- a/examples/server_async.py
  +++ b/examples/server_async.py
  @@ -313,4 +313,4 @@
   
   
   if __name__ == "__main__":
  -    asyncio.run(run_async_server("."), debug=True)
  +    asyncio.run(run_async_server(), debug=True)
  diff --git a/examples/server_sync.py b/examples/server_sync.py
  --- a/examples/server_sync.py
  +++ b/examples/server_sync.py
  @@ -313,5 +313,5 @@
   
   
   if __name__ == "__main__":
  -    server = run_sync_server(".")
  +    server = run_sync_server()
       server.shutdown()
  
  ```
  
golden_diff: diff --git a/db/records/operations/sort.py b/db/records/operations/sort.py
  --- a/db/records/operations/sort.py
  +++ b/db/records/operations/sort.py
  @@ -44,27 +44,37 @@
       To be used when we have failed to find any other ordering criteria,
       since ordering by all columns is inherently inefficient.
   
  -    Note the filtering out of internal columns. Before applying this fix, psycopg was throwing an error
  -    like "could not identify an ordering operator for type json", because we were trying to
  -    sort by an internal column like `__mathesar_group_metadata`, which has type `json`, which
  -    requires special handling to be sorted. The problem is bypassed by not attempting to sort on
  -    internal columns.
  +    Note the filtering out some columns, namely internal columns and non-orderable columns. See
  +    their docstrings for details.
       """
       return [
           {'field': col, 'direction': 'asc'}
           for col
           in relation.columns
  -        if not _is_internal_column(col)
  +        if _is_col_orderable(col) and not _is_internal_column(col)
       ]
   
   
   def _is_internal_column(col):
       """
  +    Columns that Mathesar adds for its own devices and does not expose to the user. We don't want
  +    to sort by these.
  +
       Might not be exhaustive, take care.
       """
       return col.name == '__mathesar_group_metadata'
   
   
  +def _is_col_orderable(col):
  +    """
  +    Some columns are not orderable (or at least don't have a non-ambiguous way to define order
  +    without additional logic). We only want to order by orderably columns.
  +    """
  +    data_type = col.type
  +    non_orderable_type = ['Binary', 'LargeBinary', 'PickleType', 'ARRAY', 'JSON', 'JSONB']
  +    return str(data_type) not in non_orderable_type
  +
  +
   def apply_relation_sorting(relation, sort_spec):
       order_by_list = [
           _get_sorted_column_obj_from_spec(relation, spec) for spec in sort_spec
  
verification_info: {"golden_diff": "diff --git a/db/records/operations/sort.py b/db/records/operations/sort.py\n--- a/db/records/operations/sort.py\n+++ b/db/records/operations/sort.py\n@@ -44,27 +44,37 @@\n     To be used when we have failed to find any other ordering criteria,\n     since ordering by all columns is inherently inefficient.\n \n-    Note the filtering out of internal columns. Before applying this fix, psycopg was throwing an error\n-    like \"could not identify an ordering operator for type json\", because we were trying to\n-    sort by an internal column like `__mathesar_group_metadata`, which has type `json`, which\n-    requires special handling to be sorted. The problem is bypassed by not attempting to sort on\n-    internal columns.\n+    Note the filtering out some columns, namely internal columns and non-orderable columns. See\n+    their docstrings for details.\n     \"\"\"\n     return [\n         {'field': col, 'direction': 'asc'}\n         for col\n         in relation.columns\n-        if not _is_internal_column(col)\n+        if _is_col_orderable(col) and not _is_internal_column(col)\n     ]\n \n \n def _is_internal_column(col):\n     \"\"\"\n+    Columns that Mathesar adds for its own devices and does not expose to the user. We don't want\n+    to sort by these.\n+\n     Might not be exhaustive, take care.\n     \"\"\"\n     return col.name == '__mathesar_group_metadata'\n \n \n+def _is_col_orderable(col):\n+    \"\"\"\n+    Some columns are not orderable (or at least don't have a non-ambiguous way to define order\n+    without additional logic). We only want to order by orderably columns.\n+    \"\"\"\n+    data_type = col.type\n+    non_orderable_type = ['Binary', 'LargeBinary', 'PickleType', 'ARRAY', 'JSON', 'JSONB']\n+    return str(data_type) not in non_orderable_type\n+\n+\n def apply_relation_sorting(relation, sort_spec):\n     order_by_list = [\n         _get_sorted_column_obj_from_spec(relation, spec) for spec in sort_spec\n", "issue": "Ordering by a non-orderable column (implicitly or explicitly) causes a backend crash\n## Bug\r\n\r\nBrent [writes](https://github.com/centerofci/mathesar/pull/2253#pullrequestreview-1251718380):\r\n\r\n> At one point, you note in a comment that there's a bug whenever we have internal columns of JSON type, and no primary key in a table. This bug is still present for _any_ non-orderable column type if there is no primary key in the table (e.g., if the user attaches to a preexisting DB). It's easy to figure out whether or not a type is orderable in PostgreSQL. See the [docs page for ORDER BY](https://www.postgresql.org/docs/current/queries-order.html#id-1.5.6.9.5.10) and the [docs page for Operator Classes](https://www.postgresql.org/docs/9.5/indexes-opclass.html).\r\n\r\nBrent is referring to [this comment](https://github.com/centerofci/mathesar/blob/8c215b2efdd740da0d530b624763cf260e12ca90/db/records/operations/sort.py#L47).\r\n\r\nThe error message received upon such a crash is: `could not identify an ordering operator for type json` (`json` is the type of the unorderable column in this case.\r\n\r\n## Solution\r\n\r\nThe solution is, as explained by Brent, to make sure that every column we order by (implicitly or explicitly) is orderable.\r\n\r\n## Severity\r\n\r\nBecause of this, a JSON column (it's non-orderable), can make a query/table view unrenderable, but only when the table/query does not have a primary key, which cannot happen in a Mathesar-created table. It can only happen when Mathesar is used to access a pre-existing database.\n", "before_files": [{"content": "from collections import namedtuple\nfrom sqlalchemy import select\nfrom db.columns import utils as col_utils\nfrom db.records.exceptions import BadSortFormat, SortFieldNotFound\n\n\ndef make_order_by_deterministic(relation, order_by=None):\n    \"\"\"\n    Makes an order_by deterministic (totally ordering).\n\n    Given a relation, and a `order_by` spec, that defines the ordering to be applied to the\n    relation, returns a new order_by that is the totally ordered (deterministic) version of the\n    input order_by.\n\n    Appending primary key sort guarantees determinism, but if that fails, we revert to ordering by\n    all columns.\n    \"\"\"\n    if order_by is None:\n        order_by = []\n    order_by = _append_primary_key_sort(relation, order_by)\n    if not order_by:\n        order_by = _build_order_by_all_columns_clause(relation)\n    return order_by\n\n\ndef _append_primary_key_sort(relation, order_by):\n    \"\"\"\n    Given an order by clause, we can guarantee a deterministic ordering\n    overall by appending a final ordering by primary key if one exists.\n    \"\"\"\n    pk_cols = col_utils.get_primary_key_column_collection_from_relation(relation)\n    order_by = list(order_by)\n    if pk_cols is not None:\n        order_by += [\n            {'field': col, 'direction': 'asc'}\n            for col\n            in set(pk_cols).intersection(relation.columns)\n        ]\n    return order_by\n\n\ndef _build_order_by_all_columns_clause(relation):\n    \"\"\"\n    To be used when we have failed to find any other ordering criteria,\n    since ordering by all columns is inherently inefficient.\n\n    Note the filtering out of internal columns. Before applying this fix, psycopg was throwing an error\n    like \"could not identify an ordering operator for type json\", because we were trying to\n    sort by an internal column like `__mathesar_group_metadata`, which has type `json`, which\n    requires special handling to be sorted. The problem is bypassed by not attempting to sort on\n    internal columns.\n    \"\"\"\n    return [\n        {'field': col, 'direction': 'asc'}\n        for col\n        in relation.columns\n        if not _is_internal_column(col)\n    ]\n\n\ndef _is_internal_column(col):\n    \"\"\"\n    Might not be exhaustive, take care.\n    \"\"\"\n    return col.name == '__mathesar_group_metadata'\n\n\ndef apply_relation_sorting(relation, sort_spec):\n    order_by_list = [\n        _get_sorted_column_obj_from_spec(relation, spec) for spec in sort_spec\n    ]\n    return select(relation).order_by(*order_by_list)\n\n\ndef _get_sorted_column_obj_from_spec(relation, spec):\n    try:\n        sort_spec = _deserialize_sort_spec(spec)\n    except (KeyError, TypeError, AssertionError):\n        raise BadSortFormat\n\n    try:\n        column = col_utils.get_column_obj_from_relation(relation, sort_spec.field)\n    except KeyError as e:\n        raise SortFieldNotFound(e)\n    except AttributeError:\n        raise BadSortFormat\n\n    try:\n        directed_col = _build_directed_column_expr(column, sort_spec)\n    except AttributeError:\n        raise BadSortFormat\n\n    return directed_col\n\n\ndef _deserialize_sort_spec(spec):\n    sort_spec = namedtuple(\n        '_sort_spec',\n        ['field', 'direction', 'nullsfirst', 'nullslast']\n    )(\n        field=spec['field'],\n        direction=spec['direction'],\n        nullsfirst=spec.get('nullsfirst', False),\n        nullslast=spec.get('nullslast', False)\n    )\n    # Since it's not valid to have both nullsfirst and nullslast.\n    assert not sort_spec.nullsfirst or not sort_spec.nullslast\n    return sort_spec\n\n\ndef _build_directed_column_expr(column, sort_spec):\n    directed_col = getattr(column, sort_spec.direction)()\n    if sort_spec.nullsfirst:\n        directed_col = directed_col.nulls_first()\n    elif sort_spec.nullslast:\n        directed_col = directed_col.nulls_last()\n    return directed_col\n", "path": "db/records/operations/sort.py"}], "after_files": [{"content": "from collections import namedtuple\nfrom sqlalchemy import select\nfrom db.columns import utils as col_utils\nfrom db.records.exceptions import BadSortFormat, SortFieldNotFound\n\n\ndef make_order_by_deterministic(relation, order_by=None):\n    \"\"\"\n    Makes an order_by deterministic (totally ordering).\n\n    Given a relation, and a `order_by` spec, that defines the ordering to be applied to the\n    relation, returns a new order_by that is the totally ordered (deterministic) version of the\n    input order_by.\n\n    Appending primary key sort guarantees determinism, but if that fails, we revert to ordering by\n    all columns.\n    \"\"\"\n    if order_by is None:\n        order_by = []\n    order_by = _append_primary_key_sort(relation, order_by)\n    if not order_by:\n        order_by = _build_order_by_all_columns_clause(relation)\n    return order_by\n\n\ndef _append_primary_key_sort(relation, order_by):\n    \"\"\"\n    Given an order by clause, we can guarantee a deterministic ordering\n    overall by appending a final ordering by primary key if one exists.\n    \"\"\"\n    pk_cols = col_utils.get_primary_key_column_collection_from_relation(relation)\n    order_by = list(order_by)\n    if pk_cols is not None:\n        order_by += [\n            {'field': col, 'direction': 'asc'}\n            for col\n            in set(pk_cols).intersection(relation.columns)\n        ]\n    return order_by\n\n\ndef _build_order_by_all_columns_clause(relation):\n    \"\"\"\n    To be used when we have failed to find any other ordering criteria,\n    since ordering by all columns is inherently inefficient.\n\n    Note the filtering out some columns, namely internal columns and non-orderable columns. See\n    their docstrings for details.\n    \"\"\"\n    return [\n        {'field': col, 'direction': 'asc'}\n        for col\n        in relation.columns\n        if _is_col_orderable(col) and not _is_internal_column(col)\n    ]\n\n\ndef _is_internal_column(col):\n    \"\"\"\n    Columns that Mathesar adds for its own devices and does not expose to the user. We don't want\n    to sort by these.\n\n    Might not be exhaustive, take care.\n    \"\"\"\n    return col.name == '__mathesar_group_metadata'\n\n\ndef _is_col_orderable(col):\n    \"\"\"\n    Some columns are not orderable (or at least don't have a non-ambiguous way to define order\n    without additional logic). We only want to order by orderably columns.\n    \"\"\"\n    data_type = col.type\n    non_orderable_type = ['Binary', 'LargeBinary', 'PickleType', 'ARRAY', 'JSON', 'JSONB']\n    return str(data_type) not in non_orderable_type\n\n\ndef apply_relation_sorting(relation, sort_spec):\n    order_by_list = [\n        _get_sorted_column_obj_from_spec(relation, spec) for spec in sort_spec\n    ]\n    return select(relation).order_by(*order_by_list)\n\n\ndef _get_sorted_column_obj_from_spec(relation, spec):\n    try:\n        sort_spec = _deserialize_sort_spec(spec)\n    except (KeyError, TypeError, AssertionError):\n        raise BadSortFormat\n\n    try:\n        column = col_utils.get_column_obj_from_relation(relation, sort_spec.field)\n    except KeyError as e:\n        raise SortFieldNotFound(e)\n    except AttributeError:\n        raise BadSortFormat\n\n    try:\n        directed_col = _build_directed_column_expr(column, sort_spec)\n    except AttributeError:\n        raise BadSortFormat\n\n    return directed_col\n\n\ndef _deserialize_sort_spec(spec):\n    sort_spec = namedtuple(\n        '_sort_spec',\n        ['field', 'direction', 'nullsfirst', 'nullslast']\n    )(\n        field=spec['field'],\n        direction=spec['direction'],\n        nullsfirst=spec.get('nullsfirst', False),\n        nullslast=spec.get('nullslast', False)\n    )\n    # Since it's not valid to have both nullsfirst and nullslast.\n    assert not sort_spec.nullsfirst or not sort_spec.nullslast\n    return sort_spec\n\n\ndef _build_directed_column_expr(column, sort_spec):\n    directed_col = getattr(column, sort_spec.direction)()\n    if sort_spec.nullsfirst:\n        directed_col = directed_col.nulls_first()\n    elif sort_spec.nullslast:\n        directed_col = directed_col.nulls_last()\n    return directed_col\n", "path": "db/records/operations/sort.py"}]}