Why nested has a limit of 3?

[FreCap]

trafficstars

Search for "size" : 3, in the text below.

Why this limit? It appears returning only three result top.

I tried to look in code but I couldn't find where it is defined. Can anyone point it to me?

SQL query:

POST /_opendistro/_sql
{
  "query" : """
        SELECT e.name AS employeeName,
               p.name AS projectName
        FROM employees_nested AS e,
             e.projects AS p
        WHERE p.name LIKE '%security%'
        """
}

Explain:

{
  "from" : 0,
  "size" : 200,
  "query" : {
    "bool" : {
      "filter" : [
        {
          "bool" : {
            "must" : [
              {
                "nested" : {
                  "query" : {
                    "wildcard" : {
                      "projects.name" : {
                        "wildcard" : "*security*",
                        "boost" : 1.0
                      }
                    }
                  },
                  "path" : "projects",
                  "ignore_unmapped" : false,
                  "score_mode" : "none",
                  "boost" : 1.0,
                  "inner_hits" : {
                    "ignore_unmapped" : false,
                    "from" : 0,
                    "size" : 3,
                    "version" : false,
                    "seq_no_primary_term" : false,
                    "explain" : false,
                    "track_scores" : false,
                    "_source" : {
                      "includes" : [
                        "projects.name"
                      ],
                      "excludes" : [ ]
                    }
                  }
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        }
      ],
      "adjust_pure_negative" : true,
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [
      "name"
    ],
    "excludes" : [ ]
  }
}

[dai-chen]

Thanks for reporting the issue! Will investigate.

[penghuo]

size:3 is the default setting of inner_hits builder.

https://github.com/opendistro-for-elasticsearch/sql/blob/develop/legacy/src/main/java/com/amazon/opendistroforelasticsearch/sql/legacy/rewriter/nestedfield/NestedFieldProjection.java#L143

[FreCap]

Thank you for the poitner! Is there any reason why we should keep it that way and not a large number? It's completely opaque from the user perspective

[FreCap]

@penghuo would you have any suggestion/internal agreement on changing the default value or how to make this parametric?

Any preference in making this parametric between:

1. static config in yml
2. REST "opendistro.sql.engine.nested.limit": 100
3. nested(myNested, myNested.field =123, 100)
4. nested(myNested, myNested.field =123, limit=100)

[penghuo]

REST "opendistro.sql.engine.nested.limit": 100 with default = 10 more make sense to me.

[deepspamnani]

Hi.. I am trying to query nested attributes in ElasticSearch using opendistro writing similar query: POST /_opendistro/_sql { "query" : """ SELECT e.name AS employeeName, p.name AS projectName FROM employees_nested AS e, e.projects AS p WHERE p.name LIKE '%security%' """ } Output should contain more than 20,000 records but its returning only 600 records. I am not able to figure out what the issue could be.. Can anyone please help me with this?