~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
# Initialize
2
--disable_warnings
3
drop table if exists t1,t2,t3;
4
--enable_warnings
5
6
select * from (select 2) b;
7
-- error 1054
8
SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
9
-- error 1054
10
SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
11
CREATE TABLE t1 (a int not null, b char (10) not null);
12
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
13
CREATE TABLE t2 (a int not null, b char (10) not null);
14
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
15
select t1.a,t3.y from t1,(select a as y from t2  where b='c') as t3  where t1.a = t3.y;
16
select t1.a,t3.a from t1,(select * from t2  where b='c') as t3  where t1.a = t3.a;
17
CREATE TABLE t3 (a int not null, b char (10) not null);
18
insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c');
19
select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3.a>3) as t5  where t2.b=t5.b) as t4  where t1.a = t4.y;
20
--error 1054
21
SELECT a FROM (SELECT 1 FROM (SELECT 1) a HAVING a=1) b;
22
--error 1052
23
SELECT a,b as a FROM (SELECT '1' as a,'2' as b) b  HAVING a=1;
24
SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=2;
25
SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1;
26
--error 1054
27
SELECT 1 FROM (SELECT 1) a WHERE a=2;
28
--error 1054
29
SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1  HAVING a=1) as a;
30
select * from t1 as x1, (select * from t1) as x2;
31
explain select * from t1 as x1, (select * from t1) as x2;
32
drop table if exists  t2,t3;
33
select * from (select 1) as a;
34
select a from (select 1 as a) as b;
35
select 1 from (select 1) as a;
36
select * from (select * from t1 union select * from t1) a;
37
select * from (select * from t1 union all select * from t1) a;
38
select * from (select * from t1 union all select * from t1 limit 2) a;
39
explain select * from (select * from t1 union select * from t1) a;
40
explain select * from (select * from t1 union all select * from t1) a;
41
CREATE TABLE t2 (a int not null);
42
insert into t2 values(1);
43
select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a;
44
select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a;
45
explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1;
46
drop table t1, t2;
47
create table t1(a int not null, t char(8), index(a));
48
disable_query_log;
49
let $1 = 10000;
50
while ($1)
51
 {
52
  eval insert into t1 values ($1,'$1'); 
53
  dec $1;
54
 }
55
enable_query_log;
56
SELECT * FROM (SELECT * FROM t1) as b ORDER BY a  ASC LIMIT 0,20;
57
explain select count(*) from t1 as tt1, (select * from t1) as tt2;
58
drop table t1;
59
SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
60
select * from (select 1 as a) b  left join (select 2 as a) c using(a);
61
--error 1054
62
SELECT * FROM (SELECT 1 UNION SELECT a) b;
63
--error 1054
64
SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
65
--error 1054
66
SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b;
67
--error 1054
68
select 1 from  (select 2) a order by 0;
69
70
#
71
# Test of explain (bug #251)
72
#
73
74
create table t1 (id int);
75
insert into t1 values (1),(2),(3);
76
describe select * from (select * from t1 group by id) bar;
77
drop table t1;
78
79
#
80
# test->used_keys test for derived tables
81
#
82
create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
83
create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
84
insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
85
insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
86
87
SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
88
SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
89
90
explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
91
explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
92
drop table t1,t2;
93
94
#
95
# derived table reference
96
#
97
SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1;
98
99
#
100
# UPDATE/DELETE/INSERT of derived tables
101
#
102
create table t1 (a int);
103
insert into t1 values (1),(2),(3);
104
-- error 1288
105
update (select * from t1) as t1 set a = 5;
106
-- error 1064
107
delete from (select * from t1);
108
-- error 1064
109
insert into  (select * from t1) values (5);
110
drop table t1;
111
112
#
113
# deived tables with subquery inside all by one table
114
#
115
create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1)
116
);
117
insert into t1 VALUES(1,1,1), (2,2,1);
118
select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
119
explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
120
drop table t1;
121
122
create table t1 (a int);
123
insert into t1 values (1),(2);
124
select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
125
explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
126
drop table t1;
127
128
#
129
# multi-update & multi-delete with derived tables
130
#
131
CREATE TABLE `t1` (
132
  `N` int(11) unsigned NOT NULL default '0',
133
  `M` tinyint(1) default '0'
134
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
135
INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0);
136
UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
137
select * from t1;
138
-- error 1288
139
UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2;
140
-- error 1054
141
UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
142
delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
143
select * from t1;
144
--replace_result P2 p2
145
--error ER_NON_UPDATABLE_TABLE
146
delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
147
-- error 1054
148
delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
149
drop table t1;
150
151
#
152
# correct lex->current_select
153
#
154
CREATE TABLE t1 (
155
  OBJECTID int(11) NOT NULL default '0',
156
  SORTORDER int(11) NOT NULL auto_increment,
157
  KEY t1_SortIndex (SORTORDER),
158
  KEY t1_IdIndex (OBJECTID)
159
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
160
CREATE TABLE t2 (
161
  ID int(11) default NULL,
162
  PARID int(11) default NULL,
163
  UNIQUE KEY t2_ID_IDX (ID),
164
  KEY t2_PARID_IDX (PARID)
165
) engine=MyISAM DEFAULT CHARSET=latin1;
166
INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
167
CREATE TABLE t3 (
168
  ID int(11) default NULL,
169
  DATA decimal(10,2) default NULL,
170
  UNIQUE KEY t3_ID_IDX (ID)
171
) engine=MyISAM DEFAULT CHARSET=latin1;
172
INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
173
select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA      from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP;
174
drop table t1, t2, t3;
175
176
#
177
# explain derived
178
#
179
CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL);
180
INSERT INTO t1 VALUES ('a',1),  ('a',2),  ('a',2),  ('a',2),  ('a',3),  ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);
181
SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
182
explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
183
drop table t1;
184
185
#
186
# "Using index" in explain
187
#
188
create table t2 (a int, b int, primary key (a));
189
insert into t2 values (1,7),(2,7);
190
explain select a from t2 where a>1;
191
explain select a from (select a from t2 where a>1) tt;
192
drop table t2;
193
194
#
195
# select list counter
196
#
197
CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY  (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`));
198
insert into t1 values (128, 'rozn', 2, curdate(), 10),
199
  (128, 'rozn', 1, curdate(), 10);
200
SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND  grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices;
201
DROP TABLE t1;
202
203
#
204
# DISTINCT over grouped select on subquery in the FROM clause
205
#
206
create table t1 (a integer, b integer);
207
insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
208
select distinct sum(b) from t1 group by a;
209
select distinct sum(b) from (select a,b from t1) y group by a;
210
drop table t1;
211
212
213
#
214
# Test for bug #7413 "Subquery with non-scalar results participating in
215
# select list of derived table crashes server" aka "VIEW with sub query can
216
# cause the MySQL server to crash". If we have encountered problem during
217
# filling of derived table we should report error and perform cleanup
218
# properly.
219
#
220
CREATE TABLE t1 (a char(10), b char(10));
221
INSERT INTO t1 VALUES ('root','localhost'), ('root','%');
222
--error 1242
223
SELECT * FROM (SELECT (SELECT a.a FROM t1 AS a WHERE a.a = b.a) FROM t1 AS b) AS c;
224
DROP TABLE t1;
225
#
226
# test of union subquery in the FROM clause with complex distinct/all (BUG#6565)
227
#
228
create table t1(a int);
229
create table t2(a int);
230
create table t3(a int);
231
insert into t1 values(1),(1);
232
insert into t2 values(2),(2);
233
insert into t3 values(3),(3);
234
select * from t1 union distinct select * from t2 union all select * from t3;
235
select * from (select * from t1 union distinct select * from t2 union all select * from t3) X;
236
drop table t1, t2, t3;
237
238
#
239
# Bug #11864 non unique names are allowed in subquery
240
#
241
create table t1 (a int);
242
create table t2 (a int);
243
--error 1060
244
select * from (select * from t1,t2) foo;
245
drop table t1,t2;
246
247
#
248
# Bug#10586 - query works with 4.1.8, but not with 4.1.11
249
#
250
create table t1 (ID int unsigned not null auto_increment,
251
                 DATA varchar(5) not null, primary key (ID));
252
create table t2 (ID int unsigned not null auto_increment,
253
                 DATA varchar(5) not null, FID int unsigned not null,
254
                 primary key (ID));
255
select A.* from (t1 inner join (select * from t2) as A on t1.ID = A.FID);
256
select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID);
257
select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID;
258
drop table t1, t2;
259
260
disconnect con1;
261
connection default;
262
drop user mysqltest_1;
263
264
# End of 4.1 tests