vitess.io/vitess@v0.16.2/go/vt/vtgate/planbuilder/ordering.go (about) 1 /* 2 Copyright 2020 The Vitess Authors. 3 Licensed under the Apache License, Version 2.0 (the "License"); 4 you may not use this file except in compliance with the License. 5 You may obtain a copy of the License at 6 http://www.apache.org/licenses/LICENSE-2.0 7 Unless required by applicable law or agreed to in writing, software 8 distributed under the License is distributed on an "AS IS" BASIS, 9 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 10 See the License for the specific language governing permissions and 11 limitations under the License. 12 */ 13 14 package planbuilder 15 16 import ( 17 "fmt" 18 19 "vitess.io/vitess/go/mysql/collations" 20 "vitess.io/vitess/go/vt/sqlparser" 21 "vitess.io/vitess/go/vt/vterrors" 22 "vitess.io/vitess/go/vt/vtgate/engine" 23 ) 24 25 type v3Order struct { 26 *sqlparser.Order 27 fromGroupBy bool 28 } 29 30 type v3OrderBy []*v3Order 31 32 func planOrdering(pb *primitiveBuilder, input logicalPlan, orderBy v3OrderBy) (logicalPlan, error) { 33 switch node := input.(type) { 34 case *simpleProjection, *vindexFunc: 35 if len(orderBy) == 0 { 36 return node, nil 37 } 38 return newMemorySort(node, orderBy) 39 case *distinct: 40 // TODO: this is weird, but needed 41 newInput, err := planOrdering(pb, node.input, orderBy) 42 node.input = newInput 43 return node, err 44 case *pulloutSubquery: 45 plan, err := planOrdering(pb, node.underlying, orderBy) 46 if err != nil { 47 return nil, err 48 } 49 node.underlying = plan 50 return node, nil 51 case *route: 52 return planRouteOrdering(orderBy, node) 53 case *join: 54 return planJoinOrdering(pb, orderBy, node) 55 case *orderedAggregate: 56 return planOAOrdering(pb, orderBy, node) 57 case *mergeSort: 58 return nil, vterrors.VT12001("ORDER BY on top of ORDER BY") 59 case *concatenate: 60 if len(orderBy) == 0 { 61 return input, nil 62 } 63 return nil, vterrors.VT12001("ORDER BY on top of UNION") 64 } 65 return nil, vterrors.VT13001(fmt.Sprintf("unreachable %T.ordering", input)) 66 } 67 68 func planOAOrdering(pb *primitiveBuilder, orderBy v3OrderBy, oa *orderedAggregate) (logicalPlan, error) { 69 // The requested order must be such that the ordering can be done 70 // before the group by, which will allow us to push it down to the 71 // route. This is actually true in most use cases, except for situations 72 // where ordering is requested on values of an aggregate result. 73 // Such constructs will need to be handled by a separate 'Sorter' 74 // primitive, after aggregation is done. For example, the following 75 // constructs are allowed: 76 // 'select a, b, count(*) from t group by a, b order by a desc, b asc' 77 // 'select a, b, count(*) from t group by a, b order by b' 78 // The following construct is not allowed: 79 // 'select a, count(*) from t group by a order by count(*)' 80 // Treat order by null as nil order by. 81 if len(orderBy) == 1 { 82 if _, ok := orderBy[0].Expr.(*sqlparser.NullVal); ok { 83 orderBy = nil 84 } 85 } 86 87 // referenced tracks the keys referenced by the order by clause. 88 referenced := make([]bool, len(oa.groupByKeys)) 89 postSort := false 90 selOrderBy := make(v3OrderBy, 0, len(orderBy)) 91 for _, order := range orderBy { 92 // Identify the order by column. 93 var orderByCol *column 94 switch expr := order.Expr.(type) { 95 case *sqlparser.Literal: 96 num, err := ResultFromNumber(oa.resultColumns, expr, "order clause") 97 if err != nil { 98 return nil, err 99 } 100 orderByCol = oa.resultColumns[num].column 101 case *sqlparser.ColName: 102 orderByCol = expr.Metadata.(*column) 103 case *sqlparser.CastExpr: 104 col, ok := expr.Expr.(*sqlparser.ColName) 105 if !ok { 106 return nil, vterrors.VT12001(fmt.Sprintf("in scatter query: complex ORDER BY expression: %s", sqlparser.String(expr))) 107 } 108 orderByCol = col.Metadata.(*column) 109 case *sqlparser.ConvertExpr: 110 col, ok := expr.Expr.(*sqlparser.ColName) 111 if !ok { 112 return nil, vterrors.VT12001(fmt.Sprintf("in scatter query: complex ORDER BY expression: %s", sqlparser.String(expr))) 113 } 114 orderByCol = col.Metadata.(*column) 115 default: 116 return nil, vterrors.VT12001(fmt.Sprintf("in scatter query: complex ORDER BY expression: %v", sqlparser.String(expr))) 117 } 118 119 // Match orderByCol against the group by columns. 120 found := false 121 for j, groupBy := range oa.groupByKeys { 122 if oa.resultColumns[groupBy.KeyCol].column != orderByCol { 123 continue 124 } 125 126 found = true 127 referenced[j] = true 128 order.fromGroupBy = groupBy.FromGroupBy 129 selOrderBy = append(selOrderBy, order) 130 break 131 } 132 if !found { 133 postSort = true 134 } 135 } 136 137 // Append any unreferenced keys at the end of the order by. 138 for i, groupByKey := range oa.groupByKeys { 139 if referenced[i] { 140 continue 141 } 142 // Build a brand new reference for the key. 143 col, err := BuildColName(oa.input.ResultColumns(), groupByKey.KeyCol) 144 if err != nil { 145 return nil, vterrors.Wrapf(err, "generating ORDER BY clause") 146 } 147 selOrderBy = append(selOrderBy, &v3Order{ 148 Order: &sqlparser.Order{Expr: col, Direction: sqlparser.AscOrder}, 149 fromGroupBy: groupByKey.FromGroupBy, 150 }) 151 } 152 153 // Append the distinct aggregate if any. 154 if oa.extraDistinct != nil { 155 selOrderBy = append(selOrderBy, &v3Order{ 156 Order: &sqlparser.Order{Expr: oa.extraDistinct, Direction: sqlparser.AscOrder}, 157 fromGroupBy: true, 158 }) 159 } 160 161 // Push down the order by. 162 // It's ok to push the original AST down because all references 163 // should point to the route. Only aggregate functions are originated 164 // by node, and we currently don't allow the ORDER BY to reference them. 165 plan, err := planOrdering(pb, oa.input, selOrderBy) 166 if err != nil { 167 return nil, err 168 } 169 oa.input = plan 170 if postSort { 171 return newMemorySort(oa, orderBy) 172 } 173 return oa, nil 174 } 175 176 func planJoinOrdering(pb *primitiveBuilder, orderBy v3OrderBy, node *join) (logicalPlan, error) { 177 isSpecial := false 178 switch len(orderBy) { 179 case 0: 180 isSpecial = true 181 case 1: 182 if _, ok := orderBy[0].Expr.(*sqlparser.NullVal); ok { 183 isSpecial = true 184 } else if f, ok := orderBy[0].Expr.(*sqlparser.FuncExpr); ok { 185 if f.Name.Lowered() == "rand" { 186 isSpecial = true 187 } 188 } 189 } 190 if isSpecial { 191 l, err := planOrdering(pb, node.Left, orderBy) 192 if err != nil { 193 return nil, err 194 } 195 node.Left = l 196 r, err := planOrdering(pb, node.Right, orderBy) 197 if err != nil { 198 return nil, err 199 } 200 node.Right = r 201 return node, nil 202 } 203 204 for _, order := range orderBy { 205 if e, ok := order.Expr.(*sqlparser.Literal); ok { 206 // This block handles constructs that use ordinals for 'ORDER BY'. For example: 207 // SELECT a, b, c FROM t1, t2 ORDER BY 1, 2, 3. 208 num, err := ResultFromNumber(node.ResultColumns(), e, "order clause") 209 if err != nil { 210 return nil, err 211 } 212 if node.ResultColumns()[num].column.Origin().Order() > node.Left.Order() { 213 return newMemorySort(node, orderBy) 214 } 215 } else { 216 // Analyze column references within the expression to make sure they all 217 // go to the left. 218 err := sqlparser.Walk(func(in sqlparser.SQLNode) (kontinue bool, err error) { 219 switch e := in.(type) { 220 case *sqlparser.ColName: 221 if e.Metadata.(*column).Origin().Order() > node.Left.Order() { 222 return false, vterrors.VT12001("ORDER BY spans across shards") 223 } 224 case *sqlparser.Subquery: 225 // Unreachable because ResolveSymbols perfoms this check up above. 226 return false, vterrors.VT12001("ORDER BY has subquery") 227 } 228 return true, nil 229 }, order.Expr) 230 if err != nil { 231 return newMemorySort(node, orderBy) 232 } 233 } 234 } 235 236 // There were no errors. We can push the order by to the left-most route. 237 l, err := planOrdering(pb, node.Left, orderBy) 238 if err != nil { 239 return nil, err 240 } 241 node.Left = l 242 // Still need to push an empty order by to the right. 243 r, err := planOrdering(pb, node.Right, nil) 244 if err != nil { 245 return nil, err 246 } 247 node.Right = r 248 return node, nil 249 } 250 251 func planRouteOrdering(orderBy v3OrderBy, node *route) (logicalPlan, error) { 252 switch len(orderBy) { 253 case 0: 254 return node, nil 255 case 1: 256 isSpecial := false 257 if _, ok := orderBy[0].Expr.(*sqlparser.NullVal); ok { 258 isSpecial = true 259 } else if f, ok := orderBy[0].Expr.(*sqlparser.FuncExpr); ok { 260 if f.Name.Lowered() == "rand" { 261 isSpecial = true 262 } 263 } 264 if isSpecial { 265 node.Select.AddOrder(orderBy[0].Order) 266 return node, nil 267 } 268 } 269 270 if node.isSingleShard() { 271 for _, order := range orderBy { 272 node.Select.AddOrder(order.Order) 273 } 274 return node, nil 275 } 276 277 // If it's a scatter, we have to populate the OrderBy field. 278 for _, order := range orderBy { 279 colNumber := -1 280 switch expr := order.Expr.(type) { 281 case *sqlparser.Literal: 282 var err error 283 if colNumber, err = ResultFromNumber(node.resultColumns, expr, "order clause"); err != nil { 284 return nil, err 285 } 286 case *sqlparser.ColName: 287 c := expr.Metadata.(*column) 288 for i, rc := range node.resultColumns { 289 if rc.column == c { 290 colNumber = i 291 break 292 } 293 } 294 case *sqlparser.UnaryExpr: 295 col, ok := expr.Expr.(*sqlparser.ColName) 296 if !ok { 297 return nil, vterrors.VT12001(fmt.Sprintf("in scatter query: complex ORDER BY expression: %s", sqlparser.String(expr))) 298 } 299 c := col.Metadata.(*column) 300 for i, rc := range node.resultColumns { 301 if rc.column == c { 302 colNumber = i 303 break 304 } 305 } 306 default: 307 return nil, vterrors.VT12001(fmt.Sprintf("in scatter query: complex ORDER BY expression: %s", sqlparser.String(expr))) 308 } 309 // If column is not found, then the order by is referencing 310 // a column that's not on the select list. 311 if colNumber == -1 { 312 return nil, vterrors.VT12001(fmt.Sprintf("in scatter query: ORDER BY must reference a column in the SELECT list: %s", sqlparser.String(order))) 313 } 314 starColFixedIndex := colNumber 315 if selectStatement, ok := node.Select.(*sqlparser.Select); ok { 316 for i, selectExpr := range selectStatement.SelectExprs { 317 if starExpr, ok := selectExpr.(*sqlparser.StarExpr); ok { 318 if i < colNumber { 319 tableName := starExpr.TableName 320 tableMap := node.resultColumns[i].column.st.tables 321 var tableMeta *table 322 if tableName.IsEmpty() && len(tableMap) == 1 { 323 for j := range tableMap { 324 tableMeta = tableMap[j] 325 } 326 } else { 327 tableMeta = tableMap[tableName] 328 } 329 if tableMeta == nil || !tableMeta.isAuthoritative { 330 return nil, vterrors.VT12001("in scatter query, cannot ORDER BY a column that comes after `*` expressions in the SELECT list") 331 } 332 starColFixedIndex += len(tableMeta.columnNames) - 1 333 } 334 } 335 } 336 } 337 338 // TODO(king-11) pass in collation here 339 ob := engine.OrderByParams{ 340 Col: colNumber, 341 WeightStringCol: -1, 342 Desc: order.Direction == sqlparser.DescOrder, 343 StarColFixedIndex: starColFixedIndex, 344 FromGroupBy: order.fromGroupBy, 345 CollationID: collations.Unknown, 346 } 347 node.eroute.OrderBy = append(node.eroute.OrderBy, ob) 348 349 node.Select.AddOrder(order.Order) 350 } 351 return newMergeSort(node), nil 352 }