cockroachdb / cockroachdb/cockroach
sql: USING clause in join cannot reference NOT VISIBLE columns
- 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
Assessment
This issue has not been assessed yet.