1drop table if exists t1,t2,t3;
2SET SQL_WARNINGS=1;
3CREATE TABLE t1 (
4auto int(5) unsigned NOT NULL auto_increment,
5string char(10) default "hello",
6tiny tinyint(4) DEFAULT '0' NOT NULL ,
7short smallint(6) DEFAULT '1' NOT NULL ,
8medium mediumint(8) DEFAULT '0' NOT NULL,
9long_int int(11) DEFAULT '0' NOT NULL,
10longlong bigint(13) DEFAULT '0' NOT NULL,
11real_float float(13,1) DEFAULT 0.0 NOT NULL,
12real_double double(16,4),
13utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
14ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
15umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
16ulong int(11) unsigned DEFAULT '0' NOT NULL,
17ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
18time_stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
19date_field date,
20time_field time,
21date_time datetime,
22blob_col blob,
23tinyblob_col tinyblob,
24mediumblob_col mediumblob  not null default '',
25longblob_col longblob  not null default '',
26options enum('one','two','tree') not null ,
27flags set('one','two','tree') not null default '',
28PRIMARY KEY (auto),
29KEY (utiny),
30KEY (tiny),
31KEY (short),
32KEY any_name (medium),
33KEY (longlong),
34KEY (real_float),
35KEY (ushort),
36KEY (umedium),
37KEY (ulong),
38KEY (ulonglong,ulong),
39KEY (options,flags)
40);
41show full fields from t1;
42Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
43auto	int(5) unsigned	NULL	NO	PRI	NULL	auto_increment	#
44string	char(10)	latin1_swedish_ci	YES		hello		#
45tiny	tinyint(4)	NULL	NO	MUL	0		#
46short	smallint(6)	NULL	NO	MUL	1		#
47medium	mediumint(8)	NULL	NO	MUL	0		#
48long_int	int(11)	NULL	NO		0		#
49longlong	bigint(13)	NULL	NO	MUL	0		#
50real_float	float(13,1)	NULL	NO	MUL	0.0		#
51real_double	double(16,4)	NULL	YES		NULL		#
52utiny	tinyint(3) unsigned	NULL	NO	MUL	0		#
53ushort	smallint(5) unsigned zerofill	NULL	NO	MUL	00000		#
54umedium	mediumint(8) unsigned	NULL	NO	MUL	0		#
55ulong	int(11) unsigned	NULL	NO	MUL	0		#
56ulonglong	bigint(13) unsigned	NULL	NO	MUL	0		#
57time_stamp	timestamp	NULL	NO		current_timestamp()	on update current_timestamp()	#
58date_field	date	NULL	YES		NULL		#
59time_field	time	NULL	YES		NULL		#
60date_time	datetime	NULL	YES		NULL		#
61blob_col	blob	NULL	YES		NULL		#
62tinyblob_col	tinyblob	NULL	YES		NULL		#
63mediumblob_col	mediumblob	NULL	NO		''		#
64longblob_col	longblob	NULL	NO		''		#
65options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	NULL		#
66flags	set('one','two','tree')	latin1_swedish_ci	NO				#
67show keys from t1;
68Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
69t1	0	PRIMARY	1	auto	A	0	NULL	NULL		BTREE
70t1	1	utiny	1	utiny	A	NULL	NULL	NULL		BTREE
71t1	1	tiny	1	tiny	A	NULL	NULL	NULL		BTREE
72t1	1	short	1	short	A	NULL	NULL	NULL		BTREE
73t1	1	any_name	1	medium	A	NULL	NULL	NULL		BTREE
74t1	1	longlong	1	longlong	A	NULL	NULL	NULL		BTREE
75t1	1	real_float	1	real_float	A	NULL	NULL	NULL		BTREE
76t1	1	ushort	1	ushort	A	NULL	NULL	NULL		BTREE
77t1	1	umedium	1	umedium	A	NULL	NULL	NULL		BTREE
78t1	1	ulong	1	ulong	A	NULL	NULL	NULL		BTREE
79t1	1	ulonglong	1	ulonglong	A	NULL	NULL	NULL		BTREE
80t1	1	ulonglong	2	ulong	A	NULL	NULL	NULL		BTREE
81t1	1	options	1	options	A	NULL	NULL	NULL		BTREE
82t1	1	options	2	flags	A	NULL	NULL	NULL		BTREE
83CREATE UNIQUE INDEX test on t1 ( auto ) ;
84CREATE INDEX test2 on t1 ( ulonglong,ulong) ;
85Warnings:
86Note	1831	Duplicate index `test2`. This is deprecated and will be disallowed in a future release
87CREATE INDEX test3 on t1 ( medium ) ;
88Warnings:
89Note	1831	Duplicate index `test3`. This is deprecated and will be disallowed in a future release
90DROP INDEX test ON t1;
91insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one');
92insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one');
93insert ignore into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3);
94Warnings:
95Warning	1265	Data truncated for column 'string' at row 1
96insert ignore into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1);
97Warnings:
98Warning	1264	Out of range value for column 'utiny' at row 1
99Warning	1264	Out of range value for column 'ushort' at row 1
100Warning	1264	Out of range value for column 'umedium' at row 1
101Warning	1264	Out of range value for column 'ulong' at row 1
102Warning	1264	Out of range value for column 'ulonglong' at row 1
103Warning	1265	Data truncated for column 'options' at row 1
104Warning	1265	Data truncated for column 'flags' at row 1
105insert ignore into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"one,two,tree");
106Warnings:
107Warning	1265	Data truncated for column 'string' at row 1
108Warning	1264	Out of range value for column 'tiny' at row 1
109Warning	1264	Out of range value for column 'short' at row 1
110Warning	1264	Out of range value for column 'medium' at row 1
111Warning	1264	Out of range value for column 'long_int' at row 1
112Warning	1264	Out of range value for column 'utiny' at row 1
113Warning	1264	Out of range value for column 'ushort' at row 1
114Warning	1264	Out of range value for column 'umedium' at row 1
115Warning	1264	Out of range value for column 'ulong' at row 1
116Warning	1264	Out of range value for column 'ulonglong' at row 1
117Warning	1265	Data truncated for column 'options' at row 1
118insert ignore into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);
119Warnings:
120Warning	1264	Out of range value for column 'tiny' at row 1
121Warning	1264	Out of range value for column 'short' at row 1
122Warning	1264	Out of range value for column 'medium' at row 1
123Warning	1264	Out of range value for column 'long_int' at row 1
124Warning	1264	Out of range value for column 'utiny' at row 1
125Warning	1264	Out of range value for column 'ushort' at row 1
126Warning	1264	Out of range value for column 'umedium' at row 1
127Warning	1265	Data truncated for column 'options' at row 1
128insert into t1 (tiny) values (1);
129select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,time_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1;
130auto	string	tiny	short	medium	long_int	longlong	real_float	real_double	utiny	ushort	umedium	ulong	ulonglong	mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000)	date_field	time_field	date_time	blob_col	tinyblob_col	mediumblob_col	longblob_col
13110	1	1	1	1	1	1	1.0	1.0000	1	00001	1	1	1	0	0000-00-00	00:00:00	0000-00-00 00:00:00	1	1	1	1
13211	2	2	2	2	2	2	2.0	2.0000	2	00002	2	2	2	0	NULL	NULL	NULL	NULL	NULL	2	2
13312	0.33333333	3	3	3	3	3	3.0	3.0000	3	00003	3	3	3	0	1997-03-03	10:10:10	1997-03-03 10:10:10				3
13413	-1	-1	-1	-1	-1	-1	-1.0	-1.0000	0	00000	0	0	0	0	1997-08-07	08:07:06	1997-04-03 09:08:07	-1	-1	-1	-1
13514	-429496729	-128	-32768	-8388608	-2147483648	-4294967295	-4294967296.0	-4294967295.0000	0	00000	0	0	0	0	0000-00-00	00:00:00	0000-00-00 00:00:00	-4294967295	-4294967295	-4294967295	-4294967295
13615	4294967295	127	32767	8388607	2147483647	4294967295	4294967296.0	4294967295.0000	255	65535	16777215	4294967295	4294967295	0	0000-00-00	00:00:00	0000-00-00 00:00:00	4294967295	4294967295	4294967295	4294967295
13716	hello	1	1	0	0	0	0.0	NULL	0	00000	0	0	0	0	NULL	NULL	NULL	NULL	NULL
138ALTER TABLE t1
139add new_field char(10) default "new" not null,
140change blob_col new_blob_col varchar(20),
141change date_field date_field char(10),
142alter column string set default "newdefault",
143alter short drop default,
144DROP INDEX utiny,
145DROP INDEX ushort,
146DROP PRIMARY KEY,
147DROP FOREIGN KEY any_name,
148ADD INDEX (auto);
149LOCK TABLES t1 WRITE;
150ALTER TABLE t1
151RENAME as t2,
152DROP longblob_col;
153UNLOCK TABLES;
154ALTER TABLE t2 rename as t3;
155LOCK TABLES t3 WRITE ;
156ALTER TABLE t3 rename as t1;
157UNLOCK TABLES;
158select auto,new_field,new_blob_col,date_field from t1 ;
159auto	new_field	new_blob_col	date_field
16010	new	1	0000-00-00
16111	new	NULL	NULL
16212	new		1997-03-03
16313	new	-1	1997-08-07
16414	new	-4294967295	0000-00-00
16515	new	4294967295	0000-00-00
16616	new	NULL	NULL
167CREATE TABLE t2 (
168auto int(5) unsigned NOT NULL auto_increment,
169string char(20),
170mediumblob_col mediumblob not null,
171new_field char(2),
172PRIMARY KEY (auto)
173);
174INSERT IGNORE INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10;
175Warnings:
176Warning	1265	Data truncated for column 'new_field' at row 2
177Warning	1265	Data truncated for column 'new_field' at row 3
178Warning	1265	Data truncated for column 'new_field' at row 4
179Warning	1265	Data truncated for column 'new_field' at row 5
180Warning	1265	Data truncated for column 'new_field' at row 6
181Warning	1265	Data truncated for column 'new_field' at row 7
182select * from t2;
183auto	string	mediumblob_col	new_field
1841	2	2	ne
1852	0.33333333		ne
1863	-1	-1	ne
1874	-429496729	-4294967295	ne
1885	4294967295	4294967295	ne
1896	hello		ne
190select distinct flags from t1;
191flags
192
193one,two,tree
194one
195one,two
196select flags from t1 where find_in_set("two",flags)>0;
197flags
198one,two,tree
199one,two,tree
200one,two
201one,two
202select flags from t1 where find_in_set("unknown",flags)>0;
203flags
204select options,flags from t1 where options="ONE" and flags="ONE";
205options	flags
206one	one
207select options,flags from t1 where options="one" and flags="one";
208options	flags
209one	one
210drop table t2;
211create table t2 select * from t1;
212update t2 set string="changed" where auto=16;
213show full columns from t1;
214Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
215auto	int(5) unsigned	NULL	NO	MUL	NULL	auto_increment	#
216string	char(10)	latin1_swedish_ci	YES		newdefault		#
217tiny	tinyint(4)	NULL	NO	MUL	0		#
218short	smallint(6)	NULL	NO	MUL	NULL		#
219medium	mediumint(8)	NULL	NO	MUL	0		#
220long_int	int(11)	NULL	NO		0		#
221longlong	bigint(13)	NULL	NO	MUL	0		#
222real_float	float(13,1)	NULL	NO	MUL	0.0		#
223real_double	double(16,4)	NULL	YES		NULL		#
224utiny	tinyint(3) unsigned	NULL	NO		0		#
225ushort	smallint(5) unsigned zerofill	NULL	NO		00000		#
226umedium	mediumint(8) unsigned	NULL	NO	MUL	0		#
227ulong	int(11) unsigned	NULL	NO	MUL	0		#
228ulonglong	bigint(13) unsigned	NULL	NO	MUL	0		#
229time_stamp	timestamp	NULL	NO		current_timestamp()	on update current_timestamp()	#
230date_field	char(10)	latin1_swedish_ci	YES		NULL		#
231time_field	time	NULL	YES		NULL		#
232date_time	datetime	NULL	YES		NULL		#
233new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#
234tinyblob_col	tinyblob	NULL	YES		NULL		#
235mediumblob_col	mediumblob	NULL	NO		''		#
236options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	NULL		#
237flags	set('one','two','tree')	latin1_swedish_ci	NO				#
238new_field	char(10)	latin1_swedish_ci	NO		new		#
239show full columns from t2;
240Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
241auto	int(5) unsigned	NULL	NO		0		#
242string	char(10)	latin1_swedish_ci	YES		newdefault		#
243tiny	tinyint(4)	NULL	NO		0		#
244short	smallint(6)	NULL	NO		NULL		#
245medium	mediumint(8)	NULL	NO		0		#
246long_int	int(11)	NULL	NO		0		#
247longlong	bigint(13)	NULL	NO		0		#
248real_float	float(13,1)	NULL	NO		0.0		#
249real_double	double(16,4)	NULL	YES		NULL		#
250utiny	tinyint(3) unsigned	NULL	NO		0		#
251ushort	smallint(5) unsigned zerofill	NULL	NO		00000		#
252umedium	mediumint(8) unsigned	NULL	NO		0		#
253ulong	int(11) unsigned	NULL	NO		0		#
254ulonglong	bigint(13) unsigned	NULL	NO		0		#
255time_stamp	timestamp	NULL	NO		current_timestamp()	on update current_timestamp()	#
256date_field	char(10)	latin1_swedish_ci	YES		NULL		#
257time_field	time	NULL	YES		NULL		#
258date_time	datetime	NULL	YES		NULL		#
259new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#
260tinyblob_col	tinyblob	NULL	YES		NULL		#
261mediumblob_col	mediumblob	NULL	NO		''		#
262options	enum('one','two','tree')	latin1_swedish_ci	NO		NULL		#
263flags	set('one','two','tree')	latin1_swedish_ci	NO				#
264new_field	char(10)	latin1_swedish_ci	NO		new		#
265select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null)));
266auto	auto
26716	16
268select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and not (t1.string<=>t2.string and t1.tiny<=>t2.tiny and t1.short<=>t2.short and t1.medium<=>t2.medium and t1.long_int<=>t2.long_int and t1.longlong<=>t2.longlong and t1.real_float<=>t2.real_float and t1.real_double<=>t2.real_double and t1.utiny<=>t2.utiny and t1.ushort<=>t2.ushort and t1.umedium<=>t2.umedium and t1.ulong<=>t2.ulong and t1.ulonglong<=>t2.ulonglong and t1.time_stamp<=>t2.time_stamp and t1.date_field<=>t2.date_field and t1.time_field<=>t2.time_field and t1.date_time<=>t2.date_time and t1.new_blob_col<=>t2.new_blob_col and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags and t1.new_field<=>t2.new_field);
269auto	auto
27016	16
271drop table t2;
272create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;
273show full columns from t2;
274Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
275auto	bigint(11) unsigned	NULL	NO	PRI	NULL		#
276t1	int(1)	NULL	NO		NULL		#
277t2	varchar(1)	latin1_swedish_ci	NO		NULL		#
278t3	varchar(256)	latin1_swedish_ci	YES		NULL		#
279t4	varbinary(256)	NULL	YES		NULL		#
280t5	text	latin1_swedish_ci	YES		NULL		#
281t6	blob	NULL	YES		NULL		#
282t7	char(0)	latin1_swedish_ci	NO		NULL		#
283t8	binary(0)	NULL	YES		NULL		#
284select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
285t1	t2	length(t3)	length(t4)	length(t5)	length(t6)	t7	t8
2861	a	256	256	4096	4096
2871	a	256	256	4096	4096
2881	a	256	256	4096	4096
2891	a	256	256	4096	4096
2901	a	256	256	4096	4096
2911	a	256	256	4096	4096
2921	a	256	256	4096	4096
293drop table t1,t2;
294create table t1 (c int);
295insert into t1 values(1),(2);
296create table t2 select * from t1;
297create table t3 select * from t1, t2;
298ERROR 42S21: Duplicate column name 'c'
299create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
300show full columns from t3;
301Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
302c1	int(11)	NULL	YES		NULL		#
303c2	int(11)	NULL	YES		NULL		#
304const	int(1)	NULL	NO		NULL		#
305drop table t1,t2,t3;
306create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
307Warnings:
308Note	1831	Duplicate index `myfield_2`. This is deprecated and will be disallowed in a future release
309drop table t1;
310create table t1 ( id integer unsigned not null primary key );
311create table t2 ( id integer unsigned not null primary key );
312insert into t1 values (1), (2);
313insert into t2 values (1);
314select  t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
315id_A	id_B
3161	1
3172	NULL
318select  t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
319id_A	id_B
3201	1
3212	NULL
322create table t3 (id_A integer unsigned not null, id_B integer unsigned null  );
323insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
324select * from t3;
325id_A	id_B
3261	1
3272	NULL
328truncate table t3;
329insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
330select * from t3;
331id_A	id_B
3321	1
3332	NULL
334drop table t3;
335create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
336select * from t3;
337id_A	id_B
3381	1
3392	NULL
340drop table t3;
341create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
342select * from t3;
343id_A	id_B
3441	1
3452	NULL
346drop table t1,t2,t3;
347