TomThomson (3/30/2015)
peter-757102 (3/30/2015)
The forum ate my post due to a parsing error...grrr...not going to type it all again...just this:Set types and scalar types follow different logic and sets are not null-able, nor do they need to be!
No they aren't nullable in SQL. Yes, they do need to be (but that's far from its biggest problem). Try actually working in a multi-valued logic framework instead of just paying it lip-service round the edges.
The idea of "null in", "null out" really does not compute when sets are involved.
The idea that if you don't know what goes in you can't say what comes out works perfectly well for all functions whose results depend on the values of their arguments - including set-valued functions with set-valued arguments.
A set works with its own logic such as: union, intersect, except and joins.
Logic tailored to fit its "structure" and "purpose" best.
It is easier to accept it for what it is and get "null in", "null out" behavior using an empty set!
Just use "left outer join" or "outer apply" to get the record with a null column in there when you want to.
I haven't a clue why you imagine that outer join or outer apply has any relevance at all to the case where the value of the set itself is unknown. Of course that's a case that can't occur in SQL because SQL can't handle three-valued logic for anything other than comparisons of atomic values which have system-defined types and hence can't conceive of anything else being allowed not to have a known value.
I have no idea where you are coming from, but we totally cannot understand each other it seems.
Try actually working in a multi-valued logic framework instead of just paying it lip-service round the edges.
Why do I need to....and I am certainly not paying lip-service.
Languages are full of trade-offs as they need to fit he problem domain well and still be as easy to implement and understand as possible.
Having null-able sets would open up a whole can of worms and not only for SQL itself but also for every system build on top of it!
As such it would be a horrible design choice to make unless your problem domain frequently needs it to function right.
The idea that if you don't know what goes in you can't say what comes out works perfectly well for all functions whose results depend on the values of their arguments - including set-valued functions with set-valued arguments.
This would require set types to exists (thus have a known structure) while being null-able.
Else you would not be able to validate a query against a null set.
This brings me back to the point I made in my comment about null-able sets.
Regardless of this, faking null-sets by conditionally re-purposing the only row in a set is just complicating matters.
I haven't a clue why you imagine that outer join or outer apply has any relevance at all to the case where the value of the set itself is unknown.
You misunderstand my point.
What I wrote is that returning an empty set works just as well and is less complicated then returning one row with a null value.
In cases where you want to interpreted the empty set as having one row with a null value, a left outer joint or outer apply would give you just that.