QuoteNULL Fields are always treated a bit differently by the database engine.
See Expressions Involving NULL in this reference (http://www.firebirdsql.org/manual/qsg2-databases.html#qsg2-databases-sql). I've found the tip of thinking of NULL as UNKNOWN particularly helpful.
Did this not help?QuoteNULL Fields are always treated a bit differently by the database engine.
See Expressions Involving NULL in this reference (http://www.firebirdsql.org/manual/qsg2-databases.html#qsg2-databases-sql). I've found the tip of thinking of NULL as UNKNOWN particularly helpful.
You might find it easier to do a simple search for Rating = 0. ;D
It can only be "bad" if you choose to look at it that way. So "0" means "unrated." Most would agree, any movie rated less than 5 is "bad." You can't possibly argue a scale from "1" meaning the worst movie of all time to "5" is insufficient to rate the nuances between bad movies. You can search for "rating <3 AND >0." Clicking on the extreme left-hand side of the stars graphic removes (or resets to 0, if you prefer) the rating.
Weren't the result should been 2533-1817=1716? 716
If you know that some fields have ratings, then you can use:
(rating is null or rating < 1) for unrated movies
(rating is not null and rating > 0) for rated movies.
imdbrating > 0 or rating > 0 or additional rating > 0 (any movie that has a rating)
imdbrating < 1 and rating < 1 and additional rating < 1 (any movie with no rating)
imdbrating = 0 and rating = 0 and additional rating = 0
It's strange to see a text field classified as NULL and NOT NULL at the same time.Rick, you posted this while I was writing my post. What you stated here is the consequence, and the cause is my last bolded question above, I think. Or maybe not... ;D
I have idea how to prevent this, but first we need to reach an answer for 0=NULL
since it allows a column to have a NULL value, it is definetely not a relational tableAnd, the most painless thing is if we're wrong here about NULL. But, what if we're not?
I have no idea what the performance implications might be
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.
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...
we probably shouldn't volunteer Nostra's time until he has had his say.
Help. This sounds important, but I can't think why. Can you give an example of where this is important—in the UI?
God knows how many records I updated using NULL and NOT NULL while importing my 5500 movies to PVD from other collections
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).
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.
This is the only behavior that would really make sense to me, and I suspect, the majority of users.
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").
And the various "Rating" fields should make a distinction between "blank/null/unrated" and "zero", instead of treating them as equivalent!
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...
I have more than my share of movies where users commented they would have rated it lower, if it were possible.
You don't know the half of it...here's what I'm up against ;)
Topic? What topic? Good grief, why is there never a moderator when you need one? :o
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!
See how my comprehension of NULL is more suitable?
Life IS NOT NULL = not (not aware of it)... ;)
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.
QuoteAnd 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.
QuoteAnd 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."
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."
Enter Devil Advocate ModeThere is nothing wrong with having a rating scale starting at 0.Using stars alone, there's no practical way to distinguish between NULL and 0.
Exit Devil Advocate Mode