Help! behaviour changed in some upgrade on select!

#11

Op 4-3-2010 13:53, The Natural Philosopher schreef:

Quote:

Luuk wrote:
Op 4-3-2010 12:45, The Natural Philosopher schreef:
Robert Hairgrove wrote:
The Natural Philosopher wrote:
The Natural Philosopher wrote:
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;
+-------------------+-------------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default
| Extra |
+-------------------+-------------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI |
NULL | auto_increment |
| employee | int(11) | YES | |
NULL | |
| customer | int(11) | YES | |
NULL | |
| shipping | int(11) | YES | |
NULL | |
| date | date | YES | |
NULL | |
| dispatch_date | date | YES | |
NULL | |
| vat | int(11) | YES | |
NULL | |
| discount | int(11) | YES | |
NULL | |
| paid | enum('yes','no') | YES | |
no | |
| valid | enum('yes','no') | YES | |
no | |
| invoiced | enum('yes','no') | YES | |
no | |
| shipped | enum('yes','no','part') | YES | |
no | |
| customer_order | char(32) | YES | |
NULL | |
| details | text | YES | |
NULL | |
| payment_reference | char(32) | YES | |
NULL | |
| shipping_method | char(64) | YES | |
NULL | |
| tracking_number | char(32) | YES | |
NULL | |
+-------------------+-------------------------+------+-----+---------+----------------+

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.

Further to this, I have fixed the problem by disallowing nulls...in
the field... but the question still arises as to why..
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.
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.

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.
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.

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?

Thanks Luuk, I found that as well.

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;
+------+
| test |
+------+
| NULL |
+------+

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.

ok, this is without an enum-field...

mysql> desc testNULL;
+-------+---------+------+-----+---------+-------+

Quote:

Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
t | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> select * from testNULL;
+------+

Quote:

t |
+------+
1 |
3 |
NULL |
+------+

3 rows in set (0.00 sec)

mysql> select * from testNULL where t!=4;
+------+

Quote:

t |
+------+
1 |
3 |
+------+

2 rows in set (0.00 sec)

In the last query the NULL row is not shown. Is this the same 'problem'
as you are having?

Disabling all NULLs on enumarated fields seems indeed the best option...

--
Luuk

#12

Luuk wrote:

Quote:

Op 4-3-2010 13:53, The Natural Philosopher schreef:
Luuk wrote:
Op 4-3-2010 12:45, The Natural Philosopher schreef:
Robert Hairgrove wrote:
The Natural Philosopher wrote:
The Natural Philosopher wrote:
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;
+-------------------+-------------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default
| Extra |
+-------------------+-------------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI |
NULL | auto_increment |
| employee | int(11) | YES | |
NULL | |
| customer | int(11) | YES | |
NULL | |
| shipping | int(11) | YES | |
NULL | |
| date | date | YES | |
NULL | |
| dispatch_date | date | YES | |
NULL | |
| vat | int(11) | YES | |
NULL | |
| discount | int(11) | YES | |
NULL | |
| paid | enum('yes','no') | YES | |
no | |
| valid | enum('yes','no') | YES | |
no | |
| invoiced | enum('yes','no') | YES | |
no | |
| shipped | enum('yes','no','part') | YES | |
no | |
| customer_order | char(32) | YES | |
NULL | |
| details | text | YES | |
NULL | |
| payment_reference | char(32) | YES | |
NULL | |
| shipping_method | char(64) | YES | |
NULL | |
| tracking_number | char(32) | YES | |
NULL | |
+-------------------+-------------------------+------+-----+---------+----------------+

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.

Further to this, I have fixed the problem by disallowing nulls...in
the field... but the question still arises as to why..
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.
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.

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.
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.

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?

Thanks Luuk, I found that as well.

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;
+------+
| test |
+------+
| NULL |
+------+

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.

ok, this is without an enum-field...

mysql> desc testNULL;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| t | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from testNULL;
+------+
| t |
+------+
| 1 |
| 3 |
| NULL |
+------+
3 rows in set (0.00 sec)

mysql> select * from testNULL where t!=4;
+------+
| t |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.00 sec)

In the last query the NULL row is not shown. Is this the same 'problem'
as you are having?

It seems to be, yes.

Quote:

Disabling all NULLs on enumarated fields seems indeed the best option...

Indeed.

The problem is really twofold.

The practical problem is solved by disabling nulls.

The theoretical and development problem is what the behaviour *ought* to
be, and why it changed between versions.

#13

Peter H. Coffin wrote:

Quote:

On Thu, 04 Mar 2010 12:53:23 +0000, The Natural Philosopher wrote:
Ok. this gets even stranger..

BOTH systems return the following..

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

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

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.

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

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

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

I get none IIRC.

Quote:

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

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

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.

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

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.)

However it begs the question of why the two versions, both 5.2, behave
differently.

>

#14

In article <slrnhovdgu.902.hellsop (AT) abyss (DOT) ninehells.com>, "Peter H. Coffin" <hellsop (AT) ninehells (DOT) com> wrote:

Quote:

On Thu, 04 Mar 2010 12:53:23 +0000, The Natural Philosopher wrote:

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

NULL is never equal to 'yes'.

Correct.

Quote:

So you should get all rows.

Incorrect. You should get no rows, as the statement is equivalent to
SELECT id FROM orders WHERE 0;

#15

In article <hmoedd$5ph$3 (AT) news (DOT) albasani.net>, The Natural Philosopher <tnp (AT) invalid (DOT) invalid> wrote:

Quote:

However it begs the question of why the two versions, both 5.2, behave
differently.

<pet peeve>
It doesn't *beg* the question, it *raises* the question.
http://en.wikipedia.org/wiki/Begging_the_question
</pet peeve>

#16

On Thu, 04 Mar 2010 14:00:13 +0000, The Natural Philosopher wrote:

Quote:

Peter H. Coffin wrote:

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

I get none IIRC.

Yeah, that. Pre-coffee brain.

--
Either way, it'll remind the clued that there's only one letter
difference between 'turkey' and 'turnkey'.
-- Mike Andrews

#17

On 4 mar, 12:21, Robert Hairgrove <rhairgr... (AT) bigfoot (DOT) com> wrote:

Quote:

The Natural Philosopher wrote:
The Natural Philosopher wrote:
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;
+-------------------+-------------------------+------+-----+---------+----------------+

| Field * * * * * * | Type * * * * * * * ** *| Null | Key | Default |
Extra * * * * *|
+-------------------+-------------------------+------+-----+---------+----------------+

| id * * * * * * * *| int(11) * * * * * * * * | NO * | PRI | NULL * *|
auto_increment |
| employee * * * * *| int(11) * * * * * * * * | YES *| * * | NULL * *|
* * * * * * * |
| customer * * * * *| int(11) * * * * * * * * | YES *| * * | NULL * *|
* * * * * * * |
| shipping * * * * *| int(11) * * * * * * * * | YES *| * * | NULL * *|
* * * * * * * |
| date * * * * * * *| date * * * * * * * * * *| YES *| * * | NULL * *|
* * * * * * * |
| dispatch_date * * | date * * * * * * * * * *| YES *| * * | NULL * *|
* * * * * * * |
| vat * * * * * * * | int(11) * * * * * * * * | YES *| * * | NULL * *|
* * * * * * * |
| discount * * * * *| int(11) * * * * * * * * | YES *| * * | NULL * *|
* * * * * * * |
| paid * * * * * * *| enum('yes','no') * * * *| YES *| * * | no * * *|
* * * * * * * |
| valid * * * * * * | enum('yes','no') * * * *| YES *| * * | no * * *|
* * * * * * * |
| invoiced * * * * *| enum('yes','no') * * * *| YES *| * * | no * * *|
* * * * * * * |
| shipped * * * * * | enum('yes','no','part') | YES *| ** | no * * *|
* * * * * * * |
| customer_order * *| char(32) * * * * * * * *| YES *| * * | NULL * *|
* * * * * * * |
| details * * * * * | text * * * * * * * * * *| YES *| * * | NULL * *|
* * * * * * * |
| payment_reference | char(32) * * * * * * * *| YES *| * * | NULL * *|
* * * * * * * |
| shipping_method * | char(64) * * * * * * * *| YES *| * * | NULL * *|
* * * * * * * |
| tracking_number * | char(32) * * * * * * * *| YES *| * * | NULL * *|
* * * * * * * |
+-------------------+-------------------------+------+-----+---------+----------------+

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.

Further to this, I have fixed the problem by disallowing nulls...in the
field... but the question still *arises as to why..

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

The rules are:

NULL and TRUE -> NULL
NULL and FALSE -> FALSE
NULL or TRUE -> TRUE
NULL or FALSE -> NULL
NOT NULL -> NULL

In a where clause the predicate must evaluate to TRUE, in a constraint
it must not evaluate to FALSE. Confusing, indeed it is :-)

/Lennart

#18

On 4 mar, 13:53, The Natural Philosopher <t... (AT) invalid (DOT) invalid> wrote:
[...]

Quote:

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

Neither, null is null

Quote:

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

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

<> is the standard "not equals" operator. Many dbms accept != as well,
but it is better to use <> since it is more likely to work

[...]

/Lennart

#19

On 4 mar, 14:31, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
[...]

Quote:

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

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

NULL is never equal to anything, not even null, but this predicate
should evaluate to NULL, i.e. no rows should be returned

[...]

/Lennart

#20

Doug Miller wrote:

Quote:

In article <hmoedd$5ph$3 (AT) news (DOT) albasani.net>, The Natural Philosopher <tnp (AT) invalid (DOT) invalid> wrote:

However it begs the question of why the two versions, both 5.2, behave
differently.

pet peeve
It doesn't *beg* the question, it *raises* the question.
http://en.wikipedia.org/wiki/Begging_the_question
/pet peeve

Ok ;-)