Spatial join returning 0 rows with null geometry values.

[gmucha]

What happens?

I came upon a problem, where joining a table against itself using spatial join results in 0 rows - but join with the same predicates + an extra one using a "nested loop" join.

Probably related to nulls in the table- running the join and filtering for nulls I am seeing correct results.

wypopo.csv

To Reproduce

Load the CSV attached. You may need to recreate GEOM column so;

ALTER TABLE wypopo DROP COLUMN geom;
ALTER TABLE wypopo ADD COLUMN geom GEOMETRY;
UPDATE wypopo SET geom = st_point(polozenie_x, polozenie_y);

select count(*) from wypopo a join wypopo b on st_dwithin(a.geom, b.geom, 0); 

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│      0       │
└──────────────┘

explain analyze  select count(*) from wypopo a join wypopo b on st_dwithin(a.geom, b.geom, 0) ;

┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.0081s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
│                           │
│           1 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         EXTENSION         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│   ST_DWithin(geom, geom)  │              │
│                           │              │
│           0 Rows          │              │
│          (0.00s)          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││         TABLE_SCAN        │
│    ────────────────────   ││    ────────────────────   │
│       Table: wypopo       ││       Table: wypopo       │
│   Type: Sequential Scan   ││   Type: Sequential Scan   │
│     Projections: geom     ││     Projections: geom     │
│                           ││                           │
│         10000 Rows        ││         10000 Rows        │
│          (0.00s)          ││          (0.00s)          │
└───────────────────────────┘└───────────────────────────┘

select count(*) from wypopo a join wypopo b on st_dwithin(a.geom, b.geom, 0) where a.geom is not null;

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│    10277     │
└──────────────┘

select count(*) from wypopo a join wypopo b on st_dwithin(a.geom, b.geom, 0) where a.id<> b.id;

100% 
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│     330      │
└──────────────┘

OS:

MacOS

DuckDB Version:

1.3.2

DuckDB Client:

command line

Hardware:

No response

Full Name:

Grzegorz Mucha

Affiliation:

No affiliation ATM

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

No - Other reason (please specify in the issue body)

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[szarnyasg]

Hi @gmucha, thanks for the report! This seems to have been fixed in the nightly build. Can you please give it a go?

DuckDB v1.4.0-dev2825 (Development Version) 3483d12aab
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D LOAD spatial;
D CREATE TABLE wypopo AS FROM 'wypopo.csv';
D ALTER TABLE wypopo DROP COLUMN geom;
D ALTER TABLE wypopo ADD COLUMN geom GEOMETRY;
D UPDATE wypopo SET geom = st_point(polozenie_x, polozenie_y);
D
D select count(*) from wypopo a join wypopo b on st_dwithin(a.geom, b.geom, 0);

100% ▕████████████████████████████████████████████████████████████▏
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│    10277     │
└──────────────┘

[gmucha]

It returns correct count in the nightly build, but it's not using spatial join for this query - so the execution time is around 10s compared to <1s with spatial join). Anything I need to make it use SPATIAL join instead?

pragma version
  ;
┌─────────────────┬────────────┬─────────────────────┐
│ library_version │ source_id  │      codename       │
│     varchar     │  varchar   │       varchar       │
├─────────────────┼────────────┼─────────────────────┤
│ v1.4.0-dev2912  │ 2ed9bf887f │ Development Version │

D explain analyze  select count(*) from wypopo a join wypopo2 b on st_dwithin(a.geom, b.geom, 0) ;


────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││               Total Time: 9.73s              ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        count_star()       │
│                           │
│           1 row           │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│     BLOCKWISE_NL_JOIN     │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│         Condition:        ├──────────────┐
│ST_DWithin(geom, geom, 0.0)│              │
│                           │              │
│        10,277 rows        │              │
│          (9.72s)          │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         TABLE_SCAN        ││         TABLE_SCAN        │
│    ────────────────────   ││    ────────────────────   │
│       Table: wypopo       ││       Table: wypopo       │
│   Type: Sequential Scan   ││   Type: Sequential Scan   │
│     Projections: geom     ││     Projections: geom     │
│                           ││                           │
│        10,000 rows        ││        10,000 rows        │
│          (0.00s)          ││          (0.00s)          │
└───────────────────────────┘└───────────────────────────┘
Run Time (s): real 9.728 user 9.667107 sys 0.055216
D 

[gmucha]

OK, looks like it was using the core version of spatial, but I'm unable to install the nightly

D force INSTALL spatial FROM core_nightly;
Run Time (s): real 0.403 user 0.400206 sys 0.004727
HTTP Error:
Failed to download extension "spatial" at URL "http://nightly-extensions.duckdb.org/2ed9bf887f/osx_arm64/spatial.duckdb_extension.gz" (HTTP 403)
Extension "spatial" is an existing extension.

[szarnyasg]

Thanks for the feedback. It's possible that the spatial extension is not available for the nightly build at the moment.
In any case, I've transferred this issue to the spatial repository.

[Maxxen]

Hello! Thanks for reporting this issue. Ive included a fix for it as part of this PR #663. Once that is in, I will in turn merge this PR duckdb/duckdb#18197, which will update spatial on the nightly build.