Inconsistency when using ON KEY ... FOR

N1QL
,
1

I am having inconsistent results when I use ON KEY... FOR joins in my queries. Below I provide examples of two different approaches, one working as expected and the other not doing so. Of course, I tried to simplify the model to illustrate the problem and with the data I included, I could just take the path that works, but there are some cases, for complex queries and models, when you can’t just rewrite a query using ON KEY... FOR to another using ON KEYS.

Is this a bug or am I doing something wrong? Any workarounds?

Approach 1. Qualified documents
Data:

INSERT INTO aBucket (key, value) VALUES ('a123', {'a' : {'name': 'a 123'}});
INSERT INTO aBucket (key, value) VALUES ('b123', {'b' : {'name': 'b 123', 'a': 'a123'}});
INSERT INTO aBucket (key, value) VALUES ('b456', {'b' : {'name': 'b 456', 'a': 'a123'}});
INSERT INTO aBucket (key, value) VALUES ('a456', {'a' : {'name': 'a 456'}});
INSERT INTO aBucket (key, value) VALUES ('b789', {'b' : {'name': 'b 789', 'a': 'a456'}});
INSERT INTO aBucket (key, value) VALUES ('b012', {'b' : {'name': 'b 012', 'a': 'a456'}});

Query 1 (using ON KEYS):

SELECT META(a).id AS `key`, a.name, ARRAY_AGG({'key' : META(b).id, 'name': b.name}) AS children
  FROM aBucket.b AS b
    JOIN aBucket.a AS a ON KEYS b.a
  GROUP BY a;  /* Works! */

Query 2 (using ON KEY):

SELECT META(a).id AS `key`, a.name, ARRAY_AGG({'key' : META(b).id, 'name' : b.name}) AS children
  FROM aBucket.a AS a
    JOIN aBucket.b AS b ON KEY b.a FOR a
  GROUP BY a;   /* Doesn't work */

Approach 2. Storing documents with documentType
Data:

INSERT INTO aBucket (key, value) VALUES ('a123_', {'docType': 'a', 'name': 'a 123'});
INSERT INTO aBucket (key, value) VALUES ('b123_', {'docType': 'b', 'name': 'b 123', 'a': 'a123_'});
INSERT INTO aBucket (key, value) VALUES ('b456_', {'docType': 'b', 'name': 'b 456', 'a': 'a123_'});
INSERT INTO aBucket (key, value) VALUES ('a456_', {'docType': 'a', 'name': 'a 456'});
INSERT INTO aBucket (key, value) VALUES ('b789_', {'docType': 'b', 'name': 'b 789', 'a': 'a456_'});
INSERT INTO aBucket (key, value) VALUES ('b012_', {'docType': 'b', 'name': 'b 012', 'a': 'a456_'});

Query 1 (using ON KEYS):

SELECT META(a).id AS `key`, a.name, ARRAY_AGG({'key' : META(b).id, 'name': b.name}) AS children
  FROM aBucket AS b
    JOIN aBucket AS a ON KEYS b.a
  WHERE a.docType = 'a'
    AND b.docType = 'b'
  GROUP BY a  /* Works! */

Query 2 (using ON KEY):

SELECT META(a).id AS `key`, a.name, ARRAY_AGG({'key' : META(b).id, 'name' : b.name}) AS children
  FROM aBucket AS a
    JOIN aBucket AS b ON KEY b.a FOR a
  WHERE a.docType = 'a'
    AND b.docType = 'b'
  GROUP BY a  /* Works! */
2

There is some known inconsistent behavior when using paths in the FROM clause or JOIN clause. We will be deprecating the use of paths in the FROM and JOIN clauses. Do you see inconsistency if you rewrite your queries to remove paths in these clauses?

3

Well, if I understand what you mean with “paths”, this was my rewrite:

SELECT META(a).id AS `key`, a.a.name, ARRAY_AGG({'key' : META(b).id, 'name' : b.b.name}) AS children
  FROM aBucket AS a
    JOIN aBucket AS b ON KEY b.b.a FOR a
  GROUP BY a

And it worked!

I lose part of the convenience to use paths, but at least I avoid having to include document type checks in WHERE clauses, which is error-prone.

Is my understanding of paths correct? Are there any plans to correct this instead of deprecating it?

4

Your understanding is correct. We will deprecate it, because the road to hell is paved with convenience. :slight_smile:

1 Like