[oclug]MySQL question

[Ian Wormsbecker]

On January 19, 2003 07:59 pm, Rod Giffin wrote:
> On Sun, 2003-01-19 at 17:29, B McKee wrote:
> > Thanks guys for the good info - When I hit a head scratcher like that I
>
> You're on the right track Brian.  But I've got something else for you to
> consider - especially if you're using this in a program.  I don't want
> this to get too complicated so I won't go into the details of what can
> occur unless you want me to, or if you run into trouble with it.
>
> In the example you gave, you had a sponsor name with a value of NULL.
> That does not necessarily mean what you expected - and could result in
> some logic errors in the translation between SQL and your program.  You
> are going to have to trap the value coming from the sponsor name field,
> and test it for the content of "NULL" (record 0 was selected), or a
> value of NULL (an error occurred... nothing was returned.)

Is this neccessarily the case? I am not sure how mysql works, but I beleive 
that you can't test an element of a tuple for NULL directly. If you compare a 
tuple element that is NULL with NULL, the comparison will be false always. 
Which is why you can usually use operators such as IS NULL or NOT NULL etc in 
your SQL statements.

For instance, the query SELECT * FROM table WHERE a_column = NULL; will always 
return 0 rows regardless of how many tuples have NULL for a_column.

>
> See, in a program (depending on the language), you can initialize a
> variable to null.  The variable exists, but doesn't contain anything.
> But in SQL, that is represented by "", by the character '', by the
> integer 0, or the float 0.00. (depending on what the field type is).  In
> SQL, null means NOTHING like that exists.  If a result set is null, it
> means that no records that match the query were returned.  If a field
> returns a null, it does not mean the name is empty - it means it doesn't
> exist.  The difference is subtle, but important to grasp.
>

I am confused as to what you are saying here. In SQL, a NULL integer is just 
NULL. A NULL float is NULL. Also, an empty string "" is not NULL, it is an 
empty string.

It seems as though you are trying to explain how you can determine if a value 
is NULL in a tuple from within a program, for instance a perl script. I am 
not familiar with perl so cannot comment on that, but I should hope that NULL 
is not 0.00 as 0.00 is a valid float value. 


Some other examples of problems that NULL can cause is when you have a column 
that contains an integer value and can be NULL. You want to run an SQL query 
to find the average of all the columns. So you run something such as SELECT 
sum(a_column) / count(a_column); How do you add NULL to your total? What kind 
of behavior do you get? Does the NULL value count towards the total number of 
elements you have added? These are all difficult things that you have to deal 
with when using NULL.

In the case the OP had, I would suggest using an emptry string to signify that 
the user did not enter data for that field rather than NULL.

> If I just confused you, for a more concise explanation of how it works
> see
>
> http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Pro
>blems_with_NULL

There was also a discussion on one of the postgresql lists. Here is the link:
http://archives.postgresql.org/pgsql-sql/2003-01/msg00278.php


Ian