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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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