2
# Test ranges for all types and some other basic tests
6
drop table if exists t1,t2,t3;
11
auto int(5) unsigned NOT NULL auto_increment,
12
string char(10) default "hello",
13
tiny tinyint(4) DEFAULT '0' NOT NULL ,
14
short smallint(6) DEFAULT '1' NOT NULL ,
15
medium mediumint(8) DEFAULT '0' NOT NULL,
16
long_int int(11) DEFAULT '0' NOT NULL,
17
longlong bigint(13) DEFAULT '0' NOT NULL,
18
real_float float(13,1) DEFAULT 0.0 NOT NULL,
19
real_double double(16,4),
20
utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
21
ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
22
umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
23
ulong int(11) unsigned DEFAULT '0' NOT NULL,
24
ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
30
tinyblob_col tinyblob,
31
mediumblob_col mediumblob not null default '',
32
longblob_col longblob not null default '',
33
options enum('one','two','tree') not null ,
34
flags set('one','two','tree') not null default '',
39
KEY any_name (medium),
45
KEY (ulonglong,ulong),
49
# We mask out the Privileges column because it differs with embedded server
51
show full fields from t1;
54
CREATE UNIQUE INDEX test on t1 ( auto ) ;
55
CREATE INDEX test2 on t1 ( ulonglong,ulong) ;
56
CREATE INDEX test3 on t1 ( medium ) ;
57
DROP INDEX test ON t1;
59
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');
60
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');
61
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);
62
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);
63
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");
64
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);
65
insert into t1 (tiny) values (1);
67
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;
71
add new_field char(10) default "new" not null,
72
change blob_col new_blob_col varchar(20),
73
change date_field date_field char(10),
74
alter column string set default "newdefault",
75
alter short drop default,
79
DROP FOREIGN KEY any_name,
88
ALTER TABLE t2 rename as t3;
89
LOCK TABLES t3 WRITE ;
90
ALTER TABLE t3 rename as t1;
93
select auto,new_field,new_blob_col,date_field from t1 ;
96
# check with old syntax
99
auto int(5) unsigned NOT NULL auto_increment,
101
mediumblob_col mediumblob not null,
106
INSERT INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10;
111
select distinct flags from t1;
112
select flags from t1 where find_in_set("two",flags)>0;
113
select flags from t1 where find_in_set("unknown",flags)>0;
114
select options,flags from t1 where options="ONE" and flags="ONE";
115
select options,flags from t1 where options="one" and flags="one";
120
# Check CREATE ... SELECT
123
create table t2 select * from t1;
124
update t2 set string="changed" where auto=16;
125
# We mask out the Privileges column because it differs with embedded server
127
show full columns from t1;
129
show full columns from t2;
130
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)));
131
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);
135
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;
136
# We mask out the Privileges column because it differs with embedded server
138
show full columns from t2;
139
select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
142
create table t1 (c int);
143
insert into t1 values(1),(2);
144
create table t2 select * from t1;
146
create table t3 select * from t1, t2; # Should give an error
147
create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
148
# We mask out the Privileges column because it differs with embedded server
150
show full columns from t3;
153
create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
156
create table t1 ( id integer unsigned not null primary key );
157
create table t2 ( id integer unsigned not null primary key );
158
insert into t1 values (1), (2);
159
insert into t2 values (1);
160
select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
161
select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
162
create table t3 (id_A integer unsigned not null, id_B integer unsigned null );
163
insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
166
insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
169
create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
172
create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
added
removed
mysql-test/t/type_ranges.test
