'Snowflake SQL Compilation Error: View Definition Declared but view Query Produced

I've just gotten a new query error that I haven't changed anything to. Any advice on what to do? Thanks

SQL compilation error:

View definition for '**********' declared 115 column(s), but view query produces 117 column(s).

sqlsnowflake-cloud-data-platform


Solution 1:[1]

This is speculation, but it sounds like your view is using select x.*, where * means to get all the columns from some table.

Then, the underlying table changes . . . and voila, you might have a problem.

Solution 2:[2]

I've just gotten a new query error that I haven't changed anything to. Any advice on what to do?

If the query started to produce errors it means that the defintion of view is no longer "valid/up-to-date". Most likely the underlying table has been altered.

CREATE VIEW

View definitions are not dynamic. A view is not automatically updated if the underlying sources are modified such that they no longer match the view definition, particularly when columns are dropped. For example:

  • A view is created referencing a specific column in a source table and the column is subsequently dropped from the table.

  • A view is created using SELECT * from a table and any column is subsequently dropped from the table.

In either of these scenarios, querying the view returns a column mismatch error.

Steps to reproduce the scenario:

CREATE OR REPLACE TABLE t(col1 INT, col2 INT);
INSERT INTO t(col1, col2) VALUES (1,1);

CREATE OR REPLACE VIEW v_t AS SELECT * FROM t;

SELECT * FROM v_t;
--COL1  COL2
--1 1

So far so good. Now altering the underlying table by adding new column:

ALTER TABLE t ADD COLUMN col3 INT DEFAULT 3;

SELECT * FROM v_t;

SQL compilation error: View definition for 'V_T' declared 2 column(s), but view query produces 3 column(s).

Recreation of the view and keeping its definition on par with underlying tables should resolve it:

CREATE OR REPLACE VIEW v_t 
COPY GRANTS 
AS 
SELECT * FROM t;
-- using * will work to refresh it but I would not recommend it 
-- and explicitly describe columns instead


CREATE OR REPLACE VIEW v_t 
COPY GRANTS   -- to preserve already granted permissions
AS 
SELECT col1, col2, col3 FROM t;

SELECT * FROM v_t;
-- COL1   COL2    COL3
-- 1      1     3

Solution 3:[3]

I found myself with a similar issue this morning. I had copied my query from a txt file I had and pasted it into a worksheet and tired to run it and got the same error. I had used the Table with the definition issue for a join and only for 1 column so I didn't see why it was giving me such an error.

All a look of check tables I commented on the Worksheet the error I was seeing. But I decided to run it again and it worked.

This tells me that Snowflake was using what it had cached but after editing the query it saw it as a new query and re-ran it instead of erroring out when what is in the cache doesn't match the definition.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2
Solution 3 DharmanBot

Related Questions