Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
-
ghx-label-5
Description
The toSql functions in CreateViewStmt and AlterViewStmt generate SQL by appending types to the column definitions. This is invalid because view definitions should not specify the type of a column. The column type should be inherited from the source table.
Example query to reproduce:
create view foo (a, b) as select int_col, bigint_col from functional.alltypes;
The SQL generated by the toSql() function:
CREATE VIEW foo(a INT, b BIGINT) AS SELECT int_col, bigint_col FROM functional.alltypes
Executing the query generated by toSql():
[localhost:21000] default> CREATE VIEW foo(a INT, b BIGINT) AS SELECT int_col, bigint_col FROM functional.alltypes; Query: CREATE VIEW foo(a INT, b BIGINT) AS SELECT int_col, bigint_col FROM functional.alltypes ERROR: AnalysisException: Syntax error in line 1: CREATE VIEW foo(a INT, b BIGINT) AS SELECT int... ^ Encountered: INTEGER Expected: COMMENT, COMMA CAUSED BY: Exception: Syntax error
In other databases like MySQL and PostgreSQL, the view definition statements can't explicitly set the column types. The type of a column in a view should always be inherited from the source table.