Repository: genjidb/genji (go)

Issue:
NULL is not sorted first when using ORDER BY
Table example:

```sql
BEGIN TRANSACTION;
CREATE TABLE players (...);
INSERT INTO players VALUES {"name": "Rafael Nadal", "age": 36, "nationality": "Spain", "career": {"australia": 2, "france": 14, "wimbledon": 2, "us": 4}, "coach": ["Francisco Roig", "Carlos Moyá", "Marc López"]};
INSERT INTO players VALUES {"name": "Roger Federer", "age": 40, "nationality": "Switzerland", "career": {"australia": 6, "france": 1, "wimbledon": 8, "us": 5}, "coach": ["Ivan Ljubičić", "Severin Lüthi"]};
INSERT INTO players VALUES {"name": "Andrew Barron Murray", "coach": ["Ivan Lendl"]};
COMMIT;
```

Query:

```sql
genji> SELECT name, career.australia AS australia FROM players ORDER BY career.australia;
{
  "name": "Rafael Nadal",
  "australia": 2
}
{
  "name": "Roger Federer",
  "australia": 6
}
{
  "name": "Andrew Barron Murray",
  "australia": null
}
```

The last document should be first

Pull request:
orderby: fix evaluation of ORDER BY expr when used with projection
When the ORDER BY expression uses fields that are not present in the preceding projection operator,
it was evaluating the expression to NULL, which was breaking the ordering.
It now evaluates the expr against the result of the projection first, then if it is NULL
evaluates it against the original document.

Example:
```bash
table.Scan("test") | docs.Project(b) | docs.TempTreeSort(a)
```

Fixes #466

Patch:
diff --git a/internal/stream/docs/temp_tree_sort.go b/internal/stream/docs/temp_tree_sort.go
index 2f7276f5..9dcf61bb 100644
--- a/internal/stream/docs/temp_tree_sort.go
+++ b/internal/stream/docs/temp_tree_sort.go
@@ -3,6 +3,7 @@ package docs
 import (
 	"fmt"
 
+	"github.com/cockroachdb/errors"
 	"github.com/genjidb/genji/internal/encoding"
 	"github.com/genjidb/genji/internal/environment"
 	"github.com/genjidb/genji/internal/expr"
@@ -51,6 +52,17 @@ func (op *TempTreeSortOperator) Iterate(in *environment.Environment, fn func(out
 			return err
 		}
 
+		if types.IsNull(v) {
+			// the expression might be pointing to the original document.
+			v, err = op.Expr.Eval(out.Outer)
+			if err != nil {
+				// the only valid error here is a missing field.
+				if !errors.Is(err, types.ErrFieldNotFound) {
+					return err
+				}
+			}
+		}
+
 		doc, ok := out.GetDocument()
 		if !ok {
 			panic("missing document")
diff --git a/types/value.go b/types/value.go
index f7f2ae9a..e7d7a320 100644
--- a/types/value.go
+++ b/types/value.go
@@ -124,6 +124,10 @@ func Is[T any](v Value) (T, bool) {
 	return vv.v, true
 }
 
+func IsNull(v Value) bool {
+	return v == nil || v.Type() == NullValue
+}
+
 // IsTruthy returns whether v is not equal to the zero value of its type.
 func IsTruthy(v Value) (bool, error) {
 	if v.Type() == NullValue {


Test patch:
diff --git a/sqltests/SELECT/order_by.sql b/sqltests/SELECT/order_by.sql
index c77f3378..2de5753a 100644
--- a/sqltests/SELECT/order_by.sql
+++ b/sqltests/SELECT/order_by.sql
@@ -1,6 +1,6 @@
 -- setup:
-CREATE TABLE test(a double);
-INSERT INTO test (a) VALUES (1), (2), (3);
+CREATE TABLE test(a double, b double);
+INSERT INTO test (a, b) VALUES (50, 3), (100, 4), (10, 2), (null, 1);
 
 -- suite: no index
 
@@ -8,30 +8,77 @@ INSERT INTO test (a) VALUES (1), (2), (3);
 CREATE INDEX ON test(a);
 
 -- test: asc
+SELECT b FROM test ORDER BY a;
+/* result:
+{
+    b: 1.0,
+}
+{
+    b: 2.0
+}
+{
+    b: 3.0
+}
+{
+    b: 4.0
+}
+*/
+
+
+-- test: asc / wildcard
 SELECT * FROM test ORDER BY a;
 /* result:
 {
-    a: 1.0
+    b: 1.0,
 }
 {
-    a: 2.0
+    a: 10.0,
+    b: 2.0
 }
 {
-    a: 3.0
+    a: 50.0,
+    b: 3.0
+}
+{
+    a: 100.0,
+    b: 4.0
 }
 */
 
+
 -- test: desc
-SELECT * FROM test ORDER BY a DESC;
+SELECT b FROM test ORDER BY a DESC;
 /* result:
 {
-    a: 3.0
+    b: 4.0,
+}
+{
+    b: 3.0
 }
 {
-    a: 2.0
+    b: 2.0
 }
 {
-    a: 1.0
+    b: 1.0
 }
 */
 
+-- test: desc / wildcard
+SELECT * FROM test ORDER BY a DESC;
+/* result:
+{
+    a: 100.0,
+    b: 4.0,
+}
+{
+    a: 50.0,
+    b: 3.0
+}
+{
+    a: 10.0,
+    b: 2.0
+}
+{
+    b: 1.0
+}
+*/