cockroachdb / cockroachdb/cockroach

sql: USING clause in join cannot reference NOT VISIBLE columns

Open
#126,767 2 comments 0 reactions 0 assignees View on GitHub
branch-master C-bug E-quick-win T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

```
demo@127.0.0.1:26257/demoapp/movr> show create table bar;
table_name | create_statement
-------------+--------------------------------------------------------------
bar | CREATE TABLE public.bar (
| a INT8 NULL,
| rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
| CONSTRAINT bar_pkey PRIMARY KEY (rowid ASC)
| )
(1 row)

Time: 15ms total (execution 15ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> show create table foo;
table_name | create_statement
-------------+--------------------------------------------------------------
foo | CREATE TABLE public.foo (
| a INT8 NULL,
| rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
| CONSTRAINT foo_pkey PRIMARY KEY (rowid ASC)
| )
(1 row)

Time: 13ms total (execution 13ms / network 0ms)
```

We cannot reference the rowid column in a `USING` join clause:

```
demo@127.0.0.1:26257/demoapp/movr> SELECT * FROM foo LEFT JOIN bar USING(rowid);
ERROR: column "rowid" specified in USING clause does not exist in left table
SQLSTATE: 42703
```

Even though it is perfectly able to be referenced explicitly:

```
demo@127.0.0.1:26257/demoapp/movr> SELECT * FROM foo LEFT JOIN bar ON foo.rowid = bar.rowid;
a | a
----+----
(0 rows)

Time: 3ms total (execution 3ms / network 0ms)
```

Jira issue: CRDB-40114

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.