system sh/stop_dnodes.sh system sh/deploy.sh -n dnode1 -i 1 system sh/cfg.sh -n dnode1 -c walLevel -v 0 system sh/exec.sh -n dnode1 -s start sleep 1000 sql connect $dbPrefix = select_tags_db $tbPrefix = select_tags_tb $mtPrefix = select_tags_mt $tbNum = 16 $rowNum = 800 $totalNum = $tbNum * $rowNum print =============== select_with_tags.sim $i = 0 $db = $dbPrefix . $i $mt = $mtPrefix . $i $tstart = 100000 sql drop database if exists $db -x step1 step1: sql create database if not exists $db keep 36500 sql use $db sql create table $mt (ts timestamp, c1 int, c2 float, c3 bigint, c4 smallint, c5 tinyint, c6 double, c7 bool, c8 binary(10), c9 nchar(9)) TAGS(t1 int, t2 binary(12)) $i = 0 $j = 1 while $i < $tbNum $tb = $tbPrefix . $i $tg2 = ' . abc $tg2 = $tg2 . $i $tg2 = $tg2 . ' sql create table $tb using $mt tags( $i , $tg2 ) $x = 0 while $x < $rowNum $ms = $x . m $c = $x / 100 $c = $c * 100 $c = $x - $c $c1 = $c + $i $binary = ' . binary $binary = $binary . $c $binary = $binary . ' $nchar = ' . nchar $nchar = $nchar . $c $nchar = $nchar . ' sql insert into $tb values ($tstart , $c1 , $c , $c , $c , $c , $c , $c , $binary , $nchar ) $tstart = $tstart + 1 $x = $x + 1 endw $i = $i + 1 $j = $j + 10000 $tstart = 100000 + $j endw sleep 100 #================================ sql select ts from select_tags_mt0 print $rows if $rows != 12800 then return -1 endi sql select first(ts), tbname, t1, t2 from select_tags_mt0; if $rows != 1 then return -1 endi if $data00 != @70-01-01 08:01:40.000@ then return -1 endi if $data01 != @select_tags_tb0@ then return -1 endi if $data02 != 0 then return -1 endi if $data03 != @abc0@ then return -1 endi sql select last(ts), tbname, t1, t2 from select_tags_mt0; if $rows != 1 then return -1 endi if $data00 != @70-01-01 08:04:10.800@ then return -1 endi if $data01 != @select_tags_tb15@ then return -1 endi if $data02 != 15 then return -1 endi if $data03 != @abc15@ then return -1 endi sql select min(c1), tbname, t1, t2 from select_tags_mt0; if $rows != 1 then return -1 endi if $data00 != 0 then return -1 endi if $data01 != @select_tags_tb0@ then return -1 endi if $data02 != 0 then return -1 endi if $data03 != @abc0@ then return -1 endi sql select max(c1), tbname, t1, t2 from select_tags_mt0; if $rows != 1 then return -1 endi if $data00 != 114 then return -1 endi if $data01 != @select_tags_tb15@ then return -1 endi if $data02 != 15 then return -1 endi if $data03 != @abc15@ then return -1 endi sql select top(c1, 100), tbname, t1, t2 from select_tags_mt0; if $rows != 100 then return -1 endi if $data00 != @70-01-01 08:03:30.100@ then return -1 endi if $data10 != @70-01-01 08:03:30.200@ then return -1 endi if $data01 != 110 then return -1 endi if $data02 != @select_tags_tb11@ then return -1 endi if $data03 != 11 then return -1 endi if $data04 != @abc11@ then return -1 endi sql select top(c1, 80), tbname, t1, t2 from select_tags_mt0; if $rows != 80 then return -1 endi if $data00 != @70-01-01 08:03:40.100@ then return -1 endi if $data10 != @70-01-01 08:03:40.200@ then return -1 endi if $data01 != 111 then return -1 endi if $data02 != @select_tags_tb12@ then return -1 endi if $data03 != 12 then return -1 endi if $data04 != @abc12@ then return -1 endi sql select bottom(c1, 100), tbname, t1, t2 from select_tags_mt0; if $rows != 100 then return -1 endi if $data00 != @70-01-01 08:01:40.000@ then return -1 endi if $data10 != @70-01-01 08:01:40.001@ then return -1 endi if $data01 != 0 then return -1 endi if $data02 != @select_tags_tb0@ then return -1 endi if $data03 != 0 then return -1 endi if $data04 != @abc0@ then return -1 endi sql select last_row(c1, c2), tbname, t1, t2 from select_tags_mt0; if $rows != 1 then return -1 endi if $data00 != 114 then return -1 endi print $data01 if $data01 != 99.00000 then return -1 endi if $data02 != @select_tags_tb15@ then return -1 endi if $data03 != 15 then return -1 endi print ====== selectivity+tags+group by tags======================= sql select first(c1), tbname, t1, t2 from select_tags_mt0 group by tbname; if $rows != 16 then return -1 endi if $data00 != 0 then return -1 endi if $data10 != 1 then return -1 endi print $data01 if $data01 != @select_tags_tb0@ then return -1 endi if $data11 != @select_tags_tb1@ then return -1 endi if $data02 != 0 then return -1 endi if $data03 != @abc0@ then return -1 endi if $data04 != @select_tags_tb0@ then return -1 endi sql select last_row(ts,c1), tbname, t1, t2 from select_tags_mt0 group by tbname; if $rows != 16 then return -1 endi if $data00 != @70-01-01 08:01:40.799@ then return -1 endi if $data10 != @70-01-01 08:01:50.800@ then return -1 endi print $data01 if $data01 != 99 then return -1 endi if $data11 != 100 then return -1 endi if $data02 != @select_tags_tb0@ then return -1 endi if $data03 != 0 then return -1 endi if $data04 != @abc0@ then return -1 endi sql select tbname,t1,t2 from select_tags_mt0; if $row != 16 then return -1 endi if $data00 != @select_tags_tb0@ then return -1 endi if $data01 != 0 then return -1 endi if $data02 != @abc0@ then return -1 endi if $data10 != @select_tags_tb1@ then return -1 endi if $data11 != 1 then return -1 endi if $data12 != @abc1@ then return -1 endi sql select tbname,ts from select_tags_mt0; if $row != 12800 then return -1 endi if $data00 != @select_tags_tb0@ then return -1 endi if $data10 != @select_tags_tb0@ then return -1 endi if $data01 != @70-01-01 08:01:40.000@ then return -1 endi if $data11 != @70-01-01 08:01:40.001@ then return -1 endi sql select top(c1, 100), tbname, t1, t2 from select_tags_mt0 where tbname in ('select_tags_tb0', 'select_tags_tb1') group by tbname; if $row != 200 then return -1 endi if $data00 != @70-01-01 08:01:40.087@ then return -1 endi if $data10 != @70-01-01 08:01:40.088@ then return -1 endi if $data20 != @70-01-01 08:01:40.089@ then return -1 endi if $data90 != @70-01-01 08:01:40.096@ then return -1 endi if $data01 != 87 then return -1 endi if $data02 != @select_tags_tb0@ then return -1 endi if $data03 != 0 then return -1 endi if $data04 != @abc0@ then return -1 endi sql select top(c1, 2), t2 from select_tags_mt0 where tbname in ('select_tags_tb0', 'select_tags_tb1') group by tbname,t2; if $row != 4 then return -1 endi if $data00 != @70-01-01 08:01:40.099@ then return -1 endi if $data01 != 99 then return -1 endi if $data02 != @abc0@ then return -1 endi if $data03 != @select_tags_tb0@ then return -1 endi if $data04 != @abc0@ then return -1 endi if $data10 != @70-01-01 08:01:40.199@ then return -1 endi if $data11 != 99 then return -1 endi if $data12 != @abc0@ then return -1 endi if $data13 != @select_tags_tb0@ then return -1 endi if $data14 != @abc0@ then return -1 endi if $data20 != @70-01-01 08:01:50.100@ then return -1 endi if $data21 != 100 then return -1 endi if $data22 != @abc1@ then return -1 endi if $data23 != @select_tags_tb1@ then return -1 endi if $data24 != @abc1@ then return -1 endi if $data30 != @70-01-01 08:01:50.200@ then return -1 endi if $data31 != 100 then return -1 endi if $data32 != @abc1@ then return -1 endi if $data33 != @select_tags_tb1@ then return -1 endi if $data34 != @abc1@ then return -1 endi # slimit /limit sql select top(c1, 2), t2 from select_tags_mt0 where tbname in ('select_tags_tb0', 'select_tags_tb1') group by tbname,t2 limit 2 offset 1; if $row != 2 then return -1 endi if $data00 != @70-01-01 08:01:40.199@ then return -1 endi if $data01 != 99 then return -1 endi if $data02 != @abc0@ then return -1 endi if $data03 != @select_tags_tb0@ then return -1 endi if $data04 != @abc0@ then return -1 endi print ======= selectivity + tags + group by + tags + filter =========================== sql select first(c1), t1 from select_tags_mt0 where c1<=2 group by tbname; if $row != 3 then return -1 endi if $data00 != 0 then return -1 endi if $data01 != 0 then return -1 endi if $data02 != @select_tags_tb0@ then return -1 endi if $data10 != 1 then return -1 endi if $data11 != 1 then return -1 endi if $data12 != @select_tags_tb1@ then return -1 endi if $data20 != 2 then return -1 endi if $data21 != 2 then return -1 endi if $data22 != @select_tags_tb2@ then return -1 endi sql select first(c1), tbname from select_tags_mt0 where c1<=2 interval(1s); if $row != 3 then return -1 endi if $data00 != @70-01-01 08:01:40.000@ then return -1 endi if $data01 != 0 then return -1 endi if $data02 != @select_tags_tb0@ then return -1 endi if $data10 != @70-01-01 08:01:50.000@ then return -1 endi if $data11 != 1 then return -1 endi if $data12 != @select_tags_tb1@ then return -1 endi if $data20 != @70-01-01 08:02:00.000@ then return -1 endi if $data21 != 2 then return -1 endi if $data22 != @select_tags_tb2@ then return -1 endi sql select first(ts),ts from select_tags_tb0 where c1<3 if $row != 1 then return -1 endi if $data00 != $data01 then return -1 endi sql select last(ts),ts from select_tags_tb0 where c1<3 if $row != 1 then return -1 endi if $data00 != $data01 then return -1 endi sql select first(ts), ts from select_tags_tb1 if $row != 1 then return -1 endi if $data01 != @70-01-01 08:01:50.001@ then return -1 endi print ======= selectivity + tags + group by + tags + filter + interval ================ sql select first(c1), t2, t1, tbname from select_tags_mt0 where c1<=2 interval(1d) group by tbname; if $row != 3 then return -1 endi if $data00 != @70-01-01 00:00:00.000@ then return -1 endi if $data01 != 0 then return -1 endi if $data02 != @abc0@ then return -1 endi if $data03 != 0 then return -1 endi if $data04 != @select_tags_tb0@ then return -1 endi if $data05 != @select_tags_tb0@ then return -1 endi if $data15 != @select_tags_tb1@ then return -1 endi if $data25 != @select_tags_tb2@ then return -1 endi sql select top(c1, 5), t2 from select_tags_mt0 where c1<=2 interval(1d) group by tbname; if $row != 15 then return -1 endi if $data00 != @70-01-01 08:01:40.002@ then return -1 endi if $data01 != 2 then return -1 endi if $data02 != @abc0@ then return -1 endi if $data03 != @select_tags_tb0@ then return -1 endi if $data90 != @70-01-01 08:01:50.402@ then return -1 endi if $data91 != 2 then return -1 endi if $data92 != @abc1@ then return -1 endi if $data93 != @select_tags_tb1@ then return -1 endi #if data sql select top(c1, 50), t2, t1, tbname from select_tags_mt0 where c1<=2 interval(1d) group by tbname; if $row != 48 then return -1 endi if $data00 != @70-01-01 08:01:40.000@ then return -1 endi if $data01 != 0 then return -1 endi if $data02 != @abc0@ then return -1 endi if $data03 != 0 then return -1 endi if $data04 != @select_tags_tb0@ then return -1 endi if $data05 != @select_tags_tb0@ then return -1 endi if $data10 != @70-01-01 08:01:40.001@ then return -1 endi if $data11 != 1 then return -1 endi if $data12 != @abc0@ then return -1 endi if $data13 != 0 then return -1 endi if $data14 != @select_tags_tb0@ then return -1 endi if $data15 != @select_tags_tb0@ then return -1 endi if $data90 != @70-01-01 08:01:40.300@ then return -1 endi if $data91 != 0 then return -1 endi if $data92 != @abc0@ then return -1 endi if $data93 != 0 then return -1 endi if $data94 != @select_tags_tb0@ then return -1 endi if $data95 != @select_tags_tb0@ then return -1 endi sql select last(ts),TBNAME from select_tags_mt0 interval(1y) if $row != 1 then return -1 endi print ======= selectivity + tags+ group by + tags + filter + interval + join=========== print ======error sql============================================= sql_error select first(*), tbname from select_tags_mt0; sql_error select first(ts), first(c1),tbname from select_tags_mt0; sql_error select first(ts), last(ts), tbname from select_tags_mt0; sql_error select last_row(*), first(ts), tbname, t1, t2 from select_tags_mt0; sql_error select tbname, last_row(*), t1, first(ts) from select_tags_mt0; sql_error select first(ts), tbname from select_tags_tb0; sql_error select last_row(*), t1 from select_tags_tb0; sql_error select count(*), tbname from select_tags_mt0; sql_error select sum(c2), tbname from select_tags_mt0; sql_error select avg(c3), tbname from select_tags_mt0; sql_error select percentile(c3, 50), tbname from select_tags_mt0; sql_error select apercentile(c4, 50), tbname from select_tags_mt0; sql_error select spread(c2), tbname, t1 from select_tags_mt0; sql_error select stddev(c2), tbname from select_tags_mt0; sql_error select twa(c2), tbname from select_tags_mt0; sql_error select interp(c2), tbname from select_tags_mt0 where ts=100001; sql_error select t1,t2,tbname from select_tags_mt0 group by tbname; sql_error select count(tbname) from select_tags_mt0 interval(1d); sql_error select count(tbname) from select_tags_mt0 group by t1; sql_error select count(tbname),SUM(T1) from select_tags_mt0 interval(1d); sql_error select first(c1), count(*), t2, t1, tbname from select_tags_mt0 where c1<=2 interval(1d) group by tbname; sql_error select ts from select_tags_mt0 interval(1y); sql_error select count(*), tbname from select_tags_mt0 interval(1y); sql_error select tbname, t1 from select_tags_mt0 interval(1y); #===error sql + group by =============================================== #valid sql: select first(c1), last(c2), tbname from select_tags_mt0 group by tbname; #valid sql: select first(c1), last(c2), count(*), tbname from select_tags_mt0 group by tbname; #valid sql: select first(c1), last(c2), count(*) from select_tags_mt0 group by tbname, t1; #valid sql: select first(c1), tbname, t1 from select_tags_mt0 group by t2; sql_error select first(c1), last(c2), t1 from select_tags_mt0 group by tbname; sql_error select first(c1), last(c2), tbname, t2 from select_tags_mt0 group by tbname; sql_error select first(c1), count(*), t2, t1, tbname from select_tags_mt0 group by tbname; # this sql is valid: select first(c1), t2 from select_tags_mt0 group by tbname; #sql select first(ts), tbname from select_tags_mt0 group by tbname; #sql select count(c1) from select_tags_mt0 where c1=99 group by tbname; #sql select count(*),tbname from select_tags_mt0 group by tbname system sh/exec.sh -n dnode1 -s stop -x SIGINT