checkError6.sim 10.4 KB
Newer Older
S
Shengliang Guan 已提交
1 2 3 4 5 6
system sh/stop_dnodes.sh
system sh/deploy.sh -n dnode1 -i 1
system sh/cfg.sh -n dnode1 -c debugflag -v 131
system sh/exec.sh -n dnode1 -s start -v
sql connect

X
Xiaoyu Wang 已提交
7
print =============== step1: create drop select * from information_schema.ins_dnodes
S
Shengliang Guan 已提交
8 9 10 11 12 13 14 15
$x = 0
step1:
	$x = $x + 1
	sleep 1000
	if $x == 10 then
	  print ---> dnode not ready!
		return -1
	endi
X
Xiaoyu Wang 已提交
16
sql select * from information_schema.ins_dnodes
S
Shengliang Guan 已提交
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
print ---> $data00 $data01 $data02 $data03 $data04 $data05
if $rows != 1 then
  return -1
endi
if $data(1)[4] != ready then
  goto step1
endi

$tbPrefix = tb
$tbNum = 5
$rowNum = 10

print =============== step2: prepare data
sql create database db vgroups 2
sql use db
S
Shengliang Guan 已提交
32
sql create table if not exists stb (ts timestamp, tbcol int, tbcol2 float, tbcol3 double, tbcol4 binary(30), tbcol5 binary(30)) tags (tgcol int unsigned)
S
Shengliang Guan 已提交
33 34 35 36 37 38 39 40 41

$i = 0
while $i < $tbNum
  $tb = $tbPrefix . $i
  sql create table $tb using stb tags( $i )
  $x = 0
  while $x < $rowNum
    $cc = $x * 60000
    $ms = 1601481600000 + $cc
S
Shengliang Guan 已提交
42
    sql insert into $tb values ($ms , $x , $x , $x , "abcd1234=-+*" , "123456 0" ) 
S
Shengliang Guan 已提交
43 44
    $x = $x + 1
  endw 
S
Shengliang Guan 已提交
45 46 47

  $cc = $x * 60000
  $ms = 1601481600000 + $cc
S
Shengliang Guan 已提交
48
  sql insert into $tb values ($ms , NULL , NULL , NULL , NULL , NULL ) 
S
Shengliang Guan 已提交
49 50 51
  $i = $i + 1
endw 

S
Shengliang Guan 已提交
52
print =============== step3: tb
S
Shengliang Guan 已提交
53 54 55 56
sql select avg(tbcol) from tb1
sql select avg(tbcol) from tb1 where ts <= 1601481840000
sql select avg(tbcol) as b from tb1
sql select avg(tbcol) as b from tb1 interval(1d)
S
Shengliang Guan 已提交
57 58 59 60 61 62
sql select avg(tbcol) as b from tb1 where ts <= 1601481840000 interval(1m)
sql select bottom(tbcol, 2) from tb1 where ts <= 1601481840000 
sql select top(tbcol, 2) from tb1 where ts <= 1601481840000 
sql select percentile(tbcol, 2) from tb1 where ts <= 1601481840000 
sql select leastsquares(tbcol, 1, 1) as b from tb1 where ts <= 1601481840000
sql show table distributed tb1
S
Shengliang Guan 已提交
63
sql select count(1) from tb1
S
Shengliang Guan 已提交
64 65
sql select count(tbcol) as b from tb1 where ts <= 1601481840000 interval(1m)
sql select diff(tbcol) from tb1 where ts <= 1601481840000 
S
Shengliang Guan 已提交
66
sql select diff(tbcol) from tb1 where tbcol > 5 and tbcol < 20 order by ts
S
Shengliang Guan 已提交
67
sql select first(tbcol), last(tbcol) as b from tb1 where ts <= 1601481840000 interval(1m)
S
Shengliang Guan 已提交
68 69
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), sum(tbcol), stddev(tbcol) from tb1 where ts <= 1601481840000 partition by tgcol order by tgcol
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), sum(tbcol), stddev(tbcol) from tb1 where ts <= 1601481840000 partition by tgcol interval(1m) 
X
Xiaoyu Wang 已提交
70
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from tb1 where ts <= 1601481840000 and ts >= 1601481800000 partition by tgcol interval(1m) fill(value, 0,0,0,0,0)
S
Shengliang Guan 已提交
71
sql select last_row(*) from tb1 where tbcol > 5 and tbcol < 20
S
Shengliang Guan 已提交
72
sql select _wstart, _wend, _wduration, _qstart, _qend,  count(*) from tb1 interval(10s, 2s) sliding(10s)
X
Xiaoyu Wang 已提交
73 74
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from tb1 where ts <= 1601481840000 and ts >= 1601481800000 partition by tgcol interval(1m) fill(value, 0,0,0,0,0)
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from tb1 where ts <= 1601481840000 and ts >= 1601481800000 partition by tgcol interval(1m) fill(value, 0,0,0,0,0) order by tgcol desc
S
Shengliang Guan 已提交
75 76 77 78 79
sql select log(tbcol), abs(tbcol), pow(tbcol, 2), sqrt(tbcol), sin(tbcol), cos(tbcol), tan(tbcol), asin(tbcol), acos(tbcol), atan(tbcol), ceil(tbcol), floor(tbcol), round(tbcol), atan(tbcol) from tb1
sql select length("abcd1234"), char_length("abcd1234=-+*") from tb1
sql select tbcol4, length(tbcol4), lower(tbcol4), upper(tbcol4), ltrim(tbcol4), rtrim(tbcol4), concat(tbcol4, tbcol5), concat_ws('_', tbcol4, tbcol5), substr(tbcol4, 1, 4)  from tb1
sql select * from tb1 where tbcol not in (1,2,3,null);
sql select * from tb1 where tbcol + 3 <> null;
X
Xiaoyu Wang 已提交
80
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from tb1 where ts <= 1601481840000 and ts >= 1601481800000 partition by tgcol interval(1m) fill(value, 0,0,0,0,0)
S
Shengliang Guan 已提交
81
sql select tbcol5 - tbcol3 from tb1
S
Shengliang Guan 已提交
82 83

print =============== step4: stb
S
Shengliang Guan 已提交
84 85 86 87 88
sql select avg(tbcol) as c from stb
sql select avg(tbcol) as c from stb where ts <= 1601481840000
sql select avg(tbcol) as c from stb where tgcol < 5 and ts <= 1601481840000
sql select avg(tbcol) as c from stb interval(1m)
sql select avg(tbcol) as c from stb interval(1d)
S
Shengliang Guan 已提交
89
sql select avg(tbcol) as b from stb where ts <= 1601481840000 interval(1m)
S
Shengliang Guan 已提交
90
sql select avg(tbcol) as c from stb group by tgcol
S
Shengliang Guan 已提交
91 92
sql select avg(tbcol) as b from stb where ts <= 1601481840000 partition by tgcol interval(1m)
sql show table distributed stb
S
Shengliang Guan 已提交
93
sql select count(1) from stb
S
Shengliang Guan 已提交
94 95 96 97 98 99
sql select count(tbcol) as b from stb where ts <= 1601481840000 partition by tgcol interval(1m)
sql select diff(tbcol) from stb where ts <= 1601481840000 
sql select first(tbcol), last(tbcol) as c from stb group by tgcol
sql select first(tbcol), last(tbcol) as b from stb where ts <= 1601481840000 and tbcol2 is null partition by tgcol interval(1m)
sql select first(tbcol), last(tbcol) as b from stb where ts <= 1601481840000 partition by tgcol interval(1m)
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), sum(tbcol), stddev(tbcol) from stb where ts <= 1601481840000 partition by tgcol interval(1m)
X
Xiaoyu Wang 已提交
100 101
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from stb where ts <= 1601481840000 and ts >= 1601481800000 partition by tgcol interval(1m) fill(value, 0,0,0,0,0)
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from stb where ts <= 1601481840000 and ts >= 1601481800000 and tgcol = 1 partition by tgcol interval(1m) fill(value, 0,0,0,0,0) order by tgcol desc
S
Shengliang Guan 已提交
102
sql select last_row(tbcol), stddev(tbcol) from stb where tbcol > 5 and tbcol < 20 group by tgcol
S
Shengliang Guan 已提交
103 104 105 106 107 108
sql select _wstart, _wend, _wduration, _qstart, _qend,  count(*) from stb interval(10s, 2s) sliding(10s)
sql select log(tbcol), abs(tbcol), pow(tbcol, 2), sqrt(tbcol), sin(tbcol), cos(tbcol), tan(tbcol), asin(tbcol), acos(tbcol), atan(tbcol), ceil(tbcol), floor(tbcol), round(tbcol), atan(tbcol) from stb
sql select length("abcd1234"), char_length("abcd1234=-+*") from stb
sql select tbcol4, length(tbcol4), lower(tbcol4), upper(tbcol4), ltrim(tbcol4), rtrim(tbcol4), concat(tbcol4, tbcol5), concat_ws('_', tbcol4, tbcol5), substr(tbcol4, 1, 4)  from stb
sql select * from stb where tbcol not in (1,2,3,null);
sql select * from stb where tbcol + 3 <> null;
S
Shengliang Guan 已提交
109
sql select count(tbcol), avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol) from stb where tbcol = 1 and tbcol2 = 1 and tbcol3 = 1 partition by tgcol interval(1d)
S
Shengliang Guan 已提交
110
sql select _wstart, count(*) from tb1 session(ts, 1m)
X
Xiaoyu Wang 已提交
111
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from stb where ts <= 1601481840000 and ts >= 1601481800000 partition by tgcol interval(1m) fill(value, 0,0,0,0,0)
S
Shengliang Guan 已提交
112
sql select tbcol5 - tbcol3 from stb
S
Shengliang Guan 已提交
113

S
Shengliang Guan 已提交
114 115
sql select spread( tbcol2 )/44, spread(tbcol2), 0.204545455 * 44 from stb;
sql select min(tbcol) * max(tbcol) /4, sum(tbcol2) * apercentile(tbcol2, 20), apercentile(tbcol2, 33) + 52/9 from stb;
S
Shengliang Guan 已提交
116
sql select distinct(tbname), tgcol from stb;
S
Shengliang Guan 已提交
117 118
sql select sum(tbcol) from stb partition by tbname interval(1s) slimit 1 soffset 1;
sql select sum(tbcol) from stb partition by tbname interval(1s) slimit 2 soffset 4 limit 10 offset 1;
S
Shengliang Guan 已提交
119

S
Shengliang Guan 已提交
120
print =============== step5: explain
S
Shengliang Guan 已提交
121 122 123
sql explain analyze select ts from stb where -2;
sql explain analyze select ts from tb1;
sql explain analyze select ts from stb order by ts;
D
dapan1121 已提交
124
sql explain analyze select * from information_schema.ins_stables;
S
Shengliang Guan 已提交
125 126 127
sql explain analyze select count(*),sum(tbcol) from tb1;
sql explain analyze select count(*),sum(tbcol) from stb;
sql explain analyze select count(*),sum(tbcol) from stb group by tbcol;
D
dapan1121 已提交
128 129
sql explain analyze select * from information_schema.ins_stables;
sql explain analyze verbose true select * from information_schema.ins_stables where db_name='db2';
X
Xiaoyu Wang 已提交
130 131
sql explain analyze verbose true select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from stb where ts <= 1601481840000 and ts >= 1601481800000 partition by tgcol interval(1m) fill(value, 0,0,0,0,0)
sql explain select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), count(tbcol) from stb where ts <= 1601481840000 and ts >= 1601481800000 partition by tgcol interval(1m) fill(value, 0,0,0,0,0)
S
Shengliang Guan 已提交
132

S
Shengliang Guan 已提交
133 134 135 136
print =============== step6: in cast
sql select 1+1n;
sql select cast(1 as timestamp)+1n;
sql select cast(1 as timestamp)+1y;
S
Shengliang Guan 已提交
137 138 139 140 141 142
sql select * from tb1 where ts in ('2018-07-10 16:31:01', '2022-07-10 16:31:03', 1657441865000);
sql select * from tb1 where tbcol2 in (257);
sql select * from tb1 where tbcol3 in (2, 257);
sql select * from stb where ts in ('2018-07-10 16:31:01', '2022-07-10 16:31:03', 1657441865000);
sql select * from stb where tbcol2 in (257);
sql select * from stb where tbcol3 in (2, 257);
S
Shengliang Guan 已提交
143

S
Shengliang Guan 已提交
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
print =============== check
$null=

system_content sh/checkValgrind.sh -n dnode1 
print cmd return result ----> [ $system_content ]
if $system_content > 0 then
  return -1
endi 

if $system_content == $null then
  return -1
endi 

print =============== restart
system sh/exec.sh -n dnode1 -s stop -x SIGINT
system sh/exec.sh -n dnode1 -s start -v

sql select avg(tbcol) as c from stb
sql select avg(tbcol) as c from stb where ts <= 1601481840000
sql select avg(tbcol) as c from stb where tgcol < 5 and ts <= 1601481840000
sql select avg(tbcol) as c from stb interval(1m)
sql select avg(tbcol) as c from stb interval(1d)
sql select avg(tbcol) as b from stb where ts <= 1601481840000 interval(1m)
sql select avg(tbcol) as c from stb group by tgcol
sql select avg(tbcol) as b from stb where ts <= 1601481840000 partition by tgcol interval(1m)
sql show table distributed stb
sql select count(tbcol) as b from stb where ts <= 1601481840000 partition by tgcol interval(1m)
sql select diff(tbcol) from stb where ts <= 1601481840000 
sql select first(tbcol), last(tbcol) as c from stb group by tgcol
sql select first(tbcol), last(tbcol) as b from stb where ts <= 1601481840000 and tbcol2 is null partition by tgcol interval(1m)
sql select first(tbcol), last(tbcol) as b from stb where ts <= 1601481840000 partition by tgcol interval(1m)
sql select count(tbcol), avg(tbcol), max(tbcol), min(tbcol), sum(tbcol), stddev(tbcol) from stb where ts <= 1601481840000 partition by tgcol interval(1m)
sql select last_row(tbcol), stddev(tbcol) from stb where tbcol > 5 and tbcol < 20 group by tgcol

S
Shengliang Guan 已提交
178 179 180 181 182 183 184
_OVER:
system sh/exec.sh -n dnode1 -s stop -x SIGINT
print =============== check
$null=

system_content sh/checkValgrind.sh -n dnode1 
print cmd return result ----> [ $system_content ]
185
if $system_content > 0 then
S
Shengliang Guan 已提交
186 187 188 189 190 191
  return -1
endi 

if $system_content == $null then
  return -1
endi