[Bug]: Smart substitution and prepared statements need you to explicitly cast fields

[Nikhil-Nandagopal]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

Users have reported that when working with data types in SQL, JSON and number are impossible to insert without an explicit cast.
Similarly in APIs if an input has a number, the value is always sent as a string.

Steps To Reproduce

1. Drag an Input widget
2. Create a postgres query to insert the input value into a table column with integer type
3. Enter a value and run the query
4. The query fails unless we cast it to an integer as

Insert into table1 (col1) values ({{Input1.text}} :: Integer)

The same can be reproduced for an API

Environment

Production

Version

Cloud

[sumitsum]

@Nikhil-Nandagopal can you please explain what the ask here is ? AFAIK, the reason explicit cast was introduced was because when a number is fed as input, it can be interpreted as both a string and a number, with either case being valid in different scenarios. Is the ask here to auto detect if the number should be casted to an integer or not ?

[Nikhil-Nandagopal]

@sumitsum yes the ask is the auto-detect types like JSON and number to automatically cast them

[leotom2000]

Auto-detect of types like JSON and Numbers are happening.
It seems this issue might have been addressed by the following PR's.

PR	Author	Date
fix: Json smart substitution breaks when evaluated value contains the character '?' #7031	Trisha Anand	02/09/2021
Adding support for JSON object fields in Postgres for Prepared Statement #6761	Trisha Anand	23/8/2021

This issue was raised on 25/10/2021 based on the earlier feedback of some users.
Could be the users experienced this issue before the above PR changes were moved to Production,
or the users could have used the old build without the above updates.

Can this be moved to QA for testing.
@sumitsum @nidhi-nair @trishaanand @ajinkyakulkarni

[sribalajig]

@subrata71 @btsgh can you please check if this issue is still valid?

[subrata71]

If the target column in the database is of type integer and the input comes from an input widget, then the input widget must be of Number type.
As Postgres is very strict with data types, the user must ensure the value provided to a Postgres column must match the same type.

The issue is not valid considering the above fact.
@sribalajig

[sumitsum]

@subrata71 APMU, the ask here could be to look at the column type that we already have and use that type instead of the inferred type. cc: @sribalajig

[subrata71]

This looks like a valid ask for the DB schema project where we are supposed to leverage the information of the underlying schema. @sumitsum