1
by brian
clean slate |
1 |
drop table if exists t1,t2,t3; |
2 |
SET SQL_WARNINGS=1; |
|
3 |
CREATE TABLE t1 ( |
|
506
by Brian Aker
Added back more tests. |
4 |
auto int NOT NULL auto_increment, |
1
by brian
clean slate |
5 |
string char(10) default "hello", |
506
by Brian Aker
Added back more tests. |
6 |
tiny int DEFAULT '0' NOT NULL , |
7 |
short int DEFAULT '1' NOT NULL , |
|
8 |
medium int DEFAULT '0' NOT NULL, |
|
9 |
long_int int DEFAULT '0' NOT NULL, |
|
10 |
longlong bigint DEFAULT '0' NOT NULL, |
|
11 |
real_float float DEFAULT 0.0 NOT NULL, |
|
12 |
real_double double, |
|
13 |
utiny int DEFAULT '0' NOT NULL, |
|
14 |
ushort int DEFAULT '00000' NOT NULL, |
|
15 |
umedium int DEFAULT '0' NOT NULL, |
|
16 |
ulong int DEFAULT '0' NOT NULL, |
|
17 |
ulonglong bigint DEFAULT '0' NOT NULL, |
|
1
by brian
clean slate |
18 |
time_stamp timestamp, |
19 |
date_field date, |
|
20 |
date_time datetime, |
|
21 |
blob_col blob, |
|
22 |
tinyblob_col tinyblob, |
|
23 |
mediumblob_col mediumblob not null default '', |
|
24 |
longblob_col longblob not null default '', |
|
25 |
options enum('one','two','tree') not null , |
|
506
by Brian Aker
Added back more tests. |
26 |
flags enum('one','two','tree') not null default 'one', |
1
by brian
clean slate |
27 |
PRIMARY KEY (auto), |
28 |
KEY (utiny), |
|
29 |
KEY (tiny), |
|
30 |
KEY (short), |
|
31 |
KEY any_name (medium), |
|
32 |
KEY (longlong), |
|
33 |
KEY (real_float), |
|
34 |
KEY (ushort), |
|
35 |
KEY (umedium), |
|
36 |
KEY (ulong), |
|
37 |
KEY (ulonglong,ulong), |
|
38 |
KEY (options,flags) |
|
39 |
);
|
|
1273.13.37
by Brian Aker
Remove "full" syntax. |
40 |
show fields from t1; |
1309.2.4
by Brian Aker
New version of show columns code. |
41 |
Field Type Null Default Default_is_NULL On_Update |
1660
by Brian Aker
MErge in change to do YES/NO like standard requires. |
42 |
auto INTEGER NO NO |
43 |
string VARCHAR YES hello NO |
|
44 |
tiny INTEGER NO 0 NO |
|
45 |
short INTEGER NO 1 NO |
|
46 |
medium INTEGER NO 0 NO |
|
47 |
long_int INTEGER NO 0 NO |
|
48 |
longlong BIGINT NO 0 NO |
|
49 |
real_float DOUBLE NO 0.0 NO |
|
50 |
real_double DOUBLE YES YES |
|
51 |
utiny INTEGER NO 0 NO |
|
52 |
ushort INTEGER NO 00000 NO |
|
53 |
umedium INTEGER NO 0 NO |
|
54 |
ulong INTEGER NO 0 NO |
|
55 |
ulonglong BIGINT NO 0 NO |
|
56 |
time_stamp TIMESTAMP YES YES |
|
57 |
date_field DATE YES YES |
|
58 |
date_time DATETIME YES YES |
|
59 |
blob_col BLOB YES YES |
|
60 |
tinyblob_col BLOB YES YES |
|
61 |
mediumblob_col BLOB NO NO |
|
62 |
longblob_col BLOB NO NO |
|
63 |
options ENUM NO NO |
|
64 |
flags ENUM NO one NO |
|
1
by brian
clean slate |
65 |
show keys from t1; |
1309.2.3
by Brian Aker
Update the code so use a faster index lookup method. |
66 |
Table Unique Key_name Seq_in_index Column_name |
1660
by Brian Aker
MErge in change to do YES/NO like standard requires. |
67 |
t1 YES PRIMARY 1 auto |
68 |
t1 NO utiny 1 utiny |
|
69 |
t1 NO tiny 1 tiny |
|
70 |
t1 NO short 1 short |
|
71 |
t1 NO any_name 1 medium |
|
72 |
t1 NO longlong 1 longlong |
|
73 |
t1 NO real_float 1 real_float |
|
74 |
t1 NO ushort 1 ushort |
|
75 |
t1 NO umedium 1 umedium |
|
76 |
t1 NO ulong 1 ulong |
|
77 |
t1 NO ulonglong 1 ulonglong |
|
78 |
t1 NO ulonglong 2 ulong |
|
79 |
t1 NO options 1 options |
|
80 |
t1 NO options 2 flags |
|
1
by brian
clean slate |
81 |
CREATE UNIQUE INDEX test on t1 ( auto ) ; |
82 |
CREATE INDEX test2 on t1 ( ulonglong,ulong) ; |
|
83 |
CREATE INDEX test3 on t1 ( medium ) ; |
|
84 |
DROP INDEX test ON t1; |
|
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
85 |
insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,NULL,NULL,1,1,1,1,'one','one'); |
86 |
insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,2,2,'two','one'); |
|
87 |
insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','19970303101010','','','','3',3,3); |
|
506
by Brian Aker
Added back more tests. |
88 |
ERROR 22001: Data too long for column 'string' at row 1 |
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
89 |
insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,19970403090807,-1,-1,-1,'-1',-1,-1); |
934.4.1
by Jay Pipes
Fixes ENUM field type to throw an error on bad data input. 0 is now not |
90 |
ERROR HY000: Received an invalid enum value '-1'. |
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
91 |
insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"tree"); |
506
by Brian Aker
Added back more tests. |
92 |
ERROR 22001: Data too long for column 'string' at row 1 |
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
93 |
insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,4294967295,4294967295,4294967295,'4294967295',0,0); |
506
by Brian Aker
Added back more tests. |
94 |
ERROR 22003: Out of range value for column 'tiny' at row 1 |
1
by brian
clean slate |
95 |
insert into t1 (tiny) values (1); |
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
96 |
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,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1; |
97 |
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 date_time blob_col tinyblob_col mediumblob_col longblob_col |
|
98 |
10 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL NULL 1 1 1 1 |
|
99 |
11 2 2 2 2 2 2 2 2 2 2 2 2 2 NULL NULL NULL NULL NULL 2 2 |
|
100 |
12 hello 1 1 0 0 0 0 NULL 0 0 0 0 0 NULL NULL NULL NULL NULL |
|
1
by brian
clean slate |
101 |
CREATE TABLE t2 ( |
506
by Brian Aker
Added back more tests. |
102 |
auto int NOT NULL auto_increment, |
1
by brian
clean slate |
103 |
string char(20), |
104 |
mediumblob_col mediumblob not null, |
|
105 |
new_field char(2), |
|
106 |
PRIMARY KEY (auto) |
|
107 |
);
|
|
506
by Brian Aker
Added back more tests. |
108 |
INSERT INTO t2 (string,mediumblob_col) SELECT string,mediumblob_col from t1 where auto > 10; |
1
by brian
clean slate |
109 |
select * from t2; |
110 |
auto string mediumblob_col new_field |
|
506
by Brian Aker
Added back more tests. |
111 |
1 2 2 NULL |
112 |
2 hello NULL |
|
1
by brian
clean slate |
113 |
select distinct flags from t1; |
114 |
flags
|
|
115 |
one
|
|
116 |
select flags from t1 where find_in_set("two",flags)>0; |
|
117 |
flags
|
|
118 |
select flags from t1 where find_in_set("unknown",flags)>0; |
|
119 |
flags
|
|
120 |
select options,flags from t1 where options="ONE" and flags="ONE"; |
|
121 |
options flags |
|
122 |
one one |
|
506
by Brian Aker
Added back more tests. |
123 |
one one |
1
by brian
clean slate |
124 |
select options,flags from t1 where options="one" and flags="one"; |
125 |
options flags |
|
126 |
one one |
|
506
by Brian Aker
Added back more tests. |
127 |
one one |
1
by brian
clean slate |
128 |
drop table t2; |
129 |
create table t2 select * from t1; |
|
130 |
update t2 set string="changed" where auto=16; |
|
1273.13.37
by Brian Aker
Remove "full" syntax. |
131 |
show columns from t1; |
1309.2.4
by Brian Aker
New version of show columns code. |
132 |
Field Type Null Default Default_is_NULL On_Update |
1638.10.114
by Stewart Smith
when creating a CreateField from a Field, a auto_incremen column should not have a default value of 0 as that's not magic. |
133 |
auto INTEGER NO NO |
1660
by Brian Aker
MErge in change to do YES/NO like standard requires. |
134 |
string VARCHAR YES hello NO |
135 |
tiny INTEGER NO 0 NO |
|
136 |
short INTEGER NO 1 NO |
|
137 |
medium INTEGER NO 0 NO |
|
138 |
long_int INTEGER NO 0 NO |
|
139 |
longlong BIGINT NO 0 NO |
|
140 |
real_float DOUBLE NO 0 NO |
|
141 |
real_double DOUBLE YES YES |
|
142 |
utiny INTEGER NO 0 NO |
|
143 |
ushort INTEGER NO 0 NO |
|
144 |
umedium INTEGER NO 0 NO |
|
145 |
ulong INTEGER NO 0 NO |
|
146 |
ulonglong BIGINT NO 0 NO |
|
147 |
time_stamp TIMESTAMP YES YES |
|
148 |
date_field DATE YES YES |
|
149 |
date_time DATETIME YES YES |
|
150 |
blob_col BLOB YES YES |
|
151 |
tinyblob_col BLOB YES YES |
|
152 |
mediumblob_col BLOB NO NO |
|
153 |
longblob_col BLOB NO NO |
|
154 |
options ENUM NO NO |
|
155 |
flags ENUM NO one NO |
|
1273.13.37
by Brian Aker
Remove "full" syntax. |
156 |
show columns from t2; |
1309.2.4
by Brian Aker
New version of show columns code. |
157 |
Field Type Null Default Default_is_NULL On_Update |
1660
by Brian Aker
MErge in change to do YES/NO like standard requires. |
158 |
auto INTEGER NO 0 NO |
159 |
string VARCHAR YES hello NO |
|
160 |
tiny INTEGER NO 0 NO |
|
161 |
short INTEGER NO 1 NO |
|
162 |
medium INTEGER NO 0 NO |
|
163 |
long_int INTEGER NO 0 NO |
|
164 |
longlong BIGINT NO 0 NO |
|
165 |
real_float DOUBLE NO 0 NO |
|
166 |
real_double DOUBLE YES YES |
|
167 |
utiny INTEGER NO 0 NO |
|
168 |
ushort INTEGER NO 0 NO |
|
169 |
umedium INTEGER NO 0 NO |
|
170 |
ulong INTEGER NO 0 NO |
|
171 |
ulonglong BIGINT NO 0 NO |
|
172 |
time_stamp TIMESTAMP YES YES |
|
173 |
date_field DATE YES YES |
|
174 |
date_time DATETIME YES YES |
|
175 |
blob_col BLOB YES YES |
|
176 |
tinyblob_col BLOB YES YES |
|
177 |
mediumblob_col BLOB NO NO |
|
178 |
longblob_col BLOB NO NO |
|
179 |
options ENUM NO NO |
|
180 |
flags ENUM NO one NO |
|
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
181 |
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.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time 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))); |
506
by Brian Aker
Added back more tests. |
182 |
auto auto |
896.5.1
by Jay Pipes
Removes the TIME column type and related time functions. |
183 |
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.date_time<=>t2.date_time and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags); |
506
by Brian Aker
Added back more tests. |
184 |
auto auto |
1
by brian
clean slate |
185 |
drop table t2; |
186 |
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; |
|
1273.13.37
by Brian Aker
Remove "full" syntax. |
187 |
show columns from t2; |
1309.2.4
by Brian Aker
New version of show columns code. |
188 |
Field Type Null Default Default_is_NULL On_Update |
1660
by Brian Aker
MErge in change to do YES/NO like standard requires. |
189 |
auto BIGINT NO NO |
190 |
t1 INTEGER NO NO |
|
191 |
t2 VARCHAR NO NO |
|
192 |
t3 VARCHAR YES YES |
|
1753.2.2
by Andrew Hutchings
Fix test cases |
193 |
t4 VARBINARY YES YES |
194 |
t5 TEXT YES YES |
|
1660
by Brian Aker
MErge in change to do YES/NO like standard requires. |
195 |
t6 BLOB YES YES |
196 |
t7 VARCHAR NO NO |
|
1753.2.2
by Andrew Hutchings
Fix test cases |
197 |
t8 VARBINARY YES YES |
1
by brian
clean slate |
198 |
select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2; |
199 |
t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8 |
|
200 |
1 a 256 256 4096 4096 |
|
201 |
1 a 256 256 4096 4096 |
|
202 |
1 a 256 256 4096 4096 |
|
203 |
drop table t1,t2; |
|
204 |
create table t1 (c int); |
|
205 |
insert into t1 values(1),(2); |
|
206 |
create table t2 select * from t1; |
|
207 |
create table t3 select * from t1, t2; |
|
208 |
ERROR 42S21: Duplicate column name 'c' |
|
2141.4.2
by Andrew Hutchings
Implicit joins of the form "SELECT * FROM t1, t2" without WHERE or ON now error. |
209 |
create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1 CROSS JOIN t2; |
1273.13.37
by Brian Aker
Remove "full" syntax. |
210 |
show columns from t3; |
1309.2.4
by Brian Aker
New version of show columns code. |
211 |
Field Type Null Default Default_is_NULL On_Update |
1660
by Brian Aker
MErge in change to do YES/NO like standard requires. |
212 |
c1 INTEGER YES YES |
213 |
c2 INTEGER YES YES |
|
214 |
const INTEGER NO NO |
|
1
by brian
clean slate |
215 |
drop table t1,t2,t3; |
216 |
create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield)); |
|
217 |
drop table t1; |
|
506
by Brian Aker
Added back more tests. |
218 |
create table t1 ( id integer not null primary key ); |
219 |
create table t2 ( id integer not null primary key ); |
|
1
by brian
clean slate |
220 |
insert into t1 values (1), (2); |
221 |
insert into t2 values (1); |
|
222 |
select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id ); |
|
223 |
id_A id_B |
|
224 |
1 1 |
|
225 |
2 NULL |
|
226 |
select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); |
|
227 |
id_A id_B |
|
228 |
1 1 |
|
229 |
2 NULL |
|
506
by Brian Aker
Added back more tests. |
230 |
create table t3 (id_A integer not null, id_B integer null ); |
1
by brian
clean slate |
231 |
insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id ); |
232 |
select * from t3; |
|
233 |
id_A id_B |
|
234 |
1 1 |
|
235 |
2 NULL |
|
236 |
truncate table t3; |
|
237 |
insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); |
|
238 |
select * from t3; |
|
239 |
id_A id_B |
|
240 |
1 1 |
|
241 |
2 NULL |
|
242 |
drop table t3; |
|
243 |
create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id ); |
|
244 |
select * from t3; |
|
245 |
id_A id_B |
|
246 |
1 1 |
|
247 |
2 NULL |
|
248 |
drop table t3; |
|
249 |
create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); |
|
250 |
select * from t3; |
|
251 |
id_A id_B |
|
252 |
1 1 |
|
253 |
2 NULL |
|
254 |
drop table t1,t2,t3; |