server/src/test/resources/sql/view.iq

# view.iq - DDL on views
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to you under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
!use server
!set outputformat mysql

# Create a view
create view v as
select a, a + 1 as b
from (values 1, 2) as t(a);
(0 rows modified)

!update

select * from v;
+---+---+
| A | B |
+---+---+
| 1 | 2 |
| 2 | 3 |
+---+---+
(2 rows)

!ok

# Try to create a view that already exists
create view v as
select a, a + 2 as b
from (values 1, 2) as t(a);
View 'V' already exists and REPLACE not specified
!error

create or replace view v as
select a, a + 3 as b
from (values 1, 2) as t(a);
(0 rows modified)

!update

select * from v;
+---+---+
| A | B |
+---+---+
| 1 | 4 |
| 2 | 5 |
+---+---+
(2 rows)

!ok

# Drop view
drop view v;
(0 rows modified)

!update

# Explicit column names
create view v (x, "y z") as
select a, a + 4 as b
from (values 1, 2) as t(a);
(0 rows modified)

!update

select * from v;
+---+-----+
| X | y z |
+---+-----+
| 1 |   5 |
| 2 |   6 |
+---+-----+
(2 rows)

!ok

# Wrong number of columns
create or replace view v (x, y, z) as
select a, a + 5 as b
from (values 1, 2) as t(a);
List of column aliases must have same degree as table; table has 2 columns ('A', 'B'), whereas alias list has 3 columns
!error

# Column names not unique
create or replace view v (x, x) as
select a, a + 6 as b
from (values 1, 2) as t(a);
Duplicate name 'X' in column alias list
!error

# View based on VALUES
create or replace view v (p, q) as
values (1, 'a'), (2, 'b');
(0 rows modified)

!update

select * from v;
+---+---+
| P | Q |
+---+---+
| 1 | a |
| 2 | b |
+---+---+
(2 rows)

!ok

# View based on table
create table t (i int);
(0 rows modified)

!update

insert into t values (1), (2), (3);
(3 rows modified)

!update

create or replace view v (e, d) as
select i, i + 1 from t;
(0 rows modified)

!update

# View based on UNION of another view
create or replace view v2 as
select * from v
union all
select e + d, e - d from v;
(0 rows modified)

!update

select * from v2;
+---+----+
| E | D  |
+---+----+
| 1 |  2 |
| 2 |  3 |
| 3 | -1 |
| 3 |  4 |
| 5 | -1 |
| 7 | -1 |
+---+----+
(6 rows)

!ok

# Drop view, then make sure that a query can't find it
drop view if exists v2;
(0 rows modified)

!update

select * from v2;
Object 'V2' not found
!error

# Try to drop view that does not exist
drop view v3;
View 'V3' not found
!error

drop view if exists v3;
(0 rows modified)

!update

# End view.iq