Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: equip cockroachdb to _detect_ attempts to use correlated queries #24684

Closed
knz opened this issue Apr 11, 2018 · 1 comment · Fixed by #27396
Closed

sql: equip cockroachdb to _detect_ attempts to use correlated queries #24684

knz opened this issue Apr 11, 2018 · 1 comment · Fixed by #27396
Labels
A-sql-semantics C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.

Comments

@knz
Copy link
Contributor

knz commented Apr 11, 2018

until CockroachDB supports correlated subqueries ( #3288, #24560 ) we want to tell users what's going on in error messages caused by a lack of support.

Currently a user attempting to run correlated subqueries will see either of the following:

  • "column not found" when using a column from the surrounding query
  • "no data source matches name" when using a table name as argument to a SRF that defines an implicit lateral join (cf sql: support lateral joins #24560 )

We could teach CockroachDB to report better error messages:

  • column not found: we could add some scope tracking during name resolution, which would help provide useful error messages even without supporting correlation outright.
  • no data source matches name: mention "FROM" in the error message.
@knz
Copy link
Contributor Author

knz commented Apr 11, 2018

Ideally we'd really like to detect correlation (for feature tracking purposes) but that will need to wait for the new optimizer code.

@knz knz added the S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption. label Apr 11, 2018
@knz knz mentioned this issue Apr 11, 2018
Closed
@ruzkant ruzkant mentioned this issue Apr 14, 2018
Closed
@knz knz added A-sql-semantics C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption. and removed S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption. labels Apr 24, 2018
@knz knz mentioned this issue Jul 11, 2018
Merged
craig bot pushed a commit that referenced this issue Jul 11, 2018
@knz
Merge #27390 #27396
d982482 
27390: sql: properly reject nested generators in ROWS FROM r=knz a=knz

Fixes #27389.

Prior to this patch, invalid uses of SRFs as arguments to other
functions in ROWS FROM were not properly rejected, and were only
caught at evaluation time (i.e. much too late).

This patch fixes it by rejecting these uses early.

Release note (bug fix): invalid uses of set-generating functions in
FROM clauses are now reported with the same error code as PostgreSQL.

27396: sql: help the user understand unsupported correlation r=knz a=knz

Fixes #24684.

Prior to this patch, a client trying to use an unsupported correlated
query would encounter an obscure error like "column v does not exist"
or "no data source matches prefix".

Given that the new optimizer code can determine whether a query is
correlated, we can use this information to enhance the error message.

Before:

```
> select * from pg_class a where exists (select * from pg_class b where a.oid = b.oid);
pq: no data source matches prefix: a

> select * from pg_class a where exists (select * from kv where v::oid = oid);
pq: column "oid" does not exist
```

After:

```
> select * from pg_class a where exists (select * from pg_class b where a.oid = b.oid);
pq: no data source matches prefix: a
HINT: some correlated subqueries are not supported yet - see
      #3288

> select * from pg_class a where exists (select * from kv where v::oid = oid);
pq: column "oid" does not exist
HINT: some correlated subqueries are not supported yet - see
      #3288
```

Note: some correlated queries do not benefit from this improvement,
specifically those for which the optimizer code aborts early before it
has detected correlation (e.g. because of some other unrelated
feature).

Release note (sql change): CockroachDB will now report a hint in the
error message if it encounters a correlated query that it does not
support yet.


Co-authored-by: Raphael 'kena' Poss <knz@cockroachlabs.com>
@craig craig bot closed this as completed in #27396 Jul 11, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-sql-semantics C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: help the user understand unsupported correlation knz/cockroach
1 participant
@knz