1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
|
--disable_warnings
drop table if exists t1Aa,t2Aa,v1Aa,v2Aa;
drop view if exists t1Aa,t2Aa,v1Aa,v2Aa;
drop database if exists MySQLTest;
--enable_warnings
#
# different cases in VIEW
#
create database MySQLTest;
use MySQLTest;
create table TaB (Field int);
create view ViE as select * from TAb;
show create table VIe;
drop database MySQLTest;
use test;
#
# test of updating and fetching from the same table check
#
create table t1Aa (col1 int);
create table t2aA (col1 int);
create view v1Aa as select * from t1aA;
create view v2aA as select * from v1aA;
create view v3Aa as select v2Aa.col1 from v2aA,t2Aa where v2Aa.col1 = t2aA.col1;
-- error 1443
update v2aA set col1 = (select max(col1) from v1Aa);
-- error 1443
update v2Aa set col1 = (select max(col1) from t1Aa);
-- error 1093
update v2aA set col1 = (select max(col1) from v2Aa);
-- error 1443
update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1;
-- error 1443
update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1;
-- error 1093
update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
-- error 1443
update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1;
-- error 1443
update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1;
-- error 1443
update t2Aa,v1aA set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
-- error 1443
update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1;
-- error 1093
update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
-- error 1443
update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1;
-- error 1093
update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1;
-- error 1093
update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
-- error 1093
update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1;
-- error 1093
update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
-- error 1443
update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1;
-- error 1443
update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1;
-- error 1443
update t2Aa,v2aA set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
-- error 1443
update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v2aA) where t1Aa.col1 = t2aA.col1;
-- error 1443
update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from v2Aa) where v1Aa.col1 = t2aA.col1;
-- error 1443
update v3aA set v3Aa.col1 = (select max(col1) from v1aA);
-- error 1443
update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
-- error 1443
update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
-- error 1093
update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
-- error 1443
delete from v2Aa where col1 = (select max(col1) from v1Aa);
-- error 1443
delete from v2aA where col1 = (select max(col1) from t1Aa);
-- error 1093
delete from v2Aa where col1 = (select max(col1) from v2aA);
-- error 1443
delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
-- error 1443
delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
-- error 1093
delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1;
-- error 1443
delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1;
-- error 1093
delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1;
-- error 1443
delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1;
-- error 1093
delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1;
-- error 1443
delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1;
-- error 1443
delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1;
-- error 1443
insert into v2Aa values ((select max(col1) from v1aA));
-- error 1443
insert into t1aA values ((select max(col1) from v1Aa));
-- error 1443
insert into v2aA values ((select max(col1) from v1aA));
-- error 1443
insert into v2Aa values ((select max(col1) from t1Aa));
-- error 1093
insert into t1aA values ((select max(col1) from t1Aa));
-- error 1443
insert into v2aA values ((select max(col1) from t1aA));
-- error 1093
insert into v2Aa values ((select max(col1) from v2aA));
-- error 1443
insert into t1Aa values ((select max(col1) from v2Aa));
-- error 1093
insert into v2aA values ((select max(col1) from v2Aa));
-- error 1443
insert into v3Aa (col1) values ((select max(col1) from v1Aa));
-- error 1443
insert into v3aA (col1) values ((select max(col1) from t1aA));
-- error 1443
insert into v3Aa (col1) values ((select max(col1) from v2aA));
drop view v3aA,v2Aa,v1aA;
drop table t1Aa,t2Aa;
#
# aliases in VIEWs
#
create table t1Aa (col1 int);
create view v1Aa as select col1 from t1Aa as AaA;
show create view v1AA;
drop view v1AA;
select Aaa.col1 from t1Aa as AaA;
create view v1Aa as select Aaa.col1 from t1Aa as AaA;
drop view v1AA;
create view v1Aa as select AaA.col1 from t1Aa as AaA;
show create view v1AA;
drop view v1AA;
drop table t1Aa;
#
# Bug #31562: HAVING and lower case
#
CREATE TABLE t1 (a int, b int);
select X.a from t1 AS X group by X.b having (X.a = 1);
select X.a from t1 AS X group by X.b having (x.a = 1);
select X.a from t1 AS X group by X.b having (x.b = 1);
CREATE OR REPLACE VIEW v1 AS
select X.a from t1 AS X group by X.b having (X.a = 1);
SHOW CREATE VIEW v1;
SELECT * FROM v1;
DROP VIEW v1;
DROP TABLE t1;
--echo End of 5.0 tests.
--echo #
--echo # Bug #53095: SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS
--echo # returns nothing
--echo #
CREATE TABLE `ttt` (
`f1` char(3) NOT NULL,
PRIMARY KEY (`f1`)
) ENGINE=myisam DEFAULT CHARSET=latin1;
SELECT count(COLUMN_NAME) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME =
'TTT';
SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'TTT';
DROP TABLE `ttt`;
--echo End of 5.0 tests.
|
