# name: test/sql/catalog/test_set_schema.test
# description: Default schema name changes
# group: [catalog]

# This test focuses on actual name lookup.
# See also test_set_search_path.test

statement ok
CREATE SCHEMA test;

statement ok
CREATE SCHEMA out_of_path;

statement ok
SET SESSION schema = 'test';

# Testing CREATE TABLE

statement ok
CREATE TABLE main.main_table(j INTEGER);

statement ok
CREATE TABLE test_table(i INTEGER);

statement ok
CREATE TABLE out_of_path.oop_table(k INTEGER);

statement ok
SELECT * FROM test.test_table;

statement ok
SELECT * FROM test_table;

statement ok
SELECT * FROM main_table;

statement ok
SELECT * FROM out_of_path.oop_table;

statement error
SELECT * FROM out_of_path.test_table;
----

statement error
SELECT * FROM main.test_table;
----

# Testing INSERT, UPDATE and DELETE

statement error
INSERT INTO main.test_table (i) VALUES (1);
----

statement ok
INSERT INTO test_table (i) VALUES (1);

statement ok
INSERT INTO test.test_table (i) VALUES (2), (3);

statement ok
INSERT INTO main_table (j) VALUES (4);

statement ok
INSERT INTO main.main_table (j) VALUES (5), (6);

statement error
INSERT INTO oop_table (k) VALUES (7);
----

statement ok
INSERT INTO out_of_path.oop_table (k) VALUES (8), (9);

statement error
DELETE FROM main.test_table WHERE i=3;
----

statement error
DELETE FROM test.main_table WHERE i=5;
----

statement error
DELETE FROM oop_table WHERE k=8;
----

statement ok
DELETE FROM test.test_table WHERE i=1;

statement ok
DELETE FROM test_table WHERE i=2;

statement ok
DELETE FROM main.main_table WHERE j=4;

statement ok
DELETE FROM main_table WHERE j=5;

statement ok
DELETE FROM out_of_path.oop_table WHERE k=8;

query I
SELECT i FROM test_table;
----
3

query I
SELECT j FROM main.main_table;
----
6

query I
SELECT k FROM out_of_path.oop_table;
----
9

statement error
UPDATE main.test_table SET i=10 WHERE i=1;
----

statement ok
UPDATE test_table SET i=30 WHERE i=3;

statement ok
UPDATE test.test_table SET i=300 WHERE i=30;

statement ok
UPDATE main_table SET j=60 WHERE j=6;

statement ok
UPDATE main.main_table SET j=600 WHERE j=60;

query I
SELECT i FROM test_table;
----
300

query I
SELECT j FROM main_table;
----
600

# Testing temp table.
# test_temp_table should *not* be created in the test schema, but in the temp schema

statement ok
CREATE TEMP TABLE test_temp_table(i INTEGER);

statement error
SELECT * FROM memory.main.test_temp_table;
----

statement error
SELECT * FROM test.test_temp_table;
----

statement ok
SELECT * FROM test_temp_table;

statement ok
SELECT * FROM temp.test_temp_table;

# Testing functions and aggregates
query I
SELECT abs(i) FROM test_table;
----
300

# aggregates should work as expected
query I
SELECT sum(i) FROM test_table;
----
300

# Testing Views
statement ok
CREATE VIEW test_view AS SELECT * FROM test_table;

statement ok
CREATE VIEW main.main_view AS SELECT * FROM main.main_table;

statement ok
CREATE VIEW out_of_path.oop_view AS SELECT * FROM out_of_path.oop_table;

statement error
SELECT * FROM main.test_view;
----

statement ok
SELECT * FROM test.test_view;

statement ok
SELECT * FROM test_view;

statement ok
SELECT * FROM main.main_view;

statement ok
SELECT * FROM main_view;

statement error
SELECT * FROM oop_view;
----

statement ok
SELECT * FROM out_of_path.oop_view;

statement ok
SET SESSION schema = 'main';

# Test view's schema being bound on definition.
statement error
CREATE VIEW bad_test_view AS SELECT * FROM test_table;
----
Catalog Error: Table with name test_table does not exist!

statement ok
SELECT * FROM test.test_view;

statement ok
SET SESSION schema = 'test';

statement error
DROP VIEW main.test_view
----

statement ok
DROP VIEW test_view

statement ok
DROP VIEW main_view

statement error
DROP VIEW oop_view
----

statement ok
DROP VIEW out_of_path.oop_view

# Testing Macros

statement ok
CREATE MACRO test_macro(a, b) AS a + b;

statement ok
CREATE MACRO test_macro2(c, d) AS c * d;

statement ok
CREATE MACRO main.main_macro(a, b) AS a - b;

statement ok
CREATE MACRO out_of_path.oop_macro(a, b) AS a * b;

statement error
SELECT main.test_macro(1, 2);
----

statement error
SELECT oop_macro(1, 2);
----

statement ok
SELECT main_macro(1, 2);

statement ok
SELECT main.main_macro(1, 2);

statement ok
SELECT test.test_macro(1, 2);

statement ok
SELECT test_macro(1, 2);

statement ok
SELECT out_of_path.oop_macro(1, 2);

statement error
DROP MACRO main.test_macro;
----

statement ok
DROP MACRO test_macro;

statement ok
DROP MACRO test.test_macro2;

statement ok
DROP MACRO main_macro;

statement error
DROP MACRO oop_macro;
----

statement ok
DROP MACRO out_of_path.oop_macro;

# Testing sequences.

statement ok
CREATE SEQUENCE test_sequence;

statement ok
CREATE SEQUENCE test_sequence2;

statement ok
CREATE SEQUENCE main.main_sequence;

statement ok
CREATE SEQUENCE out_of_path.oop_sequence;

statement error
SELECT main.nextval('main.test_sequence');
----

statement ok
SELECT main.nextval('test.test_sequence');

statement ok
SELECT main.nextval('test_sequence');

statement ok
SELECT main.nextval('main.main_sequence');

statement ok
SELECT main.nextval('main_sequence');

statement error
SELECT main.nextval('oop_sequence');
----

statement ok
SELECT main.nextval('out_of_path.oop_sequence');

statement error
DROP SEQUENCE main.test_sequence;
----

statement ok
DROP SEQUENCE test_sequence;

statement ok
DROP SEQUENCE test.test_sequence2;

statement ok
DROP SEQUENCE main_sequence;

statement error
DROP SEQUENCE oop_sequence;
----

statement ok
DROP SEQUENCE out_of_path.oop_sequence;

# Testing ALTER TABLE

statement error
ALTER TABLE main.test_table ADD COLUMN k INTEGER;
----

statement ok
ALTER TABLE main.main_table ADD COLUMN k INTEGER;

statement ok
ALTER TABLE main_table ADD COLUMN l INTEGER;

statement ok
ALTER TABLE test_table ADD COLUMN m INTEGER;

statement ok
ALTER TABLE test.test_table ADD COLUMN n INTEGER;

statement error
ALTER TABLE oop_table ADD COLUMN o INTEGER;
----

statement ok
ALTER TABLE out_of_path.oop_table ADD COLUMN p INTEGER;

# Testing DROP TABLE

statement error
DROP TABLE main.test_table;
----

statement error
DROP TABLE test.main_table;
----

statement ok
DROP TABLE test_table;

statement ok
DROP TABLE main_table;

statement error
DROP TABLE oop_table;
----

statement ok
DROP TABLE out_of_path.oop_table;

statement ok
CREATE TABLE test_table2(i INTEGER);

statement ok
DROP TABLE test.test_table2;

statement ok
CREATE TABLE test_table3(i INTEGER);

statement ok
DROP TABLE IF EXISTS test_table3;

statement ok
DROP TABLE IF EXISTS test_table3;

statement ok
CREATE TABLE test_table4(i INTEGER);

statement ok
DROP TABLE IF EXISTS test.test_table4;

statement ok
DROP TABLE IF EXISTS test.test_table4;

statement ok
CREATE TABLE main.main_table2(i INTEGER);

statement ok
DROP TABLE IF EXISTS main.main_table2;

statement ok
DROP TABLE IF EXISTS main.main_table2;