[
https://issues.apache.org/jira/browse/FLINK-33490?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17788648#comment-17788648
]
xuyang commented on FLINK-33490:
--------------------------------
Hi, [~libenchao] .
I test the sql you provide and some more SQL. The test results are following.
{code:java}
create table t1(
a int,
b int,
c int
);insert into t1 values(1,2,3); {code}
||Desc||SQL||Mysql 5.6||PG 9.6||Flink master||
|using query directly|select * from t1, t1|exception:
Not unique table/alias: 't1'|exception:
relation "t" does not exist Position: 15|exception:
Duplicate relation name 'T' in FROM clause|
|create view with the query|create view v as select * from t1, t1|exception:
Duplicate column name 'a'|exception:
table name "t1" specified more than once|exception:
Duplicate relation name 't1' in FROM clause|
|using query directly by alias|select * from t1 l, t1 r|{color:#ffab00}work
fine and print:
a b c a b c
1 2 3 1 2 3{color}|{color:#ffab00}work fine and print:
a b c a b c
1 2 3 1 2 3 {color}|{color:#ffab00}work fine and print:
a b c a0 b0 c0
1 2 3 1 2 3 {color}|
|create view with the query by alias|create view v as select * from t1 l, t1
r|exception:
Duplicate column name 'a'|exception:
column "a" specified more than once|{color:#de350b}work fine and when select
from this view, print:{color}
{color:#de350b}a b c a0 b0 c0{color}
{color:#de350b}1 2 3 1 2 3{color}|
Please note the red part of the result. I think that's the bug this issue wants
to fix.
> Validate the name conflicts when creating view
> ----------------------------------------------
>
> Key: FLINK-33490
> URL: https://issues.apache.org/jira/browse/FLINK-33490
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 1.19.0
> Reporter: Shengkai Fang
> Assignee: xuyang
> Priority: Major
> Labels: pull-request-available
>
> We should forbid
> ```
> CREATE VIEW id_view AS
> SELECT id, uid AS id FROM id_table
> ```
> As the SQL standards states,
> If <regular view specification> is specified, then:
> i) If any two columns in the table specified by the <query expression> have
> equivalent <column name>s, or if any column of that table has an
> implementation-dependent name, then a <view column list> shall be specified.
> ii) Equivalent <column name>s shall not be specified more than once in the
> <view column list>.
> Many databases also throw exception when view name conflicts, e.g. mysql,
> postgres.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
