Repository: tobymao/sqlglot
Pull request: fix(parser)!: virtual column with AS(expr) as ComputedColumnConstraint
Fixes #5173

Problem statement:
Snowflake Parser: Virtual Column AS (<expression>) Incorrectly Parsed as Column Constraint
sqlglot Version: 26.25.3 
Dialect: Snowflake
Problem Description:
When parsing Snowflake DDL, sqlglot does not correctly interpret the AS (<expression>) syntax used for defining virtual (generated) columns. Instead of recognizing this as the definition of the column's generation expression and populating a dedicated AST attribute, it misinterprets the entire AS (<expression>) clause as a generic ColumnConstraint.

Suggested Enhancement:
Update the Snowflake dialect parser to correctly identify the AS (<expression>) syntax for virtual columns and populate the ColumnDef.args.get('generated') attribute with the parsed expression AST.

How to reproduce:
```
python test_sqlglot_virtual_column.py 
Testing Snowflake virtual column parsing with sqlglot version: 26.25.3
------------------------------
Successfully parsed the DDL for table: 
------------------------------
Found virtual column: LINE_TOTAL
  Declared data type (AST): DECIMAL(16, 4)
  Declared data type (SQL): DECIMAL(16, 4)
  Generated expression: Not found in AST (this means it wasn't parsed as a virtual column)
  Constraints on virtual column:
    - AS ((CAST(QUANTITY AS DECIMAL(8, 2))) * UNIT_PRICE) (SQL: AS ((CAST(QUANTITY AS DECIMAL(8, 2))) * UNIT_PRICE))


cat test_sqlglot_virtual_column.py 
import sqlglot
from sqlglot import exp

# Test DDL with Snowflake virtual column syntax (double parentheses)
snowflake_virtual_col_ddl_double_paren = """
CREATE OR REPLACE TABLE sales_data_double (
    PRODUCT_ID NUMBER(10,0) NOT NULL,
    QUANTITY NUMBER(8,2) NOT NULL,
    UNIT_PRICE NUMBER(10,4) NOT NULL,
    LINE_TOTAL NUMBER(16,4) AS ((CAST(QUANTITY AS NUMBER(8,2))) * UNIT_PRICE),
    SALE_DATE DATE
);
"""

# Test DDL with Snowflake virtual column syntax (single parentheses)
snowflake_virtual_col_ddl_single_paren = """
CREATE OR REPLACE TABLE sales_data_single (
    PRODUCT_ID NUMBER(10,0) NOT NULL,
    QUANTITY NUMBER(8,2) NOT NULL,
    UNIT_PRICE NUMBER(10,4) NOT NULL,
    LINE_TOTAL NUMBER(16,4) AS (CAST(QUANTITY AS NUMBER(8,2)) * UNIT_PRICE),
    SALE_DATE DATE
);
"""

print(f"Testing Snowflake virtual column parsing with sqlglot version: {sqlglot.__version__}")

def analyze_ddl(ddl_string: str, description: str):
    print(f"\n--- Analyzing: {description} ---")
    try:
        parsed_expressions = sqlglot.parse(ddl_string, read='snowflake')
        if not parsed_expressions:
            print("SQLGlot did not return any expressions. Parsing failed or DDL was empty.")
            return

        create_table_expr = parsed_expressions[0]
        if not isinstance(create_table_expr, exp.Create):
            print(f"Expected a CREATE statement, but got: {type(create_table_expr)}. Parsing might have issues.")
            return

        print(f"Successfully parsed the DDL for table: {create_table_expr.this.name}")
        found_virtual_column = False
        for col_def in create_table_expr.find_all(exp.ColumnDef):
            col_name = col_def.this.name
            if col_name.upper() == 'LINE_TOTAL':
                found_virtual_column = True
                print(f"Found column: {col_name}")
                
                if col_def.args.get('kind'):
                    print(f"  Declared data type (SQL): {col_def.args['kind'].sql(dialect='snowflake')}")
                else:
                    print("  Declared data type: Not found in AST")
                
                generated_expr_ast = col_def.args.get('generated')
                if generated_expr_ast:
                    print(f"  SQLGlot *correctly* identified generated expression (AST): {generated_expr_ast}")
                    print(f"  Generated expression (SQL): {generated_expr_ast.sql(dialect='snowflake')}")
                else:
                    print("  SQLGlot did NOT identify a 'generated' expression directly.")
                
                constraints = col_def.args.get('constraints', [])
                if constraints:
                    print(f"  Constraints found on column '{col_name}':")
                    for i, constr in enumerate(constraints):
                        print(f"    Constraint [{i}]:")
                        print(f"      Type (AST): {type(constr).__name__}")
                        print(f"      Value (AST): {constr}")
                        print(f"      SQL: {constr.sql(dialect='snowflake')}")
                        # Check if this constraint node seems to be our AS expression
                        if isinstance(constr, exp.ColumnConstraint) and str(constr.args.get('kind')).upper().startswith("AS") :
                             print("      ----> This constraint appears to be the AS <expression> clause.")
                        elif isinstance(constr, exp.GeneratedAsIdentityColumnConstraint): # sqlglot sometimes uses this for AS
                            print("      ----> This constraint node is specific to GENERATED AS.")
                            if constr.args.get('expression'):
                                print(f"          Extracted Expression from Constraint (AST): {constr.args.get('expression')}")
                                print(f"          Extracted Expression from Constraint (SQL): {constr.args.get('expression').sql(dialect='snowflake')}")

                else:
                    print(f"  No constraints found on column '{col_name}'.")
                break
        
        if not found_virtual_column:
            print("Column 'LINE_TOTAL' not found as expected.")

    except Exception as e:
        print(f"An error occurred during parsing or AST inspection: {e}")
```

Hints from issue discussion:

Commit message: fix(parse)!: virtual column with computation as ComputedColumnConstraint

Patch to review:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 07f8e10e66..c86f124161 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -5897,11 +5897,15 @@ def _parse_column_def(
             )
         ):
             self._advance()
-            constraints.append(
-                self.expression(
-                    exp.ColumnConstraint,
-                    kind=exp.TransformColumnConstraint(this=self._parse_disjunction()),
+            constraint_expr = self._parse_disjunction()
+            if constraint_expr:
+                constraint_type = (
+                    exp.ComputedColumnConstraint
+                    if isinstance(constraint_expr.this, exp.Binary)
+                    else exp.TransformColumnConstraint
                 )
+            constraints.append(
+                self.expression(exp.ColumnConstraint, kind=constraint_type(this=constraint_expr))
             )
 
         while True:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index d5da087bd1..2404d993af 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1692,6 +1692,11 @@ def test_ddl(self):
                 exp.TransformColumnConstraint
             )
         )
+        self.assertIsNotNone(
+            self.validate_identity(
+                "CREATE TABLE foo (t1 INT, t2 INT, bar INT AS (t1 * t2 * 2))"
+            ).find(exp.ComputedColumnConstraint)
+        )
 
     def test_user_defined_functions(self):
         self.validate_all(


Code review comments:
diff_hunk: @@ -5897,11 +5897,15 @@ def _parse_column_def(
             )
         ):
             self._advance()
-            constraints.append(
-                self.expression(
-                    exp.ColumnConstraint,
-                    kind=exp.TransformColumnConstraint(this=self._parse_disjunction()),
+            constraint_expr = self._parse_disjunction()
+            if constraint_expr:
+                constraint_type = (
+                    exp.ComputedColumnConstraint
+                    if isinstance(constraint_expr.this, exp.Binary)
+                    else exp.TransformColumnConstraint
                 )
+            constraints.append(
+                self.expression(exp.ColumnConstraint, kind=constraint_type(this=constraint_expr))
path: sqlglot/parser.py
text: @user1:
You will get a `NameError` if you don't hit L5901, shouldn't this be indented?

@author:
yep, fixed it.