kdb lj ij uj joins and upgrading 2.6 to 3.x
A quick post to highlight something a lot of people are bumping into with upgrades. The joins in 3.x for uj/ij and lj all changed how they treat nulls from the keyed table. In particular nulls now by default overwrite existing values. In the past nulls from the joining table did not overwrite and left the original value in the column. See the difference in the 3/three row shown below:
q)t:([] a:1 2 3; b:`one`two`three; c:1.0 2.0 3.0)
q)u:([a:2 3 4] b:`j``l; c:100 200 300.0)
q)t
a b c
---------
1 one 1
2 two 2
3 three 3
q)u
a| b c
-| -----
2| j 100
3| 200
4| l 300
q)t lj u / v3.x The null from u overwrites previous value in column b
a b c
---------
1 one 1
2 j 100
3 200
q)t ljf u / v2.0 or ljf - The original 3 value not overwritten by null
a b c
-----------
1 one 1
2 j 100
3 three 200
Other than the int/long indexing change this is one of the biggest breaking changes in migrating kdb 2.x to 3.x.
You may also enjoy our full kdb joins article.