github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/project (about) 1 exec-ddl 2 CREATE TABLE a (x INT PRIMARY KEY, y FLOAT) 3 ---- 4 5 exec-ddl 6 CREATE TABLE b (x INT, y FLOAT) 7 ---- 8 9 build 10 SELECT 5 r 11 ---- 12 project 13 ├── columns: r:1!null 14 ├── values 15 │ └── () 16 └── projections 17 └── 5 [as=r:1] 18 19 build 20 SELECT a.x FROM a 21 ---- 22 project 23 ├── columns: x:1!null 24 └── scan a 25 └── columns: x:1!null y:2 26 27 build 28 SELECT a.x, a.y FROM a 29 ---- 30 scan a 31 └── columns: x:1!null y:2 32 33 build 34 SELECT a.y, a.x FROM a 35 ---- 36 scan a 37 └── columns: y:2 x:1!null 38 39 build 40 SELECT * FROM a 41 ---- 42 scan a 43 └── columns: x:1!null y:2 44 45 # Note that an explicit projection operator is added for table b (unlike for 46 # table a) to avoid projecting the hidden rowid column. 47 build 48 SELECT * FROM b 49 ---- 50 project 51 ├── columns: x:1 y:2 52 └── scan b 53 └── columns: x:1 y:2 rowid:3!null 54 55 build 56 SELECT (a.x + 3) AS "X", false AS "Y" FROM a 57 ---- 58 project 59 ├── columns: X:3!null Y:4!null 60 ├── scan a 61 │ └── columns: x:1!null y:2 62 └── projections 63 ├── x:1 + 3 [as=X:3] 64 └── false [as=Y:4] 65 66 build 67 SELECT *, ((x < y) OR x > 1000) AS r FROM a 68 ---- 69 project 70 ├── columns: x:1!null y:2 r:3 71 ├── scan a 72 │ └── columns: x:1!null y:2 73 └── projections 74 └── (x:1 < y:2) OR (x:1 > 1000) [as=r:3] 75 76 build 77 SELECT a.*, true FROM a 78 ---- 79 project 80 ├── columns: x:1!null y:2 bool:3!null 81 ├── scan a 82 │ └── columns: x:1!null y:2 83 └── projections 84 └── true [as=bool:3] 85 86 build 87 SELECT u + 1 AS r, v + 1 AS s FROM (SELECT a.x + 3 AS t, a.y + 1.0 AS u FROM a) AS foo(u, v) 88 ---- 89 project 90 ├── columns: r:5!null s:6 91 ├── project 92 │ ├── columns: t:3!null u:4 93 │ ├── scan a 94 │ │ └── columns: x:1!null y:2 95 │ └── projections 96 │ ├── x:1 + 3 [as=t:3] 97 │ └── y:2 + 1.0 [as=u:4] 98 └── projections 99 ├── t:3 + 1 [as=r:5] 100 └── u:4 + 1.0 [as=s:6] 101 102 build 103 SELECT rowid FROM b; 104 ---- 105 project 106 ├── columns: rowid:3!null 107 └── scan b 108 └── columns: x:1 y:2 rowid:3!null 109 110 build 111 SELECT rowid FROM (SELECT * FROM b) 112 ---- 113 error (42703): column "rowid" does not exist 114 115 build 116 SELECT rowid FROM (SELECT rowid FROM b) 117 ---- 118 project 119 ├── columns: rowid:3!null 120 └── scan b 121 └── columns: x:1 y:2 rowid:3!null 122 123 build 124 SELECT q.r FROM (SELECT rowid FROM b) AS q(r) 125 ---- 126 project 127 ├── columns: r:3!null 128 └── scan b 129 └── columns: x:1 y:2 rowid:3!null 130 131 build 132 SELECT r FROM (SELECT rowid FROM b) AS q(r) 133 ---- 134 project 135 ├── columns: r:3!null 136 └── scan b 137 └── columns: x:1 y:2 rowid:3!null 138 139 exec-ddl 140 CREATE TABLE c (x INT, y FLOAT) 141 ---- 142 143 build 144 SELECT rowid FROM b, c 145 ---- 146 error (42702): column reference "rowid" is ambiguous (candidates: b.rowid, c.rowid) 147 148 build 149 SELECT x, y, rowid FROM c WHERE rowid > 0 150 ---- 151 select 152 ├── columns: x:1 y:2 rowid:3!null 153 ├── scan c 154 │ └── columns: x:1 y:2 rowid:3!null 155 └── filters 156 └── rowid:3 > 0 157 158 build 159 SELECT r FROM (SELECT x, y, rowid AS r FROM c) 160 ---- 161 project 162 ├── columns: r:3!null 163 └── scan c 164 └── columns: x:1 y:2 rowid:3!null 165 166 build 167 SELECT rowid::string FROM b 168 ---- 169 project 170 ├── columns: rowid:4!null 171 ├── scan b 172 │ └── columns: x:1 y:2 b.rowid:3!null 173 └── projections 174 └── b.rowid:3::STRING [as=rowid:4] 175 176 build 177 SELECT (x, y)::timestamp FROM b 178 ---- 179 error (42846): invalid cast: tuple{int, float} -> timestamp 180 181 build 182 SELECT CAST(x AS int[]) FROM b 183 ---- 184 error (42846): invalid cast: int -> int[] 185 186 exec-ddl 187 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT) 188 ---- 189 190 build 191 SELECT c FROM (SELECT a FROM abc) 192 ---- 193 error (42703): column "c" does not exist 194 195 build 196 SELECT c FROM (SELECT a FROM abc ORDER BY c) 197 ---- 198 error (42703): column "c" does not exist 199 200 build 201 SELECT c FROM (SELECT a, b FROM abc ORDER BY c) 202 ---- 203 error (42703): column "c" does not exist 204 205 build fully-qualify-names 206 SELECT t.kv.k FROM abc AS kv 207 ---- 208 error (42P01): no data source matches prefix: t.kv 209 210 exec-ddl 211 CREATE TABLE kv (k INT PRIMARY KEY, v INT) 212 ---- 213 214 build fully-qualify-names 215 SELECT t.kv.k FROM kv 216 ---- 217 project 218 ├── columns: k:1!null 219 └── scan t.public.kv 220 └── columns: t.public.kv.k:1!null t.public.kv.v:2 221 222 # Check that tuple type includes labels. 223 build 224 SELECT x FROM (SELECT (row(v,v,v) AS a,b,c) AS x FROM kv) 225 ---- 226 project 227 ├── columns: x:3 228 ├── scan kv 229 │ └── columns: k:1!null v:2 230 └── projections 231 └── ((v:2, v:2, v:2) AS a, b, c) [as=x:3] 232 233 # Regression test for #48179. Star expansion of un-labeled tuple must project 234 # all columns from the tuple. 235 build 236 SELECT (b).* FROM (VALUES (((1, 2)))) as a(b) 237 ---- 238 project 239 ├── columns: "?column?":2 "?column?":3 240 ├── values 241 │ ├── columns: column1:1 242 │ └── ((1, 2),) 243 └── projections 244 ├── (column1:1).@1 [as="?column?":2] 245 └── (column1:1).@2 [as="?column?":3]