| 1 |
CREATE TABLE t1 ( |
| 2 |
line BLOB, |
| 3 |
kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po', |
| 4 |
name VARCHAR(32) |
| 5 |
) transactional=0 row_format=page engine=aria; |
| 6 |
select count(*) from t1; |
| 7 |
count(*) |
| 8 |
810 |
| 9 |
delete from t1 limit 1000; |
| 10 |
select count(*) from t1; |
| 11 |
count(*) |
| 12 |
0 |
| 13 |
select name from t1; |
| 14 |
name |
| 15 |
check table t1 extended; |
| 16 |
Table Op Msg_type Msg_text |
| 17 |
test.t1 check status OK |
| 18 |
drop table t1; |
| 19 |
create table t1 (i int) engine=aria; |
| 20 |
create table t2 (j int) engine=aria; |
| 21 |
lock table t1 write, t2 read; |
| 22 |
alter table t1 modify i int default 1; |
| 23 |
insert into t1 values (2); |
| 24 |
alter table t1 modify i bigint default 1; |
| 25 |
select count(*) from t1; |
| 26 |
count(*) |
| 27 |
1 |
| 28 |
select * from t1; |
| 29 |
i |
| 30 |
2 |
| 31 |
unlock tables; |
| 32 |
drop table t1,t2; |
| 33 |
create table t1(id int, s char(1), unique(s)) engine=aria; |
| 34 |
insert into t1 values(1,"a") on duplicate key update t1.id=t1.id+1; |
| 35 |
insert into t1 values(1,"a") on duplicate key update t1.id=t1.id+1; |
| 36 |
insert into t1 select 1,"a" on duplicate key update t1.id=t1.id+1; |
| 37 |
select * from t1; |
| 38 |
id s |
| 39 |
3 a |
| 40 |
replace into t1 select 1,"a"; |
| 41 |
select * from t1; |
| 42 |
id s |
| 43 |
1 a |
| 44 |
drop table t1; |
| 45 |
create table t1 (pk int primary key, apk int unique, data int) engine=aria; |
| 46 |
insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6); |
| 47 |
load data concurrent infile '../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); |
| 48 |
Warnings: |
| 49 |
Note 1265 Data truncated for column 'pk' at row 1 |
| 50 |
Note 1265 Data truncated for column 'apk' at row 1 |
| 51 |
Note 1265 Data truncated for column 'pk' at row 2 |
| 52 |
Note 1265 Data truncated for column 'apk' at row 2 |
| 53 |
select * from t1 order by pk; |
| 54 |
pk apk data |
| 55 |
1 1 1 |
| 56 |
3 4 NULL |
| 57 |
5 6 NULL |
| 58 |
load data infile '../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); |
| 59 |
Warnings: |
| 60 |
Note 1265 Data truncated for column 'pk' at row 1 |
| 61 |
Note 1265 Data truncated for column 'apk' at row 1 |
| 62 |
Note 1265 Data truncated for column 'pk' at row 2 |
| 63 |
Note 1265 Data truncated for column 'apk' at row 2 |
| 64 |
select * from t1 order by pk; |
| 65 |
pk apk data |
| 66 |
1 1 1 |
| 67 |
3 4 NULL |
| 68 |
5 6 NULL |
| 69 |
drop table t1; |
| 70 |
# |
| 71 |
# End of 5.5 tests |
| 72 |
# |
| 73 |
# |
| 74 |
# MDEV-27303 Table corruption after insert into a non-InnoDB table with DESC index |
| 75 |
# |
| 76 |
create table t1 ( |
| 77 |
a bigint default 0, |
| 78 |
b bigint default 0, |
| 79 |
c binary(128) not null, |
| 80 |
d datetime default '0000-00-00 00:00:00', |
| 81 |
key (c desc,b,d,a) |
| 82 |
) engine=aria; |
| 83 |
insert into t1 (c) values |
| 84 |
('xx'),('bb'),('tt'),('pp'),('mm'),('yy'),('rr'),('bb'),('yy'),('gg'), |
| 85 |
('dd'),('fx'),('wi'),('ix'),('ox'),('mu'),('ux'),('pm'),('mx'),('xu'), |
| 86 |
('ul'),('lp'),('px'),('lp'),('xx'),('pq'),('qs'),('se'),('ee'),('xx'), |
| 87 |
('rv'),('ff'),('vj'),('jy'),('yn'),('nc'),('nx'),('hj'),('ji'),('ik'), |
| 88 |
('kk'),('ww'),('xx'),('yd'),('dw'),('wk'),('kr'),('dd'),('rj'),('jf'), |
| 89 |
('bx'),('fc'),('cp'),('pm'),('mw'),('wy'),('yl'),('li'),('ic'),('he'), |
| 90 |
('ci'),('il'),('lz'),('zd'),('gz'),('xd'),('ze'),('dm'),('ms'),('xd'), |
| 91 |
('sw'),('we'),('nb'),('tx'),('vr'),('xw'),('aa'),('ah'),('hd'),('jl'), |
| 92 |
('lf'),('fw'),('wx'),('xh'),('hr'),('zx'),('vw'),('rm'),('mx'),('xt'), |
| 93 |
('tp'),('ps'),('sh'),('ga'),('df'),('as'),('gz'),('xd'),('yy'),('xr'); |
| 94 |
check table t1 extended; |
| 95 |
Table Op Msg_type Msg_text |
| 96 |
test.t1 check status OK |
| 97 |
drop table t1; |
| 98 |
# |
| 99 |
# MDEV-27309 Server crash or ASAN memcpy-param-overlap upon INSERT into Aria/MyISAM table with DESC key |
| 100 |
# |
| 101 |
CREATE TABLE t1 (id INT, c BINARY(80), PRIMARY KEY(id)); |
| 102 |
ALTER TABLE t1 ADD KEY(c DESC, id); |
| 103 |
INSERT INTO t1 VALUES (1,NULL),(2,''),(3,''); |
| 104 |
DROP TABLE t1; |
| 105 |
# |
| 106 |
# MDEV-27330 Wrong sorting order with DESC index and empty strings in MyISAM/Aria table |
| 107 |
# |
| 108 |
create table t (id int, c char(128) not null, key (c desc)) engine=aria; |
| 109 |
insert into t values (1,''),(2,'foo'),(3,''),(4,'bar'); |
| 110 |
select c from t order by c; |
| 111 |
c |
| 112 |
|
| 113 |
|
| 114 |
bar |
| 115 |
foo |
| 116 |
drop table t; |
| 117 |
# |
| 118 |
# MDEV-27340 NULL gets lost (becomes empty string), SELECT hangs with DESC index on MyISAM/Aria table |
| 119 |
# |
| 120 |
create table t (c char(8), key(c desc)) engine=aria character set utf8mb4; |
| 121 |
insert into t values (''),('foo'),(null),(''),('bar'); |
| 122 |
check table t; |
| 123 |
Table Op Msg_type Msg_text |
| 124 |
test.t check status OK |
| 125 |
check table t extended; |
| 126 |
Table Op Msg_type Msg_text |
| 127 |
test.t check status OK |
| 128 |
select distinct c from t; |
| 129 |
c |
| 130 |
NULL |
| 131 |
|
| 132 |
bar |
| 133 |
foo |
| 134 |
select c from t; |
| 135 |
c |
| 136 |
foo |
| 137 |
bar |
| 138 |
|
| 139 |
|
| 140 |
NULL |
| 141 |
drop table t; |
| 142 |
# |
| 143 |
# MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index |
| 144 |
# |
| 145 |
create table t (a int auto_increment, b int, unique(b,a desc)) engine=aria; |
| 146 |
insert ignore into t (b) values (10),(10),(10); |
| 147 |
select * from t; |
| 148 |
a b |
| 149 |
3 10 |
| 150 |
2 10 |
| 151 |
1 10 |
| 152 |
drop table t; |
| 153 |
# |
| 154 |
# MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key |
| 155 |
# |
| 156 |
create table t (c char(16), i int auto_increment, index (c,i desc)) engine=aria collate latin1_swedish_ci; |
| 157 |
insert into t (c) values ('ä'),('a'); |
| 158 |
select hex(c),c,i from t order by c, i; |
| 159 |
hex(c) c i |
| 160 |
61 a 1 |
| 161 |
C3A4 ä 1 |
| 162 |
drop table t; |
| 163 |
# |
| 164 |
# End of 10.8 tests |
| 165 |
# |