Subquery within subquery not working (wrong result)

SQL++
,
1

Hi, I’m currently having a weird result when making a subquery within a subquery.

My current data model is the following :
I have a “items” collection storing my products and my customers.
I have a “relationships” collection with a “from” and a “to” field. (My real business case is much more complex but this is an exemple to reproduce the issue)

I want to display all my products and their customers :

[
    {
        "id": "1",
        "name": "Product 1",
        "customers" : [
            {
                "id": "11",
                "name": "Customer1"
            }
        ]
    },
    {
        "id": "2",
        "name": "Product 2",
        "customers": [
            {
                "id": "12",
                "name": "Customer2"
            }
        ]
    }
    ...
]

Here is the query that I made that doesn’t work :

SELECT product.id as id, product.name as name,
       (
           SELECT customer.id AS id, customer.name AS name
           FROM `items` customer
           USE KEYS (
               SELECT RAW `to`
               FROM `relationships`
               WHERE `from`=product.id)) AS customers
       FROM `items` product

This request gives me the following result :

[
    {
        "id": "1",
        "name": "Product 1",
        "customers" : [
            {
                "id": "11",
                "name": "Customer1"
            }
        ]
    },
    {
        "id": "2",
        "name": "Product 2",
        "customers": [
            {
                "id": "11",
                "name": "Customer1"
            }
        ]
    }
    ...
]

If I re run the request I might have the Product 2 returned first and then all the products will have customer 2 instead…

However if I build my query this way I get the correct result :

SELECT product.id as id, product.name as name, customers
       FROM `items` product
       LET customerKeys = (SELECT RAW `to`
               FROM `relationships`
               WHERE `from`=product.id),
       customers = (SELECT customer.id AS id, customer.name AS name FROM `items` customer
            USE KEYS customerKeys)

Seems to me that the first request should also be working but am I missing something ?

2

Use Second query. What version of CB?. Will you post EXPLAIN of first query.

Correlation detection (2-level) might be missed.

3

I’m probably going to use the second query. However that will make me work on a lot of changes.
I’m thinking of some kind of 2-level correlation detection issue too.

I’m using Couchbase 7.1

Here is my request with a working syntax (I rebuilt the whole dataset of this exemple) :

SELECT product.id AS id,
       product.name AS name,
       (
           SELECT customer.id AS id,
                  customer.name AS name
           FROM `items` customer USE KEYS (
               SELECT RAW `to`
               FROM `relationships`
               WHERE `from`=product.id)) AS customers
       FROM `items` product
       WHERE product.id IN ["1", "2"]

This reproduces the issue well :

[
  {
    "customers": [
      {
        "id": "11",
        "name": "Customer1"
      }
    ],
    "id": "1",
    "name": "Product 1"
  },
  {
    "customers": [
      {
        "id": "11",
        "name": "Customer1"
      }
    ],
    "id": "2",
    "name": "Product 2"
  }
]

Like in the initial message.

Here is the explain of the query :

    "plan": {
        "#operator": "Sequence",
        "~children": [
            {
                "#operator": "PrimaryScan3",
                "as": "product",
                "bucket": "test",
                "index": "#primary",
                "index_projection": {
                    "primary_key": true
                },
                "keyspace": "items",
                "namespace": "default",
                "scope": "_default",
                "using": "gsi"
            },
            {
                "#operator": "Fetch",
                "as": "product",
                "bucket": "test",
                "keyspace": "items",
                "namespace": "default",
                "scope": "_default"
            },
            {
                "#operator": "Parallel",
                "~child": {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "Filter",
                            "condition": "((`product`.`id`) in [\"1\", \"2\"])"
                        },
                        {
                            "#operator": "InitialProject",
                            "result_terms": [
                                {
                                    "as": "id",
                                    "expr": "(`product`.`id`)"
                                },
                                {
                                    "as": "name",
                                    "expr": "(`product`.`name`)"
                                },
                                {
                                    "as": "customers",
                                    "expr": "(select (`customer`.`id`) as `id`, (`customer`.`name`) as `name` from `default`:`test`.`_default`.`items` as `customer` use keys correlated (select raw (`relationships`.`to`) from `default`:`test`.`_default`.`relationships` where ((`relationships`.`from`) = (`product`.`id`))))"
                                }
                            ]
                        }
                    ]
                }
            }
        ]
    }
}

If I should report a bug or whatever I can take time to do so of course.

4

Track via MB-52629

1 Like