← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/derived.test

  • Committer: Brian Aker
  • Date: 2008-07-28 18:01:38 UTC
  • Revision ID: brian@tangent.org-20080728180138-q2pxlq0qiapvqsdn
Remove YEAR field type

Show diffs side-by-side

added added

removed removed

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