On Thursday 27 October 2005 15:54, Daniel John Debrunner wrote: > > Sigh. > > Ok, so you want to play with cats? > > What is NULL? > > > > That was a rhetorical question. > > > > Here's a more practical approach and why its a bug. > > > > By the definition of a UNIQUE TABLE CONSTRAINT, I should get only a > > single row returned when I query on the tuple that are part of the > > defined constraint. > > Nope. > Junior, allow me to rephrase. With a Unique constraint in place, when I query against the tuple of the constraint, I should get at the most one row returned. This is the anticipated and implied result when the term unique is used.
Now I looked down and read your response. You seem to be able to regurgitate text book language, yet fail to comprehend its meaning. Lets simplify what you read.... You have a table, where there are no rows. You apply the constraint. When you attempt an insert in to the table, the constraint is tested. That is to say that there is an inner select statement which will return true if no rows meeting the constraint is found. Note that the exact manner of how the constraint is applied is going to be database dependent. When you attempt to insert another row that has a matching tuple, the constraint will return false because it will find a row that matches. Now the problem. What happens when a column in the constraint is NULL? Pay attention Junior, this is the important part.... A NULL value is a special case. Derby avoids this by not allowing NULL values in a column that has been defined by a constraint. Did you think that those who wrote Cloudscape did this by accident? KISS in action. (You do know the technical term KISS, right?) In all that you wrote, you are in agreement with what I am saying until you hit a situation of a NULL. I seriously doubt that you took the time to understand the analogy to Schrodinger's Cat. This is why I suggested that you consider the implied meaning of a Unique constraint. When you tell a developer that you have a unique constraint on a row, you expect either the empty set, or one row back. Because of this, how Sybase handles a NULL value is a bug. And it can be very dangerous. By looking at the implied meaning, you avoid Schrodinger's Cat. After all, what's the definition of the word unique? But hey, what do I know? I'm old school. K&R created a near perfect language. ;-) I'll just leave this in your capable hands. After all, you're paid to play here. I'm not. -G -- Michael Segel Principal MSCC (312) 952-8175
