github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats/srfs (about) 1 exec-ddl 2 CREATE TABLE xy (x INT PRIMARY KEY, y INT) 3 ---- 4 5 opt colstat=1 colstat=2 colstat=3 colstat=4 colstat=5 6 SELECT a.*, b.*, c.* FROM upper('abc') a 7 JOIN ROWS FROM (upper('def'), generate_series(1, 3), upper('ghi')) b ON true 8 JOIN generate_series(1, 4) c ON true 9 ---- 10 inner-join (cross) 11 ├── columns: a:1(string) upper:2(string) generate_series:3(int) upper:4(string) c:5(int) 12 ├── immutable, side-effects 13 ├── stats: [rows=100, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=90, distinct(3)=7, null(3)=1, distinct(4)=1, null(4)=90, distinct(5)=7, null(5)=1] 14 ├── inner-join (cross) 15 │ ├── columns: upper:1(string) upper:2(string) generate_series:3(int) upper:4(string) 16 │ ├── immutable, side-effects 17 │ ├── stats: [rows=10, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=9, distinct(3)=7, null(3)=0.1, distinct(4)=1, null(4)=9] 18 │ ├── project-set 19 │ │ ├── columns: upper:2(string) generate_series:3(int) upper:4(string) 20 │ │ ├── immutable, side-effects 21 │ │ ├── stats: [rows=10, distinct(2)=1, null(2)=9, distinct(3)=7, null(3)=0.1, distinct(4)=1, null(4)=9] 22 │ │ ├── values 23 │ │ │ ├── cardinality: [1 - 1] 24 │ │ │ ├── stats: [rows=1] 25 │ │ │ ├── key: () 26 │ │ │ └── () [type=tuple] 27 │ │ └── zip 28 │ │ ├── 'DEF' [type=string] 29 │ │ ├── generate_series(1, 3) [type=int, immutable, side-effects] 30 │ │ └── 'GHI' [type=string] 31 │ ├── project-set 32 │ │ ├── columns: upper:1(string) 33 │ │ ├── stats: [rows=1, distinct(1)=1, null(1)=0] 34 │ │ ├── values 35 │ │ │ ├── cardinality: [1 - 1] 36 │ │ │ ├── stats: [rows=1] 37 │ │ │ ├── key: () 38 │ │ │ └── () [type=tuple] 39 │ │ └── zip 40 │ │ └── 'ABC' [type=string] 41 │ └── filters (true) 42 ├── project-set 43 │ ├── columns: generate_series:5(int) 44 │ ├── immutable, side-effects 45 │ ├── stats: [rows=10, distinct(5)=7, null(5)=0.1] 46 │ ├── values 47 │ │ ├── cardinality: [1 - 1] 48 │ │ ├── stats: [rows=1] 49 │ │ ├── key: () 50 │ │ └── () [type=tuple] 51 │ └── zip 52 │ └── generate_series(1, 4) [type=int, immutable, side-effects] 53 └── filters (true) 54 55 opt 56 SELECT * FROM (SELECT * FROM upper('abc') a, generate_series(1, 2) b) GROUP BY a, b 57 ---- 58 distinct-on 59 ├── columns: a:1(string) b:2(int) 60 ├── grouping columns: upper:1(string) generate_series:2(int) 61 ├── immutable, side-effects 62 ├── stats: [rows=7, distinct(1,2)=7, null(1,2)=0] 63 ├── key: (1,2) 64 └── inner-join (cross) 65 ├── columns: upper:1(string) generate_series:2(int) 66 ├── immutable, side-effects 67 ├── stats: [rows=10, distinct(1,2)=7, null(1,2)=0] 68 ├── project-set 69 │ ├── columns: generate_series:2(int) 70 │ ├── immutable, side-effects 71 │ ├── stats: [rows=10, distinct(2)=7, null(2)=0.1] 72 │ ├── values 73 │ │ ├── cardinality: [1 - 1] 74 │ │ ├── stats: [rows=1] 75 │ │ ├── key: () 76 │ │ └── () [type=tuple] 77 │ └── zip 78 │ └── generate_series(1, 2) [type=int, immutable, side-effects] 79 ├── project-set 80 │ ├── columns: upper:1(string) 81 │ ├── stats: [rows=1, distinct(1)=1, null(1)=0] 82 │ ├── values 83 │ │ ├── cardinality: [1 - 1] 84 │ │ ├── stats: [rows=1] 85 │ │ ├── key: () 86 │ │ └── () [type=tuple] 87 │ └── zip 88 │ └── 'ABC' [type=string] 89 └── filters (true) 90 91 opt colstat=3 colstat=(1,2,3) 92 SELECT unnest(ARRAY[x,y]) FROM xy 93 ---- 94 project 95 ├── columns: unnest:3(int) 96 ├── stats: [rows=2000, distinct(3)=2, null(3)=0, distinct(1-3)=2000, null(1-3)=0] 97 └── inner-join-apply 98 ├── columns: x:1(int!null) y:2(int) unnest:3(int) 99 ├── stats: [rows=2000, distinct(3)=2, null(3)=0, distinct(1-3)=2000, null(1-3)=0] 100 ├── fd: (1)-->(2) 101 ├── scan xy 102 │ ├── columns: x:1(int!null) y:2(int) 103 │ ├── stats: [rows=1000, distinct(1,2)=1000, null(1,2)=0] 104 │ ├── key: (1) 105 │ └── fd: (1)-->(2) 106 ├── values 107 │ ├── columns: unnest:3(int) 108 │ ├── outer: (1,2) 109 │ ├── cardinality: [2 - 2] 110 │ ├── stats: [rows=2, distinct(3)=2, null(3)=0] 111 │ ├── (x:1,) [type=tuple{int}] 112 │ └── (y:2,) [type=tuple{int}] 113 └── filters (true) 114 115 opt colstat=3 colstat=4 colstat=(3, 4) colstat=(1, 3) colstat=(2, 4) 116 SELECT xy.*, generate_series(x, y), generate_series(0, 1) FROM xy 117 ---- 118 project-set 119 ├── columns: x:1(int!null) y:2(int) generate_series:3(int) generate_series:4(int) 120 ├── immutable, side-effects 121 ├── stats: [rows=10000, distinct(3)=700, null(3)=100, distinct(4)=7, null(4)=100, distinct(1,3)=10000, null(1,3)=0, distinct(2,4)=700, null(2,4)=1, distinct(3,4)=4900, null(3,4)=1] 122 ├── fd: (1)-->(2) 123 ├── scan xy 124 │ ├── columns: x:1(int!null) y:2(int) 125 │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(2)=100, null(2)=10] 126 │ ├── key: (1) 127 │ └── fd: (1)-->(2) 128 └── zip 129 ├── generate_series(x:1, y:2) [type=int, outer=(1,2), immutable, side-effects] 130 └── generate_series(0, 1) [type=int, immutable, side-effects] 131 132 exec-ddl 133 CREATE TABLE articles ( 134 id INT PRIMARY KEY, 135 body STRING, 136 description STRING, 137 title STRING, 138 slug STRING, 139 tag_list STRING[], 140 user_id STRING, 141 created_at TIMESTAMP, 142 updated_at TIMESTAMP 143 ) 144 ---- 145 146 # The following queries test the statistics for four different types of Zip 147 # functions: 148 # 1. correlated scalar functions -- upper(title) 149 # 2. correlated generator functions -- unnest(tag_list) 150 # 4. uncorrelated scalar functions -- lower('ABC') 151 # 3. uncorrelated generator functions -- generate_series(0,1) 152 # 153 # They need to be tested with different queries at the moment due to 154 # limitations with our testing infrastructure. 155 156 opt 157 SELECT id FROM articles WHERE title = ANY( 158 SELECT upper FROM ROWS FROM (upper(title), unnest(tag_list), generate_series(0,1), lower('ABC')) 159 ) 160 ---- 161 distinct-on 162 ├── columns: id:1(int!null) 163 ├── grouping columns: id:1(int!null) 164 ├── immutable, side-effects 165 ├── stats: [rows=9.85601173, distinct(1)=9.85601173, null(1)=0] 166 ├── key: (1) 167 └── select 168 ├── columns: id:1(int!null) title:4(string!null) tag_list:6(string[]) upper:10(string!null) unnest:11(string) generate_series:12(int) lower:13(string) 169 ├── immutable, side-effects 170 ├── stats: [rows=9.9, distinct(1)=9.85601173, null(1)=0, distinct(4)=9.9, null(4)=0, distinct(10)=9.9, null(10)=0] 171 ├── fd: (1)-->(4,6), (4)==(10), (10)==(4) 172 ├── project-set 173 │ ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) upper:10(string) unnest:11(string) generate_series:12(int) lower:13(string) 174 │ ├── immutable, side-effects 175 │ ├── stats: [rows=10000, distinct(1)=1000, null(1)=0, distinct(4)=100, null(4)=100, distinct(10)=100, null(10)=9000] 176 │ ├── fd: (1)-->(4,6) 177 │ ├── scan articles 178 │ │ ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) 179 │ │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(4)=100, null(4)=10] 180 │ │ ├── key: (1) 181 │ │ └── fd: (1)-->(4,6) 182 │ └── zip 183 │ ├── upper(title:4) [type=string, outer=(4), immutable] 184 │ ├── unnest(tag_list:6) [type=string, outer=(6), immutable, side-effects] 185 │ ├── generate_series(0, 1) [type=int, immutable, side-effects] 186 │ └── 'abc' [type=string] 187 └── filters 188 └── title:4 = upper:10 [type=bool, outer=(4,10), constraints=(/4: (/NULL - ]; /10: (/NULL - ]), fd=(4)==(10), (10)==(4)] 189 190 opt 191 SELECT id FROM articles WHERE title = ANY( 192 SELECT unnest FROM ROWS FROM (upper(title), unnest(tag_list), generate_series(0,1), lower('ABC')) 193 ) 194 ---- 195 distinct-on 196 ├── columns: id:1(int!null) 197 ├── grouping columns: id:1(int!null) 198 ├── immutable, side-effects 199 ├── stats: [rows=13.9135391, distinct(1)=13.9135391, null(1)=0] 200 ├── key: (1) 201 └── select 202 ├── columns: id:1(int!null) title:4(string!null) tag_list:6(string[]) upper:10(string) unnest:11(string!null) generate_series:12(int) lower:13(string) 203 ├── immutable, side-effects 204 ├── stats: [rows=14.0014286, distinct(1)=13.9135391, null(1)=0, distinct(4)=14.0014286, null(4)=0, distinct(11)=14.0014286, null(11)=0] 205 ├── fd: (1)-->(4,6), (4)==(11), (11)==(4) 206 ├── project-set 207 │ ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) upper:10(string) unnest:11(string) generate_series:12(int) lower:13(string) 208 │ ├── immutable, side-effects 209 │ ├── stats: [rows=10000, distinct(1)=1000, null(1)=0, distinct(4)=100, null(4)=100, distinct(11)=700, null(11)=100] 210 │ ├── fd: (1)-->(4,6) 211 │ ├── scan articles 212 │ │ ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) 213 │ │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(4)=100, null(4)=10] 214 │ │ ├── key: (1) 215 │ │ └── fd: (1)-->(4,6) 216 │ └── zip 217 │ ├── upper(title:4) [type=string, outer=(4), immutable] 218 │ ├── unnest(tag_list:6) [type=string, outer=(6), immutable, side-effects] 219 │ ├── generate_series(0, 1) [type=int, immutable, side-effects] 220 │ └── 'abc' [type=string] 221 └── filters 222 └── title:4 = unnest:11 [type=bool, outer=(4,11), constraints=(/4: (/NULL - ]; /11: (/NULL - ]), fd=(4)==(11), (11)==(4)] 223 224 opt 225 SELECT id FROM articles WHERE id = ANY( 226 SELECT generate_series FROM ROWS FROM (upper(title), unnest(tag_list), generate_series(0,1), lower('ABC')) 227 ) 228 ---- 229 distinct-on 230 ├── columns: id:1(int!null) 231 ├── grouping columns: id:1(int!null) 232 ├── immutable, side-effects 233 ├── stats: [rows=6, distinct(1)=6, null(1)=0] 234 ├── key: (1) 235 └── select 236 ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) upper:10(string) unnest:11(string) generate_series:12(int!null) lower:13(string) 237 ├── immutable, side-effects 238 ├── stats: [rows=9.9, distinct(1)=6, null(1)=0, distinct(12)=6, null(12)=0] 239 ├── fd: (1)-->(4,6), (1)==(12), (12)==(1) 240 ├── project-set 241 │ ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) upper:10(string) unnest:11(string) generate_series:12(int) lower:13(string) 242 │ ├── immutable, side-effects 243 │ ├── stats: [rows=10000, distinct(1)=1000, null(1)=0, distinct(12)=7, null(12)=100] 244 │ ├── fd: (1)-->(4,6) 245 │ ├── scan articles 246 │ │ ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) 247 │ │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0] 248 │ │ ├── key: (1) 249 │ │ └── fd: (1)-->(4,6) 250 │ └── zip 251 │ ├── upper(title:4) [type=string, outer=(4), immutable] 252 │ ├── unnest(tag_list:6) [type=string, outer=(6), immutable, side-effects] 253 │ ├── generate_series(0, 1) [type=int, immutable, side-effects] 254 │ └── 'abc' [type=string] 255 └── filters 256 └── id:1 = generate_series:12 [type=bool, outer=(1,12), constraints=(/1: (/NULL - ]; /12: (/NULL - ]), fd=(1)==(12), (12)==(1)] 257 258 opt 259 SELECT id FROM articles WHERE title = ANY( 260 SELECT lower FROM ROWS FROM (upper(title), unnest(tag_list), generate_series(0,1), lower('ABC')) 261 ) 262 ---- 263 distinct-on 264 ├── columns: id:1(int!null) 265 ├── grouping columns: id:1(int!null) 266 ├── immutable, side-effects 267 ├── stats: [rows=9.85601173, distinct(1)=9.85601173, null(1)=0] 268 ├── key: (1) 269 └── select 270 ├── columns: id:1(int!null) title:4(string!null) tag_list:6(string[]) upper:10(string) unnest:11(string) generate_series:12(int) lower:13(string!null) 271 ├── immutable, side-effects 272 ├── stats: [rows=9.9, distinct(1)=9.85601173, null(1)=0, distinct(4)=1e-10, null(4)=0, distinct(13)=1e-10, null(13)=0] 273 ├── fd: (1)-->(4,6), (4)==(13), (13)==(4) 274 ├── project-set 275 │ ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) upper:10(string) unnest:11(string) generate_series:12(int) lower:13(string) 276 │ ├── immutable, side-effects 277 │ ├── stats: [rows=10000, distinct(1)=1000, null(1)=0, distinct(4)=100, null(4)=100, distinct(13)=1, null(13)=9000] 278 │ ├── fd: (1)-->(4,6) 279 │ ├── scan articles 280 │ │ ├── columns: id:1(int!null) title:4(string) tag_list:6(string[]) 281 │ │ ├── stats: [rows=1000, distinct(1)=1000, null(1)=0, distinct(4)=100, null(4)=10] 282 │ │ ├── key: (1) 283 │ │ └── fd: (1)-->(4,6) 284 │ └── zip 285 │ ├── upper(title:4) [type=string, outer=(4), immutable] 286 │ ├── unnest(tag_list:6) [type=string, outer=(6), immutable, side-effects] 287 │ ├── generate_series(0, 1) [type=int, immutable, side-effects] 288 │ └── 'abc' [type=string] 289 └── filters 290 └── title:4 = lower:13 [type=bool, outer=(4,13), constraints=(/4: (/NULL - ]; /13: (/NULL - ]), fd=(4)==(13), (13)==(4)]