Not quite sure what is going on here..

I have a test database running on 5.2.6-1 and production is on 5.2.0-8

A select that works on production breaks on test.

The condition is for a field 'shipped' != 'yes'

show fields in orders;
+-------------------+-------------------------+------+-----+---------+----------------+
+-------------------+-------------------------+------+-----+---------+----------------+


Now if this enumerated field is NULL, which it is if it hasn't been
explicitly set, production accepts that is a match for "!='yes'" whereas
test does not.

Is this me, a bug or what?

i.e. the simple test

select * from orders where shipped !='yes' ;

produces a different result on the two machines.
With identical data.

Obviously there are ways around this. Its trivial to fix the code, but I
want to know why its happening.

The Natural Philosopher wrote:
field... but the question still arises as to why..

The Natural Philosopher wrote:
I can't say why one system gives you different answers than the other,
but the production system is behaving correctly. ANY Boolean comparison
where one (or both) of the operands is NULL should return false -- I
believe that is the expected (ANSI?) behavior.

If you accept NULL in a YES/NO type column (i.e., if you want to
implement tri-state logic) then you need to explicitly test for NOT NULL
before doing any Boolean comparisons.

Robert Hairgrove wrote:
Actually, I just re-read your OP and saw that this column can also have
other values 'part' ... you should definitely keep NULLs out of this
column, IMHO -- especially since you have a default value of 'no'.

Robert Hairgrove wrote:
FALSE in a comparison for, or against?

I went in at pure mysql level to delve into this: no other software
involved..

Definitely one version behaves differently from the other.


It gets worse actually. I set explicit defaults to 'no' and it doesn't
actually set the enum value to 'no'. Instead of null, it simply returns
a blank field.


So my enumerated field can take on values NULL and '' as well as the
three enumerated values.

Op 4-3-2010 12:45, The Natural Philosopher schreef:
but that is documented
(http://dev.mysql.com/doc/refman/5.1/en/enum.html)
For example, a column specified as ENUM('one', 'two', 'three') can have
any of the values shown here. The index of each value is also shown.
Value Index
NULL NULL
'' 0
'one' 1
'two' 2
'three' 3

There's also something about default values of an enum-field there, did
you check if
SELECT 0+shipped FROM table
gives same values on test and production?

--
Luuk

Robert Hairgrove wrote:
As a side remark, it would really be nice if MySQL implemented enforced
CHECK constraints ... since it does not, you might want to keep the
admissible values for the "shipped" column in a separate table and have
a foreign key constraint. Better than enum, IMHO...

Luuk wrote:

That is clear: what is not clear is the behaviour of a NULL entry in a
comparison.


ie what is the value of (NULL!='yes') and why has it changed ....

Ok. this gets even stranger..

BOTH systems return the following..

mysql> select (NULL !='yes') as test;
+------+

The question is, is NULL true, or false!!

It gets more confusing, because both data bases return the same thing in
the case of:

select id from orders where (NULL!='yes')

BUT completely different sets when I use any of the enumerated fields viz.

select id from orders where paid !='yes';

I have disallowed nulls in all enumerated fields now, since their
behaviours in this comparison seems 'undefined'

If anyone has any further info, please add it.

I am stumped.

Robert Hairgrove wrote:
Too late for that.

As I remarked, I've got consistent behaviour by disallowing nulls.
Blanks seem to be handled consistently: NULLs were not!

On Thu, 04 Mar 2010 12:53:23 +0000, The Natural Philosopher wrote:
Neither. But the important part is that NULL is not true. Ergo, all
tests that do something if the condition is true will not happen, and
ELSE conditions will be executed. HOWEVER, if you explicitly test for
FALSE, those will fail to any ELSE conditions as well.

NULL is never equal to 'yes'. So you should get all rows.

You will get all rows, except no rows where paid is set to 'yes', AND
no rows where paid is NULL, a subset of the previous results. The
probably seems backwards because NULL is not 'yes', but remember, tests
on NULL that are not NULL checks always fail. (N.B. Not "are false",)
Including the test whether to include a row, and that supercedes the
"!=" operation.

This is an endorsable course of action. If you don't know that you want
NULL values, don't allow them in the column. (I kind of like how the
AS/400 library file system does it instead; columns all default to "NOT
NULL" unless explicitly defined otherwise.)

--
"... I've seen Sun monitors on fire off the side of the multimedia lab.
I've seen NTU lights glitter in the dark near the Mail Gate.
All these things will be lost in time, like the root partition last
week. Time to die...". -- Peter Gutmann in the scary.devil.monastery