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  }