Description:
Setting a JSON column default to ('{') will cause the default value to return an error when inserting data into the table.
This can break production applications easily as new columns are often added with default values before the application is updated to actually populate and use the column.
Not all defaults can be checked on column/table creation, but simple literals can and this is a common usecase.
How to repeat:
sql> CREATE TABLE j (id SERIAL PRIMARY KEY, j JSON DEFAULT ('{'));
Query OK, 0 rows affected (0.0583 sec)
sql> INSERT INTO j VALUES ROW();
ERROR: 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'j.j'.
sql> ALTER TABLE J MODIFY COLUMN j JSON DEFAULT ('{}');
ERROR: 1146 (42S02): Table 'test.J' doesn't exist
sql> ALTER TABLE j MODIFY COLUMN j JSON DEFAULT ('{}');
Query OK, 0 rows affected (0.0262 sec)
Records: 0 Duplicates: 0 Warnings: 0
sql> INSERT INTO j VALUES ROW();
Query OK, 1 row affected (0.0148 sec)
sql> TABLE j;
+----+----+
| id | j |
+----+----+
| 1 | {} |
+----+----+
1 row in set (0.0008 sec)
Suggested fix:
Let the "CREATE TABLE ..." or "ALTER TABLE ... ADD COLUMN ..." fail if a JSON column has a literal as default that doesn't parse as valid JSON.
Description: Setting a JSON column default to ('{') will cause the default value to return an error when inserting data into the table. This can break production applications easily as new columns are often added with default values before the application is updated to actually populate and use the column. Not all defaults can be checked on column/table creation, but simple literals can and this is a common usecase. How to repeat: sql> CREATE TABLE j (id SERIAL PRIMARY KEY, j JSON DEFAULT ('{')); Query OK, 0 rows affected (0.0583 sec) sql> INSERT INTO j VALUES ROW(); ERROR: 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'j.j'. sql> ALTER TABLE J MODIFY COLUMN j JSON DEFAULT ('{}'); ERROR: 1146 (42S02): Table 'test.J' doesn't exist sql> ALTER TABLE j MODIFY COLUMN j JSON DEFAULT ('{}'); Query OK, 0 rows affected (0.0262 sec) Records: 0 Duplicates: 0 Warnings: 0 sql> INSERT INTO j VALUES ROW(); Query OK, 1 row affected (0.0148 sec) sql> TABLE j; +----+----+ | id | j | +----+----+ | 1 | {} | +----+----+ 1 row in set (0.0008 sec) Suggested fix: Let the "CREATE TABLE ..." or "ALTER TABLE ... ADD COLUMN ..." fail if a JSON column has a literal as default that doesn't parse as valid JSON.