mirror of
https://github.com/MariaDB/server.git
synced 2025-02-09 23:24:11 +01:00
c1063a1bed
- This commit rely on MDEV-28391 - When temporary table shadows the base table, error is raised (it can be changed if needed), since the procedure is relying on creating the views and view cannot be created from the temporary table. - Reviewed by: <wlad@mariadb.com>
69 lines
2.6 KiB
Text
69 lines
2.6 KiB
Text
########### suite/sysschema/t/pr_create_synonym_db.test #############
|
|
# #
|
|
# Testing of of the sys.pr_create_synonym_db() procedure #
|
|
# #
|
|
# Creation: #
|
|
# 2016-05-23 jkrogh Implement this test as part of bug 22011361 #
|
|
# #
|
|
#####################################################################
|
|
|
|
-- source include/not_embedded.inc
|
|
# Create a couple of tables and a view
|
|
CREATE TABLE t1 (t1_id int PRIMARY KEY, t1_val varchar(10));
|
|
CREATE TABLE t2 (t2_id int PRIMARY KEY, t1_id int, t2_val int, INDEX (t1_id));
|
|
CREATE TABLE `is` (t1_id int PRIMARY KEY, t1_val varchar(10));
|
|
CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10));
|
|
CREATE SQL SECURITY INVOKER VIEW myview AS SELECT * FROM t1 NATURAL JOIN t2;
|
|
|
|
# Make synonym db of test into test1
|
|
CALL sys.create_synonym_db('test', 'test1');
|
|
# Verify there's one view in test1 per table and view in test
|
|
SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'test1' ORDER BY TABLE_NAME;
|
|
# Try again (should fail)
|
|
-- error ER_SIGNAL_EXCEPTION
|
|
CALL sys.create_synonym_db('test', 'test1');
|
|
|
|
# Try to make the synonym db for an existing empty schema
|
|
CREATE SCHEMA test2;
|
|
-- error ER_SIGNAL_EXCEPTION
|
|
CALL sys.create_synonym_db('test', 'test2');
|
|
# Ensure test2 is still empty
|
|
SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test2';
|
|
|
|
# Use a reserved identifer, Bug #22011361
|
|
CALL sys.create_synonym_db('test', 'is');
|
|
# Verify there's one view in is per table and view in test
|
|
SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'is' ORDER BY TABLE_NAME;
|
|
|
|
# Copy the is schema to i`s schema:
|
|
CALL sys.create_synonym_db('is', 'i`s');
|
|
# Verify there's one view in i`s per table and view in is
|
|
SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'i`s' ORDER BY TABLE_NAME;
|
|
|
|
# Clean up
|
|
DROP SCHEMA test1;
|
|
DROP SCHEMA test2;
|
|
DROP SCHEMA `is`;
|
|
DROP SCHEMA `i``s`;
|
|
DROP VIEW test.myview;
|
|
DROP TABLE test.t1;
|
|
DROP TABLE test.t2;
|
|
DROP TABLE `is`;
|
|
DROP TABLE `ab``c`;
|
|
|
|
--echo #
|
|
--echo # MDEV-28342: sys.create_synonym_db fails
|
|
--echo # when a temporary table masks a base table
|
|
--echo #
|
|
|
|
create database db;
|
|
use db;
|
|
create table a(a int);
|
|
create table t (b int);
|
|
create table b(a int);
|
|
create temporary table b (a int);
|
|
--error ER_SIGNAL_EXCEPTION
|
|
call sys.create_synonym_db('db','db_copy');
|
|
|
|
drop database db;
|
|
drop database db_copy;
|