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
|
#
# Test of unions
#
--disable_warnings
drop table if exists t1,t2,t3;
--enable_warnings
CREATE TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
CREATE TABLE t2 (a int not null, b char (10) not null);
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');
select a,b from t1 union select a,b from t2;
select a,b from t1 union all select a,b from t2;
select a,b from t1 union all select a,b from t2 order by b;
select a,b from t1 union all select a,b from t2 union select 7,'g';
select 0,'#' union select a,b from t1 union all select a,b from t2 union select 7,'gg';
select a,b from t1 union select a,b from t1;
select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b;
#test alternate syntax for unions
(select a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 4;
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1);
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc;
--error 1248
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b;
explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc;
#(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2;
select found_rows();
#
# Test some error conditions with UNION
#
explain select a,b from t1 union all select a,b from t2;
--error 1054
explain select xx from t1 union select 1;
explain select a,b from t1 union select 1;
explain select 1 union select a,b from t1 union select 1;
explain select a,b from t1 union select 1 limit 0;
--error 1221
select a,b from t1 into outfile 'skr' union select a,b from t2;
--error 1221
select a,b from t1 order by a union select a,b from t2;
--error 1221
insert into t3 select a from t1 order by a union select a from t2;
--error 1222
create table t3 select a,b from t1 union select a from t2;
--error 1222
select a,b from t1 union select a from t2;
--error 1222
select * from t1 union select a from t2;
--error 1222
select a from t1 union select * from t2;
--error 1234
select * from t1 union select SQL_BUFFER_RESULT * from t2;
# Test CREATE, INSERT and REPLACE
create table t3 select a,b from t1 union all select a,b from t2;
insert into t3 select a,b from t1 union all select a,b from t2;
replace into t3 select a,b as c from t1 union all select a,b from t2;
drop table t1,t2,t3;
#
# Test bug reported by joc@presence-pc.com
#
CREATE TABLE t1 (
`pseudo` char(35) NOT NULL default '',
`pseudo1` char(35) NOT NULL default '',
`same` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`pseudo1`),
KEY `pseudo` (`pseudo`)
) TYPE=MyISAM;
INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1);
SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce';
SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce';
SELECT * FROM t1 WHERE pseudo1='joce' UNION SELECT * FROM t1 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc;
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT pseudo FROM t1 WHERE pseudo1='joce';
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t1 WHERE pseudo1='joce';
SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT 1;
drop table t1;
create table t1 (a int);
create table t2 (a int);
insert into t1 values (1),(2),(3),(4),(5);
insert into t2 values (11),(12),(13),(14),(15);
(select * from t1 limit 2) union (select * from t2 limit 3) limit 4;
(select * from t1 limit 2) union (select * from t2 limit 3);
(select * from t1 limit 2) union (select * from t2 limit 20,3);
set SQL_SELECT_LIMIT=2;
(select * from t1 limit 1) union (select * from t2 limit 3);
set SQL_SELECT_LIMIT=DEFAULT;
drop table t1,t2;
--error 1096
select * union select 1;
select 1 as a,(select a union select a);
--disable_warnings
drop table if exists t1,t2;
--enable_warnings
CREATE TABLE t1 ( id int(3) unsigned default '0') TYPE=MyISAM;
INSERT INTO t1 (id) VALUES("1");
CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) TYPE=MyISAM;
INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1",
"foo1", "bar1");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",
"foo2", "bar2");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("3", "1", NULL,
"bar3");
INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1",
"foo4", "bar4");
SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;
SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;
drop table if exists t1,t2;
(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
#
# Test of bug when using the same table multiple times
#
create table t1 (a int not null primary key auto_increment, b int, key(b));
create table t2 (a int not null primary key auto_increment, b int);
insert into t1 (b) values (1),(2),(2),(3);
insert into t2 (b) values (10),(11),(12),(13);
explain (select * from t1 where a=1) union (select * from t2 where a=1);
(select * from t1 where a=5) union (select * from t2 where a=1);
(select * from t1 where a=5 and a=6) union (select * from t2 where a=1);
(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1);
(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);
explain (select * from t1 where a=1 and b=10) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);
explain (select * from t1 where a=1) union (select * from t1 where b=1);
drop table t1,t2;
--error 1054
(select 1) union (select 2) order by 0;
SELECT @a:=1 UNION SELECT @a:=@a+1;
--error 1054
(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a);
|
