[Bug]-[1120]:Where condition on GSheets fails to query if there's mixed data type present in column data

[ramsaptami]

Description

If a GSheet column has mixed data type (number and string) then where condition fails to query results that favour the filter condition.

This is an extension of #7688

Steps to reproduce the behaviour:

1. Go to GSheets datasource and add a new query
2. Add where condition for a table where mixed data types are present and observe the error

Important Details

• Version: Cloud
• OS: Win10
• Browser: Chrome
• Environment: Release

Front conversations

[leotom2000]

Findings and Resolution

Existing Behaviour and Failure reason

• The system takes the datatype of each column from the first row.
• if there is a 4 digit Number it considers all data in the columns and set it as an INTEGER
• In case another row contains a 15 digit Number, it still generates the insert query as preparedStatement.setInteger(15DigitNumber)
• so it fails

Resolution

• Understanding the handling and behaviour of datatype in googleSheet, the following is done
• (1) When we identify the datatype as INTEGER, we set the column dataType as LONG
• (2) When we identify the datatype as FLOAT, we set the column dataType as DOUBLE.
• Now with this fix, we can handle data with bigger datatype which are introduced later in lower rows of the grid.

Files Modified appsmith-interfaces/src/main/java/com/appsmith/external/constants/FieldName.java appsmith-interfaces/src/main/java/com/appsmith/external/helpers/DataTypeStringUtils.java appsmith-interfaces/src/main/java/com/appsmith/external/services/ce/FilterDataServiceCE.java appsmith-interfaces/src/main/java/com/appsmith/external/services/ce/IFilterDataServiceCE.java appsmith-plugins/googleSheetsPlugin/src/main/java/com/external/config/GetValuesMethod.java

Issues Fixed #9667 #7794 - This issue #8878

Generating a Single PR addressing the above 3 issues since they are related

[ramsaptami]

Reopening this since we no longer see an error but querying fails when using where clause on different datatypes

[leotom2000]

Earlier it was throwing error on mixed datatype, the existing fix ignores inconsistent datatype values. Patches can be done to resolve this issue, but since it would make the common plugin codes messy, moving this issue to product backlog. A new enhancement addressing many issues like this will handle this issue. (Schema for unstructured databases like Google Sheets #10705)

This issue can be closed when #10705 is closed.

[rohan-arthur]

gsheets usage in 6 months: 1400 assume 10% face this problem: 140

Stats

Stat	Values
Reach	140
Effort (months)	0.25

[ame-appsmith]

This issue was reported again on Discord I was able to reproduce it in this screen recording.

[felixsuarez0727]

We are able to reproduce this error. I am currently determining the specific scenarios in which they occur.

[felixsuarez0727]

The problem occurs when, working in a Gsheet document, we have a column that begins with a number of data type and in that same column we have a string data type. The execution of the query to filter the data will give an error.

For example:

We start the column with a data of type number in this column the bug will appear.

Error:

[yan-hic]

Is there any traction on this ? We experienced the same but not on filtering, the string type values are just not displayed if AS determined the column should be Number. Setting the column to plain text in AS or in GoogleSheet does not help. How can we stop this auto detect and instead force for a data type ??