Database enum breaks built in filter all columns, all views including Builder

[JohnnyMarnell]

Checklist

• I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Cloud
  • Tenant ID: partsorder.budibase.app

Describe the bug
In custom Budibase views built as well as built in Datasource table view pages in Data, clicking on magnifying glass and typing anything yields:

There was a problem loading your grid
Table updated externally, please re-fetch - operator does not exist: <any enum type> ~~* unknown

To Reproduce
Steps to reproduce the behavior:

1. Create or use any existing Postgres SQL datasource and table with an ENUM type, e.g. CREATE TYPE public.fruit_enum AS ENUM ('APPLE', 'BANANA');
2. Re-fetch
3. Click on Magnifying glass in column header in Data -> Postgres SQL -> table view (or any custom page using this) and type.
4. See error

Expected behavior
Filtering works, as for text fields (maybe casting ::text would fix?). Would also be great if these quick filters defaulted to matching substring anywhere, as opposed to prefix / start.

Screenshots

App Export
If possible - please attach an export of your budibase application for debugging/reproduction purposes.

Desktop (please complete the following information):

• OS: Mac Sonoma 14.16
• Chrome
• Tried multiple Budibase app versions, e.g. latest + 3.2.11

[linear]

BUDI-8889 Database enum breaks built in filter all columns, all views including Builder

[ConorWebb96]

Hey @JohnnyMarnell,

Do you have the table create scripts? I wasn't able to replicate this myself.

Can you also include the version of Postgres you are using?

[JohnnyMarnell]

Hi thanks for taking a look, the problem is happenning with any postgres enum, looks like you have status and test already. e.g.:

CREATE TYPE staus AS ENUM ('pending', 'processing');

In your screenshot views above, if you click the icon next to status and test column headers so it turns to a magnifying glass to filter, do you get the error?

[ConorWebb96]

@JohnnyMarnell,

Thanks for the clarity. I was able to replicate it.

Attaching a simple database create script for our engineers.

Postgres

CREATE SEQUENCE IF NOT EXISTS orders_order_id_seq;
DROP TYPE IF EXISTS "public"."order_status";
CREATE TYPE "public"."order_status" AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
CREATE TABLE "public"."orders" (
    "order_id" int4 NOT NULL DEFAULT nextval('orders_order_id_seq'::regclass),
    "status" "public"."order_status",
    PRIMARY KEY ("order_id")
);

[JohnnyMarnell]

Thanks for looking! When I snoop the underlying SQL query with tshark, I can see it's trying order_status ilike $1, and I think adding a cast to text for enum types would get around this as in order_status::text ilike $1.

By the way, it looks like Like conditional filtering on columns for SQL table data sources works with partial match "in" behavior (e.g. "o" matches "foo"), whereas these column magnifying glasses require prefix matching (e.g. "fo" matches "foo", but not "o"). Looks like they use same ilike under the hood (maybe the former surrounds with '%'?), it would be great if the column magnifying glass worked the same!