// orderBy constructs a sortNode based on the ORDER BY clause. // // In the general case (SELECT/UNION/VALUES), we can sort by a column index or a // column name. // // However, for a SELECT, we can also sort by the pre-alias column name (SELECT // a AS b ORDER BY b) as well as expressions (SELECT a, b, ORDER BY a+b). In // this case, construction of the sortNode might adjust the number of render // targets in the selectNode if any ordering expressions are specified. // // TODO(dan): SQL also allows sorting a VALUES or UNION by an expression. // Support this. It will reduce some of the special casing below, but requires a // generalization of how to add derived columns to a SelectStatement. func (p *planner) orderBy(orderBy parser.OrderBy, n planNode) (*sortNode, error) { if orderBy == nil { return nil, nil } // Multiple tests below use selectNode as a special case. // So factor the cast. s, _ := n.(*selectNode) // We grab a copy of columns here because we might add new render targets // below. This is the set of columns requested by the query. columns := n.Columns() numOriginalCols := len(columns) if s != nil { numOriginalCols = s.numOriginalCols } var ordering sqlbase.ColumnOrdering for _, o := range orderBy { direction := encoding.Ascending if o.Direction == parser.Descending { direction = encoding.Descending } index := -1 // Unwrap parenthesized expressions like "((a))" to "a". expr := parser.StripParens(o.Expr) // The logical data source for ORDER BY is the list of render // expressions for a SELECT, as specified in the input SQL text // (or an entire UNION or VALUES clause). Alas, SQL has some // historical baggage from SQL92 and there are some special cases: // // SQL92 rules: // // 1) if the expression is the aliased (AS) name of a render // expression in a SELECT clause, then use that // render as sort key. // e.g. SELECT a AS b, b AS c ORDER BY b // this sorts on the first render. // // 2) column ordinals. If a simple integer literal is used, // optionally enclosed within parentheses but *not subject to // any arithmetic*, then this refers to one of the columns of // the data source. Then use the render expression at that // ordinal position as sort key. // // SQL99 rules: // // 3) otherwise, if the expression is already in the render list, // then use that render as sort key. // e.g. SELECT b AS c ORDER BY b // this sorts on the first render. // (this is an optimization) // // 4) if the sort key is not dependent on the data source (no // IndexedVar) then simply do not sort. (this is an optimization) // // 5) otherwise, add a new render with the ORDER BY expression // and use that as sort key. // e.g. SELECT a FROM t ORDER by b // e.g. SELECT a, b FROM t ORDER by a+b // First, deal with render aliases. if vBase, ok := expr.(parser.VarName); index == -1 && ok { v, err := vBase.NormalizeVarName() if err != nil { return nil, err } if c, ok := v.(*parser.ColumnItem); ok && c.TableName.Table() == "" { // Look for an output column that matches the name. This // handles cases like: // // SELECT a AS b FROM t ORDER BY b target := c.ColumnName.Normalize() for j, col := range columns { if parser.ReNormalizeName(col.Name) == target { if index != -1 { // There is more than one render alias that matches the ORDER BY // clause. Here, SQL92 is specific as to what should be done: // if the underlying expression is known (we're on a selectNode) // and it is equivalent, then just accept that and ignore the ambiguity. // This plays nice with `SELECT b, * FROM t ORDER BY b`. Otherwise, // reject with an ambituity error. if s == nil || !s.equivalentRenders(j, index) { return nil, errors.Errorf("ORDER BY \"%s\" is ambiguous", target) } } index = j } } } } // So Then, deal with column ordinals. if index == -1 { col, err := p.colIndex(numOriginalCols, expr, "ORDER BY") if err != nil { return nil, err } if col != -1 { index = col } } if s != nil { // Try to optimize constant sorts. For this we need to resolve // names to IndexedVars then see if there were any row-dependent // expressions found in the sort expression. sortExpr := expr if index != -1 { // We found a render above, so fetch it. This is needed // because if the sort expression is a reference to a render // alias, resolveNames below would be incorrect. sortExpr = s.render[index] } _, hasRowDependentValues, err := p.resolveNames(sortExpr, s.sourceInfo, s.ivarHelper) if err != nil { return nil, err } if !hasRowDependentValues { // No sorting needed! continue } } // Finally, if we haven't found anything so far, we really // need a new render. // TODO(knz/dan) currently this is only possible for selectNode. // If we are dealing with a UNION or something else we would need // to fabricate an intermediate selectNode to add the new render. if index == -1 && s != nil { cols, exprs, hasStar, err := p.computeRender(parser.SelectExpr{Expr: expr}, parser.TypeAny, s.source.info, s.ivarHelper, true) if err != nil { return nil, err } s.isStar = s.isStar || hasStar if len(cols) == 0 { // Nothing was expanded! No order here. continue } colIdxs := s.addOrMergeRenders(cols, exprs, true) for i := 0; i < len(colIdxs)-1; i++ { // If more than 1 column were expanded, turn them into sort columns too. // Except the last one, which will be added below. ordering = append(ordering, sqlbase.ColumnOrderInfo{ColIdx: colIdxs[i], Direction: direction}) } index = colIdxs[len(colIdxs)-1] } if index == -1 { return nil, errors.Errorf("column %s does not exist", expr) } ordering = append(ordering, sqlbase.ColumnOrderInfo{ColIdx: index, Direction: direction}) } if ordering == nil { // All the sort expressions are constant. Simply drop the sort node. return nil, nil } return &sortNode{ctx: p.ctx(), p: p, columns: columns, ordering: ordering}, nil }
// orderBy constructs a sortNode based on the ORDER BY clause. // // In the general case (SELECT/UNION/VALUES), we can sort by a column index or a // column name. // // However, for a SELECT, we can also sort by the pre-alias column name (SELECT // a AS b ORDER BY b) as well as expressions (SELECT a, b, ORDER BY a+b). In // this case, construction of the sortNode might adjust the number of render // targets in the selectNode if any ordering expressions are specified. // // TODO(dan): SQL also allows sorting a VALUES or UNION by an expression. // Support this. It will reduce some of the special casing below, but requires a // generalization of how to add derived columns to a SelectStatement. func (p *planner) orderBy(orderBy parser.OrderBy, n planNode) (*sortNode, error) { if orderBy == nil { return nil, nil } // Multiple tests below use selectNode as a special case. // So factor the cast. s, _ := n.(*selectNode) // We grab a copy of columns here because we might add new render targets // below. This is the set of columns requested by the query. columns := n.Columns() numOriginalCols := len(columns) if s != nil { numOriginalCols = s.numOriginalCols } var ordering sqlbase.ColumnOrdering for _, o := range orderBy { direction := encoding.Ascending if o.Direction == parser.Descending { direction = encoding.Descending } index := -1 // Unwrap parenthesized expressions like "((a))" to "a". expr := parser.StripParens(o.Expr) // The logical data source for ORDER BY is the list of render // expressions for a SELECT, as specified in the input SQL text // (or an entire UNION or VALUES clause). Alas, SQL has some // historical baggage and there are some special cases: // // 1) column ordinals. If a simple integer literal is used, // optionally enclosed within parentheses but *not subject to // any arithmetic*, then this refers to one of the columns of // the data source. Then use the render expression at that // ordinal position as sort key. // // 2) if the expression is the aliased (AS) name of a render // expression in a SELECT clause, then use that // render as sort key. // e.g. SELECT a AS b, b AS c ORDER BY b // this sorts on the first render. // // 3) otherwise, if the expression is already in the render list, // then use that render as sort key. // e.g. SELECT b AS c ORDER BY b // this sorts on the first render. // (this is an optimization) // // 4) if the sort key is not dependent on the data source (no // IndexedVar) then simply do not sort. (this is an optimization) // // 5) otherwise, add a new render with the ORDER BY expression // and use that as sort key. // e.g. SELECT a FROM t ORDER by b // e.g. SELECT a, b FROM t ORDER by a+b // // So first, deal with column ordinals. col, err := p.colIndex(numOriginalCols, expr, "ORDER BY") if err != nil { return nil, err } if col != -1 { index = col } // Now, deal with render aliases. if vBase, ok := expr.(parser.VarName); index == -1 && ok { v, err := vBase.NormalizeVarName() if err != nil { return nil, err } if c, ok := v.(*parser.ColumnItem); ok && c.TableName.Table() == "" { // Look for an output column that matches the name. This // handles cases like: // // SELECT a AS b FROM t ORDER BY b target := c.ColumnName.Normalize() for j, col := range columns { if parser.ReNormalizeName(col.Name) == target { index = j break } } } } if s != nil { // Try to optimize constant sorts. For this we need to resolve // names to IndexedVars then see if there were any vars // substituted. sortExpr := expr if index != -1 { // We found a render above, so fetch it. This is needed // because if the sort expression is a reference to a render // alias, resolveNames below would be incorrect. sortExpr = s.render[index] } resolved, hasVars, err := p.resolveNames(sortExpr, s.sourceInfo, s.ivarHelper) if err != nil { return nil, err } if !hasVars { // No sorting needed! continue } // Now, try to find an equivalent render. We use the syntax // representation as approximation of equivalence. // We also use the expression after name resolution so // that comparison occurs after replacing ordinal references // to IndexedVars. if index == -1 { exprStr := parser.AsStringWithFlags(resolved, parser.FmtSymbolicVars) for j, render := range s.render { if parser.AsStringWithFlags(render, parser.FmtSymbolicVars) == exprStr { index = j break } } } } // Finally, if we haven't found anything so far, we really // need a new render. // TODO(knz/dan) currently this is only possible for selectNode. // If we are dealing with a UNION or something else we would need // to fabricate an intermediate selectNode to add the new render. if index == -1 && s != nil { // We need to add a new render, but let's be careful: if there's // a star in there, we're really adding multiple columns. These // all become sort columns! And if no columns are expanded, then // no columns become sort keys. nextRenderIdx := len(s.render) // TODO(knz) the optimizations above which reuse existing // renders and skip ordering for constant expressions are not // applied by addRender(). In other words, "ORDER BY foo.*" will // not be optimized as well as "ORDER BY foo.x, foo.y". We // could do this either here or as a separate later // optimization. if err := s.addRender(parser.SelectExpr{Expr: expr}, nil); err != nil { return nil, err } for extraIdx := nextRenderIdx; extraIdx < len(s.render)-1; extraIdx++ { // If more than 1 column were expanded, turn them into sort columns too. // Except the last one, which will be added below. ordering = append(ordering, sqlbase.ColumnOrderInfo{ColIdx: extraIdx, Direction: direction}) } if len(s.render) == nextRenderIdx { // Nothing was expanded! So there is no order here. continue } index = len(s.render) - 1 } if index == -1 { return nil, errors.Errorf("column %s does not exist", expr) } ordering = append(ordering, sqlbase.ColumnOrderInfo{ColIdx: index, Direction: direction}) } if ordering == nil { // All the sort expressions are constant. Simply drop the sort node. return nil, nil } return &sortNode{ctx: p.ctx(), p: p, columns: columns, ordering: ordering}, nil }
// orderBy constructs a sortNode based on the ORDER BY clause. // // In the general case (SELECT/UNION/VALUES), we can sort by a column index or a // column name. // // However, for a SELECT, we can also sort by the pre-alias column name (SELECT // a AS b ORDER BY b) as well as expressions (SELECT a, b, ORDER BY a+b). In // this case, construction of the sortNode might adjust the number of render // targets in the selectNode if any ordering expressions are specified. // // TODO(dan): SQL also allows sorting a VALUES or UNION by an expression. // Support this. It will reduce some of the special casing below, but requires a // generalization of how to add derived columns to a SelectStatement. func (p *planner) orderBy(orderBy parser.OrderBy, n planNode) (*sortNode, error) { if orderBy == nil { return nil, nil } // We grab a copy of columns here because we might add new render targets // below. This is the set of columns requested by the query. columns := n.Columns() numOriginalCols := len(columns) if s, ok := n.(*selectNode); ok { numOriginalCols = s.numOriginalCols } var ordering sqlbase.ColumnOrdering for _, o := range orderBy { index := -1 // Unwrap parenthesized expressions like "((a))" to "a". expr := parser.StripParens(o.Expr) if vBase, ok := expr.(parser.VarName); ok { v, err := vBase.NormalizeVarName() if err != nil { return nil, err } var c *parser.ColumnItem switch t := v.(type) { case *parser.ColumnItem: c = t default: return nil, fmt.Errorf("invalid syntax for ORDER BY: %s", v) } if c.TableName.Table() == "" { // Look for an output column that matches the name. This // handles cases like: // // SELECT a AS b FROM t ORDER BY b target := c.ColumnName.Normalize() for j, col := range columns { if parser.ReNormalizeName(col.Name) == target { index = j break } } } if s, ok := n.(*selectNode); ok && index == -1 { // No output column matched the name, so look for an existing // render target that matches the column name. This handles cases like: // // SELECT a AS b FROM t ORDER BY a colIdx, err := s.source.findUnaliasedColumn(c) if err != nil { return nil, err } if colIdx != invalidColIdx { for j, r := range s.render { if ivar, ok := r.(*parser.IndexedVar); ok { s.ivarHelper.AssertSameContainer(ivar) if ivar.Idx == colIdx { index = j break } } } } } } if index == -1 { // The order by expression matched neither an output column nor an // existing render target. if col, err := colIndex(numOriginalCols, expr); err != nil { return nil, err } else if col >= 0 { index = col } else if s, ok := n.(*selectNode); ok { // TODO(dan): Once we support VALUES (1), (2) ORDER BY 3*4, this type // check goes away. // Add a new render expression to use for ordering. This // handles cases were the expression is either not a name or // is a name that is otherwise not referenced by the query: // // SELECT a FROM t ORDER by b // SELECT a, b FROM t ORDER by a+b if err := s.addRender(parser.SelectExpr{Expr: expr}, nil); err != nil { return nil, err } index = len(s.columns) - 1 } else { return nil, errors.Errorf("column %s does not exist", expr) } } direction := encoding.Ascending if o.Direction == parser.Descending { direction = encoding.Descending } ordering = append(ordering, sqlbase.ColumnOrderInfo{ColIdx: index, Direction: direction}) } return &sortNode{ctx: p.ctx(), p: p, columns: columns, ordering: ordering}, nil }