Repository: vitessio/vitess
Pull request #7163 (2020-12-11 10:21:12)

add support for unary expression in order by column
Signed-off-by: Harshit Gangal <harshit@planetscale.com>

## Backport
NO

## Status
**READY**

## Description
Add support for unary expression in order by column

## Impacted Areas in Vitess
List general components of the application that this PR will affect:

- [X]  Query Serving



Patch:
diff --git a/go/vt/vtgate/planbuilder/memory_sort.go b/go/vt/vtgate/planbuilder/memory_sort.go
index 26d7fb7e15..ade1e68817 100644
--- a/go/vt/vtgate/planbuilder/memory_sort.go
+++ b/go/vt/vtgate/planbuilder/memory_sort.go
@@ -60,6 +60,18 @@ func newMemorySort(plan logicalPlan, orderBy sqlparser.OrderBy) (*memorySort, er
 					break
 				}
 			}
+		case *sqlparser.UnaryExpr:
+			colName, ok := expr.Expr.(*sqlparser.ColName)
+			if !ok {
+				return nil, fmt.Errorf("unsupported: memory sort: complex order by expression: %s", sqlparser.String(expr))
+			}
+			c := colName.Metadata.(*column)
+			for i, rc := range ms.ResultColumns() {
+				if rc.column == c {
+					colNumber = i
+					break
+				}
+			}
 		default:
 			return nil, fmt.Errorf("unsupported: memory sort: complex order by expression: %s", sqlparser.String(expr))
 		}
diff --git a/go/vt/vtgate/planbuilder/ordering.go b/go/vt/vtgate/planbuilder/ordering.go
index 23a38a93fb..8d918dfbd7 100644
--- a/go/vt/vtgate/planbuilder/ordering.go
+++ b/go/vt/vtgate/planbuilder/ordering.go
@@ -91,6 +91,12 @@ func planOAOrdering(pb *primitiveBuilder, orderBy sqlparser.OrderBy, oa *ordered
 			orderByCol = oa.resultColumns[num].column
 		case *sqlparser.ColName:
 			orderByCol = expr.Metadata.(*column)
+		case *sqlparser.UnaryExpr:
+			col, ok := expr.Expr.(*sqlparser.ColName)
+			if !ok {
+				return nil, fmt.Errorf("unsupported: in scatter query: complex order by expression: %s", sqlparser.String(expr))
+			}
+			orderByCol = col.Metadata.(*column)
 		default:
 			return nil, fmt.Errorf("unsupported: in scatter query: complex order by expression: %v", sqlparser.String(expr))
 		}
@@ -263,6 +269,18 @@ func planRouteOrdering(orderBy sqlparser.OrderBy, node *route) (logicalPlan, err
 					break
 				}
 			}
+		case *sqlparser.UnaryExpr:
+			col, ok := expr.Expr.(*sqlparser.ColName)
+			if !ok {
+				return nil, fmt.Errorf("unsupported: in scatter query: complex order by expression: %s", sqlparser.String(expr))
+			}
+			c := col.Metadata.(*column)
+			for i, rc := range node.resultColumns {
+				if rc.column == c {
+					colNumber = i
+					break
+				}
+			}
 		default:
 			return nil, fmt.Errorf("unsupported: in scatter query: complex order by expression: %s", sqlparser.String(expr))
 		}


Test patch:
diff --git a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
index dbdb88abe4..0af064a763 100644
--- a/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/memory_sort_cases.txt
@@ -491,3 +491,60 @@
     ]
   }
 }
+
+# unary expression
+"select a from user order by binary a desc"
+{
+  "QueryType": "SELECT",
+  "Original": "select a from user order by binary a desc",
+  "Instructions": {
+    "OperatorType": "Route",
+    "Variant": "SelectScatter",
+    "Keyspace": {
+      "Name": "user",
+      "Sharded": true
+    },
+    "FieldQuery": "select a from user where 1 != 1",
+    "OrderBy": "0 DESC",
+    "Query": "select a from user order by binary a desc",
+    "Table": "user"
+  }
+}
+
+# unary expression in join query
+"select u.a from user u join music m on u.a = m.a order by binary a desc"
+{
+  "QueryType": "SELECT",
+  "Original": "select u.a from user u join music m on u.a = m.a order by binary a desc",
+  "Instructions": {
+    "OperatorType": "Join",
+    "Variant": "Join",
+    "JoinColumnIndexes": "-1",
+    "TableName": "user_music",
+    "Inputs": [
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select u.a from user as u where 1 != 1",
+        "OrderBy": "0 DESC",
+        "Query": "select u.a from user as u order by binary a desc",
+        "Table": "user"
+      },
+      {
+        "OperatorType": "Route",
+        "Variant": "SelectScatter",
+        "Keyspace": {
+          "Name": "user",
+          "Sharded": true
+        },
+        "FieldQuery": "select 1 from music as m where 1 != 1",
+        "Query": "select 1 from music as m where m.a = :u_a",
+        "Table": "music"
+      }
+    ]
+  }
+}