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.

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

opt: PARTITION BY column not qualified in view query #47704

Closed
RaduBerinde opened this issue Apr 20, 2020 · 0 comments · Fixed by #47709
Closed

opt: PARTITION BY column not qualified in view query #47704

RaduBerinde opened this issue Apr 20, 2020 · 0 comments · Fixed by #47709
Assignees
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.

Comments

@RaduBerinde
Copy link
Member

RaduBerinde commented Apr 20, 2020

The column reference in PARTITION BY is losing its qualification, leading to unexpected error:

CREATE TABLE a (foo UUID);
CREATE TABLE b (foo UUID);
CREATE VIEW v AS SELECT first_value(a.foo) OVER (PARTITION BY a.foo) FROM a JOIN b ON a.foo = b.foo;

[email protected]:49705/movr> select * from v;
pq: column reference "foo" is ambiguous (candidates: movr.public.a.foo, movr.public.b.foo)
[email protected]:49705/movr> show create v;
  table_name |                                                            create_statement
+------------+-----------------------------------------------------------------------------------------------------------------------------------------+
  v          | CREATE VIEW v (first_value) AS SELECT first_value(a.foo) OVER (PARTITION BY foo) FROM movr.public.a JOIN movr.public.b ON a.foo = b.foo
(1 row)
@RaduBerinde RaduBerinde self-assigned this Apr 20, 2020
@RaduBerinde RaduBerinde added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption. labels Apr 20, 2020
RaduBerinde added a commit to RaduBerinde/cockroach that referenced this issue Apr 20, 2020
The optbuilder code which handles window functions inadvertently modifies the
`WindowDef` in place. This leads to loss of qualification in the PARTITION BY
and ORDER BY columns (which get replaced with `*scopeColumn`s). This is a
problem for views where the query stored in the descriptor could be invalid
without the qualification.

This change fixes this by making copies as necessary.

Fixes cockroachdb#47704.

Release note (bug fix): fixed case where PARTITION BY and ORDER BY columns in
window specifications were losing qualifications when used inside views.
RaduBerinde added a commit to RaduBerinde/cockroach that referenced this issue Apr 20, 2020
The optbuilder code which handles window functions inadvertently modifies the
`WindowDef` in place. This leads to loss of qualification in the PARTITION BY
and ORDER BY columns (which get replaced with `*scopeColumn`s). This is a
problem for views where the query stored in the descriptor could be invalid
without the qualification.

This change fixes this by making copies as necessary.

Fixes cockroachdb#47704.

Release note (bug fix): fixed case where PARTITION BY and ORDER BY columns in
window specifications were losing qualifications when used inside views.
craig bot pushed a commit that referenced this issue Apr 21, 2020
47709: opt: fix in-place modification of window definition in optbuilder r=RaduBerinde a=RaduBerinde

The optbuilder code which handles window functions inadvertently modifies the
`WindowDef` in place. This leads to loss of qualification in the PARTITION BY
and ORDER BY columns (which get replaced with `*scopeColumn`s). This is a
problem for views where the query stored in the descriptor could be invalid
without the qualification.

This change fixes this by making copies as necessary.

Fixes #47704.

Release note (bug fix): fixed case where PARTITION BY and ORDER BY columns in
window specifications were losing qualifications when used inside views.

Co-authored-by: Radu Berinde <[email protected]>
@craig craig bot closed this as completed in 953964b Apr 21, 2020
RaduBerinde added a commit to RaduBerinde/cockroach that referenced this issue May 1, 2020
The optbuilder code which handles window functions inadvertently modifies the
`WindowDef` in place. This leads to loss of qualification in the PARTITION BY
and ORDER BY columns (which get replaced with `*scopeColumn`s). This is a
problem for views where the query stored in the descriptor could be invalid
without the qualification.

This change fixes this by making copies as necessary.

Fixes cockroachdb#47704.

Release note (bug fix): fixed case where PARTITION BY and ORDER BY columns in
window specifications were losing qualifications when used inside views.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant