← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/derived.test

  • Committer: Monty Taylor
  • Date: 2008-08-16 21:06:22 UTC
  • Revision ID: monty@inaugust.com-20080816210622-zpnn13unyinqzn72
Updated po files.

Show diffs side-by-side

added added

removed removed

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