[BUG] Nested function returns extra rows for arrays of objects

[GumpacG]

What is the bug?
The nested function separates values of an array into additional rows which duplicates some values and gives misleading results

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Load this dataset to your server:

{"index":{"_id":"1"}}
{"message":{"info":"a","author":"e","dayOfWeek":1},"comment":{"data":"ab","likes":3},"myNum":1,"someField":"b"}
{"index":{"_id":"2"}}
{"message":{"info":"b","author":"f","dayOfWeek":2},"comment":{"data":"aa","likes":2},"myNum":2,"someField":"a"}
{"index":{"_id":"3"}}
{"message":{"info":"c","author":"g","dayOfWeek":1},"comment":{"data":"aa","likes":3},"myNum":3,"someField":"a"}
{"index":{"_id":"4"}}
{"message":[{"info":"d","author":"h","dayOfWeek":4},{"info":"e","author":"i","dayOfWeek":5}],"comment":{"data":"ab","likes":1},"myNum":4,"someField":"b"}
{"index":{"_id":"5"}}
{"message": [{"info":"zz","author":"zz","dayOfWeek":6}],"comment":{"data":["aa","bb"],"likes":10},"myNum":[3,4],"someField":"a"}

3. Query

{
    "fetch_size": 20,
    "query": "SELECT nested(message.info), nested(comment.data) FROM nested_simple;"
}

5. See the results:

"total": 6,
    "datarows": [
        [
            "a",
            "ab"
        ],
        [
            "b",
            "aa"
        ],
        [
            "c",
            "aa"
        ],
        [
            "d",
            "ab"
        ],
        [
            "e",
            "ab"
        ],
        [
            "zz",
            [
                "aa",
                "bb"
            ]
        ]
    ],
    "size": 6,
    "status": 200

6. Note that "ab" is duplicated and is in an additional row. Also, note that the size of the results is 6 while the dataset only has 5 rows.

What is the expected behavior?
When calling the nested function with data having array of objects we should expect the resulting number of rows to be the same number as the dataset. For example, the dataset above should always have 5 rows in the result when the user queries SELECT nested(message.info), nested(comment.data) from nested_simple;
Expected output:

    "datarows": [
        [
            "a",
            "ab"
        ],
        [
            "b",
            "aa"
        ],
        [
            "c",
            "aa"
        ],
        [
            [
                "d",
                "e"
            ],
            "ab"
        ],
        [
            [
                "zz"
            ],
            [
                "aa",
                "bb"
            ]
        ]
    ],
    "total": 5,
    "size": 5,
    "status": 200

What is your host/environment?

• SQL plugin: 2.4

Do you have any screenshots?
Expected result:

[GumpacG]

Closing this issue because it is actually expected behaviour to follow PartiQL behaviour.

PartiQL data:

{ 
    'root': {
        'test': << 
        {
            'message': {
                'info': 'a'
            },
            'comment': {
                'numComments': 1
            }
        },
        {
            'message':  { 
                'info': 'b'
            },
            'comment': {
                'numComments': 2
            }
        },
        {
            'message':  { 
                'info': 'c'
            },
            'comment': {
                'numComments': 3
            }
        },
        {
            'message':  [
                { 
                    'info': 'c'
                },
                {
                    'info': 'a'
                }
            ],
            'comment': {
                'numComments': 4
            }
        },
        {
            'message':  [
                { 
                    'info': 'zz'
                }
            ],
            'comment': {
                'numComments': [5, 6]
            }
        }
        >> 
    }
}

PartiQL query and result:

PartiQL> SELECT message.info, comment.numComments FROM root.test as a, a.message as message, a.comment as comment;
==='
<<
  {
    'info': 'a',
    'numComments': 1
  },
  {
    'info': 'b',
    'numComments': 2
  },
  {
    'info': 'c',
    'numComments': 3
  },
  {
    'info': 'c',
    'numComments': 4
  },
  {
    'info': 'a',
    'numComments': 4
  },
  {
    'info': 'zz',
    'numComments': [
      5,
      6
    ]
  }
>>
---
OK!