Whats the difference bewteen these two queries and why one works and the ohter not?

[servermanager017]

Hello,

I am learning n1ql and I am asking myself why one querie works and the other not.

This works:

 
SELECT p, c
  FROM purchases p INNER JOIN customer c ON (p.customerId = META(c).id)
    LIMIT 1

This not works:

SELECT c, p

FROM customer c

INNER JOIN purchases p ON (p.customerId = META(c).id)

Error:

500: Internal Server Error

{
"requestID": "6d60aa7e-015d-4f14-86af-22579886fd3a",
"errors": [{"code":4330,"msg":"No index available for ANSI join term p"}],
"status": "fatal",
"metrics": {"elapsedTime": "17.388644ms","executionTime": "17.283306ms","resultCount": 0,"resultSize": 0,"serviceLoad": 25,"errorCount": 1}
}

[mreiche]

The are different queries in that the first is driven off purchases (i.e. if there are no purchases, there will be no results even if there are customers); and the second is driven off customer - so there will be results if there are customers, but no purchases).
Both run for me without issue. What version of couchbase server do you have?

Also - if you get the message “No index available for …” then the Index Advisor will tell you which indexes need to be created, and how to created them.

[servermanager017]

https://query-tutorial.couchbase.com/tutorial/#13

I run the query here you can try it both works also for me but one gives me an index error and thats not my database I cant create indexes because its the tutorial site of couchbase

[mreiche]

@servermanager017

SELECT c, p

FROM customer c

INNER JOIN purchases p ON (p.customerId = META(c).id)

You can run the index advisor like this:

Advise SELECT c, p
FROM customer c
INNER JOIN purchases p ON (p.customerId = META(c).id)

Which will give the advice as shown below. But it won’t allow you to create the indexes as it is read-only.

  "results": [
    {
      "#operator": "Advise",
      "advice": {
        "#operator": "IndexAdvice",
        "adviseinfo": {
          "current_indexes": [
            {
              "index_statement": "CREATE PRIMARY INDEX #primary ON `customer`",
              "keyspace_alias": "customer_c"
            }
          ],
          "recommended_indexes": {
            "indexes": [
              {
                "index_statement": "CREATE INDEX adv_customerId ON `purchases`(`customerId`)",
                "keyspace_alias": "purchases_p",
                "recommending_rule": "Index keys follow order of predicate types: 9. non-static join predicate."
              }
            ]

[mreiche]

btw - I think the issue you see in the sample server is Log in - Couchbase database. It says it is fixed in 7.0.4, and that sample server is apparently 7.0.0

SELECT version()

{
  "results": [
    {
      "$1": "7.0.0-N1QL"
    }
  ]
}

[vsr1]

SELECT p, c
  FROM purchases p INNER JOIN customer c ON (p.customerId = META(c).id)
    LIMIT 1;

above case p is Joined with c’s document key. Optimizer detects this and avoid index and does KV lookup Join,.

SELECT c, p

FROM customer c

INNER JOIN purchases p ON (p.customerId = META(c).id)

In this left side has document key and right side is field in p which require index

https://www.couchbase.com/blog/ansi-join-support-n1ql/