weird ilike postgres behavior

Aarekmaz5/14/2023
hi, I have a very weird issue when trying to filter using ilike in postgres, both my local instance which is running in docker and fly.io postgres, which suggests this is a drizzle issue, here is the schema of my table:
export const points = pgTable("points", {
...defaultCols,
name: text("name").notNull(),
grouping: text("grouping").notNull(),
statusId: integer("status_id")
.references(() => statuses.id)
.notNull(),
latitude: real("latitude").notNull(),
longitude: real("longitude").notNull(),
projectId: integer("project_id")
.references(() => projects.id)
.notNull(),
meta: json("meta"),
});
export const points = pgTable("points", {
...defaultCols,
name: text("name").notNull(),
grouping: text("grouping").notNull(),
statusId: integer("status_id")
.references(() => statuses.id)
.notNull(),
latitude: real("latitude").notNull(),
longitude: real("longitude").notNull(),
projectId: integer("project_id")
.references(() => projects.id)
.notNull(),
meta: json("meta"),
});
the full code of my remix loader:
export const loader = async (args: LoaderArgs) => {
const { request, params } = args;

const { searchParams } = new URL(request.url);

const { phrase, projectId } = paramsSchema.parse(searchParams);
const like = `%${phrase}%`;

const query = db
.select()
.from(points)
.where(
and(
eq(points.projectId, projectId),
phrase
? or(ilike(points.grouping, like), ilike(points.name, like))
: undefined,
),
)
.limit(500);

const results = await query.execute();

return json({ results, sql: query.toSQL() });
};
export const loader = async (args: LoaderArgs) => {
const { request, params } = args;

const { searchParams } = new URL(request.url);

const { phrase, projectId } = paramsSchema.parse(searchParams);
const like = `%${phrase}%`;

const query = db
.select()
.from(points)
.where(
and(
eq(points.projectId, projectId),
phrase
? or(ilike(points.grouping, like), ilike(points.name, like))
: undefined,
),
)
.limit(500);

const results = await query.execute();

return json({ results, sql: query.toSQL() });
};
the non expected weird thing is that only searching by the name column works, the grouping is ignored, if I exclude grouping from where clause, like so: phrase ? ilike(points.name, like) : undefined,, search by name is working, but, if I do the same with the grouping column phrase ? ilike(points.grouping, like) : undefined, no results are matched, even though .toSQL output works in the db explorer console, what could be the issue? I attached some screens of the feature I'm working on
Vvolks5/15/2023
Enable logging in drizzle and view the raw sql query that it executes, then you can see whether or not its correct
Aarekmaz5/15/2023
I have and the issue is, the sql is correct, but when I run it in db tool it gives a different result than drizzle never mind, I just found out that it's actually correct, it's react-select that doesn't display the results, thanks for help

Looking for more? Join the community!