meta user
network profile
| bio | website | |
|---|---|---|
| location | ||
| age | ||
| visits | member for | 1 year, 2 months |
| seen | 8 hours ago | |
| stats | profile views | 50 |
|
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 |
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. |
|
Sep 25 |
comment |
Parallel inserts cause deadlock on DB2 I'd be wary of any engine in which your scenario does NOT end with a deadlock. |
|
Sep 25 |
comment |
Why is the SELECT clause listed first? @aaronbertrand: the 'blunderful time' html page is back in its original location. |
|
Sep 13 |
comment |
Can this business logic be enforced by a conditional database constraint? And how strictly is that "when inserting into" to be interpreted ? Is it valid to do an allowed insert, and then delete the referenced row from the parent ? |
|
Sep 11 |
comment |
Are there DBMS that allow a Foreign Key that References a View (and not only base tables)? (But thoughts can turn out to be somewhat silly in retrospect.) |
|
Sep 11 |
comment |
Are there DBMS that allow a Foreign Key that References a View (and not only base tables)? I know. It reflects what I thought was important at the time of writing. |
|
Sep 5 |
comment |
How do I develop this data model without a circular reference? Great. How does your model allow to query which issues correspond to which call, at the time when no actions at all have been taken yet ? What's even more stunning is that this hopelessly flawed answer gets accepted. (Or are all actions taken when and while the call is being made and is there no concept at all in this helpdesk application of 2nd/3d level support ?) |
|
Aug 28 |
comment |
Obtaining gratis copy of the SQL specification? The entire specification ? Look. I don't know about your current level of knowledge. But if somebody told you he doesn't speak English very well, but wants to learn, would you direct him to the entire Webster's dictionary and learn that ? Besides. Most of what was written in the :1992 version of the standard has NOT been "superseded". The new versions ever since :1992 have for the largest part been about the addition of so-called optional conformance features. |
|
Jun 18 |
comment |
This query is choking the server. How can I improve it? You're right about the query nesting in the FEEDBACK queries. The nesting isn't needed. |
|
Jun 18 |
comment |
This query is choking the server. How can I improve it? No, I meant the JOIN ... ON ... and LEFT OUTER JOIN ... ON ... I don't particularly follow the level of conformance and/or the additional extensions that any given SQL engine has vis-a-vis the standard. For one reason or other, I'd expect those constructs to be available mostly in the engines that also have NATURAL JOIN, EXCEPT, etc ... (and those may not exactly be the majority). |
|
Jun 18 |
comment |
This query is choking the server. How can I improve it? Plus, using joins just like that will make you miss the pets that have no comments at all but some [dis]likes, and the pets that have no [dis]likes at all but some comments. -1 for answering without thinking. |
|
Jun 18 |
comment |
This query is choking the server. How can I improve it? How many rows will there be in that JOIN for one pet if that pet has, say, 4 comments, and 6 new feedbacks ? And how will that affect your Sum()s ? |
