| blob | 6e08067d8a480c2e58c0271b13e1523edf9c5e23 |
1 drop table if exists t1,t2,t3;
2 SET SQL_WARNINGS=1;
3 CREATE TABLE t1 (
4 auto int(5) unsigned NOT NULL auto_increment,
5 string char(10) default "hello",
6 tiny tinyint(4) DEFAULT '0' NOT NULL ,
7 short smallint(6) DEFAULT '1' NOT NULL ,
8 medium mediumint(8) DEFAULT '0' NOT NULL,
9 long_int int(11) DEFAULT '0' NOT NULL,
10 longlong bigint(13) DEFAULT '0' NOT NULL,
11 real_float float(13,1) DEFAULT 0.0 NOT NULL,
12 real_double double(16,4),
13 utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
14 ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
15 umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
16 ulong int(11) unsigned DEFAULT '0' NOT NULL,
17 ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
18 time_stamp timestamp,
19 date_field date,
20 time_field time,
21 date_time datetime,
22 blob_col blob,
23 tinyblob_col tinyblob,
24 mediumblob_col mediumblob not null default '',
25 longblob_col longblob not null default '',
26 options enum('one','two','tree') not null ,
27 flags set('one','two','tree') not null default '',
28 PRIMARY KEY (auto),
29 KEY (utiny),
30 KEY (tiny),
31 KEY (short),
32 KEY any_name (medium),
33 KEY (longlong),
34 KEY (real_float),
35 KEY (ushort),
36 KEY (umedium),
37 KEY (ulong),
38 KEY (ulonglong,ulong),
39 KEY (options,flags)
40 );
41 Warnings:
42 Warning 1101 BLOB/TEXT column 'mediumblob_col' can't have a default value
43 Warning 1101 BLOB/TEXT column 'longblob_col' can't have a default value
44 show full fields from t1;
45 Field Type Collation Null Key Default Extra Privileges Comment
46 auto int(5) unsigned NULL NO PRI NULL auto_increment #
47 string char(10) latin1_swedish_ci YES hello #
48 tiny tinyint(4) NULL NO MUL 0 #
49 short smallint(6) NULL NO MUL 1 #
50 medium mediumint(8) NULL NO MUL 0 #
51 long_int int(11) NULL NO 0 #
52 longlong bigint(13) NULL NO MUL 0 #
53 real_float float(13,1) NULL NO MUL 0.0 #
54 real_double double(16,4) NULL YES NULL #
55 utiny tinyint(3) unsigned NULL NO MUL 0 #
56 ushort smallint(5) unsigned zerofill NULL NO MUL 00000 #
57 umedium mediumint(8) unsigned NULL NO MUL 0 #
58 ulong int(11) unsigned NULL NO MUL 0 #
59 ulonglong bigint(13) unsigned NULL NO MUL 0 #
60 time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP #
61 date_field date NULL YES NULL #
62 time_field time NULL YES NULL #
63 date_time datetime NULL YES NULL #
64 blob_col blob NULL YES NULL #
65 tinyblob_col tinyblob NULL YES NULL #
66 mediumblob_col mediumblob NULL NO NULL #
67 longblob_col longblob NULL NO NULL #
68 options enum('one','two','tree') latin1_swedish_ci NO MUL NULL #
69 flags set('one','two','tree') latin1_swedish_ci NO #
70 show keys from t1;
71 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
72 t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE
73 t1 1 utiny 1 utiny A NULL NULL NULL BTREE
74 t1 1 tiny 1 tiny A NULL NULL NULL BTREE
75 t1 1 short 1 short A NULL NULL NULL BTREE
76 t1 1 any_name 1 medium A NULL NULL NULL BTREE
77 t1 1 longlong 1 longlong A NULL NULL NULL BTREE
78 t1 1 real_float 1 real_float A NULL NULL NULL BTREE
79 t1 1 ushort 1 ushort A NULL NULL NULL BTREE
80 t1 1 umedium 1 umedium A NULL NULL NULL BTREE
81 t1 1 ulong 1 ulong A NULL NULL NULL BTREE
82 t1 1 ulonglong 1 ulonglong A NULL NULL NULL BTREE
83 t1 1 ulonglong 2 ulong A NULL NULL NULL BTREE
84 t1 1 options 1 options A NULL NULL NULL BTREE
85 t1 1 options 2 flags A NULL NULL NULL BTREE
86 CREATE UNIQUE INDEX test on t1 ( auto ) ;
87 CREATE INDEX test2 on t1 ( ulonglong,ulong) ;
88 CREATE INDEX test3 on t1 ( medium ) ;
89 DROP INDEX test ON t1;
90 insert 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');
91 insert 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');
92 insert 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);
93 Warnings:
94 Warning 1265 Data truncated for column 'string' at row 1
95 insert 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);
96 Warnings:
97 Warning 1264 Out of range value for column 'utiny' at row 1
98 Warning 1264 Out of range value for column 'ushort' at row 1
99 Warning 1264 Out of range value for column 'umedium' at row 1
100 Warning 1264 Out of range value for column 'ulong' at row 1
101 Warning 1264 Out of range value for column 'ulonglong' at row 1
102 Warning 1265 Data truncated for column 'options' at row 1
103 Warning 1265 Data truncated for column 'flags' at row 1
104 insert 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");
105 Warnings:
106 Warning 1265 Data truncated for column 'string' at row 1
107 Warning 1264 Out of range value for column 'tiny' at row 1
108 Warning 1264 Out of range value for column 'short' at row 1
109 Warning 1264 Out of range value for column 'medium' at row 1
110 Warning 1264 Out of range value for column 'long_int' at row 1
111 Warning 1264 Out of range value for column 'utiny' at row 1
112 Warning 1264 Out of range value for column 'ushort' at row 1
113 Warning 1264 Out of range value for column 'umedium' at row 1
114 Warning 1264 Out of range value for column 'ulong' at row 1
115 Warning 1264 Out of range value for column 'ulonglong' at row 1
116 Warning 1265 Data truncated for column 'options' at row 1
117 insert 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);
118 Warnings:
119 Warning 1264 Out of range value for column 'tiny' at row 1
120 Warning 1264 Out of range value for column 'short' at row 1
121 Warning 1264 Out of range value for column 'medium' at row 1
122 Warning 1264 Out of range value for column 'long_int' at row 1
123 Warning 1264 Out of range value for column 'utiny' at row 1
124 Warning 1264 Out of range value for column 'ushort' at row 1
125 Warning 1264 Out of range value for column 'umedium' at row 1
126 Warning 1265 Data truncated for column 'options' at row 1
127 insert into t1 (tiny) values (1);
128 select 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;
129 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
130 10 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
131 11 2 2 2 2 2 2 2.0 2.0000 2 00002 2 2 2 0 NULL NULL NULL NULL NULL 2 2
132 12 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
133 13 -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
134 14 -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
135 15 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
136 16 hello 1 1 0 0 0 0.0 NULL 0 00000 0 0 0 0 NULL NULL NULL NULL NULL
137 ALTER TABLE t1
138 add new_field char(10) default "new" not null,
139 change blob_col new_blob_col varchar(20),
140 change date_field date_field char(10),
141 alter column string set default "newdefault",
142 alter short drop default,
143 DROP INDEX utiny,
144 DROP INDEX ushort,
145 DROP PRIMARY KEY,
146 DROP FOREIGN KEY any_name,
147 ADD INDEX (auto);
148 LOCK TABLES t1 WRITE;
149 ALTER TABLE t1
150 RENAME as t2,
151 DROP longblob_col;
152 UNLOCK TABLES;
153 ALTER TABLE t2 rename as t3;
154 LOCK TABLES t3 WRITE ;
155 ALTER TABLE t3 rename as t1;
156 UNLOCK TABLES;
157 select auto,new_field,new_blob_col,date_field from t1 ;
158 auto new_field new_blob_col date_field
159 10 new 1 0000-00-00
160 11 new NULL NULL
161 12 new 1997-03-03
162 13 new -1 1997-08-07
163 14 new -4294967295 0000-00-00
164 15 new 4294967295 0000-00-00
165 16 new NULL NULL
166 CREATE TABLE t2 (
167 auto int(5) unsigned NOT NULL auto_increment,
168 string char(20),
169 mediumblob_col mediumblob not null,
170 new_field char(2),
171 PRIMARY KEY (auto)
172 );
173 INSERT INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10;
174 Warnings:
175 Warning 1265 Data truncated for column 'new_field' at row 2
176 Warning 1265 Data truncated for column 'new_field' at row 3
177 Warning 1265 Data truncated for column 'new_field' at row 4
178 Warning 1265 Data truncated for column 'new_field' at row 5
179 Warning 1265 Data truncated for column 'new_field' at row 6
180 Warning 1265 Data truncated for column 'new_field' at row 7
181 select * from t2;
182 auto string mediumblob_col new_field
183 1 2 2 ne
184 2 0.33333333 ne
185 3 -1 -1 ne
186 4 -429496729 -4294967295 ne
187 5 4294967295 4294967295 ne
188 6 hello ne
189 select distinct flags from t1;
190 flags
192 one,two,tree
193 one
194 one,two
195 select flags from t1 where find_in_set("two",flags)>0;
196 flags
197 one,two,tree
198 one,two,tree
199 one,two
200 one,two
201 select flags from t1 where find_in_set("unknown",flags)>0;
202 flags
203 select options,flags from t1 where options="ONE" and flags="ONE";
204 options flags
205 one one
206 select options,flags from t1 where options="one" and flags="one";
207 options flags
208 one one
209 drop table t2;
210 create table t2 select * from t1;
211 update t2 set string="changed" where auto=16;
212 show full columns from t1;
213 Field Type Collation Null Key Default Extra Privileges Comment
214 auto int(5) unsigned NULL NO MUL NULL auto_increment #
215 string char(10) latin1_swedish_ci YES newdefault #
216 tiny tinyint(4) NULL NO MUL 0 #
217 short smallint(6) NULL NO MUL NULL #
218 medium mediumint(8) NULL NO MUL 0 #
219 long_int int(11) NULL NO 0 #
220 longlong bigint(13) NULL NO MUL 0 #
221 real_float float(13,1) NULL NO MUL 0.0 #
222 real_double double(16,4) NULL YES NULL #
223 utiny tinyint(3) unsigned NULL NO 0 #
224 ushort smallint(5) unsigned zerofill NULL NO 00000 #
225 umedium mediumint(8) unsigned NULL NO MUL 0 #
226 ulong int(11) unsigned NULL NO MUL 0 #
227 ulonglong bigint(13) unsigned NULL NO MUL 0 #
228 time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP #
229 date_field char(10) latin1_swedish_ci YES NULL #
230 time_field time NULL YES NULL #
231 date_time datetime NULL YES NULL #
232 new_blob_col varchar(20) latin1_swedish_ci YES NULL #
233 tinyblob_col tinyblob NULL YES NULL #
234 mediumblob_col mediumblob NULL NO NULL #
235 options enum('one','two','tree') latin1_swedish_ci NO MUL NULL #
236 flags set('one','two','tree') latin1_swedish_ci NO #
237 new_field char(10) latin1_swedish_ci NO new #
238 show full columns from t2;
239 Field Type Collation Null Key Default Extra Privileges Comment
240 auto int(5) unsigned NULL NO 0 #
241 string char(10) latin1_swedish_ci YES newdefault #
242 tiny tinyint(4) NULL NO 0 #
243 short smallint(6) NULL NO NULL #
244 medium mediumint(8) NULL NO 0 #
245 long_int int(11) NULL NO 0 #
246 longlong bigint(13) NULL NO 0 #
247 real_float float(13,1) NULL NO 0.0 #
248 real_double double(16,4) NULL YES NULL #
249 utiny tinyint(3) unsigned NULL NO 0 #
250 ushort smallint(5) unsigned zerofill NULL NO 00000 #
251 umedium mediumint(8) unsigned NULL NO 0 #
252 ulong int(11) unsigned NULL NO 0 #
253 ulonglong bigint(13) unsigned NULL NO 0 #
254 time_stamp timestamp NULL NO 0000-00-00 00:00:00 #
255 date_field char(10) latin1_swedish_ci YES NULL #
256 time_field time NULL YES NULL #
257 date_time datetime NULL YES NULL #
258 new_blob_col varchar(20) latin1_swedish_ci YES NULL #
259 tinyblob_col tinyblob NULL YES NULL #
260 mediumblob_col mediumblob NULL NO NULL #
261 options enum('one','two','tree') latin1_swedish_ci NO NULL #
262 flags set('one','two','tree') latin1_swedish_ci NO #
263 new_field char(10) latin1_swedish_ci NO new #
264 select 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)));
265 auto auto
266 16 16
267 select 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);
268 auto auto
269 16 16
270 drop table t2;
271 create 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;
272 show full columns from t2;
273 Field Type Collation Null Key Default Extra Privileges Comment
274 auto bigint(12) unsigned NULL NO PRI 0 #
275 t1 int(1) NULL NO 0 #
276 t2 varchar(1) latin1_swedish_ci NO #
277 t3 varchar(256) latin1_swedish_ci NO #
278 t4 varbinary(256) NULL NO #
279 t5 longtext latin1_swedish_ci NO NULL #
280 t6 longblob NULL NO NULL #
281 t7 char(0) latin1_swedish_ci NO #
282 t8 binary(0) NULL NO #
283 select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
284 t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8
285 1 a 256 256 4096 4096
286 1 a 256 256 4096 4096
287 1 a 256 256 4096 4096
288 1 a 256 256 4096 4096
289 1 a 256 256 4096 4096
290 1 a 256 256 4096 4096
291 1 a 256 256 4096 4096
292 drop table t1,t2;
293 create table t1 (c int);
294 insert into t1 values(1),(2);
295 create table t2 select * from t1;
296 create table t3 select * from t1, t2;
297 ERROR 42S21: Duplicate column name 'c'
298 create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
299 show full columns from t3;
300 Field Type Collation Null Key Default Extra Privileges Comment
301 c1 int(11) NULL YES NULL #
302 c2 int(11) NULL YES NULL #
303 const int(1) NULL NO 0 #
304 drop table t1,t2,t3;
305 create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
306 drop table t1;
307 create table t1 ( id integer unsigned not null primary key );
308 create table t2 ( id integer unsigned not null primary key );
309 insert into t1 values (1), (2);
310 insert into t2 values (1);
311 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
312 id_A id_B
313 1 1
314 2 NULL
315 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
316 id_A id_B
317 1 1
318 2 NULL
319 create table t3 (id_A integer unsigned not null, id_B integer unsigned null );
320 insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
321 select * from t3;
322 id_A id_B
323 1 1
324 2 NULL
325 truncate table t3;
326 insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
327 select * from t3;
328 id_A id_B
329 1 1
330 2 NULL
331 drop table t3;
332 create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
333 select * from t3;
334 id_A id_B
335 1 1
336 2 NULL
337 drop table t3;
338 create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
339 select * from t3;
340 id_A id_B
341 1 1
342 2 NULL
343 drop table t1,t2,t3;