[BUDI-7444] Queries use blank string instead of column default value when binding default value is empty

[abreland-amd]

Checklist

• I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: Docker Compose
  • Budibase Version: 2.9.30
  • App Version: 2.9.30

I am using a MySQL 8.0.34 database and one of my tables has the following columns:

But when I create a query and try to insert, using the following SQL/handlebars:

INSERT INTO configs (type, customer_project_id, board_id, vendor_id, build_phase_id, cvl_id)
VALUES ( {{ type }}, {{ customer_project_id }}, {{ board_id}}, {{ vendor_id}}, {{ build_phase_id}}, {{ cvl_id}} );

and the following bindings:

I get an incorrect integer value error, as follows:

I am assuming this is happening because BudiBase is trying to insert an empty string to an int column, because I left some of the binding defaults blank. In my case, those columns are nullable and have a default value of NULL, but since BudiBase is trying to insert an empty string it is getting rejected. Is there any way to use NULL as a default value directly? Or more generally, can BudiBase use the column default value when there is no default value in the binding field?

Notably, BudiBase is able to save the row just fine (And use the column default value of NULL) when I use the 'Save Row' action instead to save this data, instead of building a query.

To Reproduce
Steps to reproduce the behavior:

1. Create a MySQL database table and add a nullable INT field with a default value of null
2. Create a new query in BudiBase. Set up a binding for the INT field, but leave the default value blank. In the insert query, use handlebars syntax to refer to the binding ( Ex: INSERT INTO TestTable (NullableIntColumn) VALUES ( {{ NullableIntColumnBinding }} )
3. Run the query, and observe the error from the above screenshot.

Expected behavior
When no default value is set in the bindings, and no data has been passed to the binding from the app, BudiBase will insert the column default value.

App Export
If possible - please attach an export of your budibase application for debugging/reproduction purposes.

Desktop (please complete the following information):

• OS: Windows 11
• Browser: Chrome
• Version: 116.0.5845.140

_{From SyncLinear.com | BUDI-7444}