Unexpected Full Join Result

Open
#176,247 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
42/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
go, postgresql, sql

Research direction

Start by reproducing the supplied SQL query in CockroachDB v26.4.0; it uses nested FULL JOINs, GEOGRAPHY values, and a NULL comparison, and no tables are needed. Then trace the optimizer or execution path for FULL JOIN conditions involving spatial types. Done means the query runs without errors and returns exactly three rows; the issue does not name relevant source files or tests.

Written by the indexing model from the issue text.

Description

C-bug O-community T-sql-queries X-blathers-triaged X-move-internal

Describe the problem

Please describe the issue you observed, and any steps we can take to reproduce it:

A query involving nested FULL JOINs, GEOGRAPHY types, and NULL comparisons produces incorrect results. The query executes a FULL JOIN between an empty set combined with a NULL::GEOGRAPHY and a subquery containing a GEOGRAPHY point Cartesian joined with boolean values. Instead of returning 3 rows, it incorrectly returns 4 rows. This indicates a severe bug in how the optimizer or execution engine evaluates NULL comparisons with spatial types in a FULL JOIN.

To Reproduce

SELECT 1
FROM (
  (SELECT 1 AS x WHERE false) AS empty_side
  FULL JOIN (VALUES (NULL::GEOGRAPHY)) AS left_side(g) ON false
)
FULL JOIN (
  (VALUES ('SRID=4326;POINT(0 0)'::GEOGRAPHY)) AS geo_side(g)
  FULL JOIN (VALUES (true), (true)) AS bool_side(k) ON bool_side.k
)
ON left_side.g < geo_side.g;

Expected behavior

Expected result (3 rows):

  ?column?
------------
         1
         1
         1
(3 rows)

Actual result (4 rows):

  ?column?
------------
         1
         1
         1
         1
(4 rows)

Environment:

  • CockroachDB version: v26.4.0
  • Server OS: Linux
  • Client app: cockroach sql

Jira issue: CRDB-72246

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.