alter__for_community_version.sim 7.4 KB
Newer Older
D
dapan1121 已提交
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
system sh/stop_dnodes.sh

system sh/deploy.sh -n dnode1 -i 1
system sh/cfg.sh -n dnode1 -c walLevel -v 1
system sh/exec.sh -n dnode1 -s start
sleep 100
sql connect

$dbPrefix = m_alt_db
$tbPrefix = m_alt_tb
$mtPrefix = m_alt_mt
$tbNum = 10
$rowNum = 5
$totalNum = $tbNum * $rowNum
$ts0 = 1537146000000
$delta = 600000
print ========== alter.sim
$i = 0
$db = $dbPrefix . $i
$mt = $mtPrefix . $i

sql drop database if exists $db
sql create database $db days 10 keep 20
sql use $db
sql show databases
if $rows != 1 then
  return -1
endi
if $data07 != 20 then 
 return -1
endi

sql_error alter database $db keep "20"
sql_error alter database $db keep "20","20","20"
sql_error alter database $db keep 0
sql_error alter database $db keep 20.0
sql_error alter database $db keep 20.0,20.0,20.0
sql_error alter database $db keep 0,0,0
sql_error alter database $db keep 3
sql_error alter database $db keep -1,-1,-1
sql_error alter database $db keep 20,20
sql_error alter database $db keep 9,9,9
sql_error alter database $db keep 20,20,19
sql_error alter database $db keep 20,19,20
sql_error alter database $db keep 20,19,19
sql_error alter database $db keep 20,19,18
sql_error alter database $db keep 20,20,20,20
sql_error alter database $db keep 365001,365001,365001
sql_error alter database $db keep 365001
sql alter database $db keep 20
sql show databases
if $rows != 1 then
  return -1
endi
if $data07 != 20 then 
 return -1
endi
sql alter database $db keep 10
sql show databases
if $rows != 1 then
  return -1
endi
if $data07 != 10 then 
 return -1
endi
sql alter database $db keep 11
sql show databases
if $rows != 1 then
  return -1
endi
if $data07 != 11 then 
 return -1
endi
sql alter database $db keep 13
sql show databases
if $rows != 1 then
  return -1
endi
if $data07 != 13 then 
 return -1
endi
82
sql alter database $db keep 36500
D
dapan1121 已提交
83 84 85 86
sql show databases
if $rows != 1 then
  return -1
endi
87
if $data07 != 36500 then 
D
dapan1121 已提交
88 89 90
 return -1
endi

91
sql_error alter database $db keep 365000
D
dapan1121 已提交
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317

##### alter table test, simeplest case
sql create table tb (ts timestamp, c1 int, c2 int, c3 int)
sql insert into tb values (now, 1, 1, 1)
sql select * from tb order by ts desc
if $rows != 1 then
  return -1
endi
sql alter table tb drop column c3
sql select * from tb order by ts desc 
if $data01 != 1 then
  return -1
endi
if $data02 != 1 then 
  return -1
endi
if $data03 != null then
  return -1
endi
sql alter table tb add column c3 nchar(4)
sql select * from tb order by ts desc
if $rows != 1 then
  return -1
endi
if $data03 != NULL then
  return -1
endi
sql insert into tb values (now, 2, 2, 'taos')
sql select * from tb order by ts desc
if $rows != 2 then
  return -1
endi
print data03 = $data03
if $data03 != taos then
  print expect taos, actual: $data03
  return -1
endi
sql drop table tb

##### alter metric test, simplest case
sql create table mt (ts timestamp, c1 int, c2 int, c3 int) tags (t1 int)
sql create table tb using mt tags(1)
sql insert into tb values (now, 1, 1, 1)
sql alter table mt drop column c3
sql select * from tb order by ts desc
if $data01 != 1 then 
  return -1
endi
if $data02 != 1 then 
  return -1
endi
if $data03 != null then
  return -1
endi

sql alter table mt add column c3 nchar(4)
sql select * from tb order by ts desc
if $data03 != NULL then
  return -1
endi
sql insert into tb values (now, 2, 2, 'taos')
sql select * from tb order by ts desc
if $rows != 2 then
  return -1
endi
if $data03 != taos then
  return -1
endi
if $data13 != NULL then
  return -1
endi
sql drop table tb
sql drop table mt

## [TBASE272]
sql create table tb (ts timestamp, c1 int, c2 int, c3 int)
sql insert into tb values (now, 1, 1, 1)
sql alter table tb drop column c3
sql alter table tb add column c3 nchar(5)
sql insert into tb values(now, 2, 2, 'taos')
sql drop table tb
sql create table mt (ts timestamp, c1 int, c2 int, c3 int) tags (t1 int)
sql create table tb using mt tags(1)
sql insert into tb values (now, 1, 1, 1)
sql alter table mt drop column c3
sql select * from tb order by ts desc
if $rows != 1 then
  return -1
endi
sql drop table tb
sql drop table mt

sleep 100
### ALTER TABLE WHILE STREAMING [TBASE271]
#sql create table tb1 (ts timestamp, c1 int, c2 nchar(5), c3 int)
#sql create table strm as select count(*), avg(c1), first(c2), sum(c3) from tb1 interval(2s)
#sql select * from strm
#if $rows != 0 then
#  return -1
#endi
##sleep 12000
#sql insert into tb1 values (now, 1, 'taos', 1)
#sleep 20000
#sql select * from strm
#print rows = $rows
#if $rows != 1 then
#  return -1
#endi
#if $data04 != 1 then
#  return -1
#endi
#sql alter table tb1 drop column c3
#sleep 500
#sql insert into tb1 values (now, 2, 'taos')
#sleep 30000
#sql select * from strm
#if $rows != 2 then
#   return -1
#endi
#if $data04 != 1 then
#  return -1
#endi
#sql alter table tb1 add column c3 int
#sleep 500
#sql insert into tb1 values (now, 3, 'taos', 3);
#sleep 100
#sql select * from strm
#if $rows != 3 then
#   return -1
#endi
#if $data04 != 1 then
#  return -1
#endi

## ALTER TABLE AND INSERT BY COLUMNS
sql create table mt (ts timestamp, c1 int, c2 int) tags(t1 int)
sql create table tb using mt tags(0)
sql insert into tb values (now-1m, 1, 1)
sql alter table mt drop column c2
sql_error insert into tb (ts, c1, c2) values (now, 2, 2)
sql insert into tb (ts, c1) values (now, 2)
sql select * from tb order by ts desc
if $rows != 2 then
  return -1
endi
if $data01 != 2 then
  return -1
endi
if $data02 != null then
  return -1
endi
sql alter table mt add column c2 int
sql insert into tb (ts, c2) values (now, 3)
sql select * from tb order by ts desc 
if $data02 != 3 then
  return -1
endi

## ALTER TABLE AND IMPORT
sql drop database $db
sql create database $db
sql use $db
sql create table mt (ts timestamp, c1 int, c2 nchar(7), c3 int) tags (t1 int)
sql create table tb using mt tags(1)
sleep 100
sql insert into tb values ('2018-11-01 16:30:00.000', 1, 'insert', 1)
sql alter table mt drop column c3

sql insert into tb values ('2018-11-01 16:29:59.000', 1, 'insert')
sql import into tb values ('2018-11-01 16:29:59.000', 1, 'import')
sql select * from tb order by ts desc
if $data01 != 1 then
  return -1
endi
if $data02 != insert then
  return -1
endi
sql alter table mt add column c3 nchar(4)
sql select * from tb order by ts desc 
if $data03 != NULL then
  return -1
endi

print ============================>TD-3366 TD-3486
sql insert into td_3366(ts, c3, c1) using mt(t1) tags(911) values('2018-1-1 11:11:11', 'new1', 12);
sql insert into td_3486(ts, c3, c1) using mt(t1) tags(-12) values('2018-1-1 11:11:11', 'new1', 12);
sql insert into ttxu(ts, c3, c1) using mt(t1) tags('-121') values('2018-1-1 11:11:11', 'new1', 12);

sql insert into tb(ts, c1, c3) using mt(t1) tags(123) values('2018-11-01 16:29:58.000', 2, 'port')

sql insert into tb values ('2018-11-01 16:29:58.000', 2, 'import', 3)
sql import into tb values ('2018-11-01 16:29:58.000', 2, 'import', 3)
sql import into tb values ('2018-11-01 16:39:58.000', 2, 'import', 3)
sql select * from tb order by ts desc 
if $rows != 4 then
  return -1
endi

if $data03 != 3 then
  return -1
endi

##### ILLEGAL OPERATIONS

# try dropping columns that are defined in metric
sql_error alter table tb drop column c1;

# try dropping primary key
sql_error alter table mt drop column ts;

# try modifying two columns in a single statement
sql_error alter table mt add column c5 nchar(3) c6 nchar(4)

# duplicate columns
sql_error alter table mt add column c1 int

# drop non-existing columns
sql_error alter table mt drop column c9

#sql drop database $db
#sql show databases
#if $rows != 0 then
#  return -1
#endi

system sh/exec.sh -n dnode1 -s stop -x SIGINT