NEQ doesn't work on joins

[oddanderson]

Bug report

• [X] I confirm this is a bug with Supabase, not with my own application.
• [X] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Unable to use database filters on inner joins for nullable foreign keys

e.g.

from("tableA").select(", tableB()").eq("tableB.column", value) --> works from("tableA").select(", tableB()").neq("tableB.column", value) --> does not work, always empty

To Reproduce

Schema:

create table "public"."cities" (
    "id" uuid not null default gen_random_uuid(),
    "created_at" timestamp with time zone not null default now(),
    "country_id" uuid not null,
    "name" text not null
);

create table "public"."countries" (
    "id" uuid not null default gen_random_uuid(),
    "created_at" timestamp with time zone not null default now(),
    "active" boolean not null default true,
    "name" text not null,
    "random_id" uuid
);

create table "public"."random" (
    "id" uuid not null default gen_random_uuid(),
    "created_at" timestamp with time zone not null default now()
);

CREATE UNIQUE INDEX cities_pkey ON public.cities USING btree (id);
CREATE UNIQUE INDEX countries_pkey ON public.countries USING btree (id);
CREATE UNIQUE INDEX random_pkey ON public.random USING btree (id);

alter table "public"."cities" add constraint "cities_pkey" PRIMARY KEY using index "cities_pkey";
alter table "public"."countries" add constraint "countries_pkey" PRIMARY KEY using index "countries_pkey";
alter table "public"."random" add constraint "random_pkey" PRIMARY KEY using index "random_pkey";
alter table "public"."cities" add constraint "public_cities_country_id_fkey" FOREIGN KEY (country_id) REFERENCES countries(id) not valid;
alter table "public"."cities" validate constraint "public_cities_country_id_fkey";
alter table "public"."countries" add constraint "public_countries_random_id_fkey" FOREIGN KEY (random_id) REFERENCES random(id) not valid;
alter table "public"."countries" validate constraint "public_countries_random_id_fkey";

Test Code

const client: SupabaseClient = createClient(
    supabaseUrl,
    supabaseKey,
    options,
  );

  const { data: random } = await client.from("random").insert({}).select()
    .single();
  console.log(random);
  const { data: random2 } = await client.from("random").insert({}).select()
    .single();
  const { data: country1 } = await client.from("countries").insert({
    name: "USA",
    random_id: random.id,
  }).select().single();
  const { data: country2 } = await client.from("countries").insert({
    name: "Canada",
    active: false,
  }).select().single();

  const { data: city1 } = await client.from("cities").insert({
    name: "Atlanta",
    country_id: country1.id,
  }).select().single();
  const { data: city2 } = await client.from("cities").insert({
    name: "Seattle",
    country_id: country1.id,
  }).select().single();
  const { data: city3 } = await client.from("cities").insert({
    name: "Toronto",
    country_id: country2.id,
  }).select().single();

  const { data: cities1 } = await client.from("cities").select(
    "id, name, countries!inner(id, name, random_id, active)",
  ).eq("countries.active", true);
  console.log("expect american cities only");
  console.log(cities1);
  const { data: cities2 } = await client.from("cities").select(
    "id, name, countries!inner(id, name, random_id, active)",
  ).eq("countries.active", false);
  console.log("expect canada cities only");
  console.log(cities2);
  const { data: cities3 } = await client.from("cities").select(
    "id, name, countries!inner(id, name, random_id, active)",
  ).neq("countries.random_id", random.id);
  console.log("use neq random, expect canada"); <--- THIS ONE FAILS
  console.log(cities3);
  const { data: cities4 } = await client.from("cities").select(
    "id, name, countries!inner(id, name, random_id, active)",
  ).eq("countries.active", true).eq("countries.random_id", random.id);
  console.log("use random + active, expect usa");
  console.log(cities4);

Output

{
  id: "4efd8ada-5d52-4dfd-9f86-b25768487fbc",
  created_at: "2024-07-05T18:00:19.007316+00:00"
}
expect american cities only
[
  {
    id: "f70133da-114b-4698-b3ba-adefca117ae2",
    name: "Atlanta",
    countries: {
      id: "178cc106-056d-443d-858f-bc3abf57a8d2",
      name: "USA",
      active: true,
      random_id: "4efd8ada-5d52-4dfd-9f86-b25768487fbc"
    }
  },
  {
    id: "a6deb4b5-47df-4de9-b664-6ebcd84fb0ab",
    name: "Seattle",
    countries: {
      id: "178cc106-056d-443d-858f-bc3abf57a8d2",
      name: "USA",
      active: true,
      random_id: "4efd8ada-5d52-4dfd-9f86-b25768487fbc"
    }
  }
]
expect canada cities only
[
  {
    id: "e25dbc47-cd78-4874-8dcc-8409e5dd524b",
    name: "Toronto",
    countries: {
      id: "e3904882-366a-4b47-b60f-c9687592bc08",
      name: "Canada",
      active: false,
      random_id: null
    }
  }
]
use neq random, expect canada cities
[]
use random + active, expect usa cities
[
  {
    id: "f70133da-114b-4698-b3ba-adefca117ae2",
    name: "Atlanta",
    countries: {
      id: "178cc106-056d-443d-858f-bc3abf57a8d2",
      name: "USA",
      active: true,
      random_id: "4efd8ada-5d52-4dfd-9f86-b25768487fbc"
    }
  },
  {
    id: "a6deb4b5-47df-4de9-b664-6ebcd84fb0ab",
    name: "Seattle",
    countries: {
      id: "178cc106-056d-443d-858f-bc3abf57a8d2",
      name: "USA",
      active: true,
      random_id: "4efd8ada-5d52-4dfd-9f86-b25768487fbc"
    }
  }
]

Expected behavior

use neq random, expect canada cities --> should return canadian cities

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: [e.g. macOS, Windows]
• Browser (if applies) [e.g. chrome, safari]
• Version of supabase-js: [e.g. 6.0.2]
• Version of Node.js: [e.g. 10.10.0]

Additional context

Add any other context about the problem here.