How to emulate COALESCE?

[tmtmtmtm]

I have a regular pattern where I want to left-join two CSVs, and then produce a column that is the value from file2 if that exists, otherwise the value from file1.

So, for example, with

data.csv:

id,name
1,Artur A. Mosiyan
2,Eduard A. Aghabekyan
3,Արամայիս Աղաբեկյան
4,Eleonora V. Avanesyan

and names.csv:

id,name
3,Aramais E. Aghabekyan

I can do qsv join --left id data.csv id names.csv

to get:

id,name,id,name
1,Artur A. Mosiyan,,
2,Eduard A. Aghabekyan,,
3,Արամայիս Աղաբեկյան,3,Aramais E. Aghabekyan
4,Eleonora V. Avanesyan,,

But now I want to select the equivalent of id[0], name[1] // name[0] to get

id,name
1,Artur A. Mosiyan
2,Eduard A. Aghabekyan
3,Aramais E. Aghabekyan
4,Eleonora V. Avanesyan

I can't think of a way to do this with qsv at the minute (other than via qsv py or the like), so I usually drop to sqlite for it:

qsv join --left id data.csv id names.csv | qsv select "id,name,name[1]" | qsv rename id,original,override > tmp.csv
sqlite3 :memory: -cmd '.mode csv' -cmd ".import tmp.csv data" -header \
  'SELECT id, COALESCE(NULLIF(override, ""), original) AS name FROM data'

Am I overlooking a way to do this within qsv instead?

[jqnatividad]

@tmtmtmtm , that's why I created the polars-powered joinp command because that is a known limitation of join.

With joinp, I get:

qsv joinp --left id data.csv id names.csv
id,name,name_right
1,Artur A. Mosiyan,
2,Eduard A. Aghabekyan,
3,Արամայիս Աղաբեկյան,Aramais E. Aghabekyan
4,Eleonora V. Avanesyan,
4

The 4 is the number of rows in the result sent to stderr. You can suppress that with --quiet or redirect stderr.

You still get the uncoalesced columns though, but at least the suffix _right is appended.

I'm still exploring polars, and I found it has a new coalesce function, but most of the examples are for python, not rust, so I'm still trying to figure it out...

[tmtmtmtm]

@jqnatividad Thanks for this, but I'm afraid I'm still a bit confused. Isn't that the same output as I could get from a standard join --left by dropping the second id column, and renaming the second name column? i.e. qsv join --left id data.csv id names.csv | qsv select "id,name,name[1]" | qsv rename id,name,name_right

So then I'm still in the same place as before, needing to pass that result into sqlite or something else to get the result I want, no? Or is there something I'm overlooking that will let me get this output?:

id,name
1,Artur A. Mosiyan
2,Eduard A. Aghabekyan
3,Aramais E. Aghabekyan
4,Eleonora V. Avanesyan

I don't know polars at all, so there might be something obvious I'm missing.

[jqnatividad]

@tmtmtmtm , shouldn't your join operation result in the in my joinp example above as row 3 is the expected result from the left-join?

[tmtmtmtm]

@jqnatividad I'm not sure if I'm completely failing to understand something here, but it's easy for me to get to the results:

id	name	name_right
1	Artur A. Mosiyan
2	Eduard A. Aghabekyan
3	Արամայիս Աղաբեկյան	Aramais E. Aghabekyan
4	Eleonora V. Avanesyan

but the result I need is:

id	name
1	Artur A. Mosiyan
2	Eduard A. Aghabekyan
3	Aramais E. Aghabekyan
4	Eleonora V. Avanesyan

i.e. if name_right exists, take that, otherwise take name.

Is there a way to get that using joinp (or by some other approach)?

[jqnatividad]

Hi @tmtmtmtm ,

I misunderstood your original query as I got confused because the join was giving the right result and didn't get that your question was more about manipulating the results using a coalesce-like function...

Anyways, with the new --remap option I just added to luau(#841), you can now do this:

qsv joinp --left --quiet id data.csv id names.csv | qsv luau map id,name --remap "{id,qsv_coalesce(name_right,name)}"
id,name
1,Artur A. Mosiyan
2,Eduard A. Aghabekyan
3,Aramais E. Aghabekyan
4,Eleonora V. Avanesyan

[tmtmtmtm]

@jqnatividad that's excellent, thanks!

I can't work out how to actually get access to luau though. The docs suggest that it should be enabled by default in the prebuilt binaries, but mine doesn't have it:

> qsv luau
Could not match 'luau' with any of the allowed variants: ["apply", "behead", "cat", "count", "dedup", "diff", "enum", "excel", "exclude", "explode", "extdedup", "extsort", "fetch", "fetchpost", "fill", "fixlengths", "flatten", "fmt", "foreach", "frequency", "generate", "headers", "help", "index", "input", "join", "jsonl", "partition", "pseudo", "rename", "replace", "reverse", "safenames", "sample", "schema", "search", "searchset", "select", "slice", "sniff", "sort", "sortcheck", "split", "stats", "table", "transpose", "tojsonl", "validate"]

> qsv --version
qsv 0.91.0-standard-apply;fetch;foreach;generate;self_update-8-8;2.52 GiB-796.50 MiB-2.37 GiB-16.00 GiB (aarch64-apple-darwin compiled with Rust 1.67.1) prebuilt

Is there something else I need to install or to do to enable this?

[jqnatividad]

@tmtmtmtm I should include a manifest in the README archives, but yeah, the aarch64-apple-darwin (Apple Silicon) prebuilt does not have luau enabled because GitHub does not have Apple Silicon-based action runners and I have to cross-compile using GitHub's x86_64 macOS runners.

Cross-compilation limits me from enabling some features that require native access to the target platform, thus, those features are disabled.

I'm playing with hosting my own Apple Silicon action runner, but until that happens, you'll just have to install or compile from source to get the luau, joinp (polars feature), and to (to feature) commands running on your Apple Silicon macOS machine.

[jqnatividad]

Also, we now have a self-hosted aarch64-apple-darwin (Apple Silicon) runner.

The prebuilt macOS M1/M2 binaries do not only have all features enabled, all CPU optimizations have also been turned on.

[jqnatividad]

Hi @tmtmtmtm ,

I misunderstood your original query as I got confused because the join was giving the right result and didn't get that your question was more about manipulating the results using a coalesce-like function...

Anyways, with the new --remap option I just added to luau(#841), you can now do this:

qsv joinp --left --quiet id data.csv id names.csv | qsv luau map id,name --remap "{id,qsv_coalesce(name_right,name)}"
id,name
1,Artur A. Mosiyan
2,Eduard A. Aghabekyan
3,Aramais E. Aghabekyan
4,Eleonora V. Avanesyan

[jqnatividad]

Note that sqlp now also supports the SQL coalesce function, e.g.:

qsv sqlp data.csv "select COALESCE(NULLIF(col2, ''), 'foo') from data"

[tmtmtmtm]

Woot! That's much simpler.

So to make sure I fully understand (and to leave a few more breadcrumbs for anyone else referring to this later), in the example above I can either do it step by step in qsv, doing only the final COALESCE in sqlp:

qsv join --left id data.csv id names.csv |
  qsv rename id1,name1,id2,name2 | 
  qsv sqlp --quiet - "SELECT id1 AS id, COALESCE(NULLIF(name2, ''), name1) AS name FROM stdin"

If I don't rename the columns, clashes get expanded to <column name>_duplicate_<id>, so this could also be done as:

qsv join --left id data.csv id names.csv | 
  qsv sqlp --quiet - "SELECT id, COALESCE(NULLIF(name_duplicated_0, ''), name) AS name FROM stdin"

which saves the intermediate step, but IMO is likely to lose clarity quickly.

or, I can fall back on sqlp to do everything in SQL:

qsv sqlp --quiet data.csv names.csv \
  "SELECT id, COALESCE(NULLIF(name_right, ''), name) AS name FROM data LEFT JOIN names ON data.id = names.id"

(This handles the column-name clash slightly differently, and seems to blow up if I introduce a third table with the same columns into the mix, though I haven't dug deeply into that yet, or explored whether I can control the renaming)

These are as simple as I can come up with now, and I suspect I'll use each in different settings. Are there any other approaches or simplifications I'm missing?

[jqnatividad]

There are many ways to skin the Coalesce cat. 😄

Doing it all with sqlp is my preference, as it takes advantage of Polars' lazy execution model so it should be faster.

As for introducing a third table with the same columns, have you tried table aliases?

Select  tbl.a, tbl.b FROM data as tbl

You may also want to look into using a multi-statement SQL script to break down a complex sql query into a simpler set of queries...

https://github.com/jqnatividad/qsv/blob/7ac5f7195b70e42e13eb62e363f86b3a6a9d16ab/tests/test_sqlp.rs#L822-L852

[tmtmtmtm]

I think like I got misled by table aliasing going awry in the case of a SELECT *, which I was using for testing when building this up. Even in the simple case of a single table, it seems to get confused.

qsv sqlp data.csv names.csv "SELECT d.id FROM data AS d" works fine

But qsv sqlp data.csv names.csv "SELECT d.* FROM data AS d" complains no table named 'd' found

qsv sqlp data.csv names.csv "SELECT data.id FROM data AS d" and qsv sqlp data.csv names.csv "SELECT data.* FROM data AS d" both work.

With three tables introduced to the mix, the SELECT * was blowing up with duplicate: unable to hstack, column with name "name_right" already exists or invalid operation: Unsupported SQL join constraint: On(BinaryOp { left: CompoundIdentifier([Ident { value: "n", quote_style: None }, Ident { value: "id", quote_style: None }]), op: Eq, right: CompoundIdentifier([Ident { value: "d2", quote_style: None }, Ident { value: "id", quote_style: None }]) }) depending on which direction I did the join.

It looks like it will work fine as long as I explicitly name the columns every time, but as I had only been doing an initial probe as a tangent to see how it aliased "name_right" in the case of a third table, this type of error looked very likely to send me down yet another rabbit hole, so I quickly bailed out 😀

[jqnatividad]

Oops... sorry if I led you into yet another sqlp rabbit hole... 😄

One other solution that you may want to try to get around CSVs with identical column names are the autogenerated _t_N table aliases, where N is the 1-based index of the input CSV files:

qsv sqlp data.csv names.csv "SELECT _t_1.* FROM _t_1"