github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/scalar (about) 1 exec-ddl 2 CREATE TABLE xy (x INT PRIMARY KEY, y INT) 3 ---- 4 5 exec-ddl 6 CREATE TABLE uv (u INT, v INT NOT NULL) 7 ---- 8 9 build 10 SELECT * FROM xy WHERE x < 5 11 ---- 12 select 13 ├── columns: x:1(int!null) y:2(int) 14 ├── key: (1) 15 ├── fd: (1)-->(2) 16 ├── prune: (2) 17 ├── interesting orderings: (+1) 18 ├── scan xy 19 │ ├── columns: x:1(int!null) y:2(int) 20 │ ├── key: (1) 21 │ ├── fd: (1)-->(2) 22 │ ├── prune: (1,2) 23 │ └── interesting orderings: (+1) 24 └── filters 25 └── lt [type=bool, outer=(1), constraints=(/1: (/NULL - /4]; tight)] 26 ├── variable: x:1 [type=int] 27 └── const: 5 [type=int] 28 29 build 30 SELECT xy.x + 1 = length('foo') + xy.y AS a, uv.rowid * xy.x AS b FROM xy, uv 31 ---- 32 project 33 ├── columns: a:6(bool) b:7(int!null) 34 ├── immutable 35 ├── prune: (6,7) 36 ├── inner-join (cross) 37 │ ├── columns: x:1(int!null) y:2(int) u:3(int) v:4(int!null) rowid:5(int!null) 38 │ ├── key: (1,5) 39 │ ├── fd: (1)-->(2), (5)-->(3,4) 40 │ ├── prune: (1-5) 41 │ ├── interesting orderings: (+1) (+5) 42 │ ├── scan xy 43 │ │ ├── columns: x:1(int!null) y:2(int) 44 │ │ ├── key: (1) 45 │ │ ├── fd: (1)-->(2) 46 │ │ ├── prune: (1,2) 47 │ │ └── interesting orderings: (+1) 48 │ ├── scan uv 49 │ │ ├── columns: u:3(int) v:4(int!null) rowid:5(int!null) 50 │ │ ├── key: (5) 51 │ │ ├── fd: (5)-->(3,4) 52 │ │ ├── prune: (3-5) 53 │ │ └── interesting orderings: (+5) 54 │ └── filters (true) 55 └── projections 56 ├── eq [as=a:6, type=bool, outer=(1,2), immutable] 57 │ ├── plus [type=int] 58 │ │ ├── variable: x:1 [type=int] 59 │ │ └── const: 1 [type=int] 60 │ └── plus [type=int] 61 │ ├── function: length [type=int] 62 │ │ └── const: 'foo' [type=string] 63 │ └── variable: y:2 [type=int] 64 └── mult [as=b:7, type=int, outer=(1,5)] 65 ├── variable: rowid:5 [type=int] 66 └── variable: x:1 [type=int] 67 68 build 69 SELECT * FROM xy WHERE EXISTS(SELECT * FROM uv WHERE u=x) 70 ---- 71 select 72 ├── columns: x:1(int!null) y:2(int) 73 ├── key: (1) 74 ├── fd: (1)-->(2) 75 ├── prune: (2) 76 ├── interesting orderings: (+1) 77 ├── scan xy 78 │ ├── columns: x:1(int!null) y:2(int) 79 │ ├── key: (1) 80 │ ├── fd: (1)-->(2) 81 │ ├── prune: (1,2) 82 │ └── interesting orderings: (+1) 83 └── filters 84 └── exists [type=bool, outer=(1), correlated-subquery] 85 └── project 86 ├── columns: u:3(int!null) v:4(int!null) 87 ├── outer: (1) 88 ├── fd: ()-->(3) 89 ├── prune: (3,4) 90 └── select 91 ├── columns: u:3(int!null) v:4(int!null) rowid:5(int!null) 92 ├── outer: (1) 93 ├── key: (5) 94 ├── fd: ()-->(3), (5)-->(4) 95 ├── prune: (4,5) 96 ├── interesting orderings: (+5) 97 ├── scan uv 98 │ ├── columns: u:3(int) v:4(int!null) rowid:5(int!null) 99 │ ├── key: (5) 100 │ ├── fd: (5)-->(3,4) 101 │ ├── prune: (3-5) 102 │ └── interesting orderings: (+5) 103 └── filters 104 └── eq [type=bool, outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 105 ├── variable: u:3 [type=int] 106 └── variable: x:1 [type=int] 107 108 build 109 SELECT * FROM xy WHERE y IN (SELECT v FROM uv WHERE u=x) 110 ---- 111 select 112 ├── columns: x:1(int!null) y:2(int) 113 ├── key: (1) 114 ├── fd: (1)-->(2) 115 ├── interesting orderings: (+1) 116 ├── scan xy 117 │ ├── columns: x:1(int!null) y:2(int) 118 │ ├── key: (1) 119 │ ├── fd: (1)-->(2) 120 │ ├── prune: (1,2) 121 │ └── interesting orderings: (+1) 122 └── filters 123 └── any: eq [type=bool, outer=(1,2), correlated-subquery] 124 ├── project 125 │ ├── columns: v:4(int!null) 126 │ ├── outer: (1) 127 │ ├── prune: (4) 128 │ └── select 129 │ ├── columns: u:3(int!null) v:4(int!null) rowid:5(int!null) 130 │ ├── outer: (1) 131 │ ├── key: (5) 132 │ ├── fd: ()-->(3), (5)-->(4) 133 │ ├── prune: (4,5) 134 │ ├── interesting orderings: (+5) 135 │ ├── scan uv 136 │ │ ├── columns: u:3(int) v:4(int!null) rowid:5(int!null) 137 │ │ ├── key: (5) 138 │ │ ├── fd: (5)-->(3,4) 139 │ │ ├── prune: (3-5) 140 │ │ └── interesting orderings: (+5) 141 │ └── filters 142 │ └── eq [type=bool, outer=(1,3), constraints=(/1: (/NULL - ]; /3: (/NULL - ]), fd=(1)==(3), (3)==(1)] 143 │ ├── variable: u:3 [type=int] 144 │ └── variable: x:1 [type=int] 145 └── variable: y:2 [type=int] 146 147 # Regression for 36137: need to detect correlation in 2nd Any operator argument. 148 build 149 SELECT * FROM xy WHERE x=1 OR y IN (SELECT v FROM uv) 150 ---- 151 select 152 ├── columns: x:1(int!null) y:2(int) 153 ├── key: (1) 154 ├── fd: (1)-->(2) 155 ├── interesting orderings: (+1) 156 ├── scan xy 157 │ ├── columns: x:1(int!null) y:2(int) 158 │ ├── key: (1) 159 │ ├── fd: (1)-->(2) 160 │ ├── prune: (1,2) 161 │ └── interesting orderings: (+1) 162 └── filters 163 └── or [type=bool, outer=(1,2), correlated-subquery] 164 ├── eq [type=bool] 165 │ ├── variable: x:1 [type=int] 166 │ └── const: 1 [type=int] 167 └── any: eq [type=bool] 168 ├── project 169 │ ├── columns: v:4(int!null) 170 │ ├── prune: (4) 171 │ └── scan uv 172 │ ├── columns: u:3(int) v:4(int!null) rowid:5(int!null) 173 │ ├── key: (5) 174 │ ├── fd: (5)-->(3,4) 175 │ ├── prune: (3-5) 176 │ └── interesting orderings: (+5) 177 └── variable: y:2 [type=int] 178 179 # Side-effects: test DivOp and impure FuncOp. 180 build 181 SELECT sum(x), div 182 FROM (SELECT x, y, x/y AS div FROM xy) 183 INNER JOIN (SELECT * FROM uv WHERE now() > '2018-01-01') 184 ON x=u 185 GROUP BY div 186 ---- 187 group-by 188 ├── columns: sum:7(decimal!null) div:3(decimal) 189 ├── grouping columns: div:3(decimal) 190 ├── stable, side-effects 191 ├── key: (3) 192 ├── fd: (3)-->(7) 193 ├── prune: (7) 194 ├── project 195 │ ├── columns: x:1(int!null) div:3(decimal) 196 │ ├── stable, side-effects 197 │ ├── fd: (1)-->(3) 198 │ ├── prune: (1,3) 199 │ ├── interesting orderings: (+1) 200 │ └── inner-join (hash) 201 │ ├── columns: x:1(int!null) y:2(int) div:3(decimal) u:4(int!null) v:5(int!null) 202 │ ├── stable, side-effects 203 │ ├── fd: (1)-->(2,3), (1)==(4), (4)==(1) 204 │ ├── prune: (2,3,5) 205 │ ├── interesting orderings: (+1) 206 │ ├── multiplicity: left-rows(zero-or-more), right-rows(one-or-zero) 207 │ ├── project 208 │ │ ├── columns: div:3(decimal) x:1(int!null) y:2(int) 209 │ │ ├── immutable, side-effects 210 │ │ ├── key: (1) 211 │ │ ├── fd: (1)-->(2,3) 212 │ │ ├── prune: (1-3) 213 │ │ ├── interesting orderings: (+1) 214 │ │ ├── scan xy 215 │ │ │ ├── columns: x:1(int!null) y:2(int) 216 │ │ │ ├── key: (1) 217 │ │ │ ├── fd: (1)-->(2) 218 │ │ │ ├── prune: (1,2) 219 │ │ │ └── interesting orderings: (+1) 220 │ │ └── projections 221 │ │ └── div [as=div:3, type=decimal, outer=(1,2), immutable, side-effects] 222 │ │ ├── variable: x:1 [type=int] 223 │ │ └── variable: y:2 [type=int] 224 │ ├── project 225 │ │ ├── columns: u:4(int) v:5(int!null) 226 │ │ ├── stable, side-effects 227 │ │ ├── prune: (4,5) 228 │ │ └── select 229 │ │ ├── columns: u:4(int) v:5(int!null) rowid:6(int!null) 230 │ │ ├── stable, side-effects 231 │ │ ├── key: (6) 232 │ │ ├── fd: (6)-->(4,5) 233 │ │ ├── prune: (4-6) 234 │ │ ├── interesting orderings: (+6) 235 │ │ ├── scan uv 236 │ │ │ ├── columns: u:4(int) v:5(int!null) rowid:6(int!null) 237 │ │ │ ├── key: (6) 238 │ │ │ ├── fd: (6)-->(4,5) 239 │ │ │ ├── prune: (4-6) 240 │ │ │ └── interesting orderings: (+6) 241 │ │ └── filters 242 │ │ └── gt [type=bool, stable, side-effects] 243 │ │ ├── function: now [type=timestamptz] 244 │ │ └── const: '2018-01-01 00:00:00+00:00' [type=timestamptz] 245 │ └── filters 246 │ └── eq [type=bool, outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 247 │ ├── variable: x:1 [type=int] 248 │ └── variable: u:4 [type=int] 249 └── aggregations 250 └── sum [as=sum:7, type=decimal, outer=(1)] 251 └── variable: x:1 [type=int] 252 253 # Verify that we don't mark the division as side-effecting when the right-hand 254 # side is a constant. 255 build 256 SELECT x / 1, x::float / 2.0, x::decimal / 3.0 FROM xy 257 ---- 258 project 259 ├── columns: "?column?":3(decimal!null) "?column?":4(float!null) "?column?":5(decimal!null) 260 ├── prune: (3-5) 261 ├── scan xy 262 │ ├── columns: x:1(int!null) y:2(int) 263 │ ├── key: (1) 264 │ ├── fd: (1)-->(2) 265 │ ├── prune: (1,2) 266 │ └── interesting orderings: (+1) 267 └── projections 268 ├── div [as="?column?":3, type=decimal, outer=(1)] 269 │ ├── variable: x:1 [type=int] 270 │ └── const: 1 [type=int] 271 ├── div [as="?column?":4, type=float, outer=(1)] 272 │ ├── cast: FLOAT8 [type=float] 273 │ │ └── variable: x:1 [type=int] 274 │ └── const: 2.0 [type=float] 275 └── div [as="?column?":5, type=decimal, outer=(1)] 276 ├── cast: DECIMAL [type=decimal] 277 │ └── variable: x:1 [type=int] 278 └── const: 3.0 [type=decimal]