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

QA: internal error from ARRAY[*] expression in CREATE VIEW statement #99000

Closed
msirek opened this issue Mar 20, 2023 · 3 comments · Fixed by #99057
Closed

QA: internal error from ARRAY[*] expression in CREATE VIEW statement #99000

msirek opened this issue Mar 20, 2023 · 3 comments · Fixed by #99057
Assignees
@rharding6373
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. O-qa T-sql-queries SQL Queries Team

Comments

@msirek
Copy link
Contributor

msirek commented Mar 20, 2023 •
edited by cockroach-jira-scripts
Loading

From star expressions in view definitions QA,

The following CREATE VIEW statement with * in an ARRAY definition causes an internal error:

CREATE TABLE films (id int PRIMARY KEY, title text, kind text, classification CHAR(1), year int, language text, studio text, runtime float, country_code CHAR(2));
CREATE OR REPLACE VIEW comedies AS SELECT ARRAY[films.*]::string FROM films;
ERROR: internal error: relation "comedies" (105): column ID 2 found in depended-on-by references, no such column in this relation
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/catalog/tabledesc/validate.go:693: ValidateSelf()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:80: func1()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:198: validateDescriptorsAtLevel()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:76: Validate()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:41: Self()
github.com/cockroachdb/cockroach/pkg/sql/catalog/tabledesc/structured.go:539: AllocateIDs()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:706: addResultColumns()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:602: replaceViewDesc()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:199: 1()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:366: func1()
github.com/cockroachdb/cockroach/pkg/sql/schema_resolver.go:414: runWithOptions()
github.com/cockroachdb/cockroach/pkg/sql/create_view.go:193: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:524: func2()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:112: func1()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:299: visitInternal()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:79: visit()
github.com/cockroachdb/cockroach/pkg/sql/walk.go:43: walkPlan()
github.com/cockroachdb/cockroach/pkg/sql/plan.go:527: startExec()
github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:172: Start()
github.com/cockroachdb/cockroach/pkg/sql/colexec/columnarizer.go:183: Init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:94: init()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/stats.go:103: Init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:246: func1()
github.com/cockroachdb/cockroach/pkg/sql/colexecerror/error.go:92: CatchVectorizedRuntimeError()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:245: init()
github.com/cockroachdb/cockroach/pkg/sql/colflow/flow_coordinator.go:285: Run()
github.com/cockroachdb/cockroach/pkg/sql/colflow/vectorized_flow.go:309: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:866: Run()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1843: PlanAndRun()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1579: func2()
github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:1582: PlanAndRunAll()

HINT: You have encountered an unexpected error.

Jira issue: CRDB-25646
@msirek msirek added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. O-qa labels Mar 20, 2023
@blathers-crl blathers-crl bot added the T-sql-queries SQL Queries Team label Mar 20, 2023
@rharding6373
Copy link
Collaborator

Great find. This only repros for me if I execute some of the SQL from the QA plan before this error occurred, namely:

CREATE TABLE films (id int PRIMARY KEY, title text, kind text, classification CHAR(1), year int, language text, studio text, runtime float, country_code CHAR(2));

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

CREATE VIEW pg_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'PG';

CREATE OR REPLACE VIEW comedies AS SELECT ARRAY[films.*]::string FROM films;

While attempting to repro, I came across another potential issue from a different combination of SQL:

CREATE TABLE films (id int PRIMARY KEY, title text, kind text, classification CHAR(1), year int, language text, studio text, runtime float, country_code CHAR(2));
CREATE TABLE user_ratings (film_id int, classification text, country_code CHAR(2), rating float);

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

CREATE VIEW comedies2 AS                                                                                                     
SELECT f.*,
            f.country_code AS country,
            (SELECT avg(r.rating)     
             FROM user_ratings r   
             WHERE r.film_id = f.id) AS avg_rating  
     FROM films f
     WHERE f.kind = 'Comedy';   
 
CREATE OR REPLACE VIEW comedies AS SELECT ARRAY[films.*]::string FROM films;
ERROR: cannot drop columns from view

@rharding6373
Copy link
Collaborator

The second case is actually compatible with postgres, so it's not a bug.

@rharding6373
Copy link
Collaborator

The first case in postgres results in the error: ERROR: cannot drop columns from view

@rharding6373 rharding6373 self-assigned this Mar 20, 2023
@rharding6373 rharding6373 mentioned this issue Mar 20, 2023
Merged
craig bot pushed a commit that referenced this issue Mar 21, 2023
@jordanlewis @jbowens
Merge #97685 #99045 #99057
b26b338 
97685: sql: add default_text_search_config r=jordanlewis a=jordanlewis

Updates: #41288
Epic: CRDB-22357

All but the last commit are from #92966 and #97677.


    This commit adds the default_text_search_config variable for the tsearch
    package, which allows the user to set a default configuration for the
    text search builtin functions that take configurations, such as
    to_tsvector and to_tsquery. The default for this configuration variable
    is 'english', as it is in Postgres.

    Release note (sql change): add the default_text_search_config variable
    for compatibility with the single-argument variants of the text search
    functions to_tsvector, to_tsquery, phraseto_tsquery, and
    plainto_tsquery, which use the value of default_text_search_config
    instead of expecting one to be included as in the two-argument variants.
    The default value of this setting is 'english'.

99045: roachtest: set 30m timeout for all disk stall roachtests r=nicktrav a=jbowens

This commit sets a new 30m timeout for all disk stall roachtests. Previously,
the FUSE filesystem variants had no timeout and inherited the default 10h
timeout. The other variants had a 20m timeout, which has been observed to be
too short due to upreplication latency.

Informs #98904.
Informs #98886.
Epic: None
Release note: None


99057: sql: check replace view columns earlier r=rharding6373 a=rharding6373

Before this change, we could encounter internal errors while attempting to add result columns during a `CREATE OR REPLACE VIEW` if the number of columns in the new view was less than the number of columns in the old view. This led to an inconsistency with postgres, which would only return the error `cannot drop columns from view`.

This PR moves the check comparing the number of columns before and after the view replacement earlier so that the correct error returns.

Co-authored-by: [email protected]

Fixes: #99000
Epic: None

Release note (bug fix): Fixes an internal error that can occur when `CREATE OR REPLACE VIEW` replaces a view with fewer columns and another entity depended on the view.

Co-authored-by: Jordan Lewis <[email protected]>
Co-authored-by: Jackson Owens <[email protected]>
Co-authored-by: craig[bot] <[email protected]>
@craig craig bot closed this as completed in b80feac Mar 21, 2023
@mgartner mgartner moved this to Done in SQL Queries Jul 24, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rharding6373 rharding6373

Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. O-qa T-sql-queries SQL Queries Team
Projects
SQL Queries
Archived in project
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: check replace view columns earlier rharding6373/cockroach
2 participants
@msirek @rharding6373