github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/where (about) 1 statement ok 2 CREATE TABLE kv ( 3 k INT PRIMARY KEY, 4 v INT 5 ) 6 7 statement ok 8 INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8) 9 10 statement ok 11 CREATE TABLE kvString ( 12 k STRING PRIMARY KEY, 13 v STRING 14 ) 15 16 statement ok 17 INSERT INTO kvString VALUES ('like1', 'hell%'), ('like2', 'worl%') 18 19 query II rowsort 20 SELECT * FROM kv WHERE True 21 ---- 22 1 2 23 3 4 24 5 6 25 7 8 26 27 query II 28 SELECT * FROM kv WHERE False 29 ---- 30 31 query II rowsort 32 SELECT * FROM kv WHERE k IN (1, 3) 33 ---- 34 1 2 35 3 4 36 37 query II 38 SELECT * FROM kv WHERE v IN (6) 39 ---- 40 5 6 41 42 query II rowsort 43 SELECT * FROM kv WHERE k IN (SELECT k FROM kv) 44 ---- 45 1 2 46 3 4 47 5 6 48 7 8 49 50 query II rowsort 51 SELECT * FROM kv WHERE (k,v) IN (SELECT * FROM kv) 52 ---- 53 1 2 54 3 4 55 5 6 56 7 8 57 58 query error column "nonexistent" does not exist 59 SELECT * FROM kv WHERE nonexistent = 1 60 61 query B 62 SELECT 'hello' LIKE v FROM kvString WHERE k LIKE 'like%' ORDER BY k 63 ---- 64 true 65 false 66 67 query B 68 SELECT 'hello' SIMILAR TO v FROM kvString WHERE k SIMILAR TO 'like[1-2]' ORDER BY k 69 ---- 70 true 71 false 72 73 query B 74 SELECT 'hello' ~ replace(v, '%', '.*') FROM kvString WHERE k ~ 'like[1-2]' ORDER BY k 75 ---- 76 true 77 false 78 79 # Test mixed type tuple comparison. 80 81 query II rowsort 82 SELECT * FROM kv WHERE k IN (1, 5.0, 9) 83 ---- 84 1 2 85 5 6 86 87 # Regression tests for #22670. 88 statement ok 89 CREATE TABLE ab (a INT, b INT) 90 91 statement ok 92 INSERT INTO ab VALUES (1, 10), (2, 20), (3, 30), (4, NULL), (NULL, 50), (NULL, NULL) 93 94 query II rowsort 95 SELECT * FROM ab WHERE a IN (1, 3, 4) 96 ---- 97 1 10 98 3 30 99 4 NULL 100 101 query II rowsort 102 SELECT * FROM ab WHERE a IN (1, 3, 4, NULL) 103 ---- 104 1 10 105 3 30 106 4 NULL 107 108 query II rowsort 109 SELECT * FROM ab WHERE (a, b) IN ((1, 10), (3, 30), (4, 40)) 110 ---- 111 1 10 112 3 30 113 114 query II rowsort 115 SELECT * FROM ab WHERE (a, b) IN ((1, 10), (4, NULL), (NULL, 50)) 116 ---- 117 1 10