Repository: genjidb/genji (go)

Issue:
SELECT ... IN does not work on indexed fields that are arrays
The `SELECT WHERE x IN y` works fine with non indexed fields, but as soon as I change to an indexed field, it fails with: `IN operator takes an array`.

I'm using the latest master branch from today (779c7a6f6ffd40e44f70c9c9213c15a1c4195c46).

Test case:

```sql
genji> create table nodes;
genji> insert into nodes VALUES {name:'node1', parents:['n1', 'n2', 'n3']};
genji> select * from nodes;
{
  "name": "node1",
  "parents": [
    "n1",
    "n2",
    "n3"
  ]
}
genji> select * from nodes where 'n1' in parents;
{
  "name": "node1",
  "parents": [
    "n1",
    "n2",
    "n3"
  ]
}
genji> create index nodes_parents_index on nodes(parents);
genji> reindex;
genji> select * from nodes where 'n1' in parents;
IN operator takes an array
genji> 
```
Background on this -- I'm trying to build a graph of data:

https://github.com/simpleiot/simpleiot/issues/91

Where each node would have an array of parent ids. So I would like to index the parents field to be able to quickly find all children of a node. I'd rather not store a `children` array in the parent, as it could be quite large. 

Open to better ways to do this -- perhaps I should have another table that defines relationships or something like that as then I could traverse the graph both ways. The following project calls them edges:

https://entgo.io/docs/schema-edges/

Thinking through this, that probably makes sense and would be most flexible long term, as then the edge type could hold useful information. 



Pull request:
Prevent index usage with IN operator and indexed right operand
Fixes #281

Patch:
diff --git a/sql/planner/optimizer.go b/sql/planner/optimizer.go
index b9a9dda6..5b3000e0 100644
--- a/sql/planner/optimizer.go
+++ b/sql/planner/optimizer.go
@@ -477,7 +477,10 @@ func opCanUseIndex(op expr.Operator) (bool, expr.Path, expr.Expr) {
 	}
 
 	// expr OP path
-	if rightIsField && !leftIsField {
+	// Special case for IN operator: only left operand is valid for index usage
+	// valid:   a IN [1, 2, 3]
+	// invalid: 1 IN a
+	if rightIsField && !leftIsField && !expr.IsInOperator(op) {
 		return true, rf, op.LeftHand()
 	}
 


Test patch:
diff --git a/sql/planner/optimizer_test.go b/sql/planner/optimizer_test.go
index a0f8928e..246c5261 100644
--- a/sql/planner/optimizer_test.go
+++ b/sql/planner/optimizer_test.go
@@ -459,6 +459,38 @@ func TestUseIndexBasedOnSelectionNodeRule(t *testing.T) {
 				"foo",
 			),
 		},
+		{
+			"FROM foo WHERE a IN [1, 2]",
+			planner.NewSelectionNode(planner.NewTableInputNode("foo"),
+				expr.In(
+					expr.Path{document.PathFragment{FieldName: "a"}},
+					expr.ArrayValue(document.NewValueBuffer(document.NewIntegerValue(1), document.NewIntegerValue(2))),
+				),
+			),
+			planner.NewIndexInputNode(
+				"foo",
+				"idx_foo_a",
+				expr.In(nil, nil).(planner.IndexIteratorOperator),
+				expr.Path(parsePath(t, "a")),
+				expr.ArrayValue(document.NewValueBuffer(document.NewIntegerValue(1), document.NewIntegerValue(2))),
+				scanner.ASC,
+			),
+		},
+		{
+			"FROM foo WHERE 1 IN a",
+			planner.NewSelectionNode(planner.NewTableInputNode("foo"),
+				expr.In(
+					expr.IntegerValue(1),
+					expr.Path(parsePath(t, "a")),
+				),
+			),
+			planner.NewSelectionNode(planner.NewTableInputNode("foo"),
+				expr.In(
+					expr.IntegerValue(1),
+					expr.Path(parsePath(t, "a")),
+				),
+			),
+		},
 	}
 
 	for _, test := range tests {