[BUG] nested/object query with raw or csv format inserts unexpected newlines

[acarbonetto]

What is the bug?
When using the raw or csv format output in SQL plugin, we expect each row of output to correspond with a row from the response.

for example, from SELECT nested(message.info), nested(comment.data) FROM nested_with_nulls:

Expected output:

nested(message.info)|nested(comment.data)
|hh
b|aa
c|aa
c|ab
a|ab
zz|[aa, bb]
zz|[aa, bb]
|ee
a|ab
rr|[asdf, sdfg]

Actual output:

nested(message.info)|nested(comment.data)
|hh
b|aa
c|aa
c|ab
a|ab
zz|[aa, bb
]
zz|[aa, bb
]
|ee
a|ab
rr|[asdf, sdfg
]

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

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

What is the expected behavior?
A clear and concise description of what you expected to happen.

What is your host/environment?

• OS: [e.g. iOS]
• Version [e.g. 22]
• Plugins

Do you have any screenshots?
If applicable, add screenshots to help explain your problem.

Do you have any additional context?
Add any other context about the problem.

[acarbonetto]

Note: this might be related to errors in Dashboard processing raw and csv format from the V2 engine (seems to work for the legacy engine).

[Yury-Fridlyand]

Detected the same issue with csv format without nested:
V1

POST http://localhost:9200/_plugins/_sql?format=csv
{
    "query" : "select * from account where account_number = 70",
    "legacy": 1
}

account_number,firstname,address,balance,gender,city,employer,state,age,email,lastname
70,Deidre,685 School Lane,38172,F,Chestnut,Netplode,GA,33,[email protected],Thompson

V2

POST http://localhost:9200/_plugins/_sql?format=csv
{
    "query" : "select * from account where account_number = 70"
}

account_number,firstname,address,balance,gender,city,employer,state,age,email,lastname
70,Deidre,
685 School Lane,
38172,F,Chestnut,Netplode,GA,
33,[email protected],Thompson

[matthewryanwells]

I've narrowed down that this bug is caused by the fact in legacy the header defines the content-type as text/plain; charset=UTF-8 while in the new engine the content-type is application/json; charset=UTF-8