github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/testdata/rules/ordering (about) 1 exec-ddl 2 CREATE TABLE abcde ( 3 a INT PRIMARY KEY, 4 b INT, 5 c INT, 6 d INT, 7 e INT DEFAULT(10), 8 UNIQUE INDEX bc (b, c) 9 ) 10 ---- 11 12 exec-ddl 13 CREATE TABLE xyz ( 14 x TEXT PRIMARY KEY, 15 y INT8, 16 z FLOAT8 17 ) 18 ---- 19 20 # -------------------------------------------------- 21 # SimplifyLimitOrdering 22 # -------------------------------------------------- 23 # Remove constant column. 24 norm expect=SimplifyLimitOrdering 25 SELECT d, e FROM (SELECT d, 1 AS one, e FROM abcde) ORDER BY d, one, e LIMIT 10 26 ---- 27 limit 28 ├── columns: d:4 e:5 29 ├── internal-ordering: +4,+5 30 ├── cardinality: [0 - 10] 31 ├── ordering: +4,+5 32 ├── sort 33 │ ├── columns: d:4 e:5 34 │ ├── ordering: +4,+5 35 │ ├── limit hint: 10.00 36 │ └── scan abcde 37 │ └── columns: d:4 e:5 38 └── 10 39 40 # Remove multiple constant columns. 41 norm expect=SimplifyLimitOrdering 42 SELECT b, c FROM abcde WHERE d=1 AND e=2 ORDER BY b, c, d, e, a LIMIT 10 43 ---- 44 limit 45 ├── columns: b:2 c:3 [hidden: a:1!null d:4!null e:5!null] 46 ├── internal-ordering: +2,+3,+1 opt(4,5) 47 ├── cardinality: [0 - 10] 48 ├── key: (1) 49 ├── fd: ()-->(4,5), (1)-->(2,3), (2,3)~~>(1) 50 ├── ordering: +2,+3,+1 opt(4,5) [actual: +2,+3,+1] 51 ├── sort 52 │ ├── columns: a:1!null b:2 c:3 d:4!null e:5!null 53 │ ├── key: (1) 54 │ ├── fd: ()-->(4,5), (1)-->(2,3), (2,3)~~>(1) 55 │ ├── ordering: +2,+3,+1 opt(4,5) [actual: +2,+3,+1] 56 │ ├── limit hint: 10.00 57 │ └── select 58 │ ├── columns: a:1!null b:2 c:3 d:4!null e:5!null 59 │ ├── key: (1) 60 │ ├── fd: ()-->(4,5), (1)-->(2,3), (2,3)~~>(1) 61 │ ├── scan abcde 62 │ │ ├── columns: a:1!null b:2 c:3 d:4 e:5 63 │ │ ├── key: (1) 64 │ │ └── fd: (1)-->(2-5), (2,3)~~>(1,4,5) 65 │ └── filters 66 │ ├── d:4 = 1 [outer=(4), constraints=(/4: [/1 - /1]; tight), fd=()-->(4)] 67 │ └── e:5 = 2 [outer=(5), constraints=(/5: [/2 - /2]; tight), fd=()-->(5)] 68 └── 10 69 70 # Remove functionally dependent column that's only used in ordering. 71 norm expect=SimplifyLimitOrdering 72 SELECT c FROM abcde ORDER BY b, c, a, d LIMIT 10 73 ---- 74 limit 75 ├── columns: c:3 [hidden: a:1!null b:2] 76 ├── internal-ordering: +2,+3,+1 77 ├── cardinality: [0 - 10] 78 ├── key: (1) 79 ├── fd: (1)-->(2,3), (2,3)~~>(1) 80 ├── ordering: +2,+3,+1 81 ├── sort 82 │ ├── columns: a:1!null b:2 c:3 83 │ ├── key: (1) 84 │ ├── fd: (1)-->(2,3), (2,3)~~>(1) 85 │ ├── ordering: +2,+3,+1 86 │ ├── limit hint: 10.00 87 │ └── scan abcde 88 │ ├── columns: a:1!null b:2 c:3 89 │ ├── key: (1) 90 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 91 └── 10 92 93 # -------------------------------------------------- 94 # SimplifyOffsetOrdering 95 # -------------------------------------------------- 96 # Remove all order by columns, because all are constant. 97 norm expect=SimplifyOffsetOrdering 98 SELECT d, e FROM (SELECT d, 1 AS one, e FROM abcde) ORDER BY one OFFSET 10 99 ---- 100 offset 101 ├── columns: d:4 e:5 102 ├── scan abcde 103 │ └── columns: d:4 e:5 104 └── 10 105 106 # -------------------------------------------------- 107 # SimplifyGroupByOrdering 108 # -------------------------------------------------- 109 # Remove columns functionally dependent on key. 110 # TODO(justin): figure out why this doesn't trigger SimplifyGroupByOrdering (it 111 # triggers SimplifyRootOrdering). 112 norm 113 SELECT array_agg(b), a, c FROM abcde GROUP BY b, a, c ORDER BY a, b, c 114 ---- 115 group-by 116 ├── columns: array_agg:6 a:1!null c:3 117 ├── grouping columns: a:1!null 118 ├── key: (1) 119 ├── fd: (1)-->(3,6) 120 ├── ordering: +1 121 ├── scan abcde 122 │ ├── columns: a:1!null b:2 c:3 123 │ ├── key: (1) 124 │ ├── fd: (1)-->(2,3), (2,3)~~>(1) 125 │ └── ordering: +1 126 └── aggregations 127 ├── array-agg [as=array_agg:6, outer=(2)] 128 │ └── b:2 129 └── const-agg [as=c:3, outer=(3)] 130 └── c:3 131 132 # ScalarGroupBy case. 133 norm expect=SimplifyGroupByOrdering 134 SELECT array_agg(b) FROM (SELECT * FROM abcde ORDER BY a, b, c) 135 ---- 136 scalar-group-by 137 ├── columns: array_agg:6 138 ├── internal-ordering: +1 139 ├── cardinality: [1 - 1] 140 ├── key: () 141 ├── fd: ()-->(6) 142 ├── scan abcde 143 │ ├── columns: a:1!null b:2 144 │ ├── key: (1) 145 │ ├── fd: (1)-->(2) 146 │ └── ordering: +1 147 └── aggregations 148 └── array-agg [as=array_agg:6, outer=(2)] 149 └── b:2 150 151 # DistinctOn case. 152 norm expect=SimplifyGroupByOrdering 153 SELECT DISTINCT ON (b, c) a, b, c FROM abcde ORDER BY b, c, a, d, e 154 ---- 155 distinct-on 156 ├── columns: a:1!null b:2 c:3 157 ├── grouping columns: b:2 c:3 158 ├── internal-ordering: +1 opt(2,3) 159 ├── key: (1) 160 ├── fd: (1)-->(2,3), (2,3)-->(1) 161 ├── ordering: +2,+3 162 ├── sort 163 │ ├── columns: a:1!null b:2 c:3 164 │ ├── key: (1) 165 │ ├── fd: (1)-->(2,3), (2,3)~~>(1) 166 │ ├── ordering: +2,+3,+1 167 │ └── scan abcde 168 │ ├── columns: a:1!null b:2 c:3 169 │ ├── key: (1) 170 │ └── fd: (1)-->(2,3), (2,3)~~>(1) 171 └── aggregations 172 └── first-agg [as=a:1, outer=(1)] 173 └── a:1 174 175 # -------------------------------------------------- 176 # SimplifyOrdinalityOrdering 177 # -------------------------------------------------- 178 # Remove column functionally dependent on multi-column key. 179 norm expect=SimplifyOrdinalityOrdering 180 SELECT * FROM (SELECT * FROM abcde WHERE b IS NOT NULL AND c IS NOT NULL ORDER BY c, d, b, e) WITH ORDINALITY 181 ---- 182 ordinality 183 ├── columns: a:1!null b:2!null c:3!null d:4 e:5 ordinality:6!null 184 ├── key: (1) 185 ├── fd: (1)-->(2-6), (2,3)-->(1,4,5), (6)-->(1-5) 186 └── sort 187 ├── columns: a:1!null b:2!null c:3!null d:4 e:5 188 ├── key: (1) 189 ├── fd: (1)-->(2-5), (2,3)-->(1,4,5) 190 ├── ordering: +3,+4,+2 191 └── select 192 ├── columns: a:1!null b:2!null c:3!null d:4 e:5 193 ├── key: (1) 194 ├── fd: (1)-->(2-5), (2,3)-->(1,4,5) 195 ├── scan abcde 196 │ ├── columns: a:1!null b:2 c:3 d:4 e:5 197 │ ├── key: (1) 198 │ └── fd: (1)-->(2-5), (2,3)~~>(1,4,5) 199 └── filters 200 ├── b:2 IS NOT NULL [outer=(2), constraints=(/2: (/NULL - ]; tight)] 201 └── c:3 IS NOT NULL [outer=(3), constraints=(/3: (/NULL - ]; tight)] 202 203 # -------------------------------------------------- 204 # SimplifyExplainOrdering 205 # -------------------------------------------------- 206 # Remove functionally dependent synthesized column. 207 norm expect=SimplifyExplainOrdering 208 EXPLAIN SELECT b, b+1 AS plus, c FROM abcde ORDER BY b, plus, c 209 ---- 210 explain 211 ├── columns: tree:7 field:8 description:9 212 └── sort 213 ├── columns: b:2 plus:6 c:3 214 ├── lax-key: (2,3) 215 ├── fd: (2)-->(6) 216 ├── ordering: +2,+3 217 └── project 218 ├── columns: plus:6 b:2 c:3 219 ├── lax-key: (2,3) 220 ├── fd: (2)-->(6) 221 ├── scan abcde 222 │ ├── columns: b:2 c:3 223 │ └── lax-key: (2,3) 224 └── projections 225 └── b:2 + 1 [as=plus:6, outer=(2)] 226 227 # Regression: Explain a statement having constant column, but with no ordering. 228 norm 229 SELECT field FROM [EXPLAIN SELECT 123 AS k] 230 ---- 231 project 232 ├── columns: field:6 233 ├── explain 234 │ ├── columns: tree:2 field:3 description:4 235 │ └── values 236 │ ├── columns: k:1!null 237 │ ├── cardinality: [1 - 1] 238 │ ├── key: () 239 │ ├── fd: ()-->(1) 240 │ └── (123,) 241 └── projections 242 └── field:3 [as=field:6, outer=(3)]