LEFT OUTER JOIN + WHERE clause

SQL++
41

What are results of this query. No need explain.

SELECT o, s.status, s.docId, meta(s).id
FROM default s
LET o = ARRAY v FOR v IN (SELECT o.docType, o.clientId, o.status, o.storeId FROM default o WHERE (o.docType IS MISSING OR o.docType="Order") AND o.clientId="834be7eb-377c-4c54-8970-8c75fc703550") WHEN  v.storeId = s.docId END
WHERE s.docType="Store" AND s.status="published";
42

59 results (one for each “published” store), order array is always empty:

[
  {
    "docId": "04750480-63f3-4fa9-bb61-22d128ac3c08",
    "id": "Store::04750480-63f3-4fa9-bb61-22d128ac3c08",
    "o": [],
    "status": "published"
  },
  {
    "docId": "0af38eef-9d68-4852-a9c5-baed11f0162f",
    "id": "Store::0af38eef-9d68-4852-a9c5-baed11f0162f",
    "o": [],
    "status": "published"
  },
  {
    "docId": "0c05dd94-a6b1-489a-af0c-53b96e0c8375",
    "id": "Store::0c05dd94-a6b1-489a-af0c-53b96e0c8375",
    "o": [],
    "status": "published"
  },

I had to provide an alias for the clientId in the inner SELECT, an error was occuring (Duplicate result alias clientId).

43

Could you please let me know CB version and exact query you used.

44

No problem:

Couchbase Server Community 4.5.0

Query (noticed now that I had two times the clientId - no difference with the correction):

SELECT o, s.status, s.docId, meta(s).id
FROM default s
LET o = ARRAY v FOR v IN (SELECT o.docType, o.clientId, o.status, o.storeId FROM default o WHERE (o.docType IS MISSING OR o.docType="Order") AND o.clientId="834be7eb-377c-4c54-8970-8c75fc703550") WHEN  v.clientId = s.docId END
WHERE s.docType="Store" AND s.status="published";
45

Could you please try with 4.5.1

46

Sure… I’ll try to setup an installation later today (I’m on GMT). I’ll get back to you after that!

47

I’ve installed Couchbase Server Enterprise 4.5.1, fresh install.

I’ve used the mock data you’ve provided on the post above, created a primary index and the following indexes:

CREATE INDEX idx_storeType ON default(status, docId) WHERE docType = "Store";
CREATE INDEX idx_orderClientId ON default( clientId,docType, status, storeId ) WHERE ((`docType` IS MISSING) OR (`docType` = "Order"));

I ran the following query:

SELECT o, s.status, s.docId, meta(s).id
FROM default s
LET o = ARRAY v FOR v IN (SELECT o.docType, o.clientId, o.status, o.storeId FROM default o WHERE (o.docType IS MISSING OR o.docType="Order") AND o.clientId="9281ae36-a418-4ea3-93f0-bfd7b1a38248") WHEN  v.clientId = s.docId END
WHERE s.docType="Store" AND s.status="published";

and obtained these results:

[
  {
    "id": "Store::001",
    "o": [],
    "status": "published"
  },
  {
    "id": "Store::002",
    "o": [],
    "status": "published"
  },
  {
    "id": "Store::003",
    "o": [],
    "status": "published"
  },
  {
    "id": "Store::004",
    "o": [],
    "status": "published"
  },
  {
    "id": "Store::007",
    "o": [],
    "status": "published"
  }
]

I should have the same environment as you at this point… anything I might still be missing? Let me know if you want the EXPLAIN query.

48

My original data doesn’t have docId.
Remove that data use this.

insert into default values ("Store::001", {"status":"published", "docType":"Store","docId":"Store::001"}), ("Store::002", {"status":"published", "docType":"Store","docId":"Store::002"}),("Store::003", {"status":"published", "docType":"Store","docId":"Store::003"}),("Store::004", {"status":"published", "docType":"Store","docId":"Store::004"}), ("Store::007", {"status":"published", "docType":"Store","docId":"Store::007"});
insert into default values ("Orders::001", {"docType":"Order", "clientId":"9281ae36-a418-4ea3-93f0-bfd7b1a38248","status":"closed","storeId":"001"}), ("Orders::002", {"docType":"order", "clientId":"9281ae36-a418-4ea3-93f0-bfd7b1a38248","status":"closed","storeId":"002"}),("Orders::003", {"clientId":"9281ae36-a418-4ea3-93f0-bfd7b1a38248","status":"closed","storeId":"003"}),("Orders::004", {"docType":"Order", "clientId":"8281ae36-a418-4ea3-93f0-bfd7b1a38248","status":"closed","storeId":"004"}),("Orders::005", {"docType":"Order", "clientId":"9281ae36-a418-4ea3-93f0-bfd7b1a38248","status":"closed","storeId":"005"}), ("Orders::101", {"docType":"ppOrder", "clientId":"9281ae36-a418-4ea3-93f0-bfd7b1a38248","status":"closed","storeId":"001"});
49

Updating the query to reflect the following: WHEN ("Store::"|| v.storeId) = s.docId

produced the expected results. I’ll try to reproduce in 4.5.0 now

50

u mean (“Store::”|| v.storeId) = meta(s).id.

51

They were the same in this new dataset you sent :slight_smile:

I can confirm that the exact same combination Indexes+Data+Query does not work on 4.5.0 Community Edition. Any suggestions to go from here?

52

Option 1) You can upgrade to 4.5.1
Option 2) In 4.5.0 (https://issues.couchbase.com/browse/MB-20492) try the following index make it not covered by omitting docId
CREATE INDEX idx_storeType ON default(status) WHERE docType = “Store”;

1 Like