Using unnest in sqlp errors instead of returning results

[urkle]

Describe the bug
given a CSV with a column that contains comma delimited data and using the string_to_array & unnest functions returns an array about mismatched data frame lengths instead of returning the data.

To Reproduce
Given

id,data
1,"a,b,c"

Run
`qsv sqlp data.csv "select id, unnest(string_to_array(data, ',')) as value from data"

**Expected behavior**
It returns 3 rows of data
```csv
id,value
1,a
1,b
1,c

Actual results
Failed to execute query: select id,unnest(string_to_array(data,',')) as value from test: lengths don't match: Series id, length 1 doesn't match the DataFrame height of 3

Desktop (please complete the following information):

• OS: macOS 14.7.5
• qsv Version 6.0.1

[jqnatividad]

Thanks for the report @urkle .

This appears to be a problem in Polars.
pola-rs/polars#22545

There's an existing PR, but it has not been merged yet.
pola-rs/polars#22546

[jqnatividad]

@urkle this should work:

$ qsv sqlp data.csv "select first(id) as idf, unnest(string_to_array(data, ',')) as value from data"
idf,value
1,a
1,b
1,c

[urkle]

@jqnatividad that actually does not work, as it returns the very first ID only.

id,data
1,"a,b,c"
2,
3,"b,c,d"

$ qsv sqlp data.csv "select first(id) as id, unnest(string_to_array(data, ',')) as value from data"
id,value
1,a
1,b
1,c
1,
1,b
1,c
1,d
(7, 2)

[jqnatividad]

@urkle What about using a Common Table Expression (CTE)?

$qsv sqlp data.csv "WITH t AS (SELECT id, string_to_array(data, ',') as arr FROM data)
  SELECT id, unnest(arr) as value FROM t"
id,value
1,a
1,b
1,c
2,
3,b
3,c
3,d
(7, 2)

By first creating the array in a CTE then selecting from it, Polars SQL can maintain the row-by-row relationship...