Unexpected Full Join Result
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
- Domain
- backend-api-design, databases
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
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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day
-
bug ready-for-triage ready-to-code
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
RedHatInsights/insights-ingress-go#946 · 4 comments ·
Maintainers usually reply within 1 day