English > Support
Using NULL in Advanced Search
buah:
--- Quote ---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"
--- End quote ---
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...
--- Quote ---For me, this is weak spot and I'll be free to try to clarify it further.
--- End quote ---
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:
--- Quote ---Processing of dates is a different game. Without NULL, you can't tell if the timestamp record is 0 or NULL.
--- End quote ---
Help. This sounds important, but I can't think why. Can you give an example of where this is important—in the UI?
--- Quote ---Of course, we all realize that changing the database format...
--- End quote ---
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.
--- Quote ---we probably shouldn't volunteer Nostra's time until he has had his say.
--- End quote ---
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. ;D
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.
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version