github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/logprops/offset (about) 1 exec-ddl 2 CREATE TABLE xyzs (x INT PRIMARY KEY, y INT, z FLOAT NOT NULL, s STRING, UNIQUE (s DESC, z)) 3 ---- 4 5 exec-ddl 6 CREATE TABLE kuv (k INT PRIMARY KEY, u FLOAT, v STRING) 7 ---- 8 9 build 10 SELECT * FROM xyzs OFFSET 1 11 ---- 12 offset 13 ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 14 ├── key: (1) 15 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 16 ├── prune: (1-4) 17 ├── interesting orderings: (+1) (-4,+3,+1) 18 ├── scan xyzs 19 │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 20 │ ├── key: (1) 21 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 22 │ ├── prune: (1-4) 23 │ └── interesting orderings: (+1) (-4,+3,+1) 24 └── const: 1 [type=int] 25 26 build 27 SELECT * FROM xyzs OFFSET (SELECT 1) 28 ---- 29 offset 30 ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 31 ├── key: (1) 32 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 33 ├── prune: (1-4) 34 ├── interesting orderings: (+1) (-4,+3,+1) 35 ├── scan xyzs 36 │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 37 │ ├── key: (1) 38 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 39 │ ├── prune: (1-4) 40 │ └── interesting orderings: (+1) (-4,+3,+1) 41 └── subquery [type=int] 42 └── max1-row 43 ├── columns: "?column?":5(int!null) 44 ├── error: "more than one row returned by a subquery used as an expression" 45 ├── cardinality: [1 - 1] 46 ├── key: () 47 ├── fd: ()-->(5) 48 └── project 49 ├── columns: "?column?":5(int!null) 50 ├── cardinality: [1 - 1] 51 ├── key: () 52 ├── fd: ()-->(5) 53 ├── prune: (5) 54 ├── values 55 │ ├── cardinality: [1 - 1] 56 │ ├── key: () 57 │ └── tuple [type=tuple] 58 └── projections 59 └── const: 1 [as="?column?":5, type=int] 60 61 build 62 SELECT * FROM xyzs OFFSET 0 63 ---- 64 offset 65 ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 66 ├── key: (1) 67 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 68 ├── prune: (1-4) 69 ├── interesting orderings: (+1) (-4,+3,+1) 70 ├── scan xyzs 71 │ ├── columns: x:1(int!null) y:2(int) z:3(float!null) s:4(string) 72 │ ├── key: (1) 73 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 74 │ ├── prune: (1-4) 75 │ └── interesting orderings: (+1) (-4,+3,+1) 76 └── const: 0 [type=int] 77 78 # Propagate outer columns. 79 build 80 SELECT (SELECT x FROM kuv OFFSET y) FROM xyzs 81 ---- 82 project 83 ├── columns: x:9(int) 84 ├── prune: (9) 85 ├── scan xyzs 86 │ ├── columns: xyzs.x:1(int!null) y:2(int) z:3(float!null) s:4(string) 87 │ ├── key: (1) 88 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 89 │ ├── prune: (1-4) 90 │ └── interesting orderings: (+1) (-4,+3,+1) 91 └── projections 92 └── subquery [as=x:9, type=int, outer=(1,2), correlated-subquery] 93 └── max1-row 94 ├── columns: x:8(int) 95 ├── error: "more than one row returned by a subquery used as an expression" 96 ├── outer: (1,2) 97 ├── cardinality: [0 - 1] 98 ├── key: () 99 ├── fd: ()-->(8) 100 └── offset 101 ├── columns: x:8(int) 102 ├── outer: (1,2) 103 ├── fd: ()-->(8) 104 ├── prune: (8) 105 ├── project 106 │ ├── columns: x:8(int) 107 │ ├── outer: (1) 108 │ ├── fd: ()-->(8) 109 │ ├── prune: (8) 110 │ ├── scan kuv 111 │ │ ├── columns: k:5(int!null) u:6(float) v:7(string) 112 │ │ ├── key: (5) 113 │ │ ├── fd: (5)-->(6,7) 114 │ │ ├── prune: (5-7) 115 │ │ └── interesting orderings: (+5) 116 │ └── projections 117 │ └── variable: xyzs.x:1 [as=x:8, type=int, outer=(1)] 118 └── variable: y:2 [type=int] 119 120 # Reduce cardinality of input set. 121 build 122 SELECT * 123 FROM ((SELECT x FROM xyzs LIMIT 10) UNION ALL (SELECT * FROM (VALUES (1), (2), (3)))) 124 OFFSET 2 125 ---- 126 offset 127 ├── columns: x:6(int!null) 128 ├── cardinality: [1 - 11] 129 ├── prune: (6) 130 ├── union-all 131 │ ├── columns: x:6(int!null) 132 │ ├── left columns: xyzs.x:1(int) 133 │ ├── right columns: column1:5(int) 134 │ ├── cardinality: [3 - 13] 135 │ ├── prune: (6) 136 │ ├── limit 137 │ │ ├── columns: xyzs.x:1(int!null) 138 │ │ ├── cardinality: [0 - 10] 139 │ │ ├── key: (1) 140 │ │ ├── prune: (1) 141 │ │ ├── interesting orderings: (+1) 142 │ │ ├── project 143 │ │ │ ├── columns: xyzs.x:1(int!null) 144 │ │ │ ├── key: (1) 145 │ │ │ ├── limit hint: 10.00 146 │ │ │ ├── prune: (1) 147 │ │ │ ├── interesting orderings: (+1) 148 │ │ │ └── scan xyzs 149 │ │ │ ├── columns: xyzs.x:1(int!null) y:2(int) z:3(float!null) s:4(string) 150 │ │ │ ├── key: (1) 151 │ │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 152 │ │ │ ├── limit hint: 10.00 153 │ │ │ ├── prune: (1-4) 154 │ │ │ └── interesting orderings: (+1) (-4,+3,+1) 155 │ │ └── const: 10 [type=int] 156 │ └── values 157 │ ├── columns: column1:5(int!null) 158 │ ├── cardinality: [3 - 3] 159 │ ├── prune: (5) 160 │ ├── tuple [type=tuple{int}] 161 │ │ └── const: 1 [type=int] 162 │ ├── tuple [type=tuple{int}] 163 │ │ └── const: 2 [type=int] 164 │ └── tuple [type=tuple{int}] 165 │ └── const: 3 [type=int] 166 └── const: 2 [type=int] 167 168 # Test very high offset (> max uint32). 169 opt 170 SELECT s, x FROM (SELECT * FROM xyzs LIMIT 100) WHERE s='foo' OFFSET 4294967296 171 ---- 172 values 173 ├── columns: s:4(string!null) x:1(int!null) 174 ├── cardinality: [0 - 0] 175 ├── key: () 176 ├── fd: ()-->(1,4) 177 └── prune: (1,4)