Able to order on an unorderable field #3066
Description
Description
Some of our types are unorderable (they don't have a defined order, e.g. arrays, json): see this method and docstring. Backend already has logic for not ordering by them automatically, however, both the frontend and the backend still allows to order by these types via the table view.
GET http://localhost:8000/api/db/v0/tables/24/records/?limit=500&offset=0&order_by=[{"field":933,"direction":"asc"}], where column 933 is of type point, returns a 400 with the body:
[
{
"code": 4207,
"message": "could not identify an ordering operator for type point",
"field": null,
"detail": "could not identify an ordering operator for type point",
"stacktrace": [
"1. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 1799, in _execute_context",
"2. self.dialect.do_execute(",
"3. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py\", line 717, in do_execute",
"4. cursor.execute(statement, parameters)",
"5. psycopg2.errors.UndefinedFunction: could not identify an ordering operator for type point",
"6. LINE 6: FROM anon_3 ORDER BY anon_3.point_column2 ASC),",
"7. ^",
"8. HINT: Use an explicit ordering operator or modify the query.",
"9. ",
"10. ",
"11. The above exception was the direct cause of the following exception:",
"12. ",
"13. Traceback (most recent call last):",
"14. File \"/code/db/utils.py\", line 18, in execute_statement",
"15. return conn.execute(statement)",
"16. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/future/engine.py\", line 280, in execute",
"17. return self._execute_20(",
"18. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 1611, in _execute_20",
"19. return meth(self, args_10style, kwargs_10style, execution_options)",
"20. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py\", line 325, in _execute_on_connection",
"21. return connection._execute_clauseelement(",
"22. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 1478, in _execute_clauseelement",
"23. ret = self._execute_context(",
"24. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 1842, in _execute_context",
"25. self._handle_dbapi_exception(",
"26. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 2023, in _handle_dbapi_exception",
"27. util.raise_(",
"28. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py\", line 207, in raise_",
"29. raise exception",
"30. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 1799, in _execute_context",
"31. self.dialect.do_execute(",
"32. File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py\", line 717, in do_execute",
"33. cursor.execute(statement, parameters)",
"34. sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) could not identify an ordering operator for type point",
"35. LINE 6: FROM anon_3 ORDER BY anon_3.point_column2 ASC),",
"36. ^",
"37. HINT: Use an explicit ordering operator or modify the query.",
"38. ",
"39. [SQL: WITH anon_3 AS",
"40. (SELECT public.my_table.text_column2 AS text_column2, public.my_table.point_column2 AS point_column2",
"41. FROM public.my_table),",
"42. anon_2 AS",
"43. (SELECT anon_3.text_column2 AS text_column2, anon_3.point_column2 AS point_column2",
"44. FROM anon_3 ORDER BY anon_3.point_column2 ASC),",
"45. anon_1 AS",
"46. (SELECT anon_2.text_column2 AS text_column2, anon_2.point_column2 AS point_column2",
"47. FROM anon_2",
"48. LIMIT %(param_1)s)",
"49. SELECT anon_1.text_column2, anon_1.point_column2",
"50. FROM anon_1]",
"51. [parameters: {'param_1': 500}]",
"52. (Background on this error at: https://sqlalche.me/e/14/f405)",
"53. ",
"54. During handling of the above exception, another exception occurred:",
"55. ",
"56. Traceback (most recent call last):",
"57. File \"/code/mathesar/api/db/viewsets/records.py\", line 71, in list",
"58. records = paginator.paginate_queryset(",
"59. File \"/code/mathesar/api/pagination.py\", line 95, in paginate_queryset",
"60. records = query.get_records(",
"61. File \"/code/mathesar/models/query.py\", line 71, in get_records",
"62. return self.db_query.get_records(**kwargs)",
"63. File \"/code/db/queries/base.py\", line 116, in get_records",
"64. return records_select.get_records(",
"65. File \"/code/db/records/operations/select.py\", line 68, in get_records",
"66. return execute_pg_query(engine, relation)",
"67. File \"/code/db/utils.py\", line 32, in execute_pg_query",
"68. return execute_statement(engine, executable, connection_to_use=connection_to_use).fetchall()",
"69. File \"/code/db/utils.py\", line 22, in execute_statement",
"70. raise exceptions.UndefinedFunction(message)",
"71. db.records.exceptions.UndefinedFunction: could not identify an ordering operator for type point",
"72. ",
"73. During handling of the above exception, another exception occurred:",
"74. ",
"75. Traceback (most recent call last):",
"76. File \"/usr/local/lib/python3.9/site-packages/rest_framework/views.py\", line 506, in dispatch",
"77. response = handler(request, *args, **kwargs)",
"78. File \"/code/mathesar/api/db/viewsets/records.py\", line 98, in list",
"79. raise database_api_exceptions.UndefinedFunctionAPIException(",
"80. mathesar.api.exceptions.database_exceptions.exceptions.UndefinedFunctionAPIException: [{'code': 4207, 'message': 'could not identify an ordering operator for type point', 'field': None, 'detail': 'could not identify an ordering operator for type point'}]"
]
}
]
Expected behavior
Frontend shouldn't allow creating orderings that are undefined, and backend should return a clearer error in case such a request is submitted.
To Reproduce
Have a table with an unorderable column and execute a similar HTTP request to the one outlined above.