Query JSON with SQL, coalesce not working

App Building
,
1

Hi Retool Community,

An interesting issue:
the coalesce will return null, even the resp.FIRSTNAME is null but raw.FIRSTNAME is not

image
image651×717 30.7 KB

with an additional nullif(resp.FIRSTNAME, null) wrapped around, it can now function correctly.

image
image625×642 22.1 KB

Is this a bug?

2

Hello @zelterNN!

That is odd that the coalesce operator is not picking up that the raw.FIRSTNAME is not null, yet the nullif() can pick up that the value is not null and return the value to run properly.

Will be looking to duplicate the bug and report it to our eng team. Did you notice this only in "Query JSON with SQL" or does the coalesce have this same issue with other queries such as from a Postgres table?

Did the order of the inputs matter at all? Thank you for finding this!

3

Hi @Jack_T,

Thanks for your reply.

So far, I noticed this issue only on the 'Query JSON with SQL'. The coalesce in the data pull directly from SQL Server works as desired.

A few more examples to show you:

I created a one line data in the SQL Server database, here's the three column I'm gonna test
image

pulled it into the Retool

image
image778×385 10.3 KB

query the data pull in 'Query Json with SQL'

image
image751×1032 40 KB

  • if the null value is recognized as undefined, then the coalesce works properly as if undefined = null
  • if the null value is recognized as null, then the coalesce is not working.
  • order of the items in coalesce does matter. it seems the issue is just the null not being processed properly not the coalesce itself.
4

Another interesting overservation:

the table I used to created the dummy data has actually more than three column

If I run

select * from XXX

then on the later 'Query Json with SQL', Retool will show FirstName as 'undefined' instead of 'null' and the coalesece works as desired

image
image723×916 33.2 KB

However,
If I run

select CreateDate, FirstName, fldLanguages from XXX

then the subsequent 'Query Json with SQL' will show all three column as null as in my previous reply.

5

@zelterNN thank you so much for the detailed bug hunting!

Very interesting, so in the case where the null value for 'FirstName' is queried and displayed by Retool as undefined.

With the COALESCE operator working as it should, but the value should be null and not undefined as the table has null stored in it.....

And the reason the value shows up as undefined instead of null is because you used SELECT * on the table :thinking: where as when you specify the columns in the SQL query it properly grabs null values.

I think you are right in postulating that the issue seems to be that null is not being processed properly. Will create a bug report for our eng team to look at.

Thanks again!