2 drop table if exists t1Aa,t2Aa,v1Aa,v2Aa;
3 drop view if exists t1Aa,t2Aa,v1Aa,v2Aa;
4 drop database if exists MySQLTest;
8 # different cases in VIEW
10 create database MySQLTest;
12 create table TaB (Field int);
13 create view ViE as select * from TAb;
14 show create table VIe;
15 drop database MySQLTest;
19 # test of updating and fetching from the same table check
21 create table t1Aa (col1 int);
22 create table t2aA (col1 int);
23 create view v1Aa as select * from t1aA;
24 create view v2aA as select * from v1aA;
25 create view v3Aa as select v2Aa.col1 from v2aA,t2Aa where v2Aa.col1 = t2aA.col1;
27 update v2aA set col1 = (select max(col1) from v1Aa);
29 update v2Aa set col1 = (select max(col1) from t1Aa);
31 update v2aA set col1 = (select max(col1) from v2Aa);
33 update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1;
35 update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1;
37 update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
39 update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1;
41 update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1;
43 update t2Aa,v1aA set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
45 update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1;
47 update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
49 update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1;
51 update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1;
53 update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
55 update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1;
57 update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
59 update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1;
61 update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1;
63 update t2Aa,v2aA set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
65 update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v2aA) where t1Aa.col1 = t2aA.col1;
67 update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from v2Aa) where v1Aa.col1 = t2aA.col1;
69 update v3aA set v3Aa.col1 = (select max(col1) from v1aA);
71 update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
73 update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
75 update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
77 delete from v2Aa where col1 = (select max(col1) from v1Aa);
79 delete from v2aA where col1 = (select max(col1) from t1Aa);
81 delete from v2Aa where col1 = (select max(col1) from v2aA);
83 delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
85 delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
87 delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1;
89 delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1;
91 delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1;
93 delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1;
95 delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1;
97 delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1;
99 delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1;
101 insert into v2Aa values ((select max(col1) from v1aA));
103 insert into t1aA values ((select max(col1) from v1Aa));
105 insert into v2aA values ((select max(col1) from v1aA));
107 insert into v2Aa values ((select max(col1) from t1Aa));
109 insert into t1aA values ((select max(col1) from t1Aa));
111 insert into v2aA values ((select max(col1) from t1aA));
113 insert into v2Aa values ((select max(col1) from v2aA));
115 insert into t1Aa values ((select max(col1) from v2Aa));
117 insert into v2aA values ((select max(col1) from v2Aa));
119 insert into v3Aa (col1) values ((select max(col1) from v1Aa));
121 insert into v3aA (col1) values ((select max(col1) from t1aA));
123 insert into v3Aa (col1) values ((select max(col1) from v2aA));
124 drop view v3aA,v2Aa,v1aA;
125 drop table t1Aa,t2Aa;
130 create table t1Aa (col1 int);
131 create view v1Aa as select col1 from t1Aa as AaA;
132 show create view v1AA;
134 select Aaa.col1 from t1Aa as AaA;
135 create view v1Aa as select Aaa.col1 from t1Aa as AaA;
137 create view v1Aa as select AaA.col1 from t1Aa as AaA;
138 show create view v1AA;
144 # Bug #31562: HAVING and lower case
147 CREATE TABLE t1 (a int, b int);
149 select X.a from t1 AS X group by X.b having (X.a = 1);
150 select X.a from t1 AS X group by X.b having (x.a = 1);
151 select X.a from t1 AS X group by X.b having (x.b = 1);
153 CREATE OR REPLACE VIEW v1 AS
154 select X.a from t1 AS X group by X.b having (X.a = 1);
164 --echo End of 5.0 tests.
168 --echo # Bug #53095: SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS
169 --echo # returns nothing
173 `f1` char(3) NOT NULL,
175 ) ENGINE=myisam DEFAULT CHARSET=latin1;
177 SELECT count(COLUMN_NAME) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME =
179 SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'TTT';
184 --echo End of 5.0 tests.