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: CREATE MATERIALIZED VIEW AS schema change job has invalid description #107445

Closed
ecwall opened this issue Jul 24, 2023 · 0 comments · Fixed by #107447
Closed

sql: CREATE MATERIALIZED VIEW AS schema change job has invalid description #107445

ecwall opened this issue Jul 24, 2023 · 0 comments · Fixed by #107447
Assignees
@ecwall
Labels
A-schema-changer-impl Related to the implementation of the new schema changer C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. db-cy-23 T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)

Comments

@ecwall
Copy link
Contributor

ecwall commented Jul 24, 2023 •
edited by exalate-issue-sync bot
Loading

The schema change job triggered by CREATE MATERIALIZED VIEW AS has a job description with invalid SQL.

Repro steps

[email protected]:26257/demoapp/movr> CREATE TABLE t (id int PRIMARY KEY);                                                                                                                                                                  
CREATE TABLE

[email protected]:26257/demoapp/movr> CREATE MATERIALIZED VIEW v AS SELECT * FROM t;                                                                                                                                                        
CREATE VIEW


[email protected]:26257/demoapp/movr> SELECT description                                                                                                                                                                                    
                                -> FROM [SHOW JOBS]                                                                                                                                                                                      
                                -> WHERE description LIKE 'CREATE%v%';                                                                                                                                                                   
                      description
-------------------------------------------------------
  CREATE VIEW "v" AS "SELECT t.id FROM movr.public.t"
(1 row)

[email protected]:26257/demoapp/movr> CREATE VIEW "v" AS "SELECT t.id FROM movr.public.t";                                                                                                                                                  
ERROR: statement ignored: at or near "SELECT t.id FROM movr.public.t": syntax error
SQLSTATE: 42601

RCA

The job description is being incorrectly manually set instead of using the tree.CreateView NodeFormatter here

cockroach/pkg/sql/create_view.go

Line 290 in 24603c9

fmt.Sprintf("CREATE VIEW %q AS %q", n.viewName, n.viewQuery),

Jira issue: CRDB-30046

Epic CRDB-27601
@ecwall ecwall added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions) A-schema-changer-impl Related to the implementation of the new schema changer db-cy-23 labels Jul 24, 2023
@ecwall ecwall self-assigned this Jul 24, 2023
@ecwall ecwall mentioned this issue Jul 24, 2023
Merged
craig bot pushed a commit that referenced this issue Jul 24, 2023
@adityamaru @rafiss @ecwall
Merge #106879 #107236 #107447
4618dbf 
106879: jobs: add table to display execution details r=maryliag a=adityamaru

In #105384 and #106629 we added support to collect and list files that had been collected as part of
a job's execution details. These files are meant
to provide improved obersvability into the state
of a job.

This change is the first of a few that exposes these endpoints on the DBConsole job details page. This change only adds support for listing files that have been requested as part of a job's execution details.
A future change will add support to request these files, sort them and download them from the job details page.

This page is not available on the Cloud Console as it is meant for advanced debugging.

Informs: #105076

Release note (ui change): add table in the Profiler job details page that lists all the available files describing a job's execution details
<img width="1505" alt="Screenshot 2023-07-18 at 2 26 50 PM" src="https://github.com/cockroachdb/cockroach/assets/13837382/aebe18a6-9c25-4c9a-ad7c-a94e2e4c97ff">
<img width="1510" alt="Screenshot 2023-07-18 at 2 27 03 PM" src="https://github.com/cockroachdb/cockroach/assets/13837382/da9b3a21-8dc6-47ca-ac02-24d8bb7d09e7">



107236: sql: use txn.NewBatch instead of &kv.Batch{} r=fqazi a=rafiss

This will make these requests properly passes along the admission control headers.

informs #79212
Epic: None
Release note: None

107447: sql: fix CREATE MATERIALIZED VIEW AS schema change job description r=fqazi a=ecwall

Fixes #107445

This changes the CREATE MATERIALIZED VIEW AS schema change job description SQL syntax. For example
```
CREATE VIEW "v" AS "SELECT t.id FROM movr.public.t";
```
becomes
```
CREATE MATERIALIZED VIEW defaultdb.public.v AS SELECT t.id FROM defaultdb.public.t WITH DATA;
```

Release note (bug fix): Fix CREATE MATERIALIZED VIEW AS schema change job description SQL syntax.

Co-authored-by: adityamaru <[email protected]>
Co-authored-by: Rafi Shamim <[email protected]>
Co-authored-by: Evan Wall <[email protected]>
@craig craig bot closed this as completed in 0477e38 Jul 24, 2023
@blathers-crl blathers-crl bot mentioned this issue Jul 24, 2023
Merged
blathers-crl bot pushed a commit that referenced this issue Jul 24, 2023
@ecwall
sql: fix CREATE MATERIALIZED VIEW AS schema change job description
b59fc36 
Fixes #107445

This changes the CREATE MATERIALIZED VIEW AS schema change job description SQL
syntax.
For example
```
CREATE VIEW "v" AS "SELECT t.id FROM movr.public.t";
```
becomes
```
CREATE MATERIALIZED VIEW defaultdb.public.v AS SELECT t.id FROM defaultdb.public.t WITH DATA;
```

Release note (bug fix): Fix CREATE MATERIALIZED VIEW AS schema change job description SQL syntax.
@ecwall ecwall mentioned this issue Jul 25, 2023
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ecwall ecwall

Labels
A-schema-changer-impl Related to the implementation of the new schema changer C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. db-cy-23 T-sql-foundations SQL Foundations Team (formerly SQL Schema + SQL Sessions)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: fix CREATE MATERIALIZED VIEW AS schema change job description
1 participant
@ecwall