github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/side_effects (about) 1 # See Logical.CanHaveSideEffects comment for the optimizer's side-effect policy. 2 3 exec-ddl 4 CREATE TABLE a (k INT PRIMARY KEY, i INT, f FLOAT, s STRING, j JSON) 5 ---- 6 7 exec-ddl 8 CREATE TABLE xy (x INT PRIMARY KEY, y INT) 9 ---- 10 11 exec-ddl 12 CREATE TABLE uv (u INT PRIMARY KEY, v INT) 13 ---- 14 15 # Don't allow ORDER BY column to be eliminated if it has a side effect. 16 norm 17 SELECT * FROM a ORDER BY length('foo'), random()+1.0 18 ---- 19 sort 20 ├── columns: k:1!null i:2 f:3 s:4 j:5 [hidden: column7:7] 21 ├── volatile, side-effects 22 ├── key: (1) 23 ├── fd: (1)-->(2-5,7) 24 ├── ordering: +7 25 └── project 26 ├── columns: column7:7 k:1!null i:2 f:3 s:4 j:5 27 ├── volatile, side-effects 28 ├── key: (1) 29 ├── fd: (1)-->(2-5,7) 30 ├── scan a 31 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 32 │ ├── key: (1) 33 │ └── fd: (1)-->(2-5) 34 └── projections 35 └── random() + 1.0 [as=column7:7, volatile, side-effects] 36 37 # Don't allow GROUP BY column to be eliminated if it has a side effect. 38 norm 39 SELECT avg(f) FROM a WHERE i=5 GROUP BY i+(random()*10)::int, i+1 40 ---- 41 project 42 ├── columns: avg:6 43 ├── volatile, side-effects 44 └── group-by 45 ├── columns: avg:6 column7:7 46 ├── grouping columns: column7:7 47 ├── volatile, side-effects 48 ├── key: (7) 49 ├── fd: (7)-->(6) 50 ├── project 51 │ ├── columns: column7:7 f:3 52 │ ├── volatile, side-effects 53 │ ├── select 54 │ │ ├── columns: i:2!null f:3 55 │ │ ├── fd: ()-->(2) 56 │ │ ├── scan a 57 │ │ │ └── columns: i:2 f:3 58 │ │ └── filters 59 │ │ └── i:2 = 5 [outer=(2), constraints=(/2: [/5 - /5]; tight), fd=()-->(2)] 60 │ └── projections 61 │ └── i:2 + (random() * 10.0)::INT8 [as=column7:7, outer=(2), volatile, side-effects] 62 └── aggregations 63 └── avg [as=avg:6, outer=(3)] 64 └── f:3 65 66 # Allow elimination of side effecting expressions during column pruning. 67 norm 68 SELECT i FROM (SELECT i, nextval('foo') FROM a) 69 ---- 70 scan a 71 └── columns: i:2 72 73 # Allow duplication of side effecting expressions during predicate pushdown. 74 norm 75 SELECT * FROM a INNER JOIN xy ON k=x WHERE k=random() 76 ---- 77 inner-join (hash) 78 ├── columns: k:1!null i:2 f:3 s:4 j:5 x:6!null y:7 79 ├── volatile, side-effects 80 ├── key: (6) 81 ├── fd: (1)-->(2-5), (6)-->(7), (1)==(6), (6)==(1) 82 ├── select 83 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 84 │ ├── volatile, side-effects 85 │ ├── key: (1) 86 │ ├── fd: (1)-->(2-5) 87 │ ├── scan a 88 │ │ ├── columns: k:1!null i:2 f:3 s:4 j:5 89 │ │ ├── key: (1) 90 │ │ └── fd: (1)-->(2-5) 91 │ └── filters 92 │ └── k:1 = random() [outer=(1), volatile, side-effects, constraints=(/1: (/NULL - ])] 93 ├── select 94 │ ├── columns: x:6!null y:7 95 │ ├── volatile, side-effects 96 │ ├── key: (6) 97 │ ├── fd: (6)-->(7) 98 │ ├── scan xy 99 │ │ ├── columns: x:6!null y:7 100 │ │ ├── key: (6) 101 │ │ └── fd: (6)-->(7) 102 │ └── filters 103 │ └── x:6 = random() [outer=(6), volatile, side-effects, constraints=(/6: (/NULL - ])] 104 └── filters 105 └── k:1 = x:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 106 107 # Decorrelate CASE WHEN branch if there are no side effects. 108 norm 109 SELECT CASE WHEN i<0 THEN (SELECT y FROM xy WHERE x=i LIMIT 1) ELSE 5 END FROM a 110 ---- 111 project 112 ├── columns: case:8 113 ├── left-join (hash) 114 │ ├── columns: i:2 x:6 y:7 115 │ ├── fd: (6)-->(7) 116 │ ├── scan a 117 │ │ └── columns: i:2 118 │ ├── scan xy 119 │ │ ├── columns: x:6!null y:7 120 │ │ ├── key: (6) 121 │ │ └── fd: (6)-->(7) 122 │ └── filters 123 │ └── x:6 = i:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 124 └── projections 125 └── CASE WHEN i:2 < 0 THEN y:7 ELSE 5 END [as=case:8, outer=(2,7)] 126 127 # Decorrelate CASE ELSE branch if there are no side effects. 128 norm 129 SELECT * FROM a WHERE (CASE WHEN i<0 THEN 5 ELSE (SELECT y FROM xy WHERE x=i LIMIT 1) END)=k 130 ---- 131 project 132 ├── columns: k:1!null i:2 f:3 s:4 j:5 133 ├── key: (1) 134 ├── fd: (1)-->(2-5) 135 └── select 136 ├── columns: k:1!null i:2 f:3 s:4 j:5 x:6 y:7 137 ├── key: (1) 138 ├── fd: (1)-->(2-7), (6)-->(7) 139 ├── left-join (hash) 140 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 x:6 y:7 141 │ ├── key: (1) 142 │ ├── fd: (1)-->(2-7), (6)-->(7) 143 │ ├── scan a 144 │ │ ├── columns: k:1!null i:2 f:3 s:4 j:5 145 │ │ ├── key: (1) 146 │ │ └── fd: (1)-->(2-5) 147 │ ├── scan xy 148 │ │ ├── columns: x:6!null y:7 149 │ │ ├── key: (6) 150 │ │ └── fd: (6)-->(7) 151 │ └── filters 152 │ └── x:6 = i:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 153 └── filters 154 └── k:1 = CASE WHEN i:2 < 0 THEN 5 ELSE y:7 END [outer=(1,2,7), constraints=(/1: (/NULL - ])] 155 156 # Don't decorrelate CASE WHEN branch if there are side effects. 157 norm 158 SELECT CASE WHEN i<0 THEN (SELECT y FROM xy WHERE x=i LIMIT (random()*10)::int) ELSE 5 END FROM a 159 ---- 160 project 161 ├── columns: case:8 162 ├── volatile, side-effects 163 ├── scan a 164 │ └── columns: i:2 165 └── projections 166 └── case [as=case:8, outer=(2), volatile, side-effects, correlated-subquery] 167 ├── true 168 ├── when 169 │ ├── i:2 < 0 170 │ └── subquery 171 │ └── project 172 │ ├── columns: y:7 173 │ ├── outer: (2) 174 │ ├── cardinality: [0 - 1] 175 │ ├── volatile, side-effects 176 │ ├── key: () 177 │ ├── fd: ()-->(7) 178 │ └── limit 179 │ ├── columns: x:6!null y:7 180 │ ├── outer: (2) 181 │ ├── cardinality: [0 - 1] 182 │ ├── volatile, side-effects 183 │ ├── key: () 184 │ ├── fd: ()-->(6,7) 185 │ ├── select 186 │ │ ├── columns: x:6!null y:7 187 │ │ ├── outer: (2) 188 │ │ ├── cardinality: [0 - 1] 189 │ │ ├── key: () 190 │ │ ├── fd: ()-->(6,7) 191 │ │ ├── scan xy 192 │ │ │ ├── columns: x:6!null y:7 193 │ │ │ ├── key: (6) 194 │ │ │ └── fd: (6)-->(7) 195 │ │ └── filters 196 │ │ └── x:6 = i:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 197 │ └── (random() * 10.0)::INT8 198 └── 5 199 200 # Don't decorrelate CASE ELSE branch if there are side effects. 201 norm 202 SELECT * FROM a WHERE (CASE WHEN i<0 THEN 5 ELSE (SELECT y FROM xy WHERE x=i AND 5/y>1) END)=k 203 ---- 204 select 205 ├── columns: k:1!null i:2 f:3 s:4 j:5 206 ├── immutable, side-effects 207 ├── key: (1) 208 ├── fd: (1)-->(2-5) 209 ├── scan a 210 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 211 │ ├── key: (1) 212 │ └── fd: (1)-->(2-5) 213 └── filters 214 └── eq [outer=(1,2), immutable, side-effects, correlated-subquery, constraints=(/1: (/NULL - ])] 215 ├── k:1 216 └── case 217 ├── true 218 ├── when 219 │ ├── i:2 < 0 220 │ └── 5 221 └── subquery 222 └── project 223 ├── columns: y:7 224 ├── outer: (2) 225 ├── cardinality: [0 - 1] 226 ├── immutable, side-effects 227 ├── key: () 228 ├── fd: ()-->(7) 229 └── select 230 ├── columns: x:6!null y:7 231 ├── outer: (2) 232 ├── cardinality: [0 - 1] 233 ├── immutable, side-effects 234 ├── key: () 235 ├── fd: ()-->(6,7) 236 ├── scan xy 237 │ ├── columns: x:6!null y:7 238 │ ├── key: (6) 239 │ └── fd: (6)-->(7) 240 └── filters 241 ├── x:6 = i:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 242 └── (5 / y:7) > 1 [outer=(7), immutable, side-effects] 243 244 245 # Don't decorrelate IFERROR branch if there are side effects 246 norm 247 SELECT * FROM a WHERE IFERROR(1/0, (SELECT y::DECIMAL FROM xy WHERE x = i AND 5/y>1))=k 248 ---- 249 select 250 ├── columns: k:1!null i:2 f:3 s:4 j:5 251 ├── immutable, side-effects 252 ├── key: (1) 253 ├── fd: (1)-->(2-5) 254 ├── scan a 255 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 256 │ ├── key: (1) 257 │ └── fd: (1)-->(2-5) 258 └── filters 259 └── eq [outer=(1,2), immutable, side-effects, correlated-subquery, constraints=(/1: (/NULL - ])] 260 ├── k:1 261 └── if-err 262 ├── 1 / 0 263 └── else 264 └── subquery 265 └── project 266 ├── columns: y:8 267 ├── outer: (2) 268 ├── cardinality: [0 - 1] 269 ├── immutable, side-effects 270 ├── key: () 271 ├── fd: ()-->(8) 272 ├── select 273 │ ├── columns: x:6!null xy.y:7 274 │ ├── outer: (2) 275 │ ├── cardinality: [0 - 1] 276 │ ├── immutable, side-effects 277 │ ├── key: () 278 │ ├── fd: ()-->(6,7) 279 │ ├── scan xy 280 │ │ ├── columns: x:6!null xy.y:7 281 │ │ ├── key: (6) 282 │ │ └── fd: (6)-->(7) 283 │ └── filters 284 │ ├── x:6 = i:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 285 │ └── (5 / xy.y:7) > 1 [outer=(7), immutable, side-effects] 286 └── projections 287 └── xy.y:7::DECIMAL [as=y:8, outer=(7)] 288 289 # Decorrelate IFERROR branch if there are no side effects 290 norm 291 SELECT * FROM a WHERE IFERROR(1/0, (SELECT y::DECIMAL FROM xy WHERE x = i))=k 292 ---- 293 project 294 ├── columns: k:1!null i:2 f:3 s:4 j:5 295 ├── immutable, side-effects 296 ├── key: (1) 297 ├── fd: (1)-->(2-5) 298 └── select 299 ├── columns: k:1!null i:2 f:3 s:4 j:5 x:6 y:8 300 ├── immutable, side-effects 301 ├── key: (1) 302 ├── fd: (1)-->(2-6,8), (6)-->(8) 303 ├── left-join (hash) 304 │ ├── columns: k:1!null i:2 f:3 s:4 j:5 x:6 y:8 305 │ ├── key: (1) 306 │ ├── fd: (1)-->(2-6,8), (6)-->(8) 307 │ ├── scan a 308 │ │ ├── columns: k:1!null i:2 f:3 s:4 j:5 309 │ │ ├── key: (1) 310 │ │ └── fd: (1)-->(2-5) 311 │ ├── project 312 │ │ ├── columns: y:8 x:6!null 313 │ │ ├── key: (6) 314 │ │ ├── fd: (6)-->(8) 315 │ │ ├── scan xy 316 │ │ │ ├── columns: x:6!null xy.y:7 317 │ │ │ ├── key: (6) 318 │ │ │ └── fd: (6)-->(7) 319 │ │ └── projections 320 │ │ └── xy.y:7::DECIMAL [as=y:8, outer=(7)] 321 │ └── filters 322 │ └── x:6 = i:2 [outer=(2,6), constraints=(/2: (/NULL - ]; /6: (/NULL - ]), fd=(2)==(6), (6)==(2)] 323 └── filters 324 └── k:1 = IFERROR(1 / 0, y:8) [outer=(1,8), immutable, side-effects, constraints=(/1: (/NULL - ])]