741 reputation
38
bio website
location
age
visits member for 2 years, 7 months
seen 2 days ago

Nov
2
comment “Zero” downtime and minimal space requirements for upgrade to PostgreSQL 9.2
Providing only version numbers without naming the product isn't exactly helpful ...
Oct
19
awarded  Enlightened
Oct
11
comment Is there a reason to use extremely abbreviated table names?
If 30 characters aren't enough to be able to come up with unique names for tables, you have a much more serious problem than any DBMS or development environment can solve : you have a problem with the level of expressiveness of your language and/or vocabulary.
Oct
8
answered Merging tables with a 1 to 1 relationship in a database
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
I've typed some stuff in that second link you gave ...
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
The space available in these comments is way too short to discuss all that in the level of detail it deserves. Is there some place else where we can take these discussions to ?
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
@AlexKuznetsov : sorry to say, but what about SUM ( { 1 3 } ) + SUM ( { 1 4 } ) == SUM ( { 1 3 } UNION { 1 4 } ) ? Remember that relational union is supposed to eliminate duplicates !!!
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
@SQL kiwi. Are you forgetting about static type checking ? If expressions like SUM() are handled by the static type checker as if they always return an integer, then obviously it should be impossible for the SUM() invocation to sometimes return something that is not an integer (e.g. an empty relation).
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
@TToni : "especially when you think about possible expansions of the standard" is not the context that the OP was referring to. the OP was very clearly referring to the current version of the standard, which does not include any sort of notion of "dynamic types" or some such. (Oh, and I only commented, but didn't downvote. Apart from that tiny slip I took issue with, nothing in your answer was wrong enough to warrant a downvote. IMO.)
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
@ypercube : Does 'WHERE x >= 5' cause the creation of a specialized type INTGE5, whose lowest value happens to be the value 5 ? Before you answer, ponder whether 'WHERE LN(x) >= 0' would then also cause the creation of a specialized type NUMBER_GE_1. Or the specialized type that would have to be created for 'WHERE EXP(-x*x) > 0.5'.
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
@ypercube : could you rephrase that first comment ? Either an identity value exists for some binary operator on some type, and then that identity value is a member of the type, or else it just doesn't exist, and then obviously no value can sensibly be returned from an aggregation over an empty set that involves that underlying binary operator. One example is HARMONICMEAN (parallel wiring of resistors), whose identity value is positive infinity, which is a problem for all types that do not include infinity as a value. For HIGHEST and LOWEST, an identity value always exists within the type.
Oct
5
awarded  Nice Answer
Oct
5
comment Why shouldn't we allow NULLs?
No, it's saying that the vendors should stop invoking excuses for nulls that might have been valid ones forty years ago, but have long outlived their reasonable retention period. I/O times are no longer in the order of magnitude of 80ms. Single CPU cycles are no longer in the order of magnitude of microseconds. Memory limits are no longer in the order of magnitude of a few Megs. Unlike forty years ago, the hardware speeds and capacities needed for working without nulls now DO exist with the cost not being prohibitive. He's saying it's time to move on.
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
You get it wrong where you say "since there is NO SET". There is a set. The set of all possible values of the declared type of the involved columns or expression. That declared type exists even if the table you're looking at is empty. Even empty tables still have a heading. And that declared type is exactly your "implied return type".
Oct
5
comment Why does ANSI SQL define SUM(no rows) as NULL?
MIN and MAX are not so different. Take an underlying binary operator LOWESTOF(x,y) and HIGHESTOF(x,y) respectively. These binary operators do have an identity value. Because in both cases (if the involved type is finite), there exists indeed some value z such that forall x : LOWESTOF(z,x)=x and forall y : HIGHESTOF (y,z)=y. (The identity value is not the same for both cases, but it does exist for both cases.) I agree that the results look extremely counterintuitive at first glance, but there is no denying the mathematical reality.
Oct
4
comment Database table and NULLs
It is not "vague" in standard SQL. UNIQUE is a constraint, constraints are violated only if their defining boolean expression evaluates to FALSE, if an involved attribute is NULL, then that expression will evaluate to UNKNOWN, which is not FALSE, hence constraint not violated in such a case.
Oct
4
answered Database table and NULLs
Oct
4
revised Why does ANSI SQL define SUM(no rows) as NULL?
added 128 characters in body
Oct
4
answered Why does ANSI SQL define SUM(no rows) as NULL?
Oct
4
awarded  Critic