handling null values in comparision operator expressions

[karthikvt26]

Here is the screenshot of the operators and their types

I have a string column which is nullable. It can contain empty string ~ "" , null and non-zero length string. I would like to run a query with _in: ["", null]. But its not allowed based on the schema.

[rikinsk]

@karthikvt26 We can achieve this using the follwing condition right?

{
  _or: [
    { _is_null: true},
    {_in: [""]}
  ]
}

[rikinsk]

cc: @coco98 @0x777. Related to our ongoing discussion about dealing with null values

[karthikvt26]

@rikinsk Yes that is exactly what I am doing. But this could become problematic when it comes to coding it since we are adding it at the first level of where.

[rikinsk]

But this could become problematic when it comes to coding it since we are adding it at the first level of where.

@karthikvt26 Can you explain this in more detail. Whats the exact problem here because of that?

[karthikvt26]

For example:
Lets assume user can filter by column A, column B, column C. Now after the filters are selected its easy to construct a query as below

    where: {
        columnA: {
           _in: [<selected_values>]
        },
        columnB: {
           _in: [<selected_values>]
        },
        columnC: {
           _in: [<selected_values>]
        }
    }

rather than a query as below

    where: {
       _or: [
          columnA: {
             _in: [""]
          },
          columnA: {
            { _is_null: true},
          },
        ]
        columnB: {
           _in: [<selected_values>]
        },
        columnC: {
           _in: [<selected_values>]
        }
    }

Now if there is a scenario user is trying to filter by "" or null for columnB too, it becomes slightly cumbersome to convert the entire where clause into _and and array of _or conditions. Hope this makes sense and let me know if I am missing something

[rikinsk]

So the takeaway for me here is that devs dont want to think of null as a special value and find it easier if it were treated like any other value while using the API.

@coco98 @0x777 Should we just do the handling of nulls internally in hasura and call it a day regarding all edge cases caused by null values.

[karthikvt26]

I think the reasoning is more from a point of view of NULL being a valid value representing a blank column

[0x777]

Should we just do the handling of nulls internally in hasura and call it a day regarding all edge cases caused by null values

We can try to do this but it'll be a little tricky, for example, if we want _in: ["", null] to work, we won't be able to use column = ANY ($1) but we should instead translate it to column = ANY($1) OR column IS NULL when there is null in the given arguments.

[RapidOwl]

Regarding the overall handling of nulls, I just found my delete mutation deleted all of the data in a table. It turns out this was because I accidentally passed a null value to a parameter in my query.

Instead of running the _eq against the null parameter, it appears the _eq is skipped. This then means additional validation code in my application because my "database" layer doesn't behave as a database would.

Is this one of the edge cases that @rikinsk mentions above?

[rikinsk]

@RapidOwl This is an issue with the way nulls are handled in compare expressions. You can see #704 (comment) (and the thread before it for context) on the latest status of this issue

[RapidOwl]

Awesome thanks for pointing me in the right direction. For now, I’ll super sanitise my inputs.

[eizemazal]

When dealing with conditions in Hasura 1.3.3, seems that passing null to _eq, _like, and other comparison operators effectively switched filtering off.

This was very convenient for me when developing JS frontend, because I have a bunch of queries like that:

query q($limit: Int = 10, $offset: Int = 0, $like: String = null,
    $status:String, $purification:jsonb, $scale:jsonb, $length_min:Int, $length_max:Int,
    $seq_like: String, $seq_unlike: String, $oligo_type: String) {
    entity_name(
      limit: $limit, offset: $offset,
      order_by: {id: desc},
where: {
        _and: [
          { _or:[
            {sequence: {_ilike: $like}},
            {name: {_ilike: $like}}
          ]},
          {meta:{_contains:$purification}},
          {meta:{_contains:$scale}},
          {status:{_eq:$status}},
          {length:{_gte:$length_min}},
          {length:{_lte:$length_max}},
          {sequence: {_ilike: $seq_like}},
          {sequence: {_nilike: $seq_unlike}},
          { oligo_type: {_eq: $oligo_type }},
        ]}) {
.....

It worked when I passed null values to the variables and thus switched off corresponding conditions. But unexpectedly, this is broken in Hasura 2.0! For string operators, I can pass '%' easily, but for integer, there is no universally matching value. Wondering if handling of nulls is desired behavior and what coding approach is suggested for these cases? Constructing graphql queries programmatically by including conditions is cumbersome...