Re: IS NULL Question
I'm having problems understanding NULL. I grasp what a NULL value is, Actually, you don't :-) NULL is not a value, it's the lack of value and a state. A column can have 2 states: NULL or NOT NULL, which is part of the reason why SQL allows for the IS NULL and IS NOT NULL clause as opposed to checking for certain values with the equals operator ( MyColumn = 'My Value') but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) If the set is empty, the columns aren't NULL. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
By chance when you set them as null did you use null or 'null' setting it as a string value? __ Lucas Heuman Phone 609.485.5401 Martijn Tonies [EMAIL PROTECTED] 05/01/2007 04:45 AM To MySQL mysql@lists.mysql.com cc Subject Re: IS NULL Question I'm having problems understanding NULL. I grasp what a NULL value is, Actually, you don't :-) NULL is not a value, it's the lack of value and a state. A column can have 2 states: NULL or NOT NULL, which is part of the reason why SQL allows for the IS NULL and IS NOT NULL clause as opposed to checking for certain values with the equals operator ( MyColumn = 'My Value') but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) If the set is empty, the columns aren't NULL. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
At 9:06p -0400 on 30 Apr 2007 John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, A NULL value is rather an oxymoron. It'd be more accurate to say that NULL means absence of a value. but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
Kevin Hunter wrote: At 9:06p -0400 on 30 Apr 2007 John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, A NULL value is rather an oxymoron. It'd be more accurate to say that NULL means absence of a value. but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin MyISAM has no problem representing NULL. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IS NULL Question
We use NULL all the time with MyISAM tables, and I've never noticed a problem. Well, there was one bizarre bit of business that's already been mentioned: my predecessor actually put the string NULL into a field, and since it looks the same in the MySQL client that gave me a devil of a time. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Kevin Hunter [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 01, 2007 11:37 AM To: John Kebbel Cc: MySQL List Subject: Re: IS NULL Question At 9:06p -0400 on 30 Apr 2007 John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, A NULL value is rather an oxymoron. It'd be more accurate to say that NULL means absence of a value. but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin MyISAM has no problem representing NULL. My mistake. Obviously old data. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
Jerry Schwartz wrote: We use NULL all the time with MyISAM tables, and I've never noticed a problem. I think there was a bug at one point dealing with NULL and empty strings, but it strikes me that it was a bug in the new client/server protocol that was introduced in (4.1? 5.0?). So, it might be that the columns contain empty strings, yet the client displays them as NULL. Regardless, the earlier suggestion about using length() should show what's really in the columns. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
It looks like it was a string named NULL posing as a null value. I got 0 for ISNULL(suffix), which I assume means false. I tried this command ... update persons set suffix = 'Empty' where suffix = ''; It changed the NULLs to Empty. On Mon, 2007-04-30 at 18:12 -0700, Jeremy Cole wrote: Hi John, Are you sure they are actually NULL and not NULL (i.e. the string NULL)? Try this: SELECT first, last, ISNULL(suffix), LENGTH(suffix) FROM persons LIMIT 5; Regards, Jeremy John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
Hi John, Are you sure they are actually NULL and not NULL (i.e. the string NULL)? Try this: SELECT first, last, ISNULL(suffix), LENGTH(suffix) FROM persons LIMIT 5; Regards, Jeremy John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT NULL question
I'm really just currious as to WHAT you would want to see as opposed to NULL? How could you have a field that has no value? What would it mean? NULL is the answer to this. It is recording the absence of something. So, I would say that this is an expected behaviour of any database engine. For example, a price field might have 3 distinct values. First value is 0. This says that there is a price, and what it is. Second value is 0. This might say that it is a 'free' item. The third value is NULL. This would say that there is no price. For data collection, 0 would mean that it was measured, but the value was 0. There might be 0 of rain fall in a day. A NULL in this case would say that while the field is there, either measurements were not done, or there was a 'problem' with the reporting device, or it has not reported yet. Ah, and remember that NULL and NULL are different. On 15 Jan 2003 at 18:48, Benjamin Pflugmann wrote: Hello. On Tue 2003-01-14 at 09:32:02 -0800, [EMAIL PROTECTED] wrote: I'm aware that NULL and are not the same thing.. I would like to prevent the column from accepting values automatically ( with out the presence of a DEFAULT). [...] The problem is: You have a DEFAULT, you just don't know it. ;-) Do a SHOW CREATE TABLE stuff, and you'll see what I mean. That is a known deficiency, as Eric told you: It's a known behaviour and is described in the MySQL manual: http://www.mysql.com/doc/en/Bugs.html You can change it if you compile MySQL server with -DDONT_USE_DEFAULT_FIELDS option. But in this case you can't use default values at all. http://www.mysql.com/doc/en/configure_options.html describes -DDONT_USE_DEFAULT_FIELDS. HTH, Benjamin. -- [EMAIL PROTECTED] --Opus-- There is no such thing as an underestimate of average intelligence. - Henry Adams Get added to my Humor list: mailto:[EMAIL PROTECTED]?subject=ADD_HUMOR Get added to my Neat list: mailto:[EMAIL PROTECTED]?subject=ADD_NEAT Get my PGP public key: mailto:[EMAIL PROTECTED]?subject=PSENDbody=send%20PublicKEY.asc Visit My Home Page: http://value.net/~opus/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NOT NULL question
Hi. On Wed 2003-01-22 at 23:07:24 -0800, [EMAIL PROTECTED] wrote: I'm really just currious as to WHAT you would want to see as opposed to NULL? Well, you asking the wrong guy, because I did not need that feature, but I'll try to explain anyhow. They want to see an error instead. It is the same why people use foreign keys and constraints: They want to enforce that only data which complies which certain rules enters the database. And before you are asking: Yes, there are situations where you know that an unknown value (NULL) is neither needed nor wanted for a column. How could you have a field that has no value? What would it mean? As I said, they want an error instead. If you don't know a value for this field, they want that you are not allowed to insert/update that row. NULL is the answer to this. It is recording the absence of something. So, I would say that this is an expected behaviour of any database engine. I see which point you are making and you are correct about it but you are missing their requirements. Bye, Benjamin. [...] On Tue 2003-01-14 at 09:32:02 -0800, [EMAIL PROTECTED] wrote: I'm aware that NULL and are not the same thing.. I would like to prevent the column from accepting values automatically ( with out the presence of a DEFAULT). [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: RE: NOT NULL question
On Wednesday 15 January 2003 22:36, Gabe Geisendorfer wrote: Thanks, I check it out.. Any idea if this 'deficiency' is scheduled to change? Yes. http://www.mysql.com/doc/en/TODO_future.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NOT NULL question
Hello. On Tue 2003-01-14 at 09:32:02 -0800, [EMAIL PROTECTED] wrote: I'm aware that NULL and are not the same thing.. I would like to prevent the column from accepting values automatically ( with out the presence of a DEFAULT). [...] The problem is: You have a DEFAULT, you just don't know it. ;-) Do a SHOW CREATE TABLE stuff, and you'll see what I mean. That is a known deficiency, as Eric told you: It's a known behaviour and is described in the MySQL manual: http://www.mysql.com/doc/en/Bugs.html You can change it if you compile MySQL server with -DDONT_USE_DEFAULT_FIELDS option. But in this case you can't use default values at all. http://www.mysql.com/doc/en/configure_options.html describes -DDONT_USE_DEFAULT_FIELDS. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: NOT NULL question
Thanks, I check it out.. Any idea if this 'deficiency' is scheduled to change? Thanks, Gabe -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 9:49 AM To: Gabe Geisendorfer Cc: [EMAIL PROTECTED] Subject: Re: NOT NULL question Hello. On Tue 2003-01-14 at 09:32:02 -0800, [EMAIL PROTECTED] wrote: I'm aware that NULL and are not the same thing.. I would like to prevent the column from accepting values automatically ( with out the presence of a DEFAULT). [...] The problem is: You have a DEFAULT, you just don't know it. ;-) Do a SHOW CREATE TABLE stuff, and you'll see what I mean. That is a known deficiency, as Eric told you: It's a known behaviour and is described in the MySQL manual: http://www.mysql.com/doc/en/Bugs.html You can change it if you compile MySQL server with -DDONT_USE_DEFAULT_FIELDS option. But in this case you can't use default values at all. http://www.mysql.com/doc/en/configure_options.html describes -DDONT_USE_DEFAULT_FIELDS. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
