← Back to branch summary

~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

« back to all changes in this revision

Viewing changes to mysql-test/t/derived.test

  • Committer: Bazaar Package Importer
  • Author(s): Norbert Tretkowski
  • Date: 2010-03-17 14:56:02 UTC
  • Revision ID: james.westby@ubuntu.com-20100317145602-x7e30l1b2sb5s6w6
Tags: upstream-5.1.45
Import upstream version 5.1.45

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/t/derived.test
 
1
# Initialize
 
2
--disable_warnings
 
3
drop table if exists t1,t2,t3;
 
4
--enable_warnings
 
5
 
 
6
select * from (select 2 from DUAL) 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
# Test for select if database is not selected.
 
101
#
 
102
# Connect without a database as user mysqltest_1
 
103
create user mysqltest_1;
 
104
create table t1 select 1 as a;
 
105
connect (con1,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
 
106
connection con1;
 
107
--error 1046
 
108
select 2 as a from (select * from t1) b;
 
109
use test;
 
110
select 2 as a from (select * from t1) b;
 
111
drop table t1;
 
112
--error 1064
 
113
select mail_id,  if(folder.f_description!='', folder.f_description, folder.f_name) as folder_name,  date, address_id, phrase, address,  subject from folder, (select  mail.mail_id as mail_id,  date_format(mail.h_date, '%b %e, %Y %h:%i') as date,  mail.folder_id,  sender.address_id as address_id,  sender.phrase as phrase, sender.address as address,    mail.h_subject as subject from    mail left join mxa as mxa_sender on mail.mail_id=mxa_sender.mail_id and mxa_sender.type='from' left join address as sender on mxa_sender.address_id=sender.address_id  mxa as mxa_recipient,   address as recipient, where 1     and mail.mail_id=mxa_recipient.mail_id   and mxa_recipient.address_id=recipient.address_id   and mxa_recipient.type='to'  and  match(sender.phrase, sender.address, sender.comment) against ('jeremy' in boolean mode)   and  match(recipient.phrase, recipient.address, recipient.comment) against ('monty' in boolean mode) order by mail.h_date desc limit 0, 25 ) as query where query.folder_id=folder.folder_id;
 
114
 
 
115
#
 
116
# UPDATE/DELETE/INSERT of derived tables
 
117
#
 
118
create table t1 (a int);
 
119
insert into t1 values (1),(2),(3);
 
120
-- error 1288
 
121
update (select * from t1) as t1 set a = 5;
 
122
-- error 1064
 
123
delete from (select * from t1);
 
124
-- error 1064
 
125
insert into  (select * from t1) values (5);
 
126
drop table t1;
 
127
 
 
128
#
 
129
# deived tables with subquery inside all by one table
 
130
#
 
131
create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1)
 
132
);
 
133
insert into t1 VALUES(1,1,1), (2,2,1);
 
134
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;
 
135
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;
 
136
drop table t1;
 
137
 
 
138
create table t1 (a int);
 
139
insert into t1 values (1),(2);
 
140
select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
 
141
explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b;
 
142
drop table t1;
 
143
 
 
144
#
 
145
# multi-update & multi-delete with derived tables
 
146
#
 
147
CREATE TABLE `t1` (
 
148
  `N` int(11) unsigned NOT NULL default '0',
 
149
  `M` tinyint(1) default '0'
 
150
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
151
INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0);
 
152
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;
 
153
select * from t1;
 
154
-- error 1288
 
155
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;
 
156
-- error 1054
 
157
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;
 
158
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;
 
159
select * from t1;
 
160
--replace_result P2 p2
 
161
--error ER_UNKNOWN_TABLE 
 
162
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;
 
163
-- error 1054
 
164
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;
 
165
drop table t1;
 
166
 
 
167
#
 
168
# correct lex->current_select
 
169
#
 
170
CREATE TABLE t1 (
 
171
  OBJECTID int(11) NOT NULL default '0',
 
172
  SORTORDER int(11) NOT NULL auto_increment,
 
173
  KEY t1_SortIndex (SORTORDER),
 
174
  KEY t1_IdIndex (OBJECTID)
 
175
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
176
CREATE TABLE t2 (
 
177
  ID int(11) default NULL,
 
178
  PARID int(11) default NULL,
 
179
  UNIQUE KEY t2_ID_IDX (ID),
 
180
  KEY t2_PARID_IDX (PARID)
 
181
) engine=MyISAM DEFAULT CHARSET=latin1;
 
182
INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
 
183
CREATE TABLE t3 (
 
184
  ID int(11) default NULL,
 
185
  DATA decimal(10,2) default NULL,
 
186
  UNIQUE KEY t3_ID_IDX (ID)
 
187
) engine=MyISAM DEFAULT CHARSET=latin1;
 
188
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);
 
189
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;
 
190
drop table t1, t2, t3;
 
191
 
 
192
#
 
193
# explain derived
 
194
#
 
195
CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL);
 
196
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);
 
197
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;
 
198
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;
 
199
drop table t1;
 
200
 
 
201
#
 
202
# "Using index" in explain
 
203
#
 
204
create table t2 (a int, b int, primary key (a));
 
205
insert into t2 values (1,7),(2,7);
 
206
explain select a from t2 where a>1;
 
207
explain select a from (select a from t2 where a>1) tt;
 
208
drop table t2;
 
209
 
 
210
#
 
211
# select list counter
 
212
#
 
213
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_`));
 
214
insert into t1 values (128, 'rozn', 2, curdate(), 10),
 
215
  (128, 'rozn', 1, curdate(), 10);
 
216
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;
 
217
DROP TABLE t1;
 
218
 
 
219
#
 
220
# DISTINCT over grouped select on subquery in the FROM clause
 
221
#
 
222
create table t1 (a integer, b integer);
 
223
insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
 
224
select distinct sum(b) from t1 group by a;
 
225
select distinct sum(b) from (select a,b from t1) y group by a;
 
226
drop table t1;
 
227
 
 
228
 
 
229
#
 
230
# Test for bug #7413 "Subquery with non-scalar results participating in
 
231
# select list of derived table crashes server" aka "VIEW with sub query can
 
232
# cause the MySQL server to crash". If we have encountered problem during
 
233
# filling of derived table we should report error and perform cleanup
 
234
# properly.
 
235
#
 
236
CREATE TABLE t1 (a char(10), b char(10));
 
237
INSERT INTO t1 VALUES ('root','localhost'), ('root','%');
 
238
--error 1242
 
239
SELECT * FROM (SELECT (SELECT a.a FROM t1 AS a WHERE a.a = b.a) FROM t1 AS b) AS c;
 
240
DROP TABLE t1;
 
241
#
 
242
# test of union subquery in the FROM clause with complex distinct/all (BUG#6565)
 
243
#
 
244
create table t1(a int);
 
245
create table t2(a int);
 
246
create table t3(a int);
 
247
insert into t1 values(1),(1);
 
248
insert into t2 values(2),(2);
 
249
insert into t3 values(3),(3);
 
250
select * from t1 union distinct select * from t2 union all select * from t3;
 
251
select * from (select * from t1 union distinct select * from t2 union all select * from t3) X;
 
252
drop table t1, t2, t3;
 
253
 
 
254
#
 
255
# Bug #11864 non unique names are allowed in subquery
 
256
#
 
257
create table t1 (a int);
 
258
create table t2 (a int);
 
259
--error 1060
 
260
select * from (select * from t1,t2) foo;
 
261
drop table t1,t2;
 
262
 
 
263
#
 
264
# Bug#10586 - query works with 4.1.8, but not with 4.1.11
 
265
#
 
266
create table t1 (ID int unsigned not null auto_increment,
 
267
                 DATA varchar(5) not null, primary key (ID));
 
268
create table t2 (ID int unsigned not null auto_increment,
 
269
                 DATA varchar(5) not null, FID int unsigned not null,
 
270
                 primary key (ID));
 
271
select A.* from (t1 inner join (select * from t2) as A on t1.ID = A.FID);
 
272
select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID);
 
273
select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID;
 
274
drop table t1, t2;
 
275
 
 
276
connection con1;
 
277
disconnect con1;
 
278
--source include/wait_until_disconnected.inc
 
279
connection default;
 
280
drop user mysqltest_1;
 
281
 
 
282
--echo # End of 4.1 tests
 
283
 
 
284
#
 
285
# Bug #41156: List of derived tables acts like a chain of
 
286
#             mutually-nested subqueries
 
287
#
 
288
 
 
289
SELECT 0 FROM
 
290
(SELECT 0) t01, (SELECT 0) t02, (SELECT 0) t03, (SELECT 0) t04, (SELECT 0) t05,
 
291
(SELECT 0) t06, (SELECT 0) t07, (SELECT 0) t08, (SELECT 0) t09, (SELECT 0) t10,
 
292
(SELECT 0) t11, (SELECT 0) t12, (SELECT 0) t13, (SELECT 0) t14, (SELECT 0) t15,
 
293
(SELECT 0) t16, (SELECT 0) t17, (SELECT 0) t18, (SELECT 0) t19, (SELECT 0) t20,
 
294
(SELECT 0) t21, (SELECT 0) t22, (SELECT 0) t23, (SELECT 0) t24, (SELECT 0) t25,
 
295
(SELECT 0) t26, (SELECT 0) t27, (SELECT 0) t28, (SELECT 0) t29, (SELECT 0) t30,
 
296
(SELECT 0) t31, (SELECT 0) t32, (SELECT 0) t33, (SELECT 0) t34, (SELECT 0) t35,
 
297
(SELECT 0) t36, (SELECT 0) t37, (SELECT 0) t38, (SELECT 0) t39, (SELECT 0) t40,
 
298
(SELECT 0) t41, (SELECT 0) t42, (SELECT 0) t43, (SELECT 0) t44, (SELECT 0) t45,
 
299
(SELECT 0) t46, (SELECT 0) t47, (SELECT 0) t48, (SELECT 0) t49, (SELECT 0) t50,
 
300
(SELECT 0) t51, (SELECT 0) t52, (SELECT 0) t53, (SELECT 0) t54, (SELECT 0) t55,
 
301
(SELECT 0) t56, (SELECT 0) t57, (SELECT 0) t58, (SELECT 0) t59, (SELECT 0) t60,
 
302
(SELECT 0) t61; # 61 == MAX_TABLES
 
303
 
 
304
--echo # End of 5.0 tests