searchable: true + NULL value causes the record not to be displayed

[phortx]

Describe the bug I have a nullable string field, with searchable: true. When the field is null, it will not be displayed in the index even without touching the search field. When I remove searchable: true or insert an value, it will be displayed in the index table.

To Reproduce Steps to reproduce the behavior:

• Create a record with a nullable string field
• Set it to searchable
• Create a record and leave the field on NULL / nil
• Visit index page
• Record is not displayed

Expected behavior I would have expected to see the record on index page unless I start a search.

Please complete the following information:

• OS: MacOS
• Browser Arc
• Elixir Version 1.17.2
• Backpex Version 0.6.0

[Flo0807]

Hey, can't reproduce this. I changed the addresses city field in our demo to null: true locally. Everything seems to work 🤔

https://github.com/user-attachments/assets/190ed2f1-b06a-4fda-8850-92bb89b0251f

Can you provide additional information on how I can reproduce this?

[phortx]

I've tried to reproduce it with the city field of the demo too and can't reproduce it.

But then I removed the street and zip fields and boom the line is gone (also added ID field to have an overview).

Initial Setup:

def fields do
    [
      street: %{
        module: Backpex.Fields.Text,
        label: "Street Name",
        searchable: true
      },
      zip: %{
        module: Backpex.Fields.Text,
        label: "Zip Code",
        searchable: true
      },
      city: %{
        module: Backpex.Fields.Text,
        label: "City",
        searchable: true
      },
      country: %{
        module: Backpex.Fields.Select,
        label: "Country",
        options: [Germany: "de", Austria: "at", Switzerland: "ch"]
      }
    ]
  end

Result:

You see the last line doesn't have a city and items count is 10.

---

Removing zip & street (and adding id, which doesn't make a difference):

def fields do
    [
      id: %{
        module: Backpex.Fields.Number,
        label: "ID",
        except: [:create, :update]
      },
      city: %{
        module: Backpex.Fields.Text,
        label: "City",
        searchable: true,
        required: false
      },
      country: %{
        module: Backpex.Fields.Select,
        label: "Country",
        options: [Germany: "de", Austria: "at", Switzerland: "ch"]
      }
    ]
  end

Result:

You see that the last line is gone and item count is 9.

Content of the addresses table:

postgres=# select * from addresses;
                  id                  |        street         |  zip  |     city      | country |     inserted_at     |     updated_at
--------------------------------------+-----------------------+-------+---------------+---------+---------------------+---------------------
 8200eefe-d7e7-45d5-86ce-f23671897f0a | 0891 Rocky River      | 88346 | O'Keefe       | at      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 f893f404-d31c-4e14-be82-2e26fd52b3a2 | 0 Shields Burg        | 48471 | West Coy      | de      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 acfd6f9c-a957-4324-a871-5f06eb7b07d6 | 06748 Bode Light      | 55517 | North Asia    | at      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 c95f6d2e-0574-4f7c-a6d6-54414c75a04b | 2842 Emmerich Cove    | 17815 | Oren          | ch      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 b4e90a21-5568-4348-94f1-41b979911fdf | 0728 Judy Wells       | 85444 | West Kelly    | de      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 d59fd679-f2a8-4920-9d53-0f285a2cde47 | 186 Jeromy Alley      | 69693 | Lacy          | de      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 435a134c-03ca-4ed0-b268-f850aaaea177 | 59191 Upton Dam       | 09624 | New Virginia  | ch      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 aa56d876-4407-46ed-abff-273e9abb27cf | 4 Legros Square       | 61992 | South Mariane | de      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 cb7a1c81-38da-488e-b847-643e93e2bcca | 06 Ola Creek          | 27130 | Connor        | ch      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14
 c2c1f0a3-f5de-43ea-950b-6568d15f996a | 7 Gorczany Throughway | 76237 |               | ch      | 2024-09-17 12:24:14 | 2024-09-17 12:24:14

Last line doesn't have a city. Changed via update addresses set city = null where id = 'c2c1f0a3-f5de-43ea-950b-6568d15f996a';.

Hope that helps :)

[Flo0807]

Hey @phortx, thanks for the detailed description. I will look into it!

[dogweather]

You might want to do a search for CJ Date and his writings about NULLs in relational databases. Tldr; avoid it because this is exactly the problem you end up with.