github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/window (about) 1 # Adapted from the window logic test. 2 3 exec-ddl 4 CREATE TABLE kv ( 5 k INT PRIMARY KEY, 6 v INT, 7 w INT, 8 f FLOAT, 9 d DECIMAL, 10 s STRING, 11 b BOOL, 12 FAMILY (k, v, w, f, b), 13 FAMILY (d), 14 FAMILY (s) 15 ) 16 ---- 17 18 # FDs + Cardinality + Not Null cols. 19 20 build 21 SELECT k, rank() OVER () FROM (SELECT * FROM kv LIMIT 10) 22 ---- 23 project 24 ├── columns: k:1(int!null) rank:8(int) 25 ├── cardinality: [0 - 10] 26 ├── key: (1) 27 ├── fd: (1)-->(8) 28 ├── prune: (1,8) 29 └── window partition=() 30 ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) rank:8(int) 31 ├── cardinality: [0 - 10] 32 ├── key: (1) 33 ├── fd: (1)-->(2-7) 34 ├── prune: (1-8) 35 ├── limit 36 │ ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) 37 │ ├── cardinality: [0 - 10] 38 │ ├── key: (1) 39 │ ├── fd: (1)-->(2-7) 40 │ ├── prune: (1-7) 41 │ ├── interesting orderings: (+1) 42 │ ├── scan kv 43 │ │ ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) 44 │ │ ├── key: (1) 45 │ │ ├── fd: (1)-->(2-7) 46 │ │ ├── limit hint: 10.00 47 │ │ ├── prune: (1-7) 48 │ │ └── interesting orderings: (+1) 49 │ └── const: 10 [type=int] 50 └── windows 51 └── rank [as=rank:8, type=int] 52 53 build 54 SELECT k, rank() OVER (PARTITION BY v ORDER BY f) FROM (SELECT * FROM kv LIMIT 10) 55 ---- 56 project 57 ├── columns: k:1(int!null) rank:8(int) 58 ├── cardinality: [0 - 10] 59 ├── key: (1) 60 ├── fd: (1)-->(8) 61 ├── prune: (1,8) 62 └── window partition=(2) ordering=+4 63 ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) rank:8(int) 64 ├── cardinality: [0 - 10] 65 ├── key: (1) 66 ├── fd: (1)-->(2-7) 67 ├── prune: (1,3,5-8) 68 ├── limit 69 │ ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) 70 │ ├── cardinality: [0 - 10] 71 │ ├── key: (1) 72 │ ├── fd: (1)-->(2-7) 73 │ ├── prune: (1-7) 74 │ ├── interesting orderings: (+1) 75 │ ├── scan kv 76 │ │ ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) 77 │ │ ├── key: (1) 78 │ │ ├── fd: (1)-->(2-7) 79 │ │ ├── limit hint: 10.00 80 │ │ ├── prune: (1-7) 81 │ │ └── interesting orderings: (+1) 82 │ └── const: 10 [type=int] 83 └── windows 84 └── rank [as=rank:8, type=int] 85 86 # Outer cols. 87 88 build 89 SELECT k, (SELECT rank() OVER () + x FROM (SELECT k AS x)) FROM kv 90 ---- 91 project 92 ├── columns: k:1(int!null) "?column?":11(int) 93 ├── key: (1) 94 ├── fd: (1)-->(11) 95 ├── prune: (1,11) 96 ├── interesting orderings: (+1) 97 ├── scan kv 98 │ ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) 99 │ ├── key: (1) 100 │ ├── fd: (1)-->(2-7) 101 │ ├── prune: (1-7) 102 │ └── interesting orderings: (+1) 103 └── projections 104 └── subquery [as="?column?":11, type=int, outer=(1), correlated-subquery] 105 └── max1-row 106 ├── columns: "?column?":10(int) 107 ├── error: "more than one row returned by a subquery used as an expression" 108 ├── outer: (1) 109 ├── cardinality: [1 - 1] 110 ├── key: () 111 ├── fd: ()-->(10) 112 └── project 113 ├── columns: "?column?":10(int) 114 ├── outer: (1) 115 ├── cardinality: [1 - 1] 116 ├── key: () 117 ├── fd: ()-->(10) 118 ├── prune: (10) 119 ├── window partition=() 120 │ ├── columns: x:8(int) rank:9(int) 121 │ ├── outer: (1) 122 │ ├── cardinality: [1 - 1] 123 │ ├── key: () 124 │ ├── fd: ()-->(8) 125 │ ├── prune: (8,9) 126 │ ├── project 127 │ │ ├── columns: x:8(int) 128 │ │ ├── outer: (1) 129 │ │ ├── cardinality: [1 - 1] 130 │ │ ├── key: () 131 │ │ ├── fd: ()-->(8) 132 │ │ ├── prune: (8) 133 │ │ ├── values 134 │ │ │ ├── cardinality: [1 - 1] 135 │ │ │ ├── key: () 136 │ │ │ └── tuple [type=tuple] 137 │ │ └── projections 138 │ │ └── variable: k:1 [as=x:8, type=int, outer=(1)] 139 │ └── windows 140 │ └── rank [as=rank:9, type=int] 141 └── projections 142 └── plus [as="?column?":10, type=int, outer=(8,9)] 143 ├── variable: rank:9 [type=int] 144 └── variable: x:8 [type=int] 145 146 build 147 SELECT lag('foo'::string) OVER (), lag(1) OVER () FROM kv 148 ---- 149 project 150 ├── columns: lag:8(string) lag:9(int) 151 ├── prune: (8,9) 152 └── window partition=() 153 ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) lag:8(string) lag:9(int) lag_1_arg1:10(string!null) lag_1_arg2:11(int!null) lag_1_arg3:12(string) lag_2_arg3:13(int) 154 ├── key: (1) 155 ├── fd: ()-->(10-13), (1)-->(2-7) 156 ├── prune: (1-9) 157 ├── project 158 │ ├── columns: lag_1_arg1:10(string!null) lag_1_arg2:11(int!null) lag_1_arg3:12(string) lag_2_arg3:13(int) k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) 159 │ ├── key: (1) 160 │ ├── fd: ()-->(10-13), (1)-->(2-7) 161 │ ├── prune: (1-7,10-13) 162 │ ├── interesting orderings: (+1) 163 │ ├── scan kv 164 │ │ ├── columns: k:1(int!null) v:2(int) w:3(int) f:4(float) d:5(decimal) s:6(string) b:7(bool) 165 │ │ ├── key: (1) 166 │ │ ├── fd: (1)-->(2-7) 167 │ │ ├── prune: (1-7) 168 │ │ └── interesting orderings: (+1) 169 │ └── projections 170 │ ├── cast: STRING [as=lag_1_arg1:10, type=string] 171 │ │ └── const: 'foo' [type=string] 172 │ ├── const: 1 [as=lag_1_arg2:11, type=int] 173 │ ├── cast: STRING [as=lag_1_arg3:12, type=string] 174 │ │ └── null [type=unknown] 175 │ └── cast: INT8 [as=lag_2_arg3:13, type=int] 176 │ └── null [type=unknown] 177 └── windows 178 ├── lag [as=lag:8, type=string, outer=(10-12)] 179 │ ├── variable: lag_1_arg1:10 [type=string] 180 │ ├── variable: lag_1_arg2:11 [type=int] 181 │ └── variable: lag_1_arg3:12 [type=string] 182 └── lag [as=lag:9, type=int, outer=(11,13)] 183 ├── variable: lag_1_arg2:11 [type=int] 184 ├── variable: lag_1_arg2:11 [type=int] 185 └── variable: lag_2_arg3:13 [type=int]