Cannot bulk clear or update when a table contains a error formula field which has a db column type not compatible with text
Steps to reproduce
- Create a single select field
- Reference the single select field with a formula field
- Delete the single select field breaking the formula field
- Try to bulk copy/paste/clear
- See the error
The formula field goes into an error state, which internally we use a TextField model field to represent on the model. But we don't actually alter the underlying db column to become a text field. Normally this is OK as we can just return Null for all error formulas. However Djangos bulk_update function explicitly cast's any expression we set to the modelfield's type causing an error as the actual column is a JSONB column.
ERROR 2022-07-15 11:59:54,807 django.request.log_response:230- Internal Server Error: /api/database/rows/table/257/batch/
Traceback (most recent call last):
File "/baserow/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.DatatypeMismatch: column "field_2024" is of type jsonb but expression is of type text
LINE 1: ... END) ELSE NULL END AS date), "field_2024" = CAST(CASE WHEN ...
^
HINT: You will need to rewrite or cast the expression.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/baserow/venv/lib/python3.7/site-packages/silk/sql.py", line 87, in execute_sql
return self._execute_sql(*args, **kwargs)
File "/baserow/venv/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
cursor.execute(sql, params)
File "/baserow/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 98, in execute
return super().execute(sql, params)
File "/baserow/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/baserow/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/baserow/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/baserow/venv/lib/python3.7/site-packages/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/baserow/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "field_2024" is of type jsonb but expression is of type text
LINE 1: ... END) ELSE NULL END AS date), "field_2024" = CAST(CASE WHEN ...
^
HINT: You will need to rewrite or cast the expression.
The fix
Possibly we should just never call bulk_update for an error formula field? Or perhaps somehow we need to change the error type to remember it's actual underlying database column? Not sure
Edited by Nigel Gott