Using NULL in Advanced Search

[buah]

I think that PVD always stores a numeric zero for unrated movies, even if that zero isn't displayed in Edit Mode, which is why "= 0" and "IS NULL" are equivalent for "Rating"

For me, this is weak spot and I'll be free to try to clarify it further.
If you try to assign value "0" to "Rating" in edit mode you won't succeed, it'll silently become blank. Why? Isn't rating a number field, and isn't zero a number? The only logical reason for me why is that so is that it was meant for value "0" to mean "Unrated" and that "Unrated" is treated like NULL, and that's how "0" became "NULL".
How I see things, Value was equaled with Condition.

Orescb already noticed this here 13months ago and I found this after we conclude it here almost exactly the same as he was back then.
Nostra asked his suggestion, he responded, but no feedback, neither changes in PVD.

The fact that after 13 months a "new generation" of devoted users again recognized this as an possible issue should put this on "to revise list".

My suggestion for "Rating"
0. Default state is blank (NULL)
1. "0" is allowed (Value)
2. "Blank" is allowed (NULL)
3. Rating in View mode means that the one wants REALLY to rate the movie. So, any action in view mode can produce only values (0-10).
4. If one wants to "unrate" the movie, he could do it only in Edit mode by clearing rating - "blank"

Other numeric fields:
5. Default box office is blank (NULL) - We still don't know it
6. "0" is allowed (Value) - The producers committed suicide
7. "Blank" is allowed (NULL) - We don't want to know, or we aren't wanted to know how reach they became. (it isn't allowed at the moment!)

This way, all numeric fields would act identically. They would all have the same "bug" (ref. Aims testings). And I'm afraid this would be only the start point for the questions of all questions:

How's 0=NULL?*




*Or "Where's the pie?", or "Where's the money?"

[rick.ca]

Wow. I'm really impressed with the thoughtful analysis of the problem. But now for the fun part. What should be done about it? I'm pretty sure it's not going to help much to make the program comply with the mathematical or database system meanings of NULL. It should behave as a normal non-technical user expects it to in the circumstances.

For text fields, NULL should mean "empty" (as in, "when I look at it, there's nothing there"). And perhaps it should be replaced with the the term EMPTY to avoid confusion. For numeric fields, NULL should have the same meaning as "0", even though in some contexts the two can be construed to mean something different. Like the question of whether a rating=0 should mean "unrated" or literally "0", I think we can do without the latter. I would be happy to see any NULL operators disappear and just use the standard arithmetic operators with "0". If it helps those who have a hard time using "0" to mean "empty" or "not set" (e.g., a rating or date), placebo EMPTY operators could be provided—they would give the same results as "= 0" and "<> 0".

I haven't thought this through very thoroughly. Am I missing anything? Are there any circumstances that would warrant exceptions to my suggested "rules"? But I have considered...

For me, this is weak spot and I'll be free to try to clarify it further.

I'm not ignoring you, buah, but you posted this after I drafted the above. I'll just add, I disagreed with Orescb 13 months ago, and I still do. I just don't think trying to accommodate distinctions between NULL, EMPTY and 0 are worth the trouble or the confusion they would cause many users even if implemented "perfectly."

I do realize, in general, there is one useful distinction between NULL and EMPTY. That's where NULL means I have no data—but there may be some to be had (e.g., the field hasn't yet been updated), while EMPTY means there is no data (e.g., the field has been updated, but there's no data available). The distinction is useful in the process of updating the database. But in any situation where this is important to me, I have the option to use other means to make the desired distinction—in more explicit terms. I will, for example, sometimes record an "n/a" (i.e., "not available" or "not applicable") in situations where I have looked for data and found none. In some numeric fields, I've used a "-1" to indicate the same thing. Even for something like My Rating, I have the option of deciding 0 means "unrated," 1 means "I've seen it, but I can't make up my mind," and 2 means "the worst movie I've seen in my entire life." If I want to be picky about the last one, instead of 2 I could use 1.4—the lowest rating on IMDb.

[mgpw4me@yahoo.com]

Personally, I don't find a difference between 'no data' and 'uninitialized'.  I still don't have data.  NULL just confuses the issue. A string comparison to "" would be better, in my opinion, than a comparison to NULL and I can live with the "huge" confusion caused by a box office of 0...sorta obvious that 0 is really NULL. 

Processing of dates is a different game.  Without NULL, you can't tell if the timestamp record is 0 or NULL.

Of course, we all realize that changing the database format means there will be a need for a conversion utility (to correct older databases) and since this project is not a democracy, we probably shouldn't volunteer Nostra's time until he has had his say.

[rick.ca]

Processing of dates is a different game.  Without NULL, you can't tell if the timestamp record is 0 or NULL.

Help. This sounds important, but I can't think why. Can you give an example of where this is important—in the UI?

Of course, we all realize that changing the database format...

Actually, I don't think it occurred to me what we're talking about implies a change in the database format. Maybe it does, but I wasn't thinking beyond the behaviour of the UI—which I assumed would be determined programmatically.

we probably shouldn't volunteer Nostra's time until he has had his say.

This always goes without saying. Our job is to enumerate all his choices before he has time to think about which TODO list any option might be added to. After this one, I won't be surprised if the infamous "Things to do when I have nothing better to do" list is replaced with a "IS NOT NULL" list. 

[buah]

I'm pretty sure that this topic won't help to get rid of NULL. And I'm sure that PVD will get more and more new users that already have some collections. Because of those users who'll import their collections to PVD in the future, I rather see this topic as a very useful to have it in mind while updating database, and please reconsider it to become sticky.

God knows how many records I updated using NULL and NOT NULL while importing my 5500 movies to PVD from other collections (I insist to repeat everywhere the number of my movies because of new users, to encourage them to do the same, cause it's easy and worthy).

I wish I had such a topic in that time. As such, it is more important to have it in Support, than in Feature forum, in my opinion.

[mgpw4me@yahoo.com]

Help. This sounds important, but I can't think why. Can you give an example of where this is important—in the UI?

I'm thinking more of birthdays and dates of death than anything.  It isn't critical information since it's unlikely I'd sort by either field, but it makes a nice supplementary fact (like place of birth).  Since there is limited manipulation of these values, a string value would probably work...age determination would require conversion, but I can't think of anything else.

[AimHere]

I'll let you guys try to sort this out.  

If it were up to me, I'd like to see "NULL" mean a blank or empty field (including fields that at one time had data but which are CURRENTLY blank/empty), and "NOT NULL" mean anything else (including numeric "zero"). Further, clearing/blanking a field should reset it to NULL status, unlike now.

And the conditions "IS NULL" and "= 0" should NOT be equivalent. A value of zero is still a value, not a "null". Then "null/not null" would have NO relation to "zero" (or any other value) other than the obvious (i.e. if the field contains "zero", then it is NOT "null").

For this to be truly useful, though, PVD would have to leave ALL fields in a new record empty/blank/NULL unless otherwise specified, rather than filling in Year/Budget/BoxOffice with numeric zero like it does now. (Remember, at one time those three fields WERE left empty or "null" by default, unlike now.)

And the various "Rating" fields should make a distinction between "blank/null/unrated" and "zero", instead of treating them as equivalent! That way, a user could actually give a movie a rating of zero, without it being confused with an UNrated status. Perhaps the user interface could be modified to "ghost out" the "star bar" next to "Rating", or simply say "Unrated", for blank/null ratings. Any rating value (from 0 to 10) would cause the stars to appear normally (including all-empty stars for a zero rating), while clearing out the text box would reset the "star bar" to ghosted/unrated status.

This is the only behavior that would really make sense to me, and I suspect, the majority of users.

Aimhere

[rick.ca]

God knows how many records I updated using NULL and NOT NULL while importing my 5500 movies to PVD from other collections

My experience may be different from yours for because...

In this case, the anomaly can be explained by this: When I first started using PVD, each new record added had a blank "Budget" field, whether I added the movie manually, by "New Movie Master", or by scanning files on disc. So those records have a truly NULL value for "Budget". BUT, at some point (possibly around the end of May '09), the behavior of PVD changed so that new records got a "Budget" value of ZERO if not otherwise specified. (A "Budget" of zero does not appear in the normal viewing mode, but will show up in Edit Mode, i.e. the field will actually have a "0" in it, as opposed to the older records where "Budget" is BLANK).

But whatever the reason, I've always found the technique of limited use if for not other reason than I have to question the results. I might find the idea of leaving NULL as it is more acceptable if we had more control over the relevant values. Maybe that will come with a new Multiple Movies Editor that allows any field to be set to NULL, blank (text) or 0 (numeric). That would allow me to use NULL to indicate "I see no cheese" and the others would mean "there is no cheese." It's still not very convenient, however, because NULL will still include the empty/0 values (I have to bookmark them to distinguish them). And then there's the issue of NOT NULL not returning a result intuitively consistent with the NULL result.

But it seems that not even this is feasible, because...

As mgpw4me has pointed out, there really is no way to set a NULL value on any field in PVD. Not-NULL values are created anytime a value is assigned to a field, and remain not-NULL forever, even if the field is cleared out or set to zero.

[buah]

All what we said here made me thought of "NULL" as a "application/database is not aware of it" and for NOT NULL as a "application/database is/became aware of it", rather than unknown/known, empty/not-empty, blank/not-blank, etc...

[rick.ca]

This is the only behavior that would really make sense to me, and I suspect, the majority of users.

You've got to be kidding! The majority of users aren't even aware of the issue or haven't found a reason to care. Most of the rest are confused because the technical operation of the NULL operator does not yield a result that is intuitive in the circumstances. It's the lack of consideration of what is sensible to the average non-technical user that is the source of the problem in the first place.

And the conditions "IS NULL" and "= 0" should NOT be equivalent. A value  of zero is still a value, not a "null". Then "null/not null" would have NO relation to "zero" (or any other value) other than the obvious (i.e. if the field contains "zero", then it is NOT "null").

This is technically correct, but doesn't have any bearing on the question at hand, until it's decided it should. I contend that it should not. As I've pointed out a number of times in a number of different ways, there's no reason why NULL and 0 should not be equivalent—if that is simpler and appropriate for the situation at hand. I'm not suggesting there's no circumstance in which different meanings would be appropriate—but they do seem difficult to find. For most users in most situations, they both mean the same thing, and any attempt to make a distinction therefore only causes unnecessary confusion.

And the various "Rating" fields should make a distinction between "blank/null/unrated" and "zero", instead of treating them as equivalent!

And this is the perfect illustration of my general point. There's absolutely no justification for the assertion there "should" be "zero" rating. As a rating scale, there's nothing wrong with it starting at 1. It is, in fact, far more sensible than what you suggest. Using stars alone, there's no practical way to distinguish between NULL and 0. And there's no reason to rate something 0 when you can just as easily decide 1 has the exact same meaning. It's also obvious most users will find it perfectly intuitive the way it is. With a 0 rating allowed, we'd be forever explaining the distinction between "0" and "unrated."

[rick.ca]

All what we said here made me thought of "NULL" as a "application/database is not aware of it" and for NOT NULL as a "application/database is/became aware of it", rather than unknown/known, empty/not-empty, blank/not-blank, etc...

That's not surprising. But it seems too technical a nuance to be useful to most users, whereas the latter are well understood and expected. If it can coexist with functions that most users need and find intuitive without creating unnecessary confusion, then fine. Otherwise, I think we should be looking for ways to simply things and make the search functions more user-friendly. Any functionality that may be lost will likely only be noticed by those of us geeky enough that we're able to find another way to do what we want to do anyway.

[mgpw4me@yahoo.com]

Clearing a movie sets all the values to default, so it IS POSSIBLE to set a NULL value in PVD (if the default value is NULL).  The description field is an example.

Regardless, I either have information or I don't.  I don't care if a script doesn't return a value...it's the same thing...there isn't any data.  To me, NULL / NOT NULL are not particularly useful and they confuse the issue...do you have data or not?  

The only place I can find where NULL has value is in setting a birthday or date of death, and those can both be text fields with a zero length...conversion to numeric values could be done easily enough if you really need to know the age of a person and can't do the math yourself.  Given that dates are text values when the script gets them, and are converted to a date value when submitted to the database, I see this as a better solution that having nulls for a single instance.

A rating of 0 is the same...it can't be set via IMDB...it isn't allowed by their database.  0 = no rating.  I have more than my share of movies where users commented they would have rated it lower, if it were possible.

On the programming end, I can see that nulls do have value.  It's easier to update a field if there is a standard value (null) for all field types (in particular when you have the option to have a field updated always, if no data exists, or never).  The PVD interface is already more than complex enough and should be simplified where possible.

[rick.ca]

I have more than my share of movies where users commented they would have rated it lower, if it were possible.

I didn't say it in my last post, but now I can't resist... It's not hard to see the virtues in a -10 to 10 scale, where negative means degrees of "bad," positive means degrees of "good" and "0" means indifferent. But, aside from being just plain silly, that's not the scale that has been chosen. I suppose these people might also comment, "Roger Ebert gave this 1/2 star—he must have liked it a little."

[mgpw4me@yahoo.com]

You don't know the half of it...here's what I'm up against 

[attachment deleted by admin]

[buah]

All this fuss started because I wanted to RATE my seen UNRATED movies.
This was my thought process.
"How to get unrated movies? Their fields are blank, so filter NULL seen movies. OK, here they are. Nice... So, let's check if rated movies are "seen minus NULL". I'll apply NOT NULL, that far out makes sense!"

And that is when curiosity killed the cat.

If PVD was designed to start rating form 1, I assure you I'd apply Asearch "Rating<1" for getting unrated movies. I would never think of NULL, and I'd still live in ignorance, blessed it was!


[EDIT] mgp, you posted while I was typing my post. Now, I look at your IMDb statistics (man, you have way too many bad movies ) and new question arises. In legend, for example "4-6", "6-8" for IMDb rating and especially for personal rating in what count rating 6.0 is included?

[mgpw4me@yahoo.com]

In life, one sometimes steps in a cow pie then wonders why the party hosts refuse them entry.  It's a strange thing this "life".

[buah]

Indeed strange. And shorter than we're ready to admit. But, at least IS NOT NULL...

[mgpw4me@yahoo.com]

Not null = not unknown...hmmmm...

[buah]

See how my comprehension of NULL is more suitable?

Life IS NOT NULL = not (not aware of it)...

[rick.ca]

You don't know the half of it...here's what I'm up against  

Wow. No wonder you're so ornery. You should watch better movies.

There may be hope for you. Since I started using PVD the proportion of movies with an IMDb rating < 6 that I've viewed has dropped to about 3% from the 23% it was in the two years before. And most cases, I viewed low-rated movies very intentionally. For example, I decided to watch the entire Planet of the Apes series, knowing full well a few of the movies were very bad. I recently watched Plan 9 from Outer Space (frequently billed as "the worst movie of all time," but getting a generous 3.6 on IMDb) because it was the subject of 8.0-rated Ed Wood.

Topic? What topic? Good grief, why is there never a moderator when you need one?