github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/sample/sample_func.result (about) 1 /* data prepare for sample from single column */ 2 drop table if exists s_t1; 3 create table s_t1 (c1 int, c2 int, c3 int); 4 insert into s_t1 values (1, 1, 0), (1, 1, 1), (1, 1, 2), (1, 1, null); 5 insert into s_t1 values (2, 1, 0); 6 insert into s_t1 values (2, 2, 0), (2, 2, null); 7 insert into s_t1 values (3, 1, null), (3, 2, null), (3, 2, null); 8 /* expected failed case */ 9 /* 1. only support one sample function in a sql */ 10 select c1, sample(c2, 1 rows), sample(c3, 1 rows) from s_t1; 11 SQL syntax error: cannot use more than one sample function at select clause. 12 /* 2. cannot fixed sample function (non-scalar function) and aggregate function (scalar function) */ 13 select c1, max(c2), sample(c3, 1 rows) from s_t1; 14 SQL syntax error: cannot fixed non-scalar function and scalar function in the same query 15 /* 3. sample(column list, N rows) requires 1 <= N <= 11000 */ 16 select sample(c1, 0 rows) from s_t1; 17 SQL syntax error: sample(expr list, N rows) requires N between 1 and 11000. 18 select sample(c1, 11001 rows) from s_t1; 19 SQL syntax error: sample(expr list, N rows) requires N between 1 and 11000. 20 /* 4. sample(column list, K percent) requires 0.00 <= K <= 100.00, should watch that 99.994 was accepted but will treat as 99.99 */ 21 select sample(c1, 101 percent) from s_t1; 22 SQL syntax error: sample(expr list, K percent) requires K between 0.00 and 100.00 23 select sample(c1, 100.01 percent) from s_t1; 24 SQL syntax error: sample(expr list, K percent) requires K between 0.00 and 100.00 25 /* 5. sample cannot be used in where clause */ 26 select c1, c2, c3 from s_t1 where sample(c1, 1 rows) = 1; 27 expr 'sample' is not yet implemented 28 select c1, c2, c3 from s_t1 where sample(c1, 1 percent) = 1; 29 expr 'sample' is not yet implemented 30 /* 6. cannot sample the group by column */ 31 select sample(c1, 1 rows) from s_t1 group by c1; 32 internal error: cannot sample the group by column. 33 select sample(*, 1 rows) from s_t1 group by c1; 34 internal error: cannot sample the group by column. 35 /* expected succeed case */ 36 /* 1. sample 2 rows from table by column c1 */ 37 select count(*) from (select sample(c1, 2 rows) from s_t1); 38 count(*) 39 2 40 /* 2. sample 2 rows from table by column c2 */ 41 select count(*) from (select sample(c2, 2 rows) from s_t1); 42 count(*) 43 2 44 /* 3. sample 2 rows from table by column c2 group by col1 */ 45 select count(*) from (select sample(c2, 2 rows) from s_t1 group by c1); 46 count(*) 47 6 48 /* 4. sample 3 rows from table by column c3 where c1 = 2, expected to get only 2 rows because one row's c3 is null */ 49 select sample(c3, 3 rows) from s_t1 where c1 = 2; 50 c3 51 0 52 0 53 /* 5. sample 3 rows from table by column c3 where c1 = 1, expected to get 3 rows (0),(1),(2) */ 54 select c1, c2, sample(c3, 3 rows) from s_t1 where c1 = 1 order by c3; 55 c1 c2 c3 56 1 1 0 57 1 1 1 58 1 1 2 59 /* 6. sample 100 percent from table by column c3, expected to get all rows except null rows */ 60 select sample(c3, 100 percent) from s_t1 order by c3; 61 c3 62 0 63 0 64 0 65 1 66 2 67 /* 7. sample 0 percent from table by column c1, expected to get empty */ 68 select sample(c1, 0 percent) from s_t1; 69 c1 70 /* 8. some case I don't know how to describe it, in short, these cases should be OK */ 71 select count(*) from (select c1, c2, sample(c2, 100 percent), c3 from s_t1); 72 count(*) 73 10 74 select count(*) from (select c1, sample(c2, 100 percent), c2, c3 from s_t1); 75 count(*) 76 10 77 /* 9. with limit */ 78 select count(*) from (select sample(c2, 2 rows) from s_t1 limit 1); 79 count(*) 80 1 81 select count(*) from (select sample(c2, 2 rows) from s_t1 group by c1 limit 2); 82 count(*) 83 2 84 /* 10. with alias */ 85 select sample(c3, 3 rows) as k from s_t1 where c1 = 2; 86 k 87 0 88 0 89 /* 11. sample from all invalid rows, should get only one invalid row */ 90 select sample(c3, 1 rows) from s_t1 where c1 = 3; 91 c3 92 null 93 select sample(c3, 2 rows) from s_t1 where c1 = 3; 94 c3 95 null 96 select c1, sample(c3, 3 rows) from s_t1 where c1 = 3 group by c1; 97 c1 c3 98 3 null 99 select c1, sample(c3, 3 rows) from s_t1 group by c1 order by c1, c3; 100 c1 c3 101 1 0 102 1 1 103 1 2 104 2 0 105 2 0 106 3 null 107 /* 12. sample as and outer filter */ 108 select * from (select c1, sample(c3, 3 rows) as k from s_t1 group by c1) where k < 2 order by c1, k; 109 c1 k 110 1 0 111 1 1 112 2 0 113 2 0 114 /* data prepare for sample from multi columns */ 115 drop table if exists s_t2; 116 create table s_t2 (cc1 int, cc2 int); 117 insert into s_t2 values (1, 1), (null, 2), (3, null), (null, null); 118 /* expected failed case */ 119 /* 1. multi sample with alias */ 120 select sample(cc1, cc2, 1 rows) as k from s_t2; 121 SQL syntax error: sample multi columns cannot have alias 122 /* expected succeed case */ 123 /* 1. sample 2 rows from table by column cc1, cc2, expected to get 3 rows because we should sample 2 not-null value for each column */ 124 select count(*) from (select sample(cc1, cc2, 2 rows) from s_t2); 125 count(*) 126 3 127 /* 2. sample 100 percent from table by column cc1, cc2, expected to get all rows except (null, null) */ 128 select sample(cc1, cc2, 100 percent) from s_t2 order by cc1 asc; 129 cc1 cc2 130 null 2 131 1 1 132 3 null 133 /* 3. sample 0 percent from table by column cc1, cc2, expected to get empty */ 134 select sample(cc1, cc2, 0 percent) from s_t2; 135 cc1 cc2 136 /* 4. should support the sample * from table */ 137 select sample(*, 100 percent) from s_t2 order by cc1 asc; 138 cc1 cc2 139 null 2 140 1 1 141 3 null 142 select sample(*, 0 percent) from s_t2; 143 cc1 cc2 144 select sample(*, 2 rows) from s_t2 order by cc1 asc; 145 cc1 cc2 146 null 2 147 1 1 148 3 null 149 /* data prepare for expression sample */ 150 drop table if exists s_t3; 151 create table s_t3 (c1 int, c2 int); 152 insert into s_t3 values (1, 3), (2, 5), (3, 6), (4, 7), (5, 8); 153 /* expected failed case */ 154 /* 1. sample(aggregate function, N rows) should failed */ 155 select sample(max(c1), 1 rows) from s_t3; 156 SQL syntax error: cannot fixed non-scalar function and scalar function in the same query 157 /* expected succeed case */ 158 /* 1. expression (not only simple column) should be OK */ 159 select c1, sample(c1+1, 100 percent) from s_t3 order by c1; 160 c1 c1 + 1 161 1 2 162 2 3 163 3 4 164 4 5 165 5 6 166 select count(*) from (select sample(c1, 2 rows, 'row') from s_t3); 167 count(*) 168 2 169 select count(*) from (select sample(c1, 2 rows, 'block') from s_t3); 170 count(*) 171 2 172 select c1, sample(c2, 1 rows, 'row') from s_t3 group by c1 order by c1; 173 c1 c2 174 1 3 175 2 5 176 3 6 177 4 7 178 5 8