Only partial results are returned for PIVOT+LIMIT queries through the REST client. Less than the fetch_size number of rows returned, even if the query has more rows to return. Setting the fetch_size has no effect on the number of returned rows.
Examples:
With LIMIT
POST http://localhost:9200/_sql?format=txt
Content-Type: application/json
{
"query": "SELECT * FROM test_emp PIVOT (AVG(salary) FOR languages IN (1, 2)) LIMIT 5"
}
HTTP/1.1 200 OK
Cursor: 46ToAwFaAWYBcAEIdGVzdF9lbXC0AwEBCWNvbXBvc2l0ZQdncm91cGJ5AQNhdmcGNGIzZTE5AAD/AQZzYWxhcnkAAAD/AAD/BwAIOTkxYTZlYTQBCmJpcnRoX2RhdGUAAAEAAAAGYWUzNzJmAQZlbXBfbm8AAAEAAAAHNjg0ZjVkYwESZmlyc3RfbmFtZS5rZXl3b3JkAAABAAAACDlkOWU5NWZiAQZnZW5kZXIAAAEAAAAIMjA5MTVhOTkBCWhpcmVfZGF0ZQAAAQAAAAc3ZmU4ZTQyARFsYXN0X25hbWUua2V5d29yZAAAAQAAAAg5Yjk0NzVjOQEJbGFuZ3VhZ2VzAAABAAAEAQoHCDk5MWE2ZWE0/wZhZTM3MmYCAAAAAAAAJzwHNjg0ZjVkYwAHTWluZ3Nlbgg5ZDllOTVmYgABRggyMDkxNWE5OQIAAACzKHyYAAc3ZmU4ZTQyAAZDYXNsZXkIOWI5NDc1YzkCAAAAAAAAAAEAAgEAAAAAAQD/////DwAAAAABBXRlcm1zP4AAAAAJbGFuZ3VhZ2VzAAcCAgAAAAAAAAABAgAAAAAAAAACAAAAAAAAAAFaAwACAgAAAAAAAAAACgACCAFrCDk5MWE2ZWE0AAEBawZhZTM3MmYAAAFrBzY4NGY1ZGMAAAFrCDlkOWU5NWZiAAABawgyMDkxNWE5OQABAWsHN2ZlOGU0MgAAAnB2AWsIOWI5NDc1YzkAAAFtBjRiM2UxOQV2YWx1ZQAAAgAAAAAAAAABAnB2AWsIOWI5NDc1YzkAAAFtBjRiM2UxOQV2YWx1ZQAAAgAAAAAAAAACAf8AAQoHBmFlMzcyZgIAAAAAAAAnGQgyMDkxNWE5OQIAAABvLyxEAAg5OTFhNmVhNAL///997SBIAAg5Yjk0NzVjOQIAAAAAAAAAAQc2ODRmNWRjAAZTdW1hbnQHN2ZlOGU0MgAEUGVhYwg5ZDllOTVmYgABRggAAAAPAAAADwAAAA8AAAAPAAAAGAAAAA8AAAAPAAAADwE=
Took-nanos: 6155665
content-type: text/plain
content-length: 685
birth_date | emp_no | first_name | gender | hire_date | last_name | 1 | 2
---------------+---------------+---------------+---------------+------------------------+---------------+---------------+---------------
null |10041 |Uri |F |1989-11-12T00:00:00.000Z|Lenart |56415.0 |null
null |10043 |Yishay |M |1990-10-20T00:00:00.000Z|Tzvieli |34341.0 |null
null |10044 |Mingsen |F |1994-05-21T00:00:00.000Z|Casley |39728.0 |null
Querying the cursor
POST http://localhost:9200/_sql?format=txt&pretty=true
Content-Type: application/json
{
"cursor": "46ToAwFaAWYBcAEIdGVzdF9lbXC0AwEBCWNvbXBvc2l0ZQdncm91cGJ5AQNhdmcGNGIzZTE5AAD/AQZzYWxhcnkAAAD/AAD/BwAIOTkxYTZlYTQBCmJpcnRoX2RhdGUAAAEAAAAGYWUzNzJmAQZlbXBfbm8AAAEAAAAHNjg0ZjVkYwESZmlyc3RfbmFtZS5rZXl3b3JkAAABAAAACDlkOWU5NWZiAQZnZW5kZXIAAAEAAAAIMjA5MTVhOTkBCWhpcmVfZGF0ZQAAAQAAAAc3ZmU4ZTQyARFsYXN0X25hbWUua2V5d29yZAAAAQAAAAg5Yjk0NzVjOQEJbGFuZ3VhZ2VzAAABAAAEAQoHCDk5MWE2ZWE0/wZhZTM3MmYCAAAAAAAAJzwHNjg0ZjVkYwAHTWluZ3Nlbgg5ZDllOTVmYgABRggyMDkxNWE5OQIAAACzKHyYAAc3ZmU4ZTQyAAZDYXNsZXkIOWI5NDc1YzkCAAAAAAAAAAEAAgEAAAAAAQD/////DwAAAAABBXRlcm1zP4AAAAAJbGFuZ3VhZ2VzAAcCAgAAAAAAAAABAgAAAAAAAAACAAAAAAAAAAFaAwACAgAAAAAAAAAACgACCAFrCDk5MWE2ZWE0AAEBawZhZTM3MmYAAAFrBzY4NGY1ZGMAAAFrCDlkOWU5NWZiAAABawgyMDkxNWE5OQABAWsHN2ZlOGU0MgAAAnB2AWsIOWI5NDc1YzkAAAFtBjRiM2UxOQV2YWx1ZQAAAgAAAAAAAAABAnB2AWsIOWI5NDc1YzkAAAFtBjRiM2UxOQV2YWx1ZQAAAgAAAAAAAAACAf8AAQoHBmFlMzcyZgIAAAAAAAAnGQgyMDkxNWE5OQIAAABvLyxEAAg5OTFhNmVhNAL///997SBIAAg5Yjk0NzVjOQIAAAAAAAAAAQc2ODRmNWRjAAZTdW1hbnQHN2ZlOGU0MgAEUGVhYwg5ZDllOTVmYgABRggAAAAPAAAADwAAAA8AAAAPAAAAGAAAAA8AAAAPAAAADwE="
}
HTTP/1.1 500 Internal Server Error
content-type: application/json; charset=UTF-8
content-length: 320
{
"error" : {
"root_cause" : [
{
"type" : "sql_illegal_argument_exception",
"reason" : "Cannot find text formatter - this is likely a bug"
}
],
"type" : "sql_illegal_argument_exception",
"reason" : "Cannot find text formatter - this is likely a bug"
},
"status" : 500
}
Without LIMIT, but with fetch_size
POST http://localhost:9200/_sql?format=txt
Content-Type: application/json
{
"query": "SELECT * FROM test_emp PIVOT (AVG(salary) FOR languages IN (1, 2))",
"fetch_size": 5
}
HTTP/1.1 200 OK
Cursor: 46ToAwFaAWYBcAEIdGVzdF9lbXC0AwEBCWNvbXBvc2l0ZQdncm91cGJ5AQNhdmcGNGIzZTE5AAD/AQZzYWxhcnkAAAD/AAD/BwAIOTkxYTZlYTQBCmJpcnRoX2RhdGUAAAEAAAAGYWUzNzJmAQZlbXBfbm8AAAEAAAAHNjg0ZjVkYwESZmlyc3RfbmFtZS5rZXl3b3JkAAABAAAACDlkOWU5NWZiAQZnZW5kZXIAAAEAAAAIMjA5MTVhOTkBCWhpcmVfZGF0ZQAAAQAAAAc3ZmU4ZTQyARFsYXN0X25hbWUua2V5d29yZAAAAQAAAAg5Yjk0NzVjOQEJbGFuZ3VhZ2VzAAABAAAEAQoHCDk5MWE2ZWE0/wZhZTM3MmYCAAAAAAAAJzwHNjg0ZjVkYwAHTWluZ3Nlbgg5ZDllOTVmYgABRggyMDkxNWE5OQIAAACzKHyYAAc3ZmU4ZTQyAAZDYXNsZXkIOWI5NDc1YzkCAAAAAAAAAAEAAgEAAAAAAQD/////DwAAAAABBXRlcm1zP4AAAAAJbGFuZ3VhZ2VzAAcCAgAAAAAAAAABAgAAAAAAAAACAAAAAAAAAAFaAwACAgAAAAAAAAAACgD/////DwgBawg5OTFhNmVhNAABAWsGYWUzNzJmAAABawc2ODRmNWRjAAABawg5ZDllOTVmYgAAAWsIMjA5MTVhOTkAAQFrBzdmZThlNDIAAAJwdgFrCDliOTQ3NWM5AAABbQY0YjNlMTkFdmFsdWUAAAIAAAAAAAAAAQJwdgFrCDliOTQ3NWM5AAABbQY0YjNlMTkFdmFsdWUAAAIAAAAAAAAAAgH/AAEKBwZhZTM3MmYCAAAAAAAAJxkIMjA5MTVhOTkCAAAAby8sRAAIOTkxYTZlYTQC////fe0gSAAIOWI5NDc1YzkCAAAAAAAAAAEHNjg0ZjVkYwAGU3VtYW50BzdmZThlNDIABFBlYWMIOWQ5ZTk1ZmIAAUYIAAAADwAAAA8AAAAPAAAADwAAABgAAAAPAAAADwAAAA8B
Took-nanos: 5508409
content-type: text/plain
content-length: 685
birth_date | emp_no | first_name | gender | hire_date | last_name | 1 | 2
---------------+---------------+---------------+---------------+------------------------+---------------+---------------+---------------
null |10041 |Uri |F |1989-11-12T00:00:00.000Z|Lenart |56415.0 |null
null |10043 |Yishay |M |1990-10-20T00:00:00.000Z|Tzvieli |34341.0 |null
null |10044 |Mingsen |F |1994-05-21T00:00:00.000Z|Casley |39728.0 |null
Querying the cursor
POST http://localhost:9200/_sql?format=txt&pretty=true
Content-Type: application/json
{
"cursor": "46ToAwFaAWYBcAEIdGVzdF9lbXC0AwEBCWNvbXBvc2l0ZQdncm91cGJ5AQNhdmcGNGIzZTE5AAD/AQZzYWxhcnkAAAD/AAD/BwAIOTkxYTZlYTQBCmJpcnRoX2RhdGUAAAEAAAAGYWUzNzJmAQZlbXBfbm8AAAEAAAAHNjg0ZjVkYwESZmlyc3RfbmFtZS5rZXl3b3JkAAABAAAACDlkOWU5NWZiAQZnZW5kZXIAAAEAAAAIMjA5MTVhOTkBCWhpcmVfZGF0ZQAAAQAAAAc3ZmU4ZTQyARFsYXN0X25hbWUua2V5d29yZAAAAQAAAAg5Yjk0NzVjOQEJbGFuZ3VhZ2VzAAABAAAEAQoHCDk5MWE2ZWE0/wZhZTM3MmYCAAAAAAAAJzwHNjg0ZjVkYwAHTWluZ3Nlbgg5ZDllOTVmYgABRggyMDkxNWE5OQIAAACzKHyYAAc3ZmU4ZTQyAAZDYXNsZXkIOWI5NDc1YzkCAAAAAAAAAAEAAgEAAAAAAQD/////DwAAAAABBXRlcm1zP4AAAAAJbGFuZ3VhZ2VzAAcCAgAAAAAAAAABAgAAAAAAAAACAAAAAAAAAAFaAwACAgAAAAAAAAAACgD/////DwgBawg5OTFhNmVhNAABAWsGYWUzNzJmAAABawc2ODRmNWRjAAABawg5ZDllOTVmYgAAAWsIMjA5MTVhOTkAAQFrBzdmZThlNDIAAAJwdgFrCDliOTQ3NWM5AAABbQY0YjNlMTkFdmFsdWUAAAIAAAAAAAAAAQJwdgFrCDliOTQ3NWM5AAABbQY0YjNlMTkFdmFsdWUAAAIAAAAAAAAAAgH/AAEKBwZhZTM3MmYCAAAAAAAAJxkIMjA5MTVhOTkCAAAAby8sRAAIOTkxYTZlYTQC////fe0gSAAIOWI5NDc1YzkCAAAAAAAAAAEHNjg0ZjVkYwAGU3VtYW50BzdmZThlNDIABFBlYWMIOWQ5ZTk1ZmIAAUYIAAAADwAAAA8AAAAPAAAADwAAABgAAAAPAAAADwAAAA8B"
}
HTTP/1.1 200 OK
Cursor: 46ToAwFaAWYBcAEIdGVzdF9lbXC7AwEBCWNvbXBvc2l0ZQdncm91cGJ5AQNhdmcGNGIzZTE5AAD/AQZzYWxhcnkAAAD/AAD/BwAIOTkxYTZlYTQBCmJpcnRoX2RhdGUAAAEAAAAGYWUzNzJmAQZlbXBfbm8AAAEAAAAHNjg0ZjVkYwESZmlyc3RfbmFtZS5rZXl3b3JkAAABAAAACDlkOWU5NWZiAQZnZW5kZXIAAAEAAAAIMjA5MTVhOTkBCWhpcmVfZGF0ZQAAAQAAAAc3ZmU4ZTQyARFsYXN0X25hbWUua2V5d29yZAAAAQAAAAg5Yjk0NzVjOQEJbGFuZ3VhZ2VzAAABAAAEAQoHCDk5MWE2ZWE0Av///4OJ7owABmFlMzcyZgIAAAAAAAAnIwc2ODRmNWRjAAdMaWxsaWFuCDlkOWU5NWZi/wgyMDkxNWE5OQIAAADXd/NEAAc3ZmU4ZTQyAAdIYWRkYWRpCDliOTQ3NWM5AgAAAAAAAAABAAIBAAAAAAEA/////w8AAAAAAQV0ZXJtcz+AAAAACWxhbmd1YWdlcwAHAgIAAAAAAAAAAQIAAAAAAAAAAgAAAAAAAAABWgMAAgIAAAAAAAAAAAoA/////w8IAWsIOTkxYTZlYTQAAQFrBmFlMzcyZgAAAWsHNjg0ZjVkYwAAAWsIOWQ5ZTk1ZmIAAAFrCDIwOTE1YTk5AAEBawc3ZmU4ZTQyAAACcHYBawg5Yjk0NzVjOQAAAW0GNGIzZTE5BXZhbHVlAAACAAAAAAAAAAECcHYBawg5Yjk0NzVjOQAAAW0GNGIzZTE5BXZhbHVlAAACAAAAAAAAAAIB/wABCgcIOTkxYTZlYTQC////iAEyVAAGYWUzNzJmAgAAAAAAACcRBzY4NGY1ZGMABkdlb3JnaQg5ZDllOTVmYgABTQgyMDkxNWE5OQIAAAB5GgtwAAc3ZmU4ZTQyAAdGYWNlbGxvCDliOTQ3NWM5AgAAAAAAAAACCAAAAA8AAAAPAAAADwAAAA8AAAAYAAAADwAAAA8AAAAPAQ==
Took-nanos: 2414580
content-type: text/plain
content-length: 411
1952-04-19T00:~|10009 |Sumant |F |1985-02-18T00:00:00.000Z|Peac |66174.0 |null
1953-01-07T00:~|10067 |Claudi |M |1987-03-04T00:00:00.000Z|Stavenow |null |52044.0
1953-01-23T00:~|10019 |Lillian |null |1999-04-30T00:00:00.000Z|Haddadi |73717.0 |null
Only partial results are returned for PIVOT+LIMIT queries through the REST client. Less than the
fetch_sizenumber of rows returned, even if the query has more rows to return. Setting thefetch_sizehas no effect on the number of returned rows.Examples:
With LIMIT
Querying the cursor
Without LIMIT, but with fetch_size
Querying the cursor