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=gcdmg-mysql-2@m.gmane.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 wrote=
:
>
> Will anything ever be equal to NULL in a SELECT query?
>
> =A0SELECT *
> =A0FROM sometable
> =A0WHERE somecolumn =3D NULL;
>
> I have a real-life query like this:
>
> =A0SELECT *
> =A0FROM sometable
> =A0WHERE somecolumn =3D NULL OR somecolumn =3D '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
> =A0find a condition where 'somecolumn' =3D NULL. =A0Shouldn't the query p=
arser be
> smart enough to rewrite the above query like this:
>
> =A0SELECT *
> =A0FROM sometable
> =A0WHERE FALSE OR somecolumn =3D 'abc';
>
> And therefor use the index I have on 'somecolumn'? =A0When I manually rew=
rite
> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Is anything ever equal to NULL?

--0016e6db2d8150cc87047bcf0e4e
Content-Type: text/plain; charset=UTF-8

On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso 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.htm l

Should answer some of your questions, Dante.

--0016e6db2d8150cc87047bcf0e4e--

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 IS NULL (has no data) or
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
= 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=gcdmg-mysql-2@m.gmane.org

Re: Is anything ever equal to NULL?

David Giragosian skrev:
> On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso 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.htm l
>
> 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=gcdmg-mysql-2@m.gmane.org

Re: Is anything ever equal to NULL?

--0016e6d785454cd1a0047bd2f37b
Content-Type: text/plain; charset=UTF-8

On Mon, Dec 28, 2009 at 5:41 PM, Carsten Pedersen wrote:

> David Giragosian skrev:
>
>> On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso
>> 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.htm l
>>
>> 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

--0016e6d785454cd1a0047bd2f37b--

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 IS NULL (has no data) or
> 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
> = 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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org

Re: Is anything ever equal to NULL?

Martijn,


thanks for your excellent mail:

Martijn Tonies wrote:
> [[...]]
>=20
> 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 IS NULL (has no data) or
> 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

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg