Zero Values in Sources/Queries force datatype of column to text resulting in column sorting problems

[SufiSR]

Checklist

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

Hosting

• Self
  • Method: Docker Single Image
  • Budibase Version: 2.1.4.2

Describe the bug
When working with Queries as Sources (in my case MYSQL) apparently an autodiscovery of the field types is made based on the first result of the query.

• If a column in the first result row contains a 0 value the whole column in the schema is considered as TEXT. This makes it impossible to sort the column from high to low for users in tables in frontend (even though other result rows may have numbers in that column)
• The Schema Tab under results pretends to offer the option to change the data type for any column, however changing the datatype will not be saved (no error message either)

CURRENT Workaround If you change the query with an order by so that the first result row does not contain 0 but another number, the same column will be considered as NUMBER in the schema and this will then eventually allow for proper sorting

To Reproduce
Steps to reproduce the behavior:

1. In Sources create a query that will deliver a 0 in a column as the first result row (e.g.: SELECT "myname" as MyName, 0 as Thisshouldbeanumber,"" as thisshouldbetext)
2. Run the Query
3. Scroll down to Results and look at the JSON Tab - you will see Thisshouldbeanumber: "0" <-- interpreted as TEXT
4. Switch Tabs to Schema and you will see Key Thisshouldbeanumber : TEXT

Expected behavior
Alternative a) 0 are interpreted as a number instead of text
Alternative b) Changes to the schema under results will allow for saving and explicitly setting a column to a specific datatype

Screenshots

Desktop (please complete the following information):

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

Other Notes
This is my first issue report. Feel free to change the wording and headline as needed :)