Subquery does not work as expected in 4.5.1

SQL++
1

Hi,

I have a question regarding a subquery that works in 4.6.*, but not in 4.5.1. I know that upgrading is the best solution, but I’m wondering if there is a way to make the following query working in 4.5.1

Sample documents tested

test1

{
  "id": 1,
  "form": "parent",
  "children": {
    "1": {
      "ids": [
        "child1"
      ]
    }
  }
}

child1

{
  "name": "child1",
  "form": "child"
}

index used:
CREATE INDEX `covered_parent` ON `test`(`id`,`children`) WHERE (`form` = "parent")

Query:

select
    c1.id,
    (
        select c2.name from test c2 use keys ARRAY_FLATTEN(
            object_values(c1.children)[*].ids, 1
        )
    ) as children1
from test c1 use index (`covered_parent`)
where
form='parent' and 
    c1.id = 1

The query returns the following output in 4.5.1

[
  {
    "children1": [],
    "id": 1
  }
]

However, this query works (notice select * in the sub query) as expected

select
    c1.id,
    (
        select * from test c2 use keys ARRAY_FLATTEN(
            object_values(c1.children)[*].ids, 1
        )
    ) as children1
from test c1 use index (`covered_parent`)
where
form='parent' and 
    c1.id = 1

Returns

[
  {
    "children1": [
      {
        "c2": {
          "form": "child",
          "name": "child1"
        }
      }
    ],
    "id": 1
  }
]

in 4.6.1

[
        {
            "children1": [
                {
                    "name": "child1"
                }
            ],
            "id": 1
        }
    ]

As you see, children1 is empty in 4.5.1 unless I select *

Is there a way to workaround this in 4.5.1?

Thanks!
Moon

2

Try this.

SELECT p.id, c AS children1
FROM  test  AS p USE INDEX  (`covered_parent`) 
NEST test AS c ON KEYS  ARRAY_FLATTEN( OBJECT_VALUES(p.children)[*].ids, 1)
WHERE p.form='parent'  AND p.id = 1;

If that works you can try the following index to reduce size. Make sure index is covered.

CREATE INDEX `covered_parent` ON `test`(`id`, ARRAY_FLATTEN( OBJECT_VALUES(children)[*].ids, 1))
 WHERE (`form` = "parent")
3

@vsr1 Thank you, but that will return same out as

select
    c1.id,
    (
        select * from test c2 use keys ARRAY_FLATTEN(
            object_values(c1.children)[*].ids, 1
        )
    ) as children1
from test c1 use index (`covered_parent`)
where
form='parent' and 
    c1.id = 1

I’m trying to make the following working to select fields

select
    c1.id,
    (
        select c2.name from test c2 use keys ARRAY_FLATTEN(
            object_values(c1.children)[*].ids, 1
        )
    ) as children1
from test c1 use index (`covered_parent`)
where
form='parent' and 
    c1.id = 1
4

There is some issue with covering this query in 4.5.1 . As you mentioned it already fixed in 4.6. For 4.5.1 You can use the following.

select c1.id, 
( select c2.name from test c2 use keys  ckeys) as children1 
from test c1 use index (`covered_parent`) 
LET ckeys = ARRAY_FLATTEN( object_values(c1.children)[*].ids, 1)
 where form='parent' and c1.id = 1;

If you need details of problem do EXPLAIN on original query and when you look c1 is covering index but in Filter there is cover around c1.children is missing , so it evaluates MISSING.

1 Like
5

Thank you so much! That works.