On Oct8, 2011, at 23:07 , Christopher Browne wrote:
> General purpose queries are nowhere near so predetermined. Indeed, whether a
> column is nullable may not be at all visible, as the value of a column may be
> computed by a function and thereby be quite opaque to static analysis.
I don't agree. To me, nullability is part of a column's type, just as the
type's OID and TYPMOD are. We do static analysis on the TYPMOD, so I don't see
why we shouldn't or couldn't do that on nullability.
> That makes me think that guessing which attributes of a query may be null
> seems like a pretty futile exercise. At first blush, we could simplify to
> PQnullable() always returning true, but that's not terribly revealing.
> However, often, there mayn't be a much better solution that isn't really
> tough to implement.
Coming up with a reasonable algorithm isn't *that* hard. Here's what I think
would be reasonable
A) All result columns which are not simple column references are nullable
B) All result columns which are simple references to nullable columns are
nullable
C) All result columns which are simple references to column from the nullable
side of an outer join are nullable
(i.e., columns from the "right" side of a LEFT JOIN, "left" side of a
RIGHT JOIN, or any side of a FULL OUTER JOIN)
D) All others are nullable
(i.e. simple column references to non-nullable columns from the
non-nullable side of a join)
If someone cared enough, (A) could be improved upon further. CASE constructs
are an obvious candidate for deeper inspection (i.e., a CASE construct is
non-nullable if all WHEN branches are non-nullable and a non-nullalbe ELSE
branch exists), as is COALESCE (similar rule).
This is mostly how it works for typmod I think - we do some analysis, but at
some point we give up and just return "-1".
As I see it, the hardest part of this feature is getting the information to the
client. I don't think the reply to a DESCRIBE message is currently extensible,
so we'd probably need to add a new version of the message. That might be a
rather tough sell, as least as long as there's isn't a clear use-case for this.
Which, unfortunately, nobody has provided so far.
> I'd not be keen on people putting much effort into futile exercises ; better
> to work on things that are "less futile."
Again, I think "futile" is the wrong word here. This is all perfectly doable,
the question is simply whether one values to feature enough to put in the word.
I certainly won't, because I don't really see the benefit. But since most of
our competitors seem to support this, and since Sun even put this into the JDBC
spec, I guess a whole lot of people disagree.
best regards,
Florian Pflug
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
