NULL Equals NULL?
-
I encourage people to read for themselves and make up their mind...
A BIG LIE!!! the EXAMPLES RUN PERFECTLY AND WERE TESTED BEFORE GETTING posted...That liar of Gonzalez mistook the writers example with mine....
The examples were amply sufficient to prove all what was said by the wirter was basically crappola...His nonsense illogical response confirmed my initial impression...
Actually the writer (me) didn't post any examples of that sort. Before you go off half-cocked with illogical, peripheral, whoop-de-whoo, garden variety nonsense and drop back to your personal insults, you should take another look at your post. Here's what you posted in your original post (copy and paste, directly from your post):
select * table1 where field1 = field2
I don't even *need* to copy this over to Query Analyzer to tell you why this won't run. Being the "RM Expert", I would expect more from you than to post buggy code like this and then lie about it stating that your "EXAMPLES RUN PERFECTLY AND WERE TESTED BEFORE GETTING posted...", while personally attacking someone new "That liar of Gonzalez mistook the writers example with mine....". Gonzalez wasn't even involved until he tried to run your bug-infested code.
LOFL. This code does not run either:
create table table1(field1 int, field2 int)
go
insert table1
select 1, 1
insert table1
select 2, 1
insert table1
select 3, 2
insert table1
select 4, NULL
select sum(field1 + field2) from table1
select(field1) + sum(field2) from table1
select * table1 where field1 = field2
For your convenience, I have highlighted, underlined, and bolded the two queries that will error out in your bug-riddled "proofs". Here are your two errors:
Server: Msg 8118, Level 16, State 1, Line 12
Column 'table1.field1' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'table1'.
Any SQL developer with six months experience under his or her belt will recognize the first one -- that you have to have a GROUP BY if you want to aggregate some columns, but not others in a SELECT query.
Any SQL developer with about 2 hours' worth of experience will recognize the need for the keyword FROM in the second SELECT query.
-
Thanks Mike. I've read the article, but still don't understand the rationale behind ANSI SQL mandating SUM to ignore NULLs. (A good thing I learnt though is that as another ANSI SQL quirk (IMO), the SUM of no rows yields NULL... 0 would have been much more consistent here : after all when I leave a shop with 0 items in my (select * from bag), the total cost of my purchase is 0$...)
-
Thanks Mike. I've read the article, but still don't understand the rationale behind ANSI SQL mandating SUM to ignore NULLs. (A good thing I learnt though is that as another ANSI SQL quirk (IMO), the SUM of no rows yields NULL... 0 would have been much more consistent here : after all when I leave a shop with 0 items in my (select * from bag), the total cost of my purchase is 0$...)
Not sure why they decided it should be that way, and it does seem rather arbitrary from my perspective, but it's what they voted on and accepted... On the plus side, with SQL 2005 UDA's, you can define your own aggregates and do whatever you like with NULLs
-
Yikes, I go away for a few hours and everyone's getting upset.
Please take a more friendly and professional tone. We can disagree and argue points without behaving poorly.
-
Someone, somewhere along this lie-laced diatribe mentioned theory versus real-world application. While Cimode appears to feel there is no real-world need to deviate from theoretical principles, one of his mentors, C. J. Date does in his book Database in Depth: Relational Theory for Practitioners. In this book, Date recognizes that "sometimes you do have to make compromises and trade-offs in the real world."
Date goes on to say that "...sometimes you'll find - because existing implementations are so far from perfect - that there are severe performance penalties for doing so...in which case you might more or less be forced into doing something not truly 'truly relational'..."
Date posits that you should have an understanding of the underlying relational model theories but understands and acknowledges that it is not always possible to implement best practices from the RM principles point-of-view.
This is not always pushed on us by our own doing (our own data models, database design or code), but by the products which are on the market to implement relational theory at the real-world level. We are forced to use software from the big boys in database products which do not always adhere to sound relational model theory.
I think Cimode is correct in asserting that a good database analyst or coder should educate themselves on relational model theory and not just on product-specific rules, but the original author, I believe, was writing from the product (implementation) side of things and was not putting himself out there as challenging the underlying relational model.
There - my two cents just cost my company $40 or $50
I hope I'm not just vehiculating here. -
With all my respect to all participants of this forum, I would like to ask Cimode few questions about how to handle situations below:
Scenario #1. Say, we need to create a database of people working in an organization. People come and go, they've got hired and leave. We need hire date and termination date to be on file. Also, those people for good working skills are awarded some sort of membership in, let's say, AAA. Membership normally has an expiration date, but some memberships are lifetime, i.e. have no expiration date.
Below is my quick code
create table personnel (
employee_id int not null identity primary key,
employee_code varchar(10) not null,
employee_name varchar(50) not null,
hire_date datetime not null,
termination_date datetime null
)
create table membership (
row_id int not null identity primary key,
employee_id int not null,
membership_expiration_date datetime null
)
drop table personnel
drop table membership
As you see, termination date allows NULLs (for those who are still with the organization), and expiration date allows NULLs (for lifelong memberships). In both cases using NULLs is justified and logical. Without NULLs, you'd have to assume these dates are somewhere well in past or in future, which is an ugly way to do, and which, by the way, was one of the causes of Y2K trouble.
Scenario #2. Imagine, you need to make a database for some lab, which receives some scientific samples, measure them, experiment with them and record parameters of the samples. Some of the parameters recorded are volume, the temperature at the beginning of an experiment and the temperature at the end of it. The parameters are taken by different people and might be taken at different times, i.e. volume might be measured before, during or after the experiment.
Here is the code:
create table samples (
sample_id int not null identity primary key,
sample_code varchar(20) not null,
sample_descr varchar(100) not null
)
create table sample_parameters (
row_id int not null identity primary key,
sample_id int not null,
sample_volume int null,
temperature_start int null,
temperature_end int null
)
drop table samples
drop table sample_parameters
As you see again, all three parameters allow NULLs, as:
- we don't know when volume and temperature will be recorded, and they are not recorded at the same time, but kept on the same row
- temperature MUST allow NULLs, as 0 is valid reading for temperature, and if I will not use NULLs, my query for the samples which had temperature = 0 at the beginning of an experiment (select * from sample_parameters where temperature_start = 0) will return WRONG number of records, as it will include samples which were not yet measured.
I believe I proved the point that NULLs are not "nonsense" and not what one has to avoid at all costs. If you disagree and can illustrate an alternative solutions for situations I have provided, please do so, as I (and I hope all others) will be very interested to see how you would do this without NULLs.
-
I do not do personal attacks but I don't mind pointing out absurdities.
[Snipped idiocracies]
Who else than an ignorant would write such absurdities?...
//I find your conflicting statements "absurd". On the one hand you claim that you "do not do personal attacks"; on the other hand you personally attack. Personal attacks do not sway most people's opinions to your cause, however. To take your rhetoric to it's logical conclusion, you must feel that Dr. Codd had "no clue about database fundamental theory" either, as NULLs were his creation. Correct?//
You are delluding yourself. You have not responded to one of the points pointed out. Which makes pretty all what you post totally worthless...
[Snipped giberrish]
//The fact that you are a self-proclaimed RM Expert who cannot even design a simple table, and whose shortest queries are infested with bugs, is extremely relevant to the conversation at hand; or any conversation on database design, implementation, etc., in which you care to partake. It basically speaks to your level of knowledge, determines your credibility, and sets the tone as to the weight your arguments carry.//
Obviously the writer mistakes SQL and RM. Any person wo knows RM knows that SQL has dumped RM principle for at least a decade.
[Snipped crappola or how to write a lot to say very little]
//#7) If you feel that any part of the information I have given is incorrect, please feel free to write and submit an article correcting any misinformation you feel I may have provided. After all, I wrote the original 4 Rules article for that exact reason myself. I for one am very eager to share in the wealth of your knowledge and experience.//
Clear Examples were provided you chose to ignore them. Choice is up to you to remain ignorant when people try to help you...Spreading such ignorance does lot of harm to database field in general...
//First of all, your proofs are poorly laid out. Your samples indicate a through lack of SQL skill. The results you provide in your "proofs" do nothing to dispute or disprove anything I've said. In fact, if your sample code actually did run, the results you claim would support the information I provided.//
OK let's do this again..I let people judge for themselves....
create table table1(field1 int, field2 int)
go
insert table1
select 1, 1
insert table1
select 2, 1
insert table1
select 3, 2
insert table1
select 4, NULL
select sum(field1 + field2) from table1
select sum (field1) + sum(field2) from table1
select * from table1 where field1 = field2
select * from table1 where field1 field2
//Second, you are contradicting yourself. You claim that 3VL and NULL are not "RM concepts". I provided information on 3VL and NULL. Yet now you claim that I am 'in no position to educate people about such ("RM") concepts.' You cannot have it both ways. Either 3VL and NULL are "RM concepts", or I was not providing information on "RM concepts". Your logic is thoroughly flawed and circular.//
You are delluding yourself and your imagination is playing tricks on you..Both NULL AND 3VK were never a part of RM (was clearly stated). Educating people is telling people the truth. Thats is why NULL should indeed NOT be put in a database in the first place. All solutions trying to make the best out of it is nothing but a hack...
//For the record, I was providing information on SQL concepts, and in SQL we have to deal with such things.
Third, I've personally read Date, Codd, et al., and it's wonderful reading. And at the end of the day I get to sit down at a SQL Server and my tables and code have to run on SQL Server; regardless of what Date wrote or what Cimode tries to sneak in between personal attacks. As such, I have to deal with NULLs on a daily basis; whether from some table someone designed with NULLable columns or from an outer join.
OUTER JOIN wer never a part of RM...//
I strongly doubt you seriously read anything from DATE else you would not pull so many absurdities I can not count...And for the record by spreading ignorance and fallacies the way you do you are no helping anybody but yourself...
[How to auto repeat yourself and still say very little]
-
As you stated:
So far I have produced:
--> Code examples to establish the dangers of NULLS
Your code examples are buggy and do not run.
Copy and paste pbs... Corrected
[Snipped diversion to primary key issue]
//And I refer you to Dr. Codd's body of work for information on NULL, why it is included, and the purpose it serves.//
A new BS...I dare Mike C to produce books...papers...
Codd never made any work focused on NULL. And if he did talk about NULLS it was ONLY to warn against their use...As he did against dupplicated records...
//So far your responses have been point by point nonsense full of ridicule and personal attacks, with absolutely zero substance. You have not backed up any of your claims, and on some "point by point" responses you simply said No Response. How disingenuous of you.//
Mike C is basically repeating the same BS over and over...He does not realize a second that hammering such nonsense still does not make it true...
//You have yet to prove that any of it is nonsensical. The only thing nonsensical is your vitriolic ranting.
Since you keep harping on about the "Relation Model" [sic] and how I "misrepresent" it (not to mention how you misspell it), feel free to share references from the articles in question where I talk about the "Relation Model" [sic]. You won't find any, because I don't reference it, nor do I talk about it. I talk about SQL and how SQL works, plain and simple. Your "harsh criticism" is vitriolic, incendiary, and completely wrong.
You are personally attacking me for "misrepresenting" the "Relation Model" [sic] when in reality I'm doing no such thing. I am trying to accurately describe how SQL works on the SQL Server platform; and it appears that plenty of people feel I have done an OK job of it. You have yet to show that any point I have made about how SQL works on the SQL Server platform is wrong.
You personally attack others who try to run your bug-riddled code snippets only to find out they don't even work.//
That's what happens when ignorants get exposed..They get mad...But in all of this Mike C haS not produced one single argument to defend use of NULLS
[Snipped]
//I have produced an article or two that describe various aspects of how SQL operates on the SQL Server platform. Other than spouting insults at me and everyone else in here with two words to say, posting bug-riddled code, and going psycho on anyone who dares point out that your crappy code samples don't even run, what exactly have you produced? What do you contribute to the community? What do you bring to the table exactly?
Obviously you bring no knowledge of SQL queries or table design. I suppose from your list of items above, your contributions can be summed up as:
(1) references to works of people who actually know what they're talking about,
(2) plenty of insulting behavior designed to aggravate and alienate people, and
(3) not much else.
"Those who can, do. Those who can't, criticize."//
The people can run the samples for themselves and draw their own conclusions...
Mike C, is obviously spreading nonsense for self promotion purposes and totally lack intellectual honnesty when his errors are pointed out to him...INstead of recognizing his error he keeps blowing smoke...and more smoke...
-
//Thanks Mike. I've read the article, but still don't understand the rationale behind ANSI SQL mandating SUM to ignore NULLs. (A good thing I learnt though is that as another ANSI SQL quirk (IMO), the SUM of no rows yields NULL... 0 would have been much more consistent here : after all when I leave a shop with 0 items in my (select * from bag), the total cost of my purchase is 0$...)//
The reason you don't see any logic is simple: there is none..It has been a long time, SQL comittee dumped the scientfic side of things...
-
//Not sure why they decided it should be that way, and it does seem rather arbitrary from my perspective, but it's what they voted on and accepted... On the plus side, with SQL 2005 UDA's, you can define your own aggregates and do whatever you like with NULLs [Smile] //
Hear!! Hear!!
-
Scenario #1. Say, we need to create a database of people working in an organization. People come and go, they've got hired and leave. We need hire date and termination date to be on file. Also, those people for good working skills are awarded some sort of membership in, let's say, AAA. Membership normally has an expiration date, but some memberships are lifetime, i.e. have no expiration date.
Below is my quick code
create table personnel (
employee_id int not null identity primary key,
employee_code varchar(10) not null,
employee_name varchar(50) not null,
hire_date datetime not null,
termination_date datetime null
)
create table membership (
row_id int not null identity primary key,
employee_id int not null,
membership_expiration_date datetime null
)
drop table personnel
drop table membership
As you see, termination date allows NULLs (for those who are still with the organization), and expiration date allows NULLs (for lifelong memberships). In both cases using NULLs is justified and logical. Without NULLs, you'd have to assume these dates are somewhere well in past or in future, which is an ugly way to do, and which, by the way, was one of the causes of Y2K trouble.//
WHile I do not usually find giving advice online very effective in helping people, I can say that your schema is not in 1NF in the first place. In other words it's flawed and does not respect rules of normalization...
instead of....
create table personnel (
employee_id int not null identity primary key,
employee_code varchar(10) not null,
employee_name varchar(50) not null,
hire_date datetime not null,
termination_date datetime null
)
you should have the following
create table active_personnel(
employee_id int not null identity primary key,
employee_code varchar(10) not null,
employee_name varchar(50) not null,
hire_date datetime not null
)
create table terminated_personnel(
employee_id not null , --> references active_personnel --> foreign key of active_personnel
hire_date datetime not null
)
--> As you can see no NULLS are allowed...(just don't forget to put indexes in the right places nonclustered on foreign)
The second table must be created to maitain 1:0 cardinality between active_personel and terminated_personnel (+ all constraints that need to be applied...)..
//
Scenario #2. Imagine, you need to make a database for some lab, which receives some scientific samples, measure them, experiment with them and record parameters of the samples. Some of the parameters recorded are volume, the temperature at the beginning of an experiment and the temperature at the end of it. The parameters are taken by different people and might be taken at different times, i.e. volume might be measured before, during or after the experiment.
Here is the code:
create table samples (
sample_id int not null identity primary key,
sample_code varchar(20) not null,
sample_descr varchar(100) not null
)
create table sample_parameters (
row_id int not null identity primary key,
sample_id int not null,
sample_volume int null,
temperature_start int null,
temperature_end int null
)
As you see again, all three parameters allow NULLs, as:
* we don't know when volume and temperature will be recorded, and they are not recorded at the same time, but kept on the same row
* temperature MUST allow NULLs, as 0 is valid reading for temperature, and if I will not use NULLs, my query for the samples which had temperature = 0 at the beginning of an experiment (select * from sample_parameters where temperature_start = 0) will return WRONG number of records, as it will include samples which were not yet measured.//
A new design flaw..the problem you are refering to is a complex problem called RVA (relation valued attributes)...I suggest you do some extra reading on the sources pointed out...
//
I believe I proved the point that NULLs are not "nonsense" and not what one has to avoid at all costs. If you disagree and can illustrate an alternative solutions for situations I have provided, please do so, as I (and I hope all others) will be very interested to see how you would do this without NULLs.//
I am afraid you have not proved anything else than you ignore the rules of normalization...Doing some extra reading will help you solve these kind of problems without resorting to NULLS..
-
Thanks for your reply, Cimode. I appreciate it.
-
Ladies and gentlemen...
I have received the following letter from the Moderator Steve Jones...
It should be censored in the next few minutes but it is an intimidation attempt...I let people judge for themselves (Probably my last post in this board as my account will probably get removed quickly...)
>Dear Sir or Madam,
>
>I noticed your posts today and wanted to send you a note.
>
>I appreciate your position and that you have a viewpoint not shared by many
>other posters. That is acceptable and I encourage you to debate with people,
>but please be more professional and respectful of others. It sounds like you
>have ideas on how to do things better, but attacking someone is not the way
>to get noticed.
When somebody publishes rubbish, one has to accept to stand publicly be challenged...
I pointed out fallacies and justified them with examples and sound proofs. Not convinced? Produce one thread where the main purpose of my critisism was not the argument itself...
OTOH, how did you react when some people called me **hole, mean . Isn't that a direct personal attack?
>Comments like "somebody who has no clue" and "IGNORE THE WRITER'S ARTICLE."
>are insulting and not relevant. You can disagree and point out inaccuracies
>without being an ass. Your tone is that of scolding and condescension.
Pointing out people's ignorance is a part of what makes boards such as yours useful...Take away that possibility to challenge people's fallacies and board such as yours become empty product oriented shells and basically useless to the database community...
>If you cannot conduct yourself in a more respectful manner, I will remove
>you from the forums.
Let me spare you the pain to justify what is nothing more than a censorship from your part .
This contribution was the first and last posted on your crappy forum who vehiculates nothing but fallacies...Just remove my account...By this attitude, it is not me you are hurting but the community and the state of database management in general...
Thanks and good luck in *moderating*...
>Thanks,
>
>Steve Jones
>Editor
>SQLServerCentral.com
-
If you look past the insults back and forth, this could have been a really good discussion. I thank Cimode for offering his ideas but wish it could have been done a bit more tactful as to not turn off readers but rather to educate us. I consider myself a lifetime student so I really enjoy reading ideas/theories when there are different points-of-view. I think we should all try to keep an open mind to ideas and learning new/alternate ways at doing something.
I would appreciate any articles he may write up on the subject. In then end I may or may not agree with what Cimode is saying but hopefully I would be better off for reading it.
I also thank Mike C for another great article on Null’s and how they are applied to SQL Server.
David
-
I do not do personal attacks but I don't mind pointing out absurdities.
Who else than an ignorant would write such absurdities?...
You are delluding [sic] yourself. You have not responded to one of the points pointed out. Which makes pretty all what you post totally worthless...
Obviously the writer mistakes SQL and RM. Any person wo knows RM knows that SQL has dumped RM principle for at least a decade.
//You have claimed that I am "misrepresenting" the "Relation [sic] Model". You also claim that I am "an ignorant", and that I am not qualified to instruct on such topics as the "Relation [sic] Model." Further, you state that "the writer mistakes SQL and RM". You are the only person here mistaking "SQL and RM." The article clearly states that it is about SQL. It does not even mention, or allude to "RM". Yet you come here flaming and arguing that I'm misrepresenting "RM". How can I be misrepresenting "RM" when I am talking about "SQL", and you (as a "person wo [sic] knows RM", know that "SQL has dumped RM principle for at least a decade"?//
Now the author backs up and separates RM from SQL because he ignores the former...I say :
What else than RM is the guiding science behind data management..SQL is nothing but a proprietary implementation...
[Snipped]
Clear Examples were provided you chose to ignore them. Choice is up to you to remain ignorant when people try to help you...Spreading such ignorance does lot of harm to database field in general...
//
Your clear examples were similar to the clear examples from another one of my articles on SQL Aggregate Functions. The only difference is that my examples actually *run*. Your examples prove absolutely nothing, except that ANSI defined aggregate functions to handle NULL in a specific manner; and that SQL Server implements the ANSI standard in that regard.//
If they had anything in common, you would have recognized your error instead of burying your head inside it...
OK let's do this again..I let people judge for themselves....
//It's nice to see that you were actually able to debug your own code samples after being told they were riddled with bugs for the umpteenth time. You owe all the people here that you called "liars" a HUGE apology for your personal issues.//
I can point out that the initial post saying it was not debuged involved a grouping and a temp table I have never produced...
Instead of apologizing to people for spreading fallacies, it's me who should apologize for exposing them...
You are delluding [sic] yourself and your imagination is playing tricks on you..Both NULL AND 3VK were never a part of RM (was clearly stated). Educating people is telling people the truth. Thats is why NULL should indeed NOT be put in a database in the first place. All solutions trying to make the best out of it is nothing but a hack...
//My article specifically talks about NULL and 3VK [sic] in SQL. Therefore:
IF NULL and 3VK [sic] "were never a part of RM (was clearly stated)",
THEN I am providing no "misinformation" about "RM"//
Given the amount of incoherence and gibberish you have demonstrated so far, I doubt there is any interest into reading you produce...
//Quid est demonstratum.// Using latin does not make reasonning...
Viewing 15 posts - 46 through 60 (of 117 total)
You must be logged in to reply to this topic. Login to reply