github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/with (about) 1 exec-ddl 2 CREATE TABLE xy (x INT PRIMARY KEY, y INT) 3 ---- 4 5 build 6 WITH foo AS (SELECT * FROM xy) SELECT * FROM foo 7 ---- 8 with &1 (foo) 9 ├── columns: x:3(int!null) y:4(int) 10 ├── key: (3) 11 ├── fd: (3)-->(4) 12 ├── prune: (3,4) 13 ├── scan xy 14 │ ├── columns: xy.x:1(int!null) xy.y:2(int) 15 │ ├── key: (1) 16 │ ├── fd: (1)-->(2) 17 │ ├── prune: (1,2) 18 │ └── interesting orderings: (+1) 19 └── with-scan &1 (foo) 20 ├── columns: x:3(int!null) y:4(int) 21 ├── mapping: 22 │ ├── xy.x:1(int) => x:3(int) 23 │ └── xy.y:2(int) => y:4(int) 24 ├── key: (3) 25 ├── fd: (3)-->(4) 26 ├── prune: (3,4) 27 └── cte-uses 28 └── &1: count=1 used-columns=(1,2) 29 30 # Side effects should be propagated up to the top-level from the Binding side 31 # of a WITH. 32 build 33 WITH foo AS (SELECT 1/0) SELECT * FROM foo 34 ---- 35 with &1 (foo) 36 ├── columns: "?column?":2(decimal!null) 37 ├── cardinality: [1 - 1] 38 ├── immutable, side-effects 39 ├── key: () 40 ├── fd: ()-->(2) 41 ├── prune: (2) 42 ├── project 43 │ ├── columns: "?column?":1(decimal!null) 44 │ ├── cardinality: [1 - 1] 45 │ ├── immutable, side-effects 46 │ ├── key: () 47 │ ├── fd: ()-->(1) 48 │ ├── prune: (1) 49 │ ├── values 50 │ │ ├── cardinality: [1 - 1] 51 │ │ ├── key: () 52 │ │ └── tuple [type=tuple] 53 │ └── projections 54 │ └── div [as="?column?":1, type=decimal, immutable, side-effects] 55 │ ├── const: 1 [type=int] 56 │ └── const: 0 [type=int] 57 └── with-scan &1 (foo) 58 ├── columns: "?column?":2(decimal!null) 59 ├── mapping: 60 │ └── "?column?":1(decimal) => "?column?":2(decimal) 61 ├── cardinality: [1 - 1] 62 ├── key: () 63 ├── fd: ()-->(2) 64 └── prune: (2) 65 66 # Side effects should be propagated up to the top-level from the Input side of 67 # a With. 68 build 69 WITH foo AS (SELECT 1) SELECT 1/0 FROM foo 70 ---- 71 with &1 (foo) 72 ├── columns: "?column?":3(decimal!null) 73 ├── cardinality: [1 - 1] 74 ├── immutable, side-effects 75 ├── key: () 76 ├── fd: ()-->(3) 77 ├── prune: (3) 78 ├── project 79 │ ├── columns: "?column?":1(int!null) 80 │ ├── cardinality: [1 - 1] 81 │ ├── key: () 82 │ ├── fd: ()-->(1) 83 │ ├── prune: (1) 84 │ ├── values 85 │ │ ├── cardinality: [1 - 1] 86 │ │ ├── key: () 87 │ │ └── tuple [type=tuple] 88 │ └── projections 89 │ └── const: 1 [as="?column?":1, type=int] 90 └── project 91 ├── columns: "?column?":3(decimal!null) 92 ├── cardinality: [1 - 1] 93 ├── immutable, side-effects 94 ├── key: () 95 ├── fd: ()-->(3) 96 ├── prune: (3) 97 ├── cte-uses 98 │ └── &1: count=1 used-columns=(1) 99 ├── with-scan &1 (foo) 100 │ ├── columns: "?column?":2(int!null) 101 │ ├── mapping: 102 │ │ └── "?column?":1(int) => "?column?":2(int) 103 │ ├── cardinality: [1 - 1] 104 │ ├── key: () 105 │ ├── fd: ()-->(2) 106 │ ├── prune: (2) 107 │ └── cte-uses 108 │ └── &1: count=1 used-columns=(1) 109 └── projections 110 └── div [as="?column?":3, type=decimal, immutable, side-effects] 111 ├── const: 1 [type=int] 112 └── const: 0 [type=int] 113 114 build 115 WITH foo AS (SELECT $1::INT) SELECT 1 FROM foo 116 ---- 117 with &1 (foo) 118 ├── columns: "?column?":3(int!null) 119 ├── cardinality: [1 - 1] 120 ├── has-placeholder 121 ├── key: () 122 ├── fd: ()-->(3) 123 ├── prune: (3) 124 ├── project 125 │ ├── columns: int8:1(int) 126 │ ├── cardinality: [1 - 1] 127 │ ├── has-placeholder 128 │ ├── key: () 129 │ ├── fd: ()-->(1) 130 │ ├── prune: (1) 131 │ ├── values 132 │ │ ├── cardinality: [1 - 1] 133 │ │ ├── key: () 134 │ │ └── tuple [type=tuple] 135 │ └── projections 136 │ └── cast: INT8 [as=int8:1, type=int] 137 │ └── placeholder: $1 [type=string] 138 └── project 139 ├── columns: "?column?":3(int!null) 140 ├── cardinality: [1 - 1] 141 ├── key: () 142 ├── fd: ()-->(3) 143 ├── prune: (3) 144 ├── cte-uses 145 │ └── &1: count=1 used-columns=(1) 146 ├── with-scan &1 (foo) 147 │ ├── columns: int8:2(int) 148 │ ├── mapping: 149 │ │ └── int8:1(int) => int8:2(int) 150 │ ├── cardinality: [1 - 1] 151 │ ├── key: () 152 │ ├── fd: ()-->(2) 153 │ ├── prune: (2) 154 │ └── cte-uses 155 │ └── &1: count=1 used-columns=(1) 156 └── projections 157 └── const: 1 [as="?column?":3, type=int] 158 159 # WithScan should not have outer columns. 160 build 161 SELECT 162 * 163 FROM 164 (VALUES (1), (2)) AS v (x), 165 LATERAL (SELECT * FROM (WITH foo AS (SELECT 1 + x) SELECT * FROM foo)) 166 ---- 167 error (0A000): CTEs may not be correlated 168 169 # Regression test for #40930. 170 171 exec-ddl 172 CREATE TABLE t40930 ( 173 s string 174 ) 175 ---- 176 177 opt 178 WITH 179 with_4189 180 AS ( 181 SELECT 182 tab_10102.s 183 FROM 184 t40930 AS tab_10102, (SELECT NULL) AS tab_10103 (col_24444) 185 ) 186 SELECT 187 NULL 188 FROM 189 t40930, with_4189 190 ---- 191 project 192 ├── columns: "?column?":7(unknown) 193 ├── fd: ()-->(7) 194 ├── prune: (7) 195 ├── inner-join (cross) 196 │ ├── scan t40930 197 │ ├── scan tab_10102 198 │ └── filters (true) 199 └── projections 200 └── null [as="?column?":7, type=unknown] 201 202 build 203 SELECT 204 * 205 FROM 206 (VALUES (1), (2)) AS v (x), 207 LATERAL (SELECT * FROM (WITH foo AS (SELECT 1 + x) SELECT * FROM foo)) 208 ---- 209 error (0A000): CTEs may not be correlated