github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/scan (about) 1 exec-ddl 2 CREATE TABLE a (x INT PRIMARY KEY, y INT, s STRING, d DECIMAL NOT NULL, UNIQUE (s DESC, d)) 3 ---- 4 5 exec-ddl 6 CREATE TABLE b (x INT, z INT NOT NULL) 7 ---- 8 9 build 10 SELECT * FROM a 11 ---- 12 scan a 13 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 14 ├── key: (1) 15 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 16 ├── prune: (1-4) 17 └── interesting orderings: (+1) (-3,+4,+1) 18 19 build 20 SELECT * FROM b 21 ---- 22 project 23 ├── columns: x:1(int) z:2(int!null) 24 ├── prune: (1,2) 25 └── scan b 26 ├── columns: x:1(int) z:2(int!null) rowid:3(int!null) 27 ├── key: (3) 28 ├── fd: (3)-->(1,2) 29 ├── prune: (1-3) 30 └── interesting orderings: (+3) 31 32 # Select subset of columns. 33 opt 34 SELECT s, x FROM a 35 ---- 36 scan a@secondary 37 ├── columns: s:3(string) x:1(int!null) 38 ├── key: (1) 39 ├── fd: (1)-->(3) 40 ├── prune: (1,3) 41 └── interesting orderings: (+1) (-3) 42 43 # Test constrained scan. 44 opt 45 SELECT s, x FROM a WHERE x=1 46 ---- 47 scan a 48 ├── columns: s:3(string) x:1(int!null) 49 ├── constraint: /1: [/1 - /1] 50 ├── cardinality: [0 - 1] 51 ├── key: () 52 ├── fd: ()-->(1,3) 53 ├── prune: (3) 54 └── interesting orderings: (+1) (-3) 55 56 # Test limited scan. 57 opt 58 SELECT s, x FROM a WHERE x > 1 LIMIT 2 59 ---- 60 scan a 61 ├── columns: s:3(string) x:1(int!null) 62 ├── constraint: /1: [/2 - ] 63 ├── limit: 2 64 ├── key: (1) 65 ├── fd: (1)-->(3) 66 ├── prune: (3) 67 └── interesting orderings: (+1) (-3) 68 69 # Test limited scan with 1 row. 70 opt 71 SELECT s, x FROM a WHERE x > 1 LIMIT 1 72 ---- 73 scan a 74 ├── columns: s:3(string) x:1(int!null) 75 ├── constraint: /1: [/2 - ] 76 ├── limit: 1 77 ├── key: () 78 ├── fd: ()-->(1,3) 79 ├── prune: (3) 80 └── interesting orderings: (+1) (-3) 81 82 # Test case where there are no weak keys available. 83 opt 84 SELECT d FROM a 85 ---- 86 scan a@secondary 87 ├── columns: d:4(decimal!null) 88 └── prune: (4) 89 90 exec-ddl 91 CREATE TABLE t ( 92 a INT, 93 b CHAR, 94 c INT, 95 d CHAR, 96 PRIMARY KEY (a, b), 97 INDEX bc (b, c), 98 INDEX dc (d, c), 99 INDEX a_desc (a DESC), 100 FAMILY (a, b), 101 FAMILY (c), 102 FAMILY (d) 103 ) 104 ---- 105 106 opt 107 SELECT 1 FROM t WHERE a > 1 AND a < 2 108 ---- 109 values 110 ├── columns: "?column?":5(int!null) 111 ├── cardinality: [0 - 0] 112 ├── key: () 113 ├── fd: ()-->(5) 114 └── prune: (5) 115 116 opt 117 SELECT * FROM t@bc WHERE b IN ('a', 'b') AND c IN (1, 2) AND a IN (2, 3) 118 ---- 119 index-join t 120 ├── columns: a:1(int!null) b:2(char!null) c:3(int!null) d:4(char) 121 ├── key: (1,2) 122 ├── fd: (1,2)-->(3,4) 123 ├── prune: (4) 124 ├── interesting orderings: (+1,+2) (+2,+3,+1) (-1,+2) 125 └── scan t@bc 126 ├── columns: a:1(int!null) b:2(char!null) c:3(int!null) 127 ├── constraint: /2/3/1 128 │ ├── [/'a'/1/2 - /'a'/1/3] 129 │ ├── [/'a'/2/2 - /'a'/2/3] 130 │ ├── [/'b'/1/2 - /'b'/1/3] 131 │ └── [/'b'/2/2 - /'b'/2/3] 132 ├── flags: force-index=bc 133 ├── cardinality: [0 - 8] 134 ├── key: (1,2) 135 ├── fd: (1,2)-->(3) 136 ├── prune: (1-3) 137 └── interesting orderings: (+1,+2) (+2,+3,+1) (-1,+2) 138 139 opt 140 SELECT * FROM a WHERE x IN (1, 2, 4, 6, 7, 9) 141 ---- 142 scan a 143 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 144 ├── constraint: /1 145 │ ├── [/1 - /2] 146 │ ├── [/4 - /4] 147 │ ├── [/6 - /7] 148 │ └── [/9 - /9] 149 ├── cardinality: [0 - 6] 150 ├── key: (1) 151 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 152 ├── prune: (2-4) 153 └── interesting orderings: (+1) (-3,+4,+1) 154 155 exec-ddl 156 CREATE TABLE date_pk (d DATE PRIMARY KEY, i INT) 157 ---- 158 159 opt 160 SELECT * FROM date_pk WHERE d IN ('2019-08-08', '2019-08-07') OR (d >= '2017-01-01' AND d < '2017-01-05') 161 ---- 162 scan date_pk 163 ├── columns: d:1(date!null) i:2(int) 164 ├── constraint: /1 165 │ ├── [/'2017-01-01' - /'2017-01-04'] 166 │ └── [/'2019-08-07' - /'2019-08-08'] 167 ├── cardinality: [0 - 6] 168 ├── key: (1) 169 ├── fd: (1)-->(2) 170 ├── prune: (2) 171 └── interesting orderings: (+1) 172 173 174 # Regression test for #42731: we were incorrectly setting cardinality [0 - 1]. 175 exec-ddl 176 CREATE TABLE t42731 (id INT PRIMARY KEY, unique_value INT UNIQUE, notnull_value INT NOT NULL) 177 ---- 178 179 norm 180 SELECT * FROM t42731 WHERE unique_value IS NULL AND notnull_value = 2000 181 ---- 182 select 183 ├── columns: id:1(int!null) unique_value:2(int) notnull_value:3(int!null) 184 ├── key: (1) 185 ├── fd: ()-->(2,3), (2)~~>(1) 186 ├── prune: (1) 187 ├── interesting orderings: (+1) (+2,+1) 188 ├── scan t42731 189 │ ├── columns: id:1(int!null) unique_value:2(int) notnull_value:3(int!null) 190 │ ├── key: (1) 191 │ ├── fd: (1)-->(2,3), (2)~~>(1,3) 192 │ ├── prune: (1-3) 193 │ └── interesting orderings: (+1) (+2,+1) 194 └── filters 195 ├── is [type=bool, outer=(2), constraints=(/2: [/NULL - /NULL]; tight), fd=()-->(2)] 196 │ ├── variable: unique_value:2 [type=int] 197 │ └── null [type=unknown] 198 └── eq [type=bool, outer=(3), constraints=(/3: [/2000 - /2000]; tight), fd=()-->(3)] 199 ├── variable: notnull_value:3 [type=int] 200 └── const: 2000 [type=int] 201 202 # The scan should be marked as side-effecting if FOR UPDATE is used. 203 build 204 SELECT * FROM a FOR UPDATE 205 ---- 206 scan a 207 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 208 ├── locking: for-update 209 ├── volatile, side-effects 210 ├── key: (1) 211 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 212 ├── prune: (1-4) 213 └── interesting orderings: (+1) (-3,+4,+1) 214 215 exec-ddl 216 CREATE TABLE kab ( 217 k INT8 PRIMARY KEY, 218 a INT8 NOT NULL, 219 b INT8 NOT NULL CHECK (b = 0), 220 INDEX ba (b, a) 221 ) 222 ---- 223 224 # Verify that check constraints are factored into Scan FDs (namely 225 # that b:3 shows up as constant). 226 build 227 SELECT * FROM kab 228 ---- 229 scan kab 230 ├── columns: k:1(int!null) a:2(int!null) b:3(int!null) 231 ├── check constraint expressions 232 │ └── eq [type=bool, outer=(3), constraints=(/3: [/0 - /0]; tight), fd=()-->(3)] 233 │ ├── variable: b:3 [type=int] 234 │ └── const: 0 [type=int] 235 ├── key: (1) 236 ├── fd: ()-->(3), (1)-->(2) 237 ├── prune: (1-3) 238 └── interesting orderings: (+1) (+3,+2,+1)