snowddl plan: Virtual column expressions seem to be invalid for CTAS stmts (or even CREATE OR ALTER TABLE stmts)

[JohnL4]

Describe the bug Looks like snowddl plan is attempting to generate CTAS stmts that appear as CREATE OR REPLACE TABLE <tblName> (<colName> <colType> AS <expr>) COPY GRANTS AS SELECT <colName> as <colName> FROM <tblName>;.

As far as I can tell, <colName> <colType> AS <expr> column definitions are only valid for naked CREATE TABLE stmts, not CTAS nor CREATE OR ALTER TABLE or even ALTER TABLE.

(It appears that some version of snowddl (<0.9.0?) didn't even try this; it just generated an empty column, which would be invalid if we weren't populating these tables from another source. For some reason, we had non-virtual columns in our d/b deployed with snowddl a long time ago but now snowddl is trying to copy the expressions over.)

Expected behavior Not this. :) For our purposes, the older behavior would be nicer. I realize that's not universally acceptable. Maybe a leading comment to the effect that an illegal CTAS is being generated, so at least it can be easily spotted and a fix put in (e.g., pre-/post-deploy along with converting the CTAS to just CREATE TABLE).

I don't expect an immediate fix, but I wanted to file this issue before I got distracted and lost the github editor window for it.

Attach log No log, but: Snowflake version = 8.44.2 (BUSINESS_CRITICAL), SnowDDL version = 0.36.0

Attach YAML config (if applicable) Not immediately available, can generate one later if necessary.

[littleK0i]

This seems to be working:

CREATE TABLE test.test.text_expr
(
    id NUMBER(38,0),
    name VARCHAR(255),
    name_len NUMBER(38,0) AS LENGTH(name)
);

INSERT INTO test.test.text_expr (id, name) VALUES (1, 'Abc');

SELECT * FROM test.test.text_expr;

CREATE OR REPLACE TABLE test.test.text_expr
(
    id NUMBER(38,0),
    name VARCHAR(255),
    name_len NUMBER(38,0) AS LENGTH(name)
)
AS
SELECT id
    , name
    --, name_len
FROM test.test.text_expr;

Guess we need to skip reading virtual columns in select. Other stuff should be alright.

[JohnL4]

Ha! Easier than I expected!