1
drop table if exists t1,t2,t3,t4;
3
Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
4
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
6
INSERT INTO t1 VALUES (9410,9412);
18
fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
19
companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
20
fld3 char(30) DEFAULT '' NOT NULL,
21
fld4 char(35) DEFAULT '' NOT NULL,
22
fld5 char(35) DEFAULT '' NOT NULL,
23
fld6 char(4) DEFAULT '' NOT NULL
25
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
28
select fld3 from t2 where fld3 like "%cultivation" ;
31
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
56
select fld3,companynr from t2 where companynr = 58 order by fld3;
81
select fld3 from t2 order by fld3 desc limit 10;
93
select fld3 from t2 order by fld3 desc limit 5;
100
select fld3 from t2 order by fld3 desc limit 5,5;
107
UPDATE t2 SET fld3="foo" WHERE fld3="b%";
871
electroencephalography
1309
UPDATE t2 SET fld3="bar" WHERE fld3="s%";
1310
select fld3 from t2;
2073
electroencephalography
2511
DELETE FROM t2 WHERE fld3="r%";
2512
SELECT fld3 FROM t2;
3275
electroencephalography
3713
DELETE FROM t2 WHERE fld3="d%" ORDER BY RAND();
3714
SELECT fld3 FROM t2;
4477
electroencephalography
4916
ALTER TABLE t2 RENAME t1;
4919
Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
4920
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
4922
INSERT INTO t1 VALUES (9410,9412);
4923
select period from t1;
4926
drop table if exists t1,t2,t3,t4;
4928
Note 1051 Unknown table 't2'
4929
Note 1051 Unknown table 't3'
4930
Note 1051 Unknown table 't4'
4931
DROP TABLE IF EXISTS bug13894;
4932
CREATE TABLE bug13894 ( val integer not null ) ENGINE = CSV;
4933
INSERT INTO bug13894 VALUES (5);
4934
INSERT INTO bug13894 VALUES (10);
4935
INSERT INTO bug13894 VALUES (11);
4936
INSERT INTO bug13894 VALUES (10);
4937
SELECT * FROM bug13894;
4943
UPDATE bug13894 SET val=6 WHERE val=10;
4944
SELECT * FROM bug13894;
4950
DROP TABLE bug13894;
4951
DROP TABLE IF EXISTS bug14672;
4952
CREATE TABLE bug14672 (c1 integer not null) engine = CSV;
4953
INSERT INTO bug14672 VALUES (1), (2), (3);
4954
SELECT * FROM bug14672;
4959
DELETE FROM bug14672 WHERE c1 = 2;
4960
SELECT * FROM bug14672;
4964
INSERT INTO bug14672 VALUES (4);
4965
SELECT * FROM bug14672;
4970
INSERT INTO bug14672 VALUES (5);
4971
SELECT * FROM bug14672;
4977
DROP TABLE bug14672;
4978
CREATE TABLE test_concurrent_insert ( val integer not null ) ENGINE = CSV;
4979
LOCK TABLES test_concurrent_insert READ LOCAL;
4980
INSERT INTO test_concurrent_insert VALUES (1);
4981
SELECT * FROM test_concurrent_insert;
4984
SELECT * FROM test_concurrent_insert;
4987
LOCK TABLES test_concurrent_insert WRITE;
4988
INSERT INTO test_concurrent_insert VALUES (2);
4989
SELECT * FROM test_concurrent_insert;
4994
DROP TABLE test_concurrent_insert;
4995
CREATE TABLE test_repair_table ( val integer not null ) ENGINE = CSV;
4996
CHECK TABLE test_repair_table;
4997
Table Op Msg_type Msg_text
4998
test.test_repair_table check status OK
4999
REPAIR TABLE test_repair_table;
5000
Table Op Msg_type Msg_text
5001
test.test_repair_table repair status OK
5002
DROP TABLE test_repair_table;
5003
CREATE TABLE test_repair_table2 ( val integer not null ) ENGINE = CSV;
5004
SELECT * from test_repair_table2;
5007
Error 1194 Table 'test_repair_table2' is marked as crashed and should be repaired
5008
SELECT * from test_repair_table2;
5010
CHECK TABLE test_repair_table2;
5011
Table Op Msg_type Msg_text
5012
test.test_repair_table2 check status OK
5013
DROP TABLE test_repair_table2;
5014
CREATE TABLE test_repair_table3 ( val integer not null ) ENGINE = CSV;
5015
CHECK TABLE test_repair_table3;
5016
Table Op Msg_type Msg_text
5017
test.test_repair_table3 check error Corrupt
5018
REPAIR TABLE test_repair_table3;
5019
Table Op Msg_type Msg_text
5020
test.test_repair_table3 repair status OK
5021
SELECT * FROM test_repair_table3;
5025
DROP TABLE test_repair_table3;
5026
CREATE TABLE test_repair_table4 (
5028
magic_no int(4) unsigned zerofill DEFAULT '0000' NOT NULL,
5029
company_name char(30) DEFAULT '' NOT NULL,
5030
founded char(4) DEFAULT '' NOT NULL
5032
SELECT * FROM test_repair_table4;
5033
num magic_no company_name founded
5035
Error 1194 Table 'test_repair_table4' is marked as crashed and should be repaired
5036
SELECT * FROM test_repair_table4;
5037
num magic_no company_name founded
5038
CHECK TABLE test_repair_table4;
5039
Table Op Msg_type Msg_text
5040
test.test_repair_table4 check status OK
5041
INSERT INTO test_repair_table4 VALUES (2,101,'SAP','1972');
5042
INSERT INTO test_repair_table4 VALUES (1,101,'Microsoft','1978');
5043
INSERT INTO test_repair_table4 VALUES (2,101,'MySQL','1995');
5044
SELECT * FROM test_repair_table4;
5045
num magic_no company_name founded
5047
1 0101 Microsoft 1978
5049
CHECK TABLE test_repair_table4;
5050
Table Op Msg_type Msg_text
5051
test.test_repair_table4 check status OK
5052
REPAIR TABLE test_repair_table4;
5053
Table Op Msg_type Msg_text
5054
test.test_repair_table4 repair status OK
5055
SELECT * FROM test_repair_table4;
5056
num magic_no company_name founded
5058
1 0101 Microsoft 1978
5060
CHECK TABLE test_repair_table4;
5061
Table Op Msg_type Msg_text
5062
test.test_repair_table4 check status OK
5063
REPAIR TABLE test_repair_table4;
5064
Table Op Msg_type Msg_text
5065
test.test_repair_table4 repair status OK
5066
SELECT * FROM test_repair_table4;
5067
num magic_no company_name founded
5069
1 0101 Microsoft 1978
5071
DROP TABLE test_repair_table4;
5072
CREATE TABLE test_repair_table5 (
5074
magic_no int(4) unsigned zerofill DEFAULT '0000' NOT NULL,
5075
company_name char(30) DEFAULT '' NOT NULL,
5076
founded char(4) DEFAULT '' NOT NULL
5078
CHECK TABLE test_repair_table5;
5079
Table Op Msg_type Msg_text
5080
test.test_repair_table5 check error Corrupt
5081
REPAIR TABLE test_repair_table5;
5082
Table Op Msg_type Msg_text
5083
test.test_repair_table5 repair status OK
5084
SELECT * FROM test_repair_table5;
5085
num magic_no company_name founded
5086
INSERT INTO test_repair_table5 VALUES (1, 102, "CORRECT", 1876);
5087
SELECT * FROM test_repair_table5;
5088
num magic_no company_name founded
5091
CHECK TABLE test_repair_table5;
5092
Table Op Msg_type Msg_text
5093
test.test_repair_table5 check error Corrupt
5094
REPAIR TABLE test_repair_table5;
5095
Table Op Msg_type Msg_text
5096
test.test_repair_table5 repair status OK
5097
SELECT * FROM test_repair_table5;
5098
num magic_no company_name founded
5100
INSERT INTO test_repair_table5 VALUES (1, 102, "CORRECT2", 1876);
5101
SELECT * FROM test_repair_table5;
5102
num magic_no company_name founded
5104
1 0102 CORRECT2 1876
5106
CHECK TABLE test_repair_table5;
5107
Table Op Msg_type Msg_text
5108
test.test_repair_table5 check error Corrupt
5109
REPAIR TABLE test_repair_table5;
5110
Table Op Msg_type Msg_text
5111
test.test_repair_table5 repair status OK
5112
SELECT * FROM test_repair_table5;
5113
num magic_no company_name founded
5115
1 0102 CORRECT2 1876
5116
DROP TABLE test_repair_table5;
5117
create table t1 (a int not null) engine=csv;
5118
insert t1 values (1);
5123
insert t1 values (1),(2);
5126
insert t1 values (1),(2),(3);
5130
insert t1 values (1),(2),(3),(4);
5132
select count(*) from t1;
5137
insert t1 values (1),(2),(3),(4),(5);
5141
create table t1 (v varchar(32) not null);
5142
insert into t1 values ('def'),('abc'),('hij'),('3r4f');
5149
alter table t1 change v v2 varchar(32);
5156
alter table t1 change v2 v varchar(64);
5163
update t1 set v = 'lmn' where v = 'hij';
5170
alter table t1 add i int auto_increment not null primary key first;
5177
update t1 set i=5 where i=3;
5184
alter table t1 change i i bigint;
5191
alter table t1 add unique key (i, v);
5192
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
5196
create table bug15205 (val int(11) not null) engine=csv;
5197
create table bug15205_2 (val int(11) not null) engine=csv;
5198
select * from bug15205;
5199
ERROR HY000: Can't get stat of './test/bug15205.CSV' (Errcode: 2)
5200
select * from bug15205_2;
5202
select * from bug15205;
5204
drop table bug15205;
5205
drop table bug15205_2;
5208
c varchar(1) not null,
5209
name varchar(64) not null
5210
) character set latin1 engine=csv;
5211
insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE');
5212
insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE');
5213
insert into t1 values (0xEE,'LATIN SMALL LETTER I WITH CIRCUMFLEX');
5214
insert into t1 values (0xFE,'LATIN SMALL LETTER THORN');
5215
insert into t1 values (0xF7,'DIVISION SIGN');
5216
insert into t1 values (0xFF,'LATIN SMALL LETTER Y WITH DIAERESIS');
5217
select hex(c), c, name from t1 order by 1;
5219
C0 � LATIN CAPITAL LETTER A WITH GRAVE
5220
E0 � LATIN SMALL LETTER A WITH GRAVE
5221
EE � LATIN SMALL LETTER I WITH CIRCUMFLEX
5223
FE � LATIN SMALL LETTER THORN
5224
FF � LATIN SMALL LETTER Y WITH DIAERESIS
5227
create table bug22080_1 (id int not null,string varchar(64) not null) Engine=CSV;
5228
create table bug22080_2 (id int not null,string varchar(64) not null) Engine=CSV;
5229
create table bug22080_3 (id int not null,string varchar(64) not null) Engine=CSV;
5230
insert into bug22080_1 values(1,'string');
5231
insert into bug22080_1 values(2,'string');
5232
insert into bug22080_1 values(3,'string');
5233
check table bug22080_2;
5234
Table Op Msg_type Msg_text
5235
test.bug22080_2 check error Corrupt
5236
check table bug22080_3;
5237
Table Op Msg_type Msg_text
5238
test.bug22080_3 check error Corrupt
5239
drop tables bug22080_1,bug22080_2,bug22080_3;
5240
create table float_test (id float not null,string varchar(64) not null) Engine=CSV;
5241
insert into float_test values(1.0,'string');
5242
insert into float_test values(2.23,'serg.g');
5243
insert into float_test values(0.03,'string');
5244
insert into float_test values(0.19,'string');
5245
insert into float_test values(.67,'string');
5246
insert into float_test values(9.67,'string');
5247
select * from float_test;
5255
drop table float_test;
5256
CREATE TABLE `bug21328` (
5257
`col1` int(11) NOT NULL,
5258
`col2` int(11) NOT NULL,
5259
`col3` int(11) NOT NULL
5261
insert into bug21328 values (1,0,0);
5262
alter table bug21328 engine=myisam;
5263
drop table bug21328;
5264
create table t1(a blob not null, b int not null) engine=csv;
5265
insert into t1 values('a', 1);
5272
create table t1(a int not null) engine=csv;
5273
insert into t1 values(-1), (-123.34), (2), (-23);
5281
Table Op Msg_type Msg_text
5282
test.t1 check status OK
5284
create table t1(a int not null, b int not null) engine=csv;
5286
Table Op Msg_type Msg_text
5287
test.t1 repair Warning Data truncated for column 'a' at row 5
5288
test.t1 repair status OK
5290
Table Op Msg_type Msg_text
5291
test.t1 check status OK
5299
Table Op Msg_type Msg_text
5300
test.t1 check status OK
5302
create table t1(a int not null) engine=csv;
5303
insert into t1 values (0), (1), (2);
5304
delete from t1 limit 2;
5306
Table Op Msg_type Msg_text
5307
test.t1 check status OK
5313
Table Op Msg_type Msg_text
5314
test.t1 check status OK
5318
create table t1(a datetime not null) engine=csv;
5319
insert into t1 values();
5321
Warning 1364 Field 'a' doesn't have a default value
5326
create table t1(a set('foo','bar') not null) engine=csv;
5327
insert into t1 values();
5329
Warning 1364 Field 'a' doesn't have a default value
5334
create table t1(a varchar(32) not null) engine=csv;
5335
insert into t1 values();
5337
Warning 1364 Field 'a' doesn't have a default value
5342
create table t1(a int not null) engine=csv;
5343
insert into t1 values();
5345
Warning 1364 Field 'a' doesn't have a default value
5350
create table t1(a blob not null) engine=csv;
5351
insert into t1 values();
5353
Warning 1364 Field 'a' doesn't have a default value
5358
create table t1(a bit(1) not null) engine=csv;
5359
insert into t1 values();
5361
Warning 1364 Field 'a' doesn't have a default value
5362
select BIN(a) from t1;
5366
create table t1(a enum('foo','bar') default null) engine=csv;
5367
ERROR 42000: The storage engine for the table doesn't support nullable columns
5368
create table t1(a enum('foo','bar') default 'foo') engine=csv;
5369
ERROR 42000: The storage engine for the table doesn't support nullable columns
5370
create table t1(a enum('foo','bar') default 'foo' not null) engine=csv;
5371
insert into t1 values();
5376
CREATE TABLE t1(a INT) ENGINE=CSV;
5377
ERROR 42000: The storage engine for the table doesn't support nullable columns
5380
Error 1178 The storage engine for the table doesn't support nullable columns
5381
Error 1005 Can't create table 'test.t1' (errno: 138)
5382
create table t1 (c1 tinyblob not null) engine=csv;
5383
insert into t1 values("This");
5384
update t1 set c1="That" where c1="This";
5386
info: Rows matched: 1 Changed: 1 Warnings: 0
added
removed
mysql-test/r/csv.result
