This is not a bug, everything works fine. It's a small SQL codegen improvement.
linq2db uses nvl to translate ??, which is nice because it's shorter than SQL standard coalesce.
The trick with nvl is that it only accepts 2 parameters, whereas coalesce accepts many.
In our codebase, we have a query that computes an error message based on a list of checks, it looks like this:
That's 7 ?? chained.
The resulting SQL is of course 7 nested nvls: nvl(.., nvl(.., nvl(.., /*4 more*/))))))).
It works well enough, I just think it'd be easier to read if it was a single coalesce(.., .., .., .., .., .., ..).
So my suggestion is to convert nested ?? expressions into a single coalesce instead of nested nvl.
This expression tree:
Could be converted into coalesce(A, B, C, D, E) (i.e. a depth-first traversal).
A related Oracle SQL codegen you could have is convert patterns a == null ? c : d (and a != null) into nvl2(a, d, c) (resp. a, c, d).
nvl2 is another Oracle proprietary function that returns the 2nd arg if the first is not null, and the 2rd arg is the first is null.
This is not a bug, everything works fine. It's a small SQL codegen improvement.
linq2db uses
nvlto translate??, which is nice because it's shorter than SQL standardcoalesce.The trick with
nvlis that it only accepts 2 parameters, whereascoalesceaccepts many.In our codebase, we have a query that computes an error message based on a list of checks, it looks like this:
That's 7
??chained.The resulting SQL is of course 7 nested nvls:
nvl(.., nvl(.., nvl(.., /*4 more*/))))))).It works well enough, I just think it'd be easier to read if it was a single
coalesce(.., .., .., .., .., .., ..).So my suggestion is to convert nested
??expressions into a singlecoalesceinstead of nestednvl.This expression tree:
Could be converted into
coalesce(A, B, C, D, E)(i.e. a depth-first traversal).A related Oracle SQL codegen you could have is convert patterns
a == null ? c : d(anda != null) intonvl2(a, d, c)(resp. a, c, d).nvl2 is another Oracle proprietary function that returns the 2nd arg if the first is not null, and the 2rd arg is the first is null.