github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/hash_join (about) 1 statement ok 2 CREATE TABLE t1 (k INT PRIMARY KEY, v INT) 3 4 statement ok 5 INSERT INTO t1 VALUES (0, 4), (2, 1), (5, 4), (3, 4), (-1, -1) 6 7 statement ok 8 CREATE TABLE t2 (x INT PRIMARY KEY, y INT) 9 10 statement ok 11 INSERT INTO t2 VALUES (1, 3), (4, 6), (0, 5), (3, 2) 12 13 statement ok 14 CREATE TABLE a (k INT, v INT) 15 16 statement ok 17 INSERT INTO a VALUES (0, 1), (1, 2), (2, 0) 18 19 statement ok 20 CREATE TABLE b (a INT, b INT, c STRING) 21 22 statement ok 23 INSERT INTO b VALUES (0, 1, 'a'), (2, 1, 'b'), (0, 2, 'c'), (0, 1, 'd') 24 25 statement ok 26 CREATE TABLE c (a INT, b STRING) 27 28 statement ok 29 INSERT INTO c VALUES (1, 'a'), (1, 'b'), (2, 'c') 30 31 query IIII 32 SELECT * FROM t1 INNER HASH JOIN t2 ON t1.k = t2.x ORDER BY 1 33 ---- 34 0 4 0 5 35 3 4 3 2 36 37 query IIII 38 SELECT * FROM a AS a1 JOIN a AS a2 ON a1.k = a2.v ORDER BY 1 39 ---- 40 0 1 2 0 41 1 2 0 1 42 2 0 1 2 43 44 query IIII 45 SELECT * FROM a AS a2 JOIN a AS a1 ON a1.k = a2.v ORDER BY 1 46 ---- 47 0 1 1 2 48 1 2 2 0 49 2 0 0 1 50 51 query II 52 SELECT t2.y, t1.v FROM t1 INNER HASH JOIN t2 ON t1.k = t2.x ORDER BY 1 DESC 53 ---- 54 5 4 55 2 4 56 57 query IIII 58 SELECT * FROM t1 JOIN t2 ON t1.v = t2.x ORDER BY 1 59 ---- 60 0 4 4 6 61 2 1 1 3 62 3 4 4 6 63 5 4 4 6 64 65 query IIII 66 SELECT * FROM t1 LEFT JOIN t2 ON t1.v = t2.x ORDER BY 1 67 ---- 68 -1 -1 NULL NULL 69 0 4 4 6 70 2 1 1 3 71 3 4 4 6 72 5 4 4 6 73 74 query IIII rowsort 75 SELECT * FROM t1 RIGHT JOIN t2 ON t1.v = t2.x 76 ---- 77 0 4 4 6 78 2 1 1 3 79 3 4 4 6 80 5 4 4 6 81 NULL NULL 0 5 82 NULL NULL 3 2 83 84 query IIII rowsort 85 SELECT * FROM t1 FULL JOIN t2 ON t1.v = t2.x 86 ---- 87 -1 -1 NULL NULL 88 0 4 4 6 89 2 1 1 3 90 3 4 4 6 91 5 4 4 6 92 NULL NULL 3 2 93 NULL NULL 0 5 94 95 query IIT 96 SELECT b.a, b.b, b.c FROM b JOIN a ON b.a = a.k AND a.v = b.b ORDER BY 3 97 ---- 98 0 1 a 99 0 1 d 100 101 query ITI 102 SELECT b.a, b.c, c.a FROM b JOIN c ON b.b = c.a AND b.c = c.b ORDER BY 2 103 ---- 104 0 a 1 105 2 b 1 106 0 c 2 107 108 # Test hash join with an empty build table. 109 statement ok 110 CREATE TABLE empty (x INT) 111 112 statement ok 113 CREATE TABLE onecolumn (x INT); INSERT INTO onecolumn(x) VALUES (44), (NULL), (42) 114 115 query II colnames 116 SELECT * FROM empty AS a(x) FULL OUTER JOIN onecolumn AS b(y) ON a.x = b.y ORDER BY b.y 117 ---- 118 x y 119 NULL NULL 120 NULL 42 121 NULL 44 122 123 # Regression test for #41407. 124 statement ok 125 CREATE TABLE t41407 AS 126 SELECT 127 g AS _float8, 128 g % 0 = 0 AS _bool, 129 g AS _decimal, 130 g AS _string, 131 g AS _bytes 132 FROM 133 generate_series(NULL, NULL) AS g; 134 135 query TRTTRRBR 136 SELECT 137 tab_1688._bytes, 138 tab_1688._float8, 139 tab_1689._string, 140 tab_1689._string, 141 tab_1688._float8, 142 tab_1688._float8, 143 tab_1689._bool, 144 tab_1690._decimal 145 FROM 146 t41407 AS tab_1688 147 JOIN t41407 AS tab_1689 148 JOIN t41407 AS tab_1690 ON 149 tab_1689._bool = tab_1690._bool ON 150 tab_1688._float8 = tab_1690._float8 151 AND tab_1688._bool = tab_1689._bool; 152 ---- 153 154 # Regression test for empty equality columns with one of the tables having only 155 # UNIQUE columns. 156 statement ok 157 CREATE TABLE t44207_0(c0 INT UNIQUE); CREATE TABLE t44207_1(c0 INT) 158 159 statement ok 160 INSERT INTO t44207_0(c0) VALUES (NULL), (NULL); INSERT INTO t44207_1(c0) VALUES (0) 161 162 query II 163 SELECT * FROM t44207_0, t44207_1 WHERE t44207_0.c0 IS NULL 164 ---- 165 NULL 0 166 NULL 0 167 168 # Regression test for the inputs that have comparable but different types (see 169 # issues #44547 and #44797). 170 statement ok 171 CREATE TABLE t44547_0(c0 INT4); CREATE TABLE t44547_1(c0 INT8) 172 173 statement ok 174 INSERT INTO t44547_0(c0) VALUES(0); INSERT INTO t44547_1(c0) VALUES(0) 175 176 # Note that integers of different width are still considered equal. 177 query I 178 SELECT * FROM t44547_0 NATURAL JOIN t44547_1 179 ---- 180 0 181 182 statement ok 183 CREATE TABLE t44797_0(a FLOAT, b DECIMAL); CREATE TABLE t44797_1(c INT2, d INT4) 184 185 statement ok 186 INSERT INTO t44797_0 VALUES (1.0, 1.0), (2.0, 2.0); INSERT INTO t44797_1 VALUES (1, 1), (2, 2) 187 188 # Note that mixed-type comparisons - of what appears to be "same" values - do 189 # not consider those values equal. 190 query RRII rowsort 191 SELECT * FROM t44797_0 NATURAL JOIN t44797_1 192 ---- 193 1 1.0 2 2 194 1 1.0 1 1 195 2 2.0 2 2 196 2 2.0 1 1 197 198 statement ok 199 CREATE TABLE t44797_2(a FLOAT); CREATE TABLE t44797_3(b DECIMAL) 200 201 statement ok 202 INSERT INTO t44797_2 VALUES (1.0), (2.0); INSERT INTO t44797_3 VALUES (1.0), (2.0) 203 204 query RR rowsort 205 SELECT * FROM t44797_2 NATURAL JOIN t44797_3 206 ---- 207 1 2.0 208 1 1.0 209 2 2.0 210 2 1.0