Re: Is anything ever equal to NULL?
Martijn, thanks for your excellent mail: Martijn Tonies wrote: [[...]] A column can have two states: null or not null. It either has data (a value, depending on the datatype), or no data (null), which is where column IS NULL (has no data) or column IS NOT NULL (has data) comes into play. To make it more explicit: The term null value is no proper expression in relational theory. Values can be compared to each other, and they are equal or not, some of them even are ordered. Null is no value but a state, signalling value is unknown. And of two unknowns you can't even tell whether they are equal or not, so the result of comparing anything (be it a value or unknown) to unknown is again unknown. That's why logic in SQL is three-valued: true, false, and unknown. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is anything ever equal to NULL?
Will anything ever be equal to NULL in a SELECT query? SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? -- Dante -- D. Dante Lorenso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
No, nothing will ever equal null. In strict relational theory, which I don't know well enough to begin expounding on here, null does not even equal another null. That's why SQL provides IS NULL and IS NOT NULL as explicit cases. - michael dykman On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? -- Dante -- D. Dante Lorenso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante.
Re: Is anything ever equal to NULL?
Hi, Will anything ever be equal to NULL in a SELECT query? No, never. Null also means unknown, if you design your tables well enough, there should be no NULLs -stored- (different from a resultset, where there can be nulls, for example in LEFT JOINs), because it's no use to store what you don't know. The only case when you want to store a null is when you do want to -know- you don't know a value. A column can have two states: null or not null. It either has data (a value, depending on the datatype), or no data (null), which is where column IS NULL (has no data) or column IS NOT NULL (has data) comes into play. Null is not the same as empty. An empty string, for example, is not equal to null (which is unknown), you cannot compare anything to what you don't know, which is why your comparison fails. NULL = NULL fails, so does NULL NULL in the strict sense. SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: You mean the optimizer, perhaps it should, perhaps it shouldn't. Yet, your query is not really the smartest, as you should avoid writing column = NULL, as this doesn't make sense. SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? Quite a bit ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
David Giragosian skrev: On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? ... What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante. Oddly enough, that page fails to mention the = operator for which NULL does indeed equal NULL. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
On Mon, Dec 28, 2009 at 5:41 PM, Carsten Pedersen cars...@bitbybit.dkwrote: David Giragosian skrev: On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? ... What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante. Oddly enough, that page fails to mention the = operator for which NULL does indeed equal NULL. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to / Carsten Good pick-up, Carsten. And that's definitely a new concept for me. David -- There is more hunger for love and appreciation in this world than for bread.- Mother Teresa
Re: Is anything ever equal to NULL?
Well, if nothing can ever equal null, then why isn't MySQL query parser smart enough to reduce my queries to something more sensible? If I'm saying this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; Why isn't it able to reduce the query to something more like this: SELECT * FROM sometable WHERE somecolumn = 'abc'; Since it already should know that somecolumn = NULL will always evaluate to FALSE (or is it NULL? ... either way, it's not TRUE)? If I run the first query above, the query takes about 15 seconds to run against 40 million records, but if I run the second query, it takes about .050 seconds. The test for NULL seems to cause the query to skip use of an index because I doubt NULL values are indexed. Am I expecting too much of the parser? -- Dante Martijn Tonies wrote: Hi, Will anything ever be equal to NULL in a SELECT query? No, never. Null also means unknown, if you design your tables well enough, there should be no NULLs -stored- (different from a resultset, where there can be nulls, for example in LEFT JOINs), because it's no use to store what you don't know. The only case when you want to store a null is when you do want to -know- you don't know a value. A column can have two states: null or not null. It either has data (a value, depending on the datatype), or no data (null), which is where column IS NULL (has no data) or column IS NOT NULL (has data) comes into play. Null is not the same as empty. An empty string, for example, is not equal to null (which is unknown), you cannot compare anything to what you don't know, which is why your comparison fails. NULL = NULL fails, so does NULL NULL in the strict sense. SELECT * FROM sometable WHERE somecolumn = NULL; I have a real-life query like this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; The 'sometable' contains about 40 million records and in this query, it appears that the where clause is doing a sequential scan of the table to find a condition where 'somecolumn' = NULL. Shouldn't the query parser be smart enough to rewrite the above query like this: You mean the optimizer, perhaps it should, perhaps it shouldn't. Yet, your query is not really the smartest, as you should avoid writing column = NULL, as this doesn't make sense. SELECT * FROM sometable WHERE FALSE OR somecolumn = 'abc'; And therefor use the index I have on 'somecolumn'? When I manually rewrite the query, I get the performance I expect but when I leave it as it was, it's 100 times slower. What's so special about NULL? Quite a bit ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- -- D. Dante Lorenso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
Well, if nothing can ever equal null, then why isn't MySQL query parser smart enough to reduce my queries to something more sensible? If I'm saying this: SELECT * FROM sometable WHERE somecolumn = NULL OR somecolumn = 'abc'; Why isn't it able to reduce the query to something more like this: SELECT * FROM sometable WHERE somecolumn = 'abc'; Since it already should know that somecolumn = NULL will always evaluate to FALSE (or is it NULL? ... either way, it's not TRUE)? If I run the first query above, the query takes about 15 seconds to run against 40 million records, but if I run the second query, it takes about .050 seconds. The test for NULL seems to cause the query to skip use of an index because I doubt NULL values are indexed. Am I expecting too much of the parser? Of the optimizer... I could as easily write: were myintegercolumn = 'test' which would also result into False (haven't tried, depending on how it evaluates, this could result in a datatype error ;-) ) Would the DBSM code have to check for all of these silly constructs? Talking about bloat. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
