Using NULL in Advanced Search

[buah]

EDIT: The original name of this topic was Funny Thing, as I can remember. Obviously the things became more serious here

Funny thing.

Filters -> Viewed = 2533

1. + Advanced search->rating IS NULL =1817, or
2. + Advanced search->rating=0 =1817, or
3. + Advanced search->rating IS NOT NULL =2533?

Heh?

And I thought I learned something about logic

[rick.ca]

Did this not help?

NULL Fields are always treated a bit differently by the database engine.

See Expressions Involving NULL in this reference. I've found the tip of thinking of NULL as UNKNOWN particularly helpful.

[buah]

Did this not help?

NULL Fields are always treated a bit differently by the database engine.

See Expressions Involving NULL in this reference. I've found the tip of thinking of NULL as UNKNOWN particularly helpful.

I thought so Rick. But when it says that all 2533 is not uknown (IS NOT NULL) and at the same time that of those 2533, 1817 is uknown (IS NULL) I don't know what to think anymore...

[rick.ca]

If IS NOT NULL is obtained by NOT-ing IS NULL and NULL means "unknown," then NULL might be expected to identify empty fields, but it's meaning is still unknown, so NOT-ing the result quite obviously doesn't exclude them, and now everything IS NOT NULL...

You might find it easier to do a simple search for Rating = 0.

[buah]

You might find it easier to do a simple search for Rating = 0.

It's getting harder thant to import 6000 movies

When I do search rating=0, for the results I got also entries which rating field is empty, not 0? That's how all began, look at my first post

[rick.ca]

My position on using "0" as a rating for really really bad movies instead of the more obvious meaning of "unrated" hasn't changed...

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.

Also, considering most professional reviewers use only four or five stars, we should be able to get by with ten.

[buah]

Ok, if I understood you well, and please correct me if not so, I need here to clarify some things.

I'm in the last phase of importing and updating all my movies to PVD - rating seen movies. So, I filtered movies to seen ones and that's how I got 2533 entries. Some of those 2533 seen movies are rated, and some not.

Of those 2533 how to get a list of only not rated movies?

The only logical answer for me was to apply IS NULL search because their rating field should be empty (uknown). That's how I got 1817 entries.

How to get a list of only rated movies?

I reset advanced search filter. The only logical answer for me was to apply IS NOT NULL search because their rating field should not be empty (filled in with some numbers, including zeros. That's how I got 2533 entries??? But how? It was supposed to be -1817 entries that aren't rated? Weren't the result should been 2533-1817=716?

Edit: None of my movies ever had rating "0", I just tested search against it.


Edit: corrected counting mistake, thx mgp

[mgpw4me@yahoo.com]

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.

[buah]

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.

1. Why it isn't possible to get desired result only with NULL search?
2. Tnx, but nope mgp, your tips don't work in my case, still same results... (1817 and 2533)

[attachment deleted by admin]

[AimHere]

Did a little quick testing on this issue myself... what I've found is that "Rating = 0" and "Rating IS NULL" are equivalent for the purposes of Advanced Search.

I have 1364 movies in my database, and either of the above search criteria returns 1329 visible records. A search for "Rating > 0", on the other hand, returns 35 movies... 1329 + 35 = 1364. (Clearly I have some work to do regarding rating my movies, hehe.  )

When editing a movie record that has any non-zero rating, you can actually enter "0" in the text box next to the stars and save the record... and the movie essentially becomes "unrated". (If you edit the movie record again, the text box will actually be blank now, but blank and "0" are still equivalent.) Same thing happens in edit mode when you click to the left of the leftmost star, or click-and-drag on the stars and move your mouse off the left end before releasing; the movie becomes unrated.

So, the search really boils down to:

• "Rating = 0" for Unrated movies [or "Rarin IS NULL" if yoiu prefer, either one will work the same
• "Rating > 0" for rated movies

Then, just be sure to use a rating of at least 0.5 (which may as well mean "absolute excrement"  ) when rating all your movies, and you're set. Never use a rating of "0" except to clear out the rating altogether.

What threw off your results using mgpw4me's suggestion was his inclusion of "rating is not null" in the second line.

Side note: I think the reason why "Rating IS NOT NULL" returns all records might be due to PVD actually assigning a value (in this case, zero) to the rating of EVERY record at the time of creation, and maintaining a value in that field at all times. A value of "zero" (meaning unrated) simply isn't shown visibly in the text box next to the stars. So, all records have some value for "Rating", and hence, the "Rating" field is never NULL.

Aimhere

[mgpw4me@yahoo.com]

I just checked my database and my method works correctly with imdbrating (395 not rated, 3168 total, 2773 rated)...NULL check should not be necessary.  I have a funny feeling that when you edit a movie, the rating might change from NULL to '0', which may account for your situation.

You'll have to add checks for each of the other ratings that is being used.
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)

AimHere is right...as best I can tell.

[AimHere]

Okay, I've done a little more testing, and I think I've figured out how the "NULL" and "NOT NULL" conditions really work.

"NULL" means that a field currently has no value (as you might expect), which is useful for searching text fields. So you could, for example, search for "Description IS NULL", get a list of all movies which have no descriptions, and fill them in.

"NOT NULL", on the other hand, seems to mean that a field has had data in it at some point, regardless of whether or not it currently has data.

As a test, I ran two advanced searches: "Tagline IS NULL", which returned 1263 records out of 1364 movies, while "Tagline IS NOT NULL" returned 107... but 1263 + 107 = 1370, not 1364! The six-movie difference can be explained by the fact that six movies preciously had values in the "Tagline" field, which were subsequently cleared out. Since they're currently blank, they are included in the "NULL" condition... but they had data in them at least once, so they are ALSO included in the "NOT NULL" condition!!! In other words, "NULL" and "NOT NULL" are NOT mutually exclusive.

If I pick a random movie which has never had a tagline and give it one, then re-run both searches, the counts change to 1262 and 108. I then re-edit the movie's record, clear out the tagline, save, and run the searches once more... 1263 and 108! So, while the "NULL" result might fluctuate up or down as fields are given data or cleared out, the "NOT NULL" results will only ever increase with normal program usage.

Given this counter-intuitive behavior, I'd recommend avoiding the use of the "NOT NULL" condition for anything. A real database guru might be able to provide a valid reason for this logic, but for most people it's totally useless.

Aimhere

[AimHere]

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)

Remember, a movie can actually have a fractional rating of 0.5, so your second line won't catch them all. You'd actually need:

imdbrating = 0 and rating = 0 and additional rating = 0

Aimhere

[mgpw4me@yahoo.com]

Forgot about the floating point field.  I'm not fond of assuming that '0' will always be there, so I'd still check against < .01

NULL means 'unknown value'.  Given the PVD interface, I don't see anyway you can set a NULL value.  Simply clearing a field won't make it null...you have to run an SQL update command to set the value to null.

Reference material for those who care:
http://www.databasedesign-resource.com/null-values-in-a-database.html

[rick.ca]

I hope we got this figured out without anyone's head exploding!

AimHere's observation is a bit disconcerting. It's strange to see a text field classified as NULL and NOT NULL at the same time. In the case of a text field, using "0" instead of "NULL" is not available as a means of avoiding the anomaly.

[buah]

Hey guys,

First of all, thank you for your time as well as for been interested to help me.
Second of all thank you for preserving my common sense, whether or not aware of it.
What might happened here, according what you wrote Aim, is that I imported "rating" xls column to PVD in the time of creation my .pvd.

Well, please stay with me now.

"Rating" xls column had some cells without values ("not rated"). But, were those cells considered to be NOT NULL? Yes, in my opinion! How?

How did I get "Rating" xls column? By exporting CATVids field "My rating" to xls. In CATVids, there were movies that were unrated. Were those records considered to be NOT NULL?
No, in my opinion, they were NULL because (condition of) those records were generated in CATVids, but not imported to it! CATVids (.mdb) database was unaware of them.

So, what happened? When you export information that some record IS NULL (no value, "not rated") to xls cell,
for that cell that information that something (what IS NULL) is imported into it, is what makes it NOT NULL!!! Regardless of xls cell becomes empty, Excel became aware of it's NULLness. So to say: it became known (NOT NULL) that it is uknown (NULL)? NOT NULL wins here.

What happened while exported further from Excel to PVD? The very known (NOT NULL) value was imported to PVD - "empty"! NOT NULL wins again!

When NULL wins? When you generate completely new record within PVD. At the moment "rating" is empty and is truly NULL. Aim, you were totally right about this: You enter some value, than erase it - it's not NULL any more: it became empty with the value "empty" (so NOT NULL)

So my final conclusion on NOT NULL would be:
NULLing is transferable only between databases. Otherwise, you can never export/import NULL information (condition) to preserve it as NULL. As such it can only exits inside the database.

Please, confirm what I think or discuss it further.

If we agreed on this, than what is left to conclude is that what was caused this topic and made a mess, and what was the real question here is (look at my first post again):

How the hack is possible that "Rating=0" = "Rating IS NULL"?

Any thoughts?


EDIT:

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...

I have idea how to prevent this, but first we need to reach an answer for 0=NULL

[rick.ca]

I have idea how to prevent this, but first we need to reach an answer for 0=NULL

I'm not sure, but since the whole NULL thing is apparently up to Firebird and outside of nostra's control, maybe the answer is to initialize all numeric fields with "0" so there are no NULL's. And maybe for string fields, the Firebird NULL should be replaced (in effect) with a programmed NULL that means "empty" (perhaps determined by a string length function returning 0?). I have no idea what the performance implications might be, even if something like this were feasible. We might prefer the illness to the cure.

[buah]

Exactly!
Because this is what scares me regarding PVD

since it allows a column to have a NULL value, it is definetely not a relational table

And, 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

1. Since it is obvious that it's not possible to import NULL condition to PVD, but only empties, performance implication while importing data to PVD IS NULL. The real and genuine IS NULL one

2 While generating new record within PVD, performance implication would be measurable in seconds, since generating full record takes several tens of seconds, right? And PVD still would be the fastest thing I ever used.

What do you think?

I mean, I'm running my 842MB PVD database on my 8 years old PIV 1,5GHz desktop without any performance problem. It's still unbelievable to me. That's why I'm inter alia so amazed with it.

[mgpw4me@yahoo.com]

If you look at the situation in binary, there is no numeric value for NULL, so numeric fields effectively can't be NULL unless there is a 'special bit' defined that says the number is NULL, and that would be proprietary / non-portable across applications.

In ASCII, the number '0' is defined as NULL. ( ASCII table reference: http://www.asciitable.com/ ) Strings (usually) end with a null (zero-terminated).  If the first byte of a string is 0, then the string is empty (has 'no value').

To get a true NULL value, go into the scripting language:

var somestring : string;  

This is a TRUE NULL value...it has not been assigned a value, but a place in memory has been reserved for it.  If you try to use 'somestring' before you set a value, the compiler will tell you that you're trying to use an uninitialized variable and your script will not compile.

In a database, remember that the values are loaded into memory, so a null value (depending on the database, and the field type) may result in the value 0 or it may simply leave the value already at that address in memory unchanged.  I have no idea how Firebird handles this.

For clarity, I'd prefer not to have null values...fields should be set to 'something' even if it is 'no value' (ascii code 0, which is '0' in a numeric field and "" in a text field).  In a date / time field, I don't know what validation checking is done, but I suspect the time is stored in a numeric format (unsigned long integer, based on an arbitrary starting date), so even setting the field to '0' will mean the date / time is the starting date, not a 'null' date...in which case NULL might be the only way to deal with it.

[AimHere]

Another follow-up to my earlier post.

I tried doing searches based on the "Budget" field. This field, like "Rating", is numeric... you can type any text you want into it in Edit Mode, but only numeric values will be saved when you save the record (non-numeric input will be "silently" rejected).

Now, it so happens that NONE of my movies have any budget data recorded for them in my database... "Budget IS NULL" returns 1364 out of 1364 records. But "Budget IS NOT NULL" returns 403 records!

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).

So, those 403 records are merely the ones that have true zeroes in "Budget", as opposed to the earlier NULL value. A search for "Budget = 0" returns the SAME 403 records.

The same thing goes for the "Box Office" and "Year" fields... earlier releases of PVD left them NULL if not specified, while newer versions set them to zero.

Note the difference between this behavior and that of the "Rating" field, as discussed earlier. For "Rating" and its siblings, "Rating = 0" and "Rating IS NULL" are equivalent (returning the same group of records), but that's not the case for "Budget"! "Budget = 0" returns ONLY the movies that have a budget actually equal to zero, NOT the ones that are NULL. "Budget IS NULL" returns movies with either zero or NULL budgets!

(Remember, 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". "Rating IS NULL" is still returning records with either zero OR NULL values, but since "Rating" is never truly NULL, the condition simply returns all records with a rating of zero. That's not the case for "Budget", etc., which, as I've found, can have a truly NULL value, at least on older records.)

What it boils down to is this: the "IS NULL" condition will return records with (numeric) zero or (text) blank values AS WELL AS true "null" values, no matter what field you're searching on.

The "NOT NULL" condition always returns all records for which the tested field has ever been assigned ANY value (other than true NULL), regardless of the field's current value.

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.

So if anything, the "IS NULL" testing is technically broken, as it technically should only return records for which the field is truly NULL (has never had any value assigned by either PVD or the user), not fields containing numeric zeros or empty strings. "IS NOT NULL", on the other hand, actually works as intended.  

Aimhere