English > Support
Using NULL in Advanced Search
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:
--- Quote from: rick.ca on March 05, 2010, 10:26:12 pm ---It's strange to see a text field classified as NULL and NOT NULL at the same time.
--- End quote ---
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
rick.ca:
--- Quote ---I have idea how to prevent this, but first we need to reach an answer for 0=NULL
--- End quote ---
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
--- Quote ---since it allows a column to have a NULL value, it is definetely not a relational table
--- End quote ---
And, the most painless thing is if we're wrong here about NULL. But, what if we're not?
--- Quote ---I have no idea what the performance implications might be
--- End quote ---
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 ;D
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. :P
Aimhere
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version