// 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
}