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 
  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:

Reply via email to