| 1 |
--source include/have_maria.inc |
| 2 |
|
| 3 |
# Test for BUG#36319 |
| 4 |
# "Aria: table is not empty but DELETE and SELECT find no rows" |
| 5 |
|
| 6 |
CREATE TABLE t1 ( |
| 7 |
line BLOB, |
| 8 |
kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po', |
| 9 |
name VARCHAR(32) |
| 10 |
) transactional=0 row_format=page engine=aria; |
| 11 |
|
| 12 |
let $query= INSERT INTO t1 (name, kind, line) VALUES |
| 13 |
("Aadaouane", "pp", GeomFromText("POINT(32.816667 35.983333)")), |
| 14 |
("Aadassiye", "pp", GeomFromText("POINT(35.816667 36.216667)")), |
| 15 |
("Aadbel", "pp", GeomFromText("POINT(34.533333 36.100000)")), |
| 16 |
("Aadchit", "pp", GeomFromText("POINT(33.347222 35.423611)")), |
| 17 |
("Aadchite", "pp", GeomFromText("POINT(33.347222 35.423611)")), |
| 18 |
("Aadchit el Qoussair", "pp", GeomFromText("POINT(33.283333 35.483333)")), |
| 19 |
("Aaddaye", "pp", GeomFromText("POINT(36.716667 40.833333)")), |
| 20 |
("'Aadeissa", "pp", GeomFromText("POINT(32.823889 35.698889)")), |
| 21 |
("Aaderup", "pp", GeomFromText("POINT(55.216667 11.766667)")), |
| 22 |
("Qalaat Aades", "pp", GeomFromText("POINT(33.503333 35.377500)")), |
| 23 |
("A ad'ino", "pp", GeomFromText("POINT(54.812222 38.209167)")), |
| 24 |
("Aadi Noia", "pp", GeomFromText("POINT(13.800000 39.833333)")), |
| 25 |
("Aad La Macta", "pp", GeomFromText("POINT(35.779444 -0.129167)")), |
| 26 |
("Aadland", "pp", GeomFromText("POINT(60.366667 5.483333)")), |
| 27 |
("Aadliye", "pp", GeomFromText("POINT(33.366667 36.333333)")), |
| 28 |
("Aadloun", "pp", GeomFromText("POINT(33.403889 35.273889)")), |
| 29 |
("Aadma", "pp", GeomFromText("POINT(58.798333 22.663889)")), |
| 30 |
("Aadma Asundus", "pp", GeomFromText("POINT(58.798333 22.663889)")), |
| 31 |
("Aadmoun", "pp", GeomFromText("POINT(34.150000 35.650000)")), |
| 32 |
("Aadneram", "pp", GeomFromText("POINT(59.016667 6.933333)")), |
| 33 |
("Aadneskaar", "pp", GeomFromText("POINT(58.083333 6.983333)")), |
| 34 |
("Aadorf", "pp", GeomFromText("POINT(47.483333 8.900000)")), |
| 35 |
("Aadorp", "pp", GeomFromText("POINT(52.366667 6.633333)")), |
| 36 |
("Aadouane", "pp", GeomFromText("POINT(32.816667 35.983333)")), |
| 37 |
("Aadoui", "pp", GeomFromText("POINT(34.450000 35.983333)")), |
| 38 |
("Aadouiye", "pp", GeomFromText("POINT(34.583333 36.183333)")), |
| 39 |
("Aadouss", "pp", GeomFromText("POINT(33.512500 35.601389)")), |
| 40 |
("Aadra", "pp", GeomFromText("POINT(33.616667 36.500000)")), |
| 41 |
("Aadzi", "pp", GeomFromText("POINT(38.100000 64.850000)")); |
| 42 |
|
| 43 |
--disable_query_log |
| 44 |
let $1=90; |
| 45 |
while($1) |
| 46 |
{ |
| 47 |
eval $query; |
| 48 |
dec $1; |
| 49 |
} |
| 50 |
let $1=90; |
| 51 |
while($1) |
| 52 |
{ |
| 53 |
delete from t1 limit 1; |
| 54 |
delete from t1 limit 10; |
| 55 |
delete from t1 limit 7; |
| 56 |
delete from t1 limit 2; |
| 57 |
dec $1; |
| 58 |
} |
| 59 |
--enable_query_log |
| 60 |
|
| 61 |
select count(*) from t1; |
| 62 |
delete from t1 limit 1000; |
| 63 |
select count(*) from t1; |
| 64 |
select name from t1; |
| 65 |
check table t1 extended; |
| 66 |
drop table t1; |
| 67 |
|
| 68 |
# |
| 69 |
# Testing of ALTER TABLE under lock tables |
| 70 |
# |
| 71 |
|
| 72 |
create table t1 (i int) engine=aria; |
| 73 |
create table t2 (j int) engine=aria; |
| 74 |
lock table t1 write, t2 read; |
| 75 |
alter table t1 modify i int default 1; |
| 76 |
insert into t1 values (2); |
| 77 |
# This caused a core dump |
| 78 |
alter table t1 modify i bigint default 1; |
| 79 |
select count(*) from t1; |
| 80 |
select * from t1; |
| 81 |
unlock tables; |
| 82 |
drop table t1,t2; |
| 83 |
|
| 84 |
# |
| 85 |
# test INSERT ON DUPLICATE KEY UPDATE |
| 86 |
# |
| 87 |
|
| 88 |
create table t1(id int, s char(1), unique(s)) engine=aria; |
| 89 |
insert into t1 values(1,"a") on duplicate key update t1.id=t1.id+1; |
| 90 |
insert into t1 values(1,"a") on duplicate key update t1.id=t1.id+1; |
| 91 |
insert into t1 select 1,"a" on duplicate key update t1.id=t1.id+1; |
| 92 |
select * from t1; |
| 93 |
|
| 94 |
# test REPLACE SELECT |
| 95 |
replace into t1 select 1,"a"; |
| 96 |
select * from t1; |
| 97 |
drop table t1; |
| 98 |
|
| 99 |
# test LOAD DATA INFILE REPLACE |
| 100 |
create table t1 (pk int primary key, apk int unique, data int) engine=aria; |
| 101 |
insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6); |
| 102 |
load data concurrent infile '../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); |
| 103 |
select * from t1 order by pk; |
| 104 |
load data infile '../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); |
| 105 |
select * from t1 order by pk; |
| 106 |
drop table t1; |
| 107 |
|
| 108 |
--echo # |
| 109 |
--echo # End of 5.5 tests |
| 110 |
--echo # |
| 111 |
|
| 112 |
--echo # |
| 113 |
--echo # MDEV-27303 Table corruption after insert into a non-InnoDB table with DESC index |
| 114 |
--echo # |
| 115 |
create table t1 ( |
| 116 |
a bigint default 0, |
| 117 |
b bigint default 0, |
| 118 |
c binary(128) not null, |
| 119 |
d datetime default '0000-00-00 00:00:00', |
| 120 |
key (c desc,b,d,a) |
| 121 |
) engine=aria; |
| 122 |
insert into t1 (c) values |
| 123 |
('xx'),('bb'),('tt'),('pp'),('mm'),('yy'),('rr'),('bb'),('yy'),('gg'), |
| 124 |
('dd'),('fx'),('wi'),('ix'),('ox'),('mu'),('ux'),('pm'),('mx'),('xu'), |
| 125 |
('ul'),('lp'),('px'),('lp'),('xx'),('pq'),('qs'),('se'),('ee'),('xx'), |
| 126 |
('rv'),('ff'),('vj'),('jy'),('yn'),('nc'),('nx'),('hj'),('ji'),('ik'), |
| 127 |
('kk'),('ww'),('xx'),('yd'),('dw'),('wk'),('kr'),('dd'),('rj'),('jf'), |
| 128 |
('bx'),('fc'),('cp'),('pm'),('mw'),('wy'),('yl'),('li'),('ic'),('he'), |
| 129 |
('ci'),('il'),('lz'),('zd'),('gz'),('xd'),('ze'),('dm'),('ms'),('xd'), |
| 130 |
('sw'),('we'),('nb'),('tx'),('vr'),('xw'),('aa'),('ah'),('hd'),('jl'), |
| 131 |
('lf'),('fw'),('wx'),('xh'),('hr'),('zx'),('vw'),('rm'),('mx'),('xt'), |
| 132 |
('tp'),('ps'),('sh'),('ga'),('df'),('as'),('gz'),('xd'),('yy'),('xr'); |
| 133 |
check table t1 extended; |
| 134 |
drop table t1; |
| 135 |
|
| 136 |
--echo # |
| 137 |
--echo # MDEV-27309 Server crash or ASAN memcpy-param-overlap upon INSERT into Aria/MyISAM table with DESC key |
| 138 |
--echo # |
| 139 |
CREATE TABLE t1 (id INT, c BINARY(80), PRIMARY KEY(id)); |
| 140 |
ALTER TABLE t1 ADD KEY(c DESC, id); |
| 141 |
INSERT INTO t1 VALUES (1,NULL),(2,''),(3,''); |
| 142 |
DROP TABLE t1; |
| 143 |
|
| 144 |
--echo # |
| 145 |
--echo # MDEV-27330 Wrong sorting order with DESC index and empty strings in MyISAM/Aria table |
| 146 |
--echo # |
| 147 |
create table t (id int, c char(128) not null, key (c desc)) engine=aria; |
| 148 |
insert into t values (1,''),(2,'foo'),(3,''),(4,'bar'); |
| 149 |
select c from t order by c; |
| 150 |
drop table t; |
| 151 |
|
| 152 |
--echo # |
| 153 |
--echo # MDEV-27340 NULL gets lost (becomes empty string), SELECT hangs with DESC index on MyISAM/Aria table |
| 154 |
--echo # |
| 155 |
create table t (c char(8), key(c desc)) engine=aria character set utf8mb4; |
| 156 |
insert into t values (''),('foo'),(null),(''),('bar'); |
| 157 |
check table t; |
| 158 |
check table t extended; |
| 159 |
select distinct c from t; |
| 160 |
select c from t; |
| 161 |
drop table t; |
| 162 |
|
| 163 |
--echo # |
| 164 |
--echo # MDEV-27434 DESC attribute does not work with auto-increment on secondary column of multi-part index |
| 165 |
--echo # |
| 166 |
create table t (a int auto_increment, b int, unique(b,a desc)) engine=aria; |
| 167 |
insert ignore into t (b) values (10),(10),(10); |
| 168 |
select * from t; |
| 169 |
drop table t; |
| 170 |
|
| 171 |
--echo # |
| 172 |
--echo # MDEV-27585 Auto-increment on secondary column increments unexpectedly with DESC key |
| 173 |
--echo # |
| 174 |
create table t (c char(16), i int auto_increment, index (c,i desc)) engine=aria collate latin1_swedish_ci; |
| 175 |
insert into t (c) values ('รค'),('a'); |
| 176 |
select hex(c),c,i from t order by c, i; |
| 177 |
drop table t; |
| 178 |
|
| 179 |
--echo # |
| 180 |
--echo # End of 10.8 tests |
| 181 |
--echo # |