e537a0c479
Co-authored-by: wjhh2008 <wjh2006-1@163.com> Co-authored-by: GongYusen <986957406@qq.com> Co-authored-by: hy-guo <fqboyg@gmail.com>
520 lines
13 KiB
Plaintext
520 lines
13 KiB
Plaintext
--disable_query_log
|
|
set @@session.explicit_defaults_for_timestamp=off;
|
|
--enable_query_log
|
|
# owner: dachuan.sdc
|
|
# owner group: SQL2
|
|
# tags: optimizer
|
|
# description:
|
|
#
|
|
--disable_warnings
|
|
drop database if exists view;
|
|
--enable_warnings
|
|
create database view;
|
|
use view;
|
|
set character_set_client = 45;
|
|
|
|
#
|
|
## test view schema weak-binding
|
|
#
|
|
|
|
#simple
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v, vv;
|
|
--enable_warnings
|
|
create table t1(c1 int, c2 int);
|
|
create view v as select c1, c2 from t1;
|
|
create view vv as select c1 from v;
|
|
select c1, c2 from v;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v;
|
|
--error 1356
|
|
select c2 from v;
|
|
--error 1356
|
|
select c1, c2 from v;
|
|
drop table t1;
|
|
--error 1356
|
|
select c1 from v;
|
|
--error 1356
|
|
select c2 from v;
|
|
--error 1356
|
|
select c1, c2 from v;
|
|
create table t1(c1 int);
|
|
--error 1356
|
|
select c1 from vv;
|
|
--error 1356
|
|
select c2 from vv;
|
|
--error 1356
|
|
select c1, c2 from vv;
|
|
--error 1356
|
|
select 1 as a from vv;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v, vv;
|
|
--enable_warnings
|
|
create table t1(c1 int, c2 int);
|
|
create view v as select c1, c2 from t1;
|
|
create view vv as select c2 from v;
|
|
select c1, c2 from v;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v;
|
|
--error 1356
|
|
select c2 from v;
|
|
--error 1356
|
|
select c1, c2 from v;
|
|
drop table t1;
|
|
--error 1356
|
|
select c1 from v;
|
|
--error 1356
|
|
select c2 from v;
|
|
--error 1356
|
|
select c1, c2 from v;
|
|
create table t1(c1 int);
|
|
--error 1356
|
|
select c1 from vv;
|
|
--error 1356
|
|
select c2 from vv;
|
|
--error 1356
|
|
select c1, c2 from vv;
|
|
--error 1356
|
|
select c2, c1 from vv;
|
|
--error 1356
|
|
select 1 as a from vv;
|
|
|
|
#select * from view
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists v,vv;
|
|
--enable_warnings
|
|
create table t1(c1 int ,c2 int);
|
|
create table t2(c1 int ,c2 int);
|
|
create view v as select c1, c2 from t1;
|
|
create view vv as select c1 from v;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select * from v;
|
|
--error 1356
|
|
select v.* from v;
|
|
--error 1356
|
|
select * from vv;
|
|
--error 1356
|
|
select vv.* from vv;
|
|
|
|
#join on and using
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create table t2(c1 int,c2 int);
|
|
create view v as select c1, c2 from t1;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select v.c1 from v join t2 on v.c1 = t2.c1;
|
|
--error 1356
|
|
select v.c1 from v join t2 on v.c2 = t2.c2;
|
|
--error 1356
|
|
select v.c1 from v join t2 using(c1);
|
|
--error 1356
|
|
select v.c1 from v join t2 using(c2);
|
|
|
|
#column in where
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select c1, c2 from t1;
|
|
select c1 from v where c2 = 1;
|
|
select c1 from v where (select c2) = 1;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v where c2 = 1;
|
|
--error 1356
|
|
select c1 from v where (select c2) = 1;
|
|
|
|
#column in order by
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select c1, c2 from t1;
|
|
select c1 from v order by c2;
|
|
select c1 from v order by (select c2);
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v order by c2;
|
|
--error 1356
|
|
select c1 from v order by (select c2);
|
|
|
|
#column in group by
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select c1, c2 from t1;
|
|
select c1 from v group by c2;
|
|
select c1 from v group by (select c2);
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v group by c2;
|
|
--error 1356
|
|
select c1 from v group by (select c2);
|
|
|
|
#column in having
|
|
#drop table if exists t1,t2;
|
|
#drop view if exists v;
|
|
#create table t1(c1 int,c2 int);
|
|
#create view v as select c1, c2 from t1;
|
|
#select c1 from v having c2 = 1;
|
|
#select c1 from v having (select c2) = 1;
|
|
#alter table t1 drop column c2;
|
|
#--error 1054
|
|
#select c1 from v having c2 = 1;
|
|
#--error 1356
|
|
#select c1 from v having (select c2) = 1;
|
|
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
insert into t1 values(1,2),(2,3),(3,4);
|
|
create view v as select c1,c2 from t1 order by c2;
|
|
alter table t1 drop column c2;
|
|
#order by 即使是从select里找到了c2(select中的c2不报错是因为view_stmt)，因为select里面的c2也是无效列，因此最终结果也是视图无效
|
|
--error 1356
|
|
select c1 from v;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
insert into t1 values(1,2),(2,3),(3,4);
|
|
create view v as select c1, -1 as c2 from t1 order by (select c2);
|
|
select c1 from v;
|
|
alter table t1 drop column c2;
|
|
# a select c2提升上来之后从t1基础列里面找不到就直接报错，不会再去select_items找
|
|
--error 1356
|
|
select c1 from v;
|
|
# b (bug)
|
|
#select c1 from (select c1, -1 as c2 from t1 order by (select c2)) v;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select c1,c2 from t1 where c2=1;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
insert into t1 values(1,2),(2,3),(3,4);
|
|
create view v as select c1, -1 as c2 from t1 order by (select c2);
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v;
|
|
|
|
# agg
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select c1,sum(c2) from t1;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v;
|
|
|
|
#insert
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
insert into t1 values(1,2),(2,3),(3,4),(4,5);
|
|
create view v as select c1,c2 from t1;
|
|
select * from v;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v;
|
|
--error 1356
|
|
select c2 from v;
|
|
--error 1356
|
|
select * from v;
|
|
alter table t1 add column c2 int default 100;
|
|
select * from v;
|
|
alter table t1 alter column c2 set default 200;
|
|
insert into t1(c1) values(5);
|
|
select * from v;
|
|
#alter table t1 change column c1 c1 varchar(20) default 'test';
|
|
#insert into t1 values();
|
|
#select * from v;
|
|
|
|
|
|
#
|
|
##test desc (show columns from table)
|
|
#
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int);
|
|
create view v as select c1 from t1;
|
|
desc v;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int);
|
|
create table t2(c1 int);
|
|
create view v as (select c1 from t1) union (select c1 from t2);
|
|
desc v;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select c1,c2 from t1;
|
|
desc v;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
desc v;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v,vv;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select c1,c2 from t1;
|
|
create view vv as select c1,c2 from v;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
desc v;
|
|
--error 1356
|
|
desc vv;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v,vv;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select c1,c2 from t1;
|
|
create view vv as select c1,c2 from v;
|
|
create or replace view v as select c1 from t1;
|
|
desc v;
|
|
--error 1356
|
|
desc vv;
|
|
create or replace view vv as select c1 from v;
|
|
desc vv;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1,t2,t3,t4;
|
|
drop view if exists v,vv;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create table t2(c1 int,c2 int);
|
|
create table t3(c1 int,c2 int);
|
|
create table t4(c1 int,c2 int);
|
|
create view v as ((select * from t1) union (select c1,c2 from t2)) union (select * from t3);
|
|
desc v;
|
|
create view vv as (select * from v) union (select * from t4);
|
|
desc vv;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create table t2(c1 int,c2 int);
|
|
create view v as select c1,c2 from t1;
|
|
select (select c2 from v limit 1) from t2;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select (select c2 from v limit 1) from t2;
|
|
--error 1356
|
|
select (select c3 from v limit 1) from t2;
|
|
alter table t2 add column c3 int;
|
|
--error 1356
|
|
select (select c3 from v limit 1) from t2;
|
|
|
|
#
|
|
##Bug
|
|
#
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists t1;
|
|
drop table if exists v1,v2,v3;
|
|
drop view if exists v1,v2,v3;
|
|
--enable_warnings
|
|
create table t1(c1 int);
|
|
create view v1 as select * from t1;
|
|
create view v2 as select * from v1;
|
|
create view v3 as select * from v2;
|
|
drop table t1;
|
|
rename table v3 to t1;
|
|
--error 1462
|
|
select * from v1;
|
|
--error 1462
|
|
select * from v2;
|
|
--error 1146
|
|
select * from v3;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists t1,t2;
|
|
drop table if exists v1,v2,v3;
|
|
drop view if exists v1,v2,v3;
|
|
--enable_warnings
|
|
create table t1(c1 int);
|
|
create table t2(c1 int);
|
|
create view v1 as select * from t1;
|
|
create view v2 as select * from t2;
|
|
create view v3 as select v1.c1 as v1c1, v2.c1 as v2c1 from v1,v2;
|
|
drop table t2;
|
|
rename table v3 to t2;
|
|
--error 1462
|
|
select * from t2;
|
|
--error 1462
|
|
select * from v2;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists t1,t2;
|
|
drop table if exists v1,v2;
|
|
drop view if exists v1,v2;
|
|
--enable_warnings
|
|
create table t1(c1 int);
|
|
create table t2(c1 int);
|
|
create view v1 as select * from t1;
|
|
create view v2 as select (select c1 from v1 limit 1) from t2;
|
|
drop table t1;
|
|
rename table v2 to t1;
|
|
--error 1462
|
|
select * from t1;
|
|
--error 1462
|
|
select * from v1;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists t1;
|
|
drop table if exists v1,v2,v3,v4;
|
|
drop view if exists v1,v2,v3,v4;
|
|
--enable_warnings
|
|
create table t1(c1 int);
|
|
create view v1 as select * from t1;
|
|
create view v2 as select * from v1;
|
|
create view v3 as select * from v2;
|
|
create view v4 as select * from v2;
|
|
drop view v1;
|
|
rename table v4 to v1;
|
|
--error 1462
|
|
select * from v1;
|
|
--error 1462
|
|
select * from v2;
|
|
--error 1462
|
|
select * from v3;
|
|
|
|
#
|
|
## Bug :
|
|
## 主要测试在非select语句引用select_stmt(view展开)的问题，select结果作为其它stmt的输入暂时不支持，
|
|
## 这里仅仅针对bug加入回归case
|
|
#
|
|
SET optimizer_switch = (SELECT variable_value FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'optimizer_switch');
|
|
--disable_warnings
|
|
drop table if exists t1,t2;
|
|
drop view if exists v1;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create table t2(c1 int,c2 int);
|
|
insert into t1(c1,c2) values(1,2),(2,3);
|
|
create view v1 as select * from t1;
|
|
insert into t2(c1,c2) select * from v1;
|
|
select * from t2;
|
|
|
|
#Bug 对于基表schema失效的视图，查询成功后不应该有warning
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select * from t1;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v;
|
|
show warnings;
|
|
|
|
#Bug 对于基表schema失效的视图，查询成功后不应该有warning
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
create view v as select * from t1;
|
|
alter table t1 drop column c2;
|
|
--error 1356
|
|
select c1 from v;
|
|
show warnings;
|
|
|
|
#Bug 创建视图中包含not exists子查询出错
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1(c1 int,c2 int);
|
|
insert into t1 values(1, 1);
|
|
create view v as select * from t1 where not exists(select * from t1 where c1>0);
|
|
--source mysql_test/include/show_create_table_old_version_replica2.inc
|
|
show create view v;
|
|
select * from v;
|
|
create view xy as select 123 from dual where not exists (select 1 from dual) limit 1;
|
|
select * from xy;
|
|
|
|
#Bug 创建视图中包含union子句并且类型为char的时候长度出错
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop table if exists t2;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1 (k int primary key, v1 int, v2 int);
|
|
create table t2 (k int primary key, v1 char(1), v2 varchar(10));
|
|
create view v as select k, v1, v2 from t1 union select k, v1, v2 from t2;
|
|
--source mysql_test/include/show_create_table_old_version_replica2.inc
|
|
show create view v;
|
|
select * from v;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop view if exists v;
|
|
--enable_warnings
|
|
create table t1 (k int, v int);
|
|
create view v as (select k, v from t1) union (select 1, 1 from t1);
|
|
--source mysql_test/include/show_create_table_old_version_replica2.inc
|
|
show create view v;
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
drop table if exists t2;
|
|
--enable_warnings
|
|
CREATE TABLE `t11` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`) );
|
|
CREATE TABLE `t21` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`) );
|
|
|
|
--real_sleep 3
|
|
explain extended_noaddr select t3.* from (select t11.a as X, t21.a as Y from t11 left join t21 on t11.a = t21.a) as t3 where t3.y is not null and t3.x > 10;
|
|
drop database if exists view;
|