Crash report trying to issue a complicated UPDATE to sqlp

[ondohotola]

when running qsv sqlp -Q large.csv small.csv u.sql with u.sql looking like this

WITH u AS (
  SELECT
    small.FQDN,
    small.NS1,
    small.NS2,
    small.NS3,
    small.NS4,
    small.NS5,
    small.NS6,
    small.NS7,
    small.NS8,
    small.NS9,
    small.NS10,
    small.NS11,
    small.NS12
  FROM
    small
    INNER JOIN large ON small.FQDN = large.FQDN
)
UPDATE
  large
SET
  FQDN = u.FQDN,
  NS1 = u.NS1,
  NS2 = u.NS2,
  NS3 = u.NS3,
  NS4 = u.NS4,
  NS5 = u.NS5,
  NS6 = u.NS6,
  NS7 = u.NS7,
  NS8 = u.NS8,
  NS9 = u.NS9,
  NS10 = u.NS10,
  NS11 = u.NS11,
  NS12 = u.NS12
FROM
  u
WHERE
  large.FQDN = u.FQDN;

and large.csv looking like

FQDN,NS1,NS2,NS3,NS4,NS5,NS6,NS7,NS8,NS9,NS10,NS11,NS12
c.ORG.na,ns1.c.org.na,ns2.c.org.na,ns3.c.org.na,,,,,,,,,
a.COM.na,ns1.d.net.na,ns2.d.net.na,ns3.d.net.na,,,,,,,,,

and small.csv loohing like

FQDN,NS1,NS2,NS3,NS4,NS5,NS6,NS7,NS8,NS9,NS10,NS11,NS12
c.org.na,ns1.c.org.na|127.0.0.1,ns2.c.org.na|127.0.0.1,ns3.c.org.na|127.0.0.1,,,,,,,,,

"name" = "qsv"
"operating_system" = "Mac OS 15.2.0 [64-bit]"
"crate_version" = "2.1.0"
"explanation" = """
Panic occurred in file '/Users/joelnatividad/.cargo/git/checkouts/polars-b0d90607192fd414/74dad84/crates/polars-sql/src/context.rs' at line 352
"""
"cause" = "internal error: entered unreachable code"
"method" = "Panic"
"backtrace" = """
   0:        0x102a667f8 - __mh_execute_header
   1:        0x102cca038 - __mh_execute_header
   2:        0x10256ba20 - __mh_execute_header
   3:        0x1050ed884 - __mh_execute_header
   4:        0x1050ed500 - __mh_execute_header
   5:        0x1050ed498 - __mh_execute_header
   6:        0x1050ed48c - __mh_execute_header
   7:        0x1054c61c8 - __Z16luaV_doarithimplIL3TMS15EEvP9lua_StateP10lua_TValuePKS3_S6_
   8:        0x1054c6208 - __Z16luaV_doarithimplIL3TMS15EEvP9lua_StateP10lua_TValuePKS3_S6_
   9:        0x104bdf5b4 - __mh_execute_header
  10:        0x104bdcb78 - __mh_execute_header
  11:        0x104bc595c - __mh_execute_header
  12:        0x104bc49dc - __mh_execute_header
  13:        0x102886ad0 - __mh_execute_header
  14:        0x1028867b8 - __mh_execute_header
  15:        0x102891a58 - __mh_execute_header
  16:        0x1028eacc4 - __mh_execute_header
  17:        0x1028e86f8 - __mh_execute_header
  18:        0x1025cbf08 - __mh_execute_header
  19:        0x1028fa058 - __mh_execute_header
"""

besides the crash, I'd love to find a way of doing this if polars doesn't seem to support UPDATE in the first place.

In CSVQ the UPDATE gives an error but I can do something like

DELETE FROM large WHERE FQDN IN (SELECT FQDN FROM small);
INSERT INTO large ( FQDN, NS1, NS2, NS3, NS4, NS5, NS6, NS7, NS8, NS9,
NS10, NS11, NS12 ) SELECT * FROM small;

but I hate to go to the opposition :-)-O

[ondohotola]

A liitle luau filter piped into a cat will achieve same, I think

[jqnatividad]

I'm afraid Polars SQL does not currently support INSERT, UPDATE nor DELETE as its primarily designed for OLAP queries.

But you're right that luau filter and cat should be able to do the trick.

Perhaps, you can whip a small recipe we can catalog in the Wiki?

[ondohotola]

Thank you, I thought so from the error messages I got when it didn't panic, but I felt I report that anyway.

I thought about writing this up, but it'l have to wait until I get it all working it's a reasonably complex transformation of DNS zone files :-)-O

[jqnatividad]

It's a legit Polars bug @ondohotola .

pola-rs/polars#20789

Have reported it upstream and preparing a fix.

[ondohotola]

Great stuff…