github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/projection-reuse (about) 1 # Tests around deduplication of projection expressions. 2 3 exec-ddl 4 CREATE TABLE ab (a FLOAT, b FLOAT) 5 ---- 6 7 # Non-side effecting expressions should be deduplicated. 8 build 9 SELECT a+b, a+b FROM ab 10 ---- 11 project 12 ├── columns: "?column?":4 "?column?":4 13 ├── scan ab 14 │ └── columns: a:1 b:2 rowid:3!null 15 └── projections 16 └── a:1 + b:2 [as="?column?":4] 17 18 # Ensure whitespace differences don't prevent deduplication. 19 build 20 SELECT a+b, a + b FROM ab 21 ---- 22 project 23 ├── columns: "?column?":4 "?column?":4 24 ├── scan ab 25 │ └── columns: a:1 b:2 rowid:3!null 26 └── projections 27 └── a:1 + b:2 [as="?column?":4] 28 29 # Side-effecting expressions are not deduplicated. 30 build 31 SELECT a/b, a/b FROM ab 32 ---- 33 project 34 ├── columns: "?column?":4 "?column?":5 35 ├── scan ab 36 │ └── columns: a:1 b:2 rowid:3!null 37 └── projections 38 ├── a:1 / b:2 [as="?column?":4] 39 └── a:1 / b:2 [as="?column?":5] 40 41 build 42 SELECT random(), random() FROM ab 43 ---- 44 project 45 ├── columns: random:4 random:5 46 ├── scan ab 47 │ └── columns: a:1 b:2 rowid:3!null 48 └── projections 49 ├── random() [as=random:4] 50 └── random() [as=random:5] 51 52 # ORDER BY does not add a new projection if the same expression is projected 53 # already, regardless of side-effects. 54 build 55 SELECT a, b, random(), random() FROM ab ORDER BY random() 56 ---- 57 sort 58 ├── columns: a:1 b:2 random:4 random:5 59 ├── ordering: +4 60 └── project 61 ├── columns: random:4 random:5 a:1 b:2 62 ├── scan ab 63 │ └── columns: a:1 b:2 rowid:3!null 64 └── projections 65 ├── random() [as=random:4] 66 └── random() [as=random:5] 67 68 # With GROUP BY, expressions identical to a grouping column are always 69 # collapsed into a single value. 70 build 71 SELECT random(), random() FROM ab GROUP BY random() 72 ---- 73 group-by 74 ├── columns: random:4 random:4 75 ├── grouping columns: column4:4 76 └── project 77 ├── columns: column4:4 78 ├── scan ab 79 │ └── columns: a:1 b:2 rowid:3!null 80 └── projections 81 └── random() [as=column4:4] 82 83 build 84 INSERT INTO ab VALUES (random(), random()) 85 ---- 86 insert ab 87 ├── columns: <none> 88 ├── insert-mapping: 89 │ ├── column1:4 => a:1 90 │ ├── column2:5 => b:2 91 │ └── column6:6 => rowid:3 92 └── project 93 ├── columns: column6:6 column1:4 column2:5 94 ├── values 95 │ ├── columns: column1:4 column2:5 96 │ └── (random(), random()) 97 └── projections 98 └── unique_rowid() [as=column6:6] 99 100 # Make sure impure default expressions are not deduplicated. 101 exec-ddl 102 CREATE TABLE abcd (a FLOAT, b FLOAT, c FLOAT DEFAULT random(), d FLOAT DEFAULT random()) 103 ---- 104 105 build 106 INSERT INTO abcd VALUES (1, 1) 107 ---- 108 insert abcd 109 ├── columns: <none> 110 ├── insert-mapping: 111 │ ├── column1:6 => a:1 112 │ ├── column2:7 => b:2 113 │ ├── column8:8 => c:3 114 │ ├── column9:9 => d:4 115 │ └── column10:10 => rowid:5 116 └── project 117 ├── columns: column8:8 column9:9 column10:10 column1:6!null column2:7!null 118 ├── values 119 │ ├── columns: column1:6!null column2:7!null 120 │ └── (1.0, 1.0) 121 └── projections 122 ├── random() [as=column8:8] 123 ├── random() [as=column9:9] 124 └── unique_rowid() [as=column10:10] 125 126 build 127 INSERT INTO abcd VALUES (random(), random()) 128 ---- 129 insert abcd 130 ├── columns: <none> 131 ├── insert-mapping: 132 │ ├── column1:6 => a:1 133 │ ├── column2:7 => b:2 134 │ ├── column8:8 => c:3 135 │ ├── column9:9 => d:4 136 │ └── column10:10 => rowid:5 137 └── project 138 ├── columns: column8:8 column9:9 column10:10 column1:6 column2:7 139 ├── values 140 │ ├── columns: column1:6 column2:7 141 │ └── (random(), random()) 142 └── projections 143 ├── random() [as=column8:8] 144 ├── random() [as=column9:9] 145 └── unique_rowid() [as=column10:10] 146 147 build 148 UPSERT INTO abcd VALUES (1, 1) 149 ---- 150 upsert abcd 151 ├── columns: <none> 152 ├── upsert-mapping: 153 │ ├── column1:6 => a:1 154 │ ├── column2:7 => b:2 155 │ ├── column8:8 => c:3 156 │ ├── column9:9 => d:4 157 │ └── column10:10 => rowid:5 158 └── project 159 ├── columns: column8:8 column9:9 column10:10 column1:6!null column2:7!null 160 ├── values 161 │ ├── columns: column1:6!null column2:7!null 162 │ └── (1.0, 1.0) 163 └── projections 164 ├── random() [as=column8:8] 165 ├── random() [as=column9:9] 166 └── unique_rowid() [as=column10:10] 167 168 build 169 UPSERT INTO abcd VALUES (random(), random()) 170 ---- 171 upsert abcd 172 ├── columns: <none> 173 ├── upsert-mapping: 174 │ ├── column1:6 => a:1 175 │ ├── column2:7 => b:2 176 │ ├── column8:8 => c:3 177 │ ├── column9:9 => d:4 178 │ └── column10:10 => rowid:5 179 └── project 180 ├── columns: column8:8 column9:9 column10:10 column1:6 column2:7 181 ├── values 182 │ ├── columns: column1:6 column2:7 183 │ └── (random(), random()) 184 └── projections 185 ├── random() [as=column8:8] 186 ├── random() [as=column9:9] 187 └── unique_rowid() [as=column10:10] 188 189 build 190 UPDATE abcd SET a = random(), b = random() WHERE a=1 191 ---- 192 update abcd 193 ├── columns: <none> 194 ├── fetch columns: a:6 b:7 c:8 d:9 rowid:10 195 ├── update-mapping: 196 │ ├── a_new:11 => a:1 197 │ └── b_new:12 => b:2 198 └── project 199 ├── columns: a_new:11 b_new:12 a:6!null b:7 c:8 d:9 rowid:10!null 200 ├── select 201 │ ├── columns: a:6!null b:7 c:8 d:9 rowid:10!null 202 │ ├── scan abcd 203 │ │ └── columns: a:6 b:7 c:8 d:9 rowid:10!null 204 │ └── filters 205 │ └── a:6 = 1.0 206 └── projections 207 ├── random() [as=a_new:11] 208 └── random() [as=b_new:12]