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]

Hope you don't mind @alexander-beedie that I'm looping you in...

any suggestions?

[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...

[urkle]

9.1.0 gives me the same error.. So this does not work for me at all.

Hopefully some of those polars fixes will get released soon to get this properly resolved.

[jqnatividad]

Once pola-rs/polars#25418 is merged, will queue up a release.

[jqnatividad]

9.1.0 gives me the same error.. So this does not work for me at all.

Hopefully some of those polars fixes will get released soon to get this properly resolved.

10.0.0 is out! The alternative CTE approach above now works.

Couldn't wait for the inline UNNEST of expressions PR (pola-rs/polars#25418) to be merged though , so it will have to wait till the next release.

[urkle]

@jqnatividad thanks!. I can confirm that workaround does work. though I do await for the proper fix to be merged.

[jqnatividad]

I can confirm that this now works with 58d9ff3

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

[alexander-beedie]

Polars SQL support for inline UNNEST of expressions (such as STRING_TO_ARRAY), coming up...
pola-rs/polars#25418