Queries: including referenced tables excludes rows without reference

[barbalex]

I have these tables:

CREATE TABLE vector_layers(
  vector_layer_id uuid PRIMARY KEY DEFAULT NULL, 
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  label text DEFAULT NULL,
  project_id uuid NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE ON UPDATE CASCADE,
  type vector_layer_type_enum DEFAULT NULL, 
  display_by_property_field text DEFAULT NULL,
  sort smallint DEFAULT NULL,
  active boolean DEFAULT NULL,
  max_zoom integer DEFAULT NULL, 
  min_zoom integer DEFAULT NULL, 
  max_features integer DEFAULT NULL, 
  wfs_url text DEFAULT NULL, 
  wfs_layer jsonb DEFAULT NULL, 
  wfs_version text DEFAULT NULL, 
  wfs_output_formats jsonb DEFAULT NULL, 
  wfs_output_format jsonb DEFAULT NULL, 
  wfs_default_crs text DEFAULT NULL,
  feature_count integer DEFAULT NULL,
  point_count integer DEFAULT NULL,
  line_count integer DEFAULT NULL,
  polygon_count integer DEFAULT NULL
);
CREATE TABLE layer_presentations(
  layer_presentation_id uuid PRIMARY KEY DEFAULT NULL,
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  tile_layer_id uuid DEFAULT NULL REFERENCES tile_layers(tile_layer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  vector_layer_id uuid DEFAULT NULL REFERENCES vector_layers(vector_layer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  active boolean DEFAULT NULL,
  sort smallint DEFAULT NULL,
  opacity_percent integer DEFAULT NULL,
  grayscale boolean DEFAULT NULL
);
CREATE TABLE vector_layer_displays(
  vector_layer_display_id uuid PRIMARY KEY DEFAULT NULL,
  account_id uuid DEFAULT NULL REFERENCES accounts(account_id) ON DELETE CASCADE ON UPDATE CASCADE,
  vector_layer_id uuid DEFAULT NULL REFERENCES vector_layers(vector_layer_id) ON DELETE CASCADE ON UPDATE CASCADE,
  display_property_value text DEFAULT NULL,
  marker_type marker_type_enum DEFAULT NULL, 
  circle_marker_radius integer DEFAULT NULL, 
  marker_symbol text DEFAULT NULL,
  marker_size integer DEFAULT NULL,
  stroke boolean DEFAULT NULL, 
  color text DEFAULT NULL, 
  weight integer DEFAULT NULL, 
  opacity_percent integer DEFAULT NULL, 
  line_cap line_cap_enum DEFAULT NULL, 
  line_join text DEFAULT NULL,
  dash_array text DEFAULT NULL,
  dash_offset text DEFAULT NULL,
  fill boolean DEFAULT NULL,
  fill_color text DEFAULT NULL,
  fill_opacity_percent integer DEFAULT NULL, 
  fill_rule fill_rule_enum DEFAULT NULL
);

When using this query, all vector_layers appear:

const { results: vectorLayers = [] } = useLiveQuery(
  db.vector_layers.liveMany(),
)

When including vector_layer_displays, all vector_layers appear:

const { results: vectorLayers = [] } = useLiveQuery(
  db.vector_layers.liveMany({
    include: { vector_layer_displays: true },
  }),
)

The issue is: when including layer_presentations, no vector_layers appear:

const { results: vectorLayers = [] } = useLiveQuery(
  db.vector_layers.liveMany({
    include: { layer_presentations: true },
  }),
)

After adding a layer_presentation for every vector_layer, all vector_layers appear again. Which explains why including vector_layer_displays worked: for every vector_layer a display exists.

My problem is: This is not what I would expect. If there are vector_layers without layer_presentations and I include layer_presentations in the query, I would expect all vector_layers to be returned. Those without layer_presentations would simply return an empty array in the layer_presentations key.

Is this a bug or are my expectations unreasonable?

I am using:

• "electric-sql": "0.12.1",
• "wa-sqlite": "rhashimoto/wa-sqlite#semver:v0.9.14",

[linear[bot]]

VAX-2001 Queries: including referenced tables excludes rows without reference

[KyleAMathews]

👋 we've been working the last month on a rebuild of the Electric server over at a temporary repo https://github.com/electric-sql/electric-next/

You can read more about why we made the decision at https://next.electric-sql.com/about

We're really excited about all the new possibilities the new server brings and we hope you'll check it out soon and give us your feedback.

We're now moving the temporary repo back here. As part of that migration we're closing all the old issues and PRs. We really appreciate you taking the time to investigate and report this issue!