When removing a column from a view in snowflake, columns IS_NULLABLE constraint changes

user_18285710 0
Viewed 51

Example of the change:

CREATE OR REPLACE VIEW1 AS SELECT CUSTOMER, ID, FLAG FROM TABLE;

CREATE OR REPLACE VIEW2 AS SELECT CUSTOMER,ID FROM TABLE

After removed a column from select statement, I compared 2 views in information_schema.columns. IS_NULLABLE value for 2 columns(E.G CUSTOMER,ID) changes from YES to NO. No luck from google, can anyone explain the reason for this changes please?

snowflake-cloud-data-platform
2 Answers

can you please describe the steps that you followed, as i tried the following and was not able to reproduce.

create or replace  table user_info2(CUSTOMER varchar2(50)  , id varchar2(100) , flag varchar2(10));
select * from information_schema.columns where table_name = 'USER_INFO2';
CREATE OR REPLACE view VIEW1 AS SELECT CUSTOMER, ID, FLAG FROM USER_INFO2;
CREATE OR REPLACE view VIEW1 AS SELECT CUSTOMER,ID FROM USER_INFO2;

select * from information_schema.columns where table_name = 'VIEW1';
user_112279190 
CREATE TABLE table_table(customer TEXT, id NUMBER, flag BOOLEAN);

CREATE VIEW view1 AS SELECT customer, id, flag FROM table_table;

SELECT table_name, column_name, ordinal_position, is_nullable 
    FROM information_schema.columns WHERE table_name = 'VIEW1';

looks nice and normal

TABLE_NAME COLUMN_NAME ORDINAL_POSITION IS_NULLABLE
VIEW1 ID 2 YES
VIEW1 FLAG 3 YES
VIEW1 CUSTOMER 1 YES

make another view:

CREATE view VIEW2 AS SELECT CUSTOMER, ID FROM table_table;

SELECT table_name, column_name, ordinal_position, is_nullable 
    FROM information_schema.columns WHERE table_name = 'VIEW2';
TABLE_NAME COLUMN_NAME ORDINAL_POSITION IS_NULLABLE
VIEW2 ID 2 YES
VIEW2 CUSTOMER 1 YES

also normal, so lets try recreate the first view, but with one less column

CREATE OR REPLACE view VIEW1 AS SELECT customer, id FROM table_table;

SELECT table_name, column_name, ordinal_position, is_nullable 
    FROM information_schema.columns WHERE table_name = 'VIEW1';
TABLE_NAME COLUMN_NAME ORDINAL_POSITION IS_NULLABLE
VIEW1 ID 2 YES
VIEW1 CUSTOMER 1 YES

Ok, lets get crazy, let alter the actual table:

CREATE OR REPLACE TABLE table_table(CUSTOMER text NOT NULL, id number NOT NULL, flag BOOLEAN);

SELECT table_name, column_name, ordinal_position, is_nullable
    FROM information_schema.columns WHERE table_name = 'VIEW1';
TABLE_NAME COLUMN_NAME ORDINAL_POSITION IS_NULLABLE
VIEW1 ID 2 YES
VIEW1 CUSTOMER 1 YES

so changing the table, on which the view sits, didn't change the the views details, now lets recreate that view..

CREATE OR REPLACE view VIEW1 AS SELECT CUSTOMER, ID FROM table_table;

SELECT table_name, column_name, ordinal_position, is_nullable 
    FROM information_schema.columns WHERE table_name = 'VIEW1';
TABLE_NAME COLUMN_NAME ORDINAL_POSITION IS_NULLABLE
VIEW1 ID 2 NO
VIEW1 CUSTOMER 1 NO

Now we see a change. So that begs the question has the table changed?

user_439920
Related