github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/distsql_numtables (about) 1 # LogicTest: 5node-default-configs 2 3 # First, we set up two data tables: 4 # - NumToSquare maps integers from 1 to 100 to their squares 5 # - NumToStr maps integers from 1 to 100*100 to strings; this table is 6 # split and distributed to all nodes. 7 statement ok 8 CREATE TABLE NumToSquare (x INT PRIMARY KEY, xsquared INT) 9 10 statement ok 11 INSERT INTO NumToSquare SELECT i, i*i FROM generate_series(1, 100) AS g(i) 12 13 statement ok 14 CREATE TABLE NumToStr (y INT PRIMARY KEY, str STRING) 15 16 # Split into five parts. 17 statement ok 18 ALTER TABLE NumToStr SPLIT AT SELECT (i * 100 * 100 / 5)::int FROM generate_series(1, 4) AS g(i) 19 20 # Relocate the five parts to the five nodes. 21 statement ok 22 ALTER TABLE NumToStr EXPERIMENTAL_RELOCATE 23 SELECT ARRAY[i+1], (i * 100 * 100 / 5)::int FROM generate_series(0, 4) AS g(i) 24 25 statement ok 26 INSERT INTO NumToStr SELECT i, to_english(i) FROM generate_series(1, 100*100) AS g(i) 27 28 # Verify data placement. 29 query TTTI colnames 30 SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE NumToSquare] 31 ---- 32 start_key end_key replicas lease_holder 33 NULL NULL {1} 1 34 35 query TTTI colnames 36 SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE NumToStr] 37 ---- 38 start_key end_key replicas lease_holder 39 NULL /2000 {1} 1 40 /2000 /4000 {2} 2 41 /4000 /6000 {3} 3 42 /6000 /8000 {4} 4 43 /8000 NULL {5} 5 44 45 # 46 # -- Basic tests -- 47 # 48 49 # Query with a restricted span. 50 51 query IIIT 52 SELECT 5, 2+y, * FROM NumToStr WHERE y <= 10 ORDER BY str 53 ---- 54 5 10 8 eight 55 5 7 5 five 56 5 6 4 four 57 5 11 9 nine 58 5 3 1 one 59 5 12 10 one-zero 60 5 9 7 seven 61 5 8 6 six 62 5 5 3 three 63 5 4 2 two 64 65 66 # Query which requires a full table scan. 67 query IIIT 68 SELECT 5, 2 + y, * FROM NumToStr WHERE y % 1000 = 0 ORDER BY str 69 ---- 70 5 8002 8000 eight-zero-zero-zero 71 5 5002 5000 five-zero-zero-zero 72 5 4002 4000 four-zero-zero-zero 73 5 9002 9000 nine-zero-zero-zero 74 5 1002 1000 one-zero-zero-zero 75 5 10002 10000 one-zero-zero-zero-zero 76 5 7002 7000 seven-zero-zero-zero 77 5 6002 6000 six-zero-zero-zero 78 5 3002 3000 three-zero-zero-zero 79 5 2002 2000 two-zero-zero-zero 80 81 # Query with a restricted span + filter. 82 query T 83 SELECT str FROM NumToStr WHERE y < 10 AND str LIKE '%e%' ORDER BY y 84 ---- 85 one 86 three 87 five 88 seven 89 eight 90 nine 91 92 # Query which requires a full table scan. 93 query T 94 SELECT str FROM NumToStr WHERE y % 1000 = 0 AND str LIKE '%i%' ORDER BY y 95 ---- 96 five-zero-zero-zero 97 six-zero-zero-zero 98 eight-zero-zero-zero 99 nine-zero-zero-zero 100 101 102 # 103 # -- Join tests -- 104 # 105 106 # Save the result of the following statement to a label. 107 query IT rowsort label-sq-str 108 SELECT i, to_english(i*i) FROM generate_series(1, 100) AS g(i) 109 110 # Compare the results of this query to the one above. 111 query IT rowsort label-sq-str 112 SELECT x, str FROM NumToSquare JOIN NumToStr ON y = xsquared 113 114 # Save the result of the following statement to a label. 115 query IT rowsort label-sq-2-str 116 SELECT 2*i, to_english(2*i) FROM generate_series(1, 50) AS g(i) 117 118 # Compare the results of this query to the one above. 119 query IT rowsort label-sq-2-str 120 SELECT x, str FROM NumToSquare JOIN NumToStr ON x = y WHERE x % 2 = 0 121 122 123 # 124 # -- Aggregation tests -- 125 # 126 127 # Sum the numbers in the NumToStr table. The expected result is 128 # n * n * (n * n + 1) / 2 129 query R 130 SELECT sum(y) FROM NumToStr 131 ---- 132 50005000 133 134 # Count the rows in the NumToStr table. 135 query I 136 SELECT count(*) FROM NumToStr 137 ---- 138 10000 139 140 # Count how many numbers contain the digit 5. 141 # Result calculated here: https://play.golang.org/p/e-YsJRDsXF 142 query I 143 SELECT count(*) FROM NumToStr WHERE str LIKE '%five%' 144 ---- 145 3439 146 147 148 # 149 # -- Limit tests -- 150 # 151 152 query I 153 SELECT y FROM NumToStr ORDER BY y LIMIT 5 154 ---- 155 1 156 2 157 3 158 4 159 5 160 161 query I 162 SELECT y FROM NumToStr WHERE y < 1000 OR y > 9000 ORDER BY y DESC LIMIT 5 163 ---- 164 10000 165 9999 166 9998 167 9997 168 9996 169 170 query I 171 SELECT y FROM NumToStr ORDER BY y OFFSET 5 LIMIT 2 172 ---- 173 6 7 174 175 query I 176 SELECT y FROM NumToStr ORDER BY y LIMIT 0 177 ---- 178 179 query I 180 SELECT * FROM (SELECT y FROM NumToStr LIMIT 3) AS a ORDER BY y OFFSET 3 181 ---- 182 183 query I 184 SELECT y FROM NumToStr ORDER BY str LIMIT 5 185 ---- 186 8 187 88 188 888 189 8888 190 8885 191 192 query I 193 SELECT y FROM (SELECT y FROM NumToStr ORDER BY y LIMIT 5) AS a WHERE y <> 2 194 ---- 195 1 196 3 197 4 198 5 199 200 # Regression test for #20481. 201 query I 202 SELECT count(*) FROM (SELECT 1 AS one FROM NumToSquare WHERE x > 10 ORDER BY xsquared LIMIT 10) 203 ---- 204 10