summaryrefslogtreecommitdiff
path: root/mysql-test/r/metadata.result
blob: fcaeb3359f0fcb861f73f24f729764d0ba86efb1 (plain)
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
drop table if exists t1,t2;
select 1, 1.0, -1, "hello", NULL;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					1	8	1	1	N	32897	0	63
def					1.0	246	4	3	N	32897	1	63
def					-1	8	2	2	N	32897	0	63
def					hello	253	5	5	N	1	31	8
def					NULL	6	0	0	Y	32896	0	63
1	1.0	-1	hello	NULL
1	1.0	-1	hello	NULL
create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10));
select * from t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	a	a	1	4	0	Y	32768	0	63
def	test	t1	t1	b	b	2	6	0	Y	32768	0	63
def	test	t1	t1	c	c	9	9	0	Y	32768	0	63
def	test	t1	t1	d	d	3	11	0	Y	32768	0	63
def	test	t1	t1	e	e	8	20	0	Y	32768	0	63
def	test	t1	t1	f	f	4	3	0	Y	32768	2	63
def	test	t1	t1	g	g	5	4	0	Y	32768	3	63
def	test	t1	t1	h	h	246	7	0	Y	32768	4	63
def	test	t1	t1	i	i	13	4	0	Y	32864	0	63
def	test	t1	t1	j	j	10	10	0	Y	128	0	63
def	test	t1	t1	k	k	7	19	0	N	9377	0	63
def	test	t1	t1	l	l	12	19	0	Y	128	0	63
def	test	t1	t1	m	m	254	1	0	Y	256	0	8
def	test	t1	t1	n	n	254	3	0	Y	2048	0	8
def	test	t1	t1	o	o	254	10	0	Y	0	0	8
a	b	c	d	e	f	g	h	i	j	k	l	m	n	o
select a b, b c from t1 as t2;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t2	a	b	1	4	0	Y	32768	0	63
def	test	t1	t2	b	c	2	6	0	Y	32768	0	63
b	c
drop table t1;
CREATE TABLE t1 (id tinyint(3) default NULL, data varchar(255) default NULL);
INSERT INTO t1 VALUES (1,'male'),(2,'female');
CREATE TABLE t2 (id tinyint(3) unsigned default NULL, data char(3) default '0');
INSERT INTO t2 VALUES (1,'yes'),(2,'no');
select t1.id, t1.data, t2.data from t1, t2 where t1.id = t2.id;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	id	id	1	3	1	Y	32768	0	63
def	test	t1	t1	data	data	253	255	6	Y	0	0	8
def	test	t2	t2	data	data	254	3	3	Y	0	0	8
id	data	data
1	male	yes
2	female	no
select t1.id, t1.data, t2.data from t1, t2 where t1.id = t2.id order by t1.id;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	id	id	1	3	1	Y	32768	0	63
def	test	t1	t1	data	data	253	255	6	Y	0	0	8
def	test	t2	t2	data	data	254	3	3	Y	0	0	8
id	data	data
1	male	yes
2	female	no
select t1.id from t1 union select t2.id from t2;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def				id	id	1	4	1	Y	32768	0	63
id
1
2
drop table t1,t2;
create table t1 ( a int, b varchar(30), primary key(a));
insert into t1 values (1,'one');
insert into t1 values (2,'two');
set @arg00=1 ;
select @arg00 FROM t1 where a=1 union distinct select 1 FROM t1 where a=1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def				@arg00	@arg00	8	20	1	Y	32768	0	63
@arg00
1
select * from (select @arg00) aaa;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def			aaa	@arg00	@arg00	8	20	1	Y	32768	0	63
@arg00
1
select 1 union select 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def				1	1	8	20	1	N	32769	0	63
1
1
select * from (select 1 union select 1) aaa;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def			aaa	1	1	8	20	1	N	32769	0	63
1
1
drop table t1;
create table t1 (i int);
insert into t1 values (1),(2),(3);
select * from t1 where i = 2;
drop table t1;//
affected rows: 0
affected rows: 3
info: Records: 3  Duplicates: 0  Warnings: 0
i
2
affected rows: 1
affected rows: 0
create table t1 (id int(10));
insert into t1 values (1);
CREATE  VIEW v1 AS select t1.id as id from t1;
CREATE  VIEW v2 AS select t1.id as renamed from t1;
CREATE  VIEW v3 AS select t1.id + 12 as renamed from t1;
select * from v1 group by id limit 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	v1	id	id	3	10	1	Y	32768	0	63
id
1
select * from v1 group by id limit 0;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	v1	v1	id	id	3	10	0	Y	32768	0	63
id
select * from v1 where id=1000 group by id;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	v1	v1	id	id	3	10	0	Y	32768	0	63
id
select * from v1 where id=1 group by id;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	v1	id	id	3	10	1	Y	32768	0	63
id
1
select * from v2 where renamed=1 group by renamed;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	v2	id	renamed	3	10	1	Y	32768	0	63
renamed
1
select * from v3 where renamed=1 group by renamed;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def		v3	v3	renamed	renamed	8	12	0	Y	32896	0	63
renamed
drop table t1;
drop view v1,v2,v3;
select a.* from (select 2147483648 as v_large) a;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def			a	v_large	v_large	8	10	10	N	32769	0	63
v_large
2147483648
select a.* from (select 214748364 as v_small) a;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def			a	v_small	v_small	3	9	9	N	32769	0	63
v_small
214748364
CREATE TABLE t1 (c1 CHAR(1));
CREATE TABLE t2 (c2 CHAR(1));
CREATE VIEW v1 AS SELECT t1.c1 FROM t1;
CREATE VIEW v2 AS SELECT t2.c2 FROM t2;
INSERT INTO t1 VALUES ('1'), ('2'), ('3');
INSERT INTO t2 VALUES ('1'), ('2'), ('3'), ('2');
SELECT v1.c1 FROM v1 JOIN t2 ON c1=c2 ORDER BY 1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	v1	c1	c1	254	1	1	Y	0	0	8
c1
1
2
2
3
SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	v1	v1	c1	c1	254	1	1	Y	0	0	8
def	test	v2	v2	c2	c2	254	1	1	Y	0	0	8
c1	c2
1	1
2	2
3	3
2	2
SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2 GROUP BY v1.c1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	v1	c1	c1	254	1	1	Y	32768	0	8
def	test	t2	v2	c2	c2	254	1	1	Y	0	0	8
c1	c2
1	1
2	2
3	3
SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2 GROUP BY v1.c1 ORDER BY v2.c2;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	v1	c1	c1	254	1	1	Y	32768	0	8
def	test	t2	v2	c2	c2	254	1	1	Y	0	0	8
c1	c2
1	1
2	2
3	3
DROP VIEW v1,v2;
DROP TABLE t1,t2;
CREATE TABLE t1 (i INT, d DATE);
INSERT INTO t1 VALUES (1, '2008-01-01'), (2, '2008-01-02'), (3, '2008-01-03');
SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d),
CASE i WHEN i THEN d ELSE d END, GREATEST(d, d), LEAST(d, d)
FROM t1 ORDER BY RAND();
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
2008-01-01	2008-01-01	2008-01-01	2008-01-01	2008-01-01	2008-01-01
2008-01-02	2008-01-02	2008-01-02	2008-01-02	2008-01-02	2008-01-02
2008-01-03	2008-01-03	2008-01-03	2008-01-03	2008-01-03	2008-01-03
COALESCE(d, d)	IFNULL(d, d)	IF(i, d, d)	CASE i WHEN i THEN d ELSE d END	GREATEST(d, d)	LEAST(d, d)
def				CASE i WHEN i THEN d ELSE d END	CASE i WHEN i THEN d ELSE d END	10	10	10	Y	128	0	63
def				COALESCE(d, d)	COALESCE(d, d)	10	10	10	Y	128	0	63
def				GREATEST(d, d)	GREATEST(d, d)	10	10	10	Y	128	0	63
def				IF(i, d, d)	IF(i, d, d)	10	10	10	Y	128	0	63
def				IFNULL(d, d)	IFNULL(d, d)	10	10	10	Y	128	0	63
def				LEAST(d, d)	LEAST(d, d)	10	10	10	Y	128	0	63
DROP TABLE t1;
#
# Bug#41788 mysql_fetch_field returns org_table == table by a view
#
CREATE TABLE t1 (f1 INT);
CREATE VIEW v1 AS SELECT f1 FROM t1;
SELECT f1 FROM v1 va;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	va	va	f1	f1	3	11	0	Y	32768	0	63
f1
DROP VIEW v1;
DROP TABLE t1;
End of 5.0 tests
create table t1(
# numeric types
bool_col bool,
boolean_col boolean,
bit_col bit(5),
tiny tinyint,
tiny_uns tinyint unsigned,
small smallint,
small_uns smallint unsigned,
medium mediumint,
medium_uns mediumint unsigned,
int_col int,
int_col_uns int unsigned,
big bigint,
big_uns bigint unsigned,
decimal_col decimal(10,5),
# synonyms of DECIMAL
numeric_col numeric(10),
fixed_col fixed(10),
dec_col dec(10),
decimal_col_uns decimal(10,5) unsigned,
fcol float,
fcol_uns float unsigned,
dcol double,
double_precision_col double precision,
dcol_uns double unsigned,
# date/time types
date_col date,
time_col time,
timestamp_col timestamp,
year_col year,
datetime_col datetime,
# string types
char_col char(5),
varchar_col varchar(10),
binary_col binary(10),
varbinary_col varbinary(10),
tinyblob_col tinyblob,
blob_col blob,
mediumblob_col mediumblob,
longblob_col longblob,
text_col text,
mediumtext_col mediumtext,
longtext_col longtext,
enum_col enum("A","B","C"),
set_col set("F","E","D")
);
select * from t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	bool_col	bool_col	1	1	0	Y	32768	0	63
def	test	t1	t1	boolean_col	boolean_col	1	1	0	Y	32768	0	63
def	test	t1	t1	bit_col	bit_col	16	5	0	Y	32	0	63
def	test	t1	t1	tiny	tiny	1	4	0	Y	32768	0	63
def	test	t1	t1	tiny_uns	tiny_uns	1	3	0	Y	32800	0	63
def	test	t1	t1	small	small	2	6	0	Y	32768	0	63
def	test	t1	t1	small_uns	small_uns	2	5	0	Y	32800	0	63
def	test	t1	t1	medium	medium	9	9	0	Y	32768	0	63
def	test	t1	t1	medium_uns	medium_uns	9	8	0	Y	32800	0	63
def	test	t1	t1	int_col	int_col	3	11	0	Y	32768	0	63
def	test	t1	t1	int_col_uns	int_col_uns	3	10	0	Y	32800	0	63
def	test	t1	t1	big	big	8	20	0	Y	32768	0	63
def	test	t1	t1	big_uns	big_uns	8	20	0	Y	32800	0	63
def	test	t1	t1	decimal_col	decimal_col	246	12	0	Y	32768	5	63
def	test	t1	t1	numeric_col	numeric_col	246	11	0	Y	32768	0	63
def	test	t1	t1	fixed_col	fixed_col	246	11	0	Y	32768	0	63
def	test	t1	t1	dec_col	dec_col	246	11	0	Y	32768	0	63
def	test	t1	t1	decimal_col_uns	decimal_col_uns	246	11	0	Y	32800	5	63
def	test	t1	t1	fcol	fcol	4	12	0	Y	32768	31	63
def	test	t1	t1	fcol_uns	fcol_uns	4	12	0	Y	32800	31	63
def	test	t1	t1	dcol	dcol	5	22	0	Y	32768	31	63
def	test	t1	t1	double_precision_col	double_precision_col	5	22	0	Y	32768	31	63
def	test	t1	t1	dcol_uns	dcol_uns	5	22	0	Y	32800	31	63
def	test	t1	t1	date_col	date_col	10	10	0	Y	128	0	63
def	test	t1	t1	time_col	time_col	11	10	0	Y	128	0	63
def	test	t1	t1	timestamp_col	timestamp_col	7	19	0	N	9377	0	63
def	test	t1	t1	year_col	year_col	13	4	0	Y	32864	0	63
def	test	t1	t1	datetime_col	datetime_col	12	19	0	Y	128	0	63
def	test	t1	t1	char_col	char_col	254	5	0	Y	0	0	8
def	test	t1	t1	varchar_col	varchar_col	253	10	0	Y	0	0	8
def	test	t1	t1	binary_col	binary_col	254	10	0	Y	128	0	63
def	test	t1	t1	varbinary_col	varbinary_col	253	10	0	Y	128	0	63
def	test	t1	t1	tinyblob_col	tinyblob_col	252	255	0	Y	144	0	63
def	test	t1	t1	blob_col	blob_col	252	65535	0	Y	144	0	63
def	test	t1	t1	mediumblob_col	mediumblob_col	252	16777215	0	Y	144	0	63
def	test	t1	t1	longblob_col	longblob_col	252	4294967295	0	Y	144	0	63
def	test	t1	t1	text_col	text_col	252	65535	0	Y	16	0	8
def	test	t1	t1	mediumtext_col	mediumtext_col	252	16777215	0	Y	16	0	8
def	test	t1	t1	longtext_col	longtext_col	252	4294967295	0	Y	16	0	8
def	test	t1	t1	enum_col	enum_col	254	1	0	Y	256	0	8
def	test	t1	t1	set_col	set_col	254	5	0	Y	2048	0	8
bool_col	boolean_col	bit_col	tiny	tiny_uns	small	small_uns	medium	medium_uns	int_col	int_col_uns	big	big_uns	decimal_col	numeric_col	fixed_col	dec_col	decimal_col_uns	fcol	fcol_uns	dcol	double_precision_col	dcol_uns	date_col	time_col	timestamp_col	year_col	datetime_col	char_col	varchar_col	binary_col	varbinary_col	tinyblob_col	blob_col	mediumblob_col	longblob_col	text_col	mediumtext_col	longtext_col	enum_col	set_col
drop table t1;
select cast('01:01:01' as time), cast('01:01:01' as time(2));
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					cast('01:01:01' as time)	11	10	8	Y	128	0	63
def					cast('01:01:01' as time(2))	11	13	11	Y	128	2	63
cast('01:01:01' as time)	cast('01:01:01' as time(2))
01:01:01	01:01:01.00