cockroachdb / cockroachdb/cockroach

Relation appears as a `record` field within itself

Open
#120,625 1 comment 0 reactions 0 assignees View on GitHub
C-bug O-community S-3-erroneous-edge-case T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

In CRDB, apparently a relation appears as a `record` field within the relation itself. This can result in unexpected results on typos.

**To Reproduce**

```
create table t1(f1 integer);
insert into t1 values (1);
select t1.t1 from t1;
f1
-------
(1)
```

**Expected behavior**

In Postgres, this justifiably errors out with a helpful hint, which I think is the safer behavior:

```
ERROR: column t1.t1 does not exist
LINE 1: select t1.t1 from t1;
^
HINT: Perhaps you meant to reference the column "t1.f1".
```

**Environment:**
- CockroachDB version v23.2.1

**Additional context**
Impact:

Consider the case where a typo was made in the `WHERE` clause, e.g. `WHERE t1.t1 IS NULL`. That condition will evaluate whether the entire `record` is NULL (usually `FALSE`, unless the record is all-NULLs), rather than a specific column, resulting in an subtle wrong result being returned from the query.

Jira issue: CRDB-36787

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.