Bug De-Morgan-Rules

Hello,

I guess I found a bug in MySQL 3.23.36. I have not had the
opportunity to test it on a more recent version, but I did not find a
clue in the mysql ChangeLog that it might have been fixed. I do hope,
it is still existent.

Preface:
The boolean expression "not (A and B)" is equivalent to "(not A) or
(not B)". This is said by the De-Morgan-Rules for the boolean
algebra.
The commutative law is "(A and B) = (B and A)" and "(A or B) = (B or
A)".

Bug-Description:
Having a colum "e" type "date" that is allowed to become NULL and
where an index is calculated over, the following statements should be
equivalent.
a) NOT ((e IS NOT NULL) AND (e>"2002-07-30"))
b) NOT ((e>"2002-07-30") AND (e IS NOT NULL))
c) ((e IS NULL) OR (e<="2002-07-30"))
d) ((e<="2002-07-30") OR (e IS NULL))
Where a) is the original column, b) is the same but after applying
the commutative law, c) is a) after "De-Morgan", d) is b) after "De-
Morgan" or a commutated c).

Searching with those expressions over a database should return the
same result-set.
This is true for a), c) and d). They all return the same valid result-
set.
But b) returns a different one missing the lines where e is NULL.

Example:

--> a)
mysql> select count(e) from f where
NOT ((e IS NOT NULL) AND (e>"2002-07-30"));
+----------+
| count(e) |
+----------+
| 1776 |
+----------+
1 row in set (0.06 sec)

--> b)
mysql> select count(*) from f where
NOT ((e>"2002-07-30") AND (e IS NOT NULL));
+----------+
| count(e) |
+----------+
| 1292 |
+----------+
1 row in set (0.07 sec)

--> Difference between a) and b):
mysql> select count(e) from f where
(e IS NULL);
+----------+
| count(e) |
+----------+
| 484 |
+----------+
1 row in set (0.06 sec)

-->
1292 + 484 = 1776

Note:
I only tried this with a date-column with an index calculated over. I
suppose the bug should occur on other column-types too.

How-to-repeat:
1) CREATE TABLE f (primkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, e
DATE);
2) CREATE INDEX e ON f (e);
3) INSERT INTO f (e) VALUES ("2002-06-10"),("2002-07-10"),("2002-08-
10"),("2002-09-10"),("2002-10-10"),(NULL);

f is now:
1 2002-06-10
2 2002-07-10
3 2002-08-10
4 2002-09-10
5 2002-10-10
6 NULL

4) Do:
mysql> select e from f where
-> NOT ((e IS NOT NULL) AND (e>"2002-07-30"));
+------------+
| e |
+------------+
| NULL |
| 2002-06-10 |
| 2002-07-10 |
+------------+
3 rows in set (0.00 sec)

mysql> select e from f where
-> NOT ((e>"2002-07-30") AND (e IS NOT NULL));
+------------+
| e |
+------------+
| 2002-06-10 |
| 2002-07-10 |
+------------+
2 rows in set (0.00 sec)

5) See the difference...

Bye

Tobias

-------------------------------
Tobias Eggendorfer
E-Mail: nospam@acme.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12882@lists.mysql.com
To unsubscribe, e-mail

Re: Bug De-Morgan-Rules

Hello,

Tobias Eggendorfer wrote:

> I guess I found a bug in MySQL 3.23.36. I have not had the
> opportunity to test it on a more recent version, but I did not find a
> clue in the mysql ChangeLog that it might have been fixed. I do hope,
> it is still existent.
>
> Preface:
> The boolean expression "not (A and B)" is equivalent to "(not A) or
> (not B)". This is said by the De-Morgan-Rules for the boolean
> algebra.
> The commutative law is "(A and B) = (B and A)" and "(A or B) = (B or
> A)".

Tobias, your further analysys is perfect but unfortunately you falled
into very common trap. SQL does not comply to Boolean logic. SQL uses
Ternary or Three-Way Logic -> true, false, NULL

Because of that result of AND, OR, XOR, NOT can be 1, 0, NULL and
results table is a bit more complicated.

SQL99 standard requires that:

1. true AND null -> null
2. false AND null -> false
3. null AND true -> null
4. null AND false -> false
5. null AND null -> null
6. true OR null -> true
7. false OR null -> null
8. null OR true -> true
9. null OR false -> null
10.null OR null -> null

What you found is that MySQL does not fully conform to it.

In MySQL:
4. null AND false -> null

> a) NOT ((e IS NOT NULL) AND (e>"2002-07-30"))
> b) NOT ((e>"2002-07-30") AND (e IS NOT NULL))
> c) ((e IS NULL) OR (e<="2002-07-30"))
> d) ((e<="2002-07-30") OR (e IS NULL))

(e>"2002-07-30") -> NULL for row where e IS NULL

So for such row you have:

a) NOT (false AND NULL)) -> 1
b) NOT (NULL AND false) -> 0
c) true OR NULL -> true
d) NULL OR true -> true

But you must note that:

b) NOT (NULL AND false) -> should be NULL according to SQL99 because
NOT NULL -> NULL;

So final result will be the same because

select ... where NULL;

will never match any row. So at least for your query MySQL conforms to
ANSI specifications.

By the way it is often forgotten fact that whole WHERE caluse can become
NULL i.e. undefined so if
select .. where ; returns 50% of rows it does NOT mean

it is wrong guess that:
select .. where NOT ; will return the rest 50% of rows


> Bye
>
> Tobias


Best regards


--
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12886@lists.mysql.com
To unsubscribe, e-mail

(Fwd) Re: Bug De-Morgan-Rules

Hi,

for not having a
How to Repeat:
line, it was rejected. New attempt.

Bye

Tobias

------- Forwarded message follows -------
Subject: Re: Bug De-Morgan-Rules

Hello,

On 1 Nov 2002 at 13:27, Alexander Keremidarski wrote:
> Tobias, your further analysys is perfect but unfortunately you
> falled into very common trap. SQL does not comply to Boolean logic.
> SQL uses Ternary or Three-Way Logic -> true, false, NULL
But this logic is also compliand to
a) the commutative law
b) the De Morgan Rules.

> 1. true AND null -> null
> 2. false AND null -> false
> 3. null AND true -> null
> 4. null AND false -> false
> 5. null AND null -> null
> 6. true OR null -> true
> 7. false OR null -> null
> 8. null OR true -> true
> 9. null OR false -> null
> 10.null OR null -> null

a) is proven by:
1. is the same as 3., 2. as 4., 5. is obvious => commutative for
"AND". 6. is the same as 8., 7. as 9., 10. is obvious => commutative
for "OR"

b) might easily be proven by a chart
Writing 0 = FALSE, 1 = TRUE, N = NULL, nA = not A, nB = not B,
AB = A AND B, AoB = A or B, n(AB) = not (A and B) and assuming that
NOT NULL = NULL

A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
N N N N N N N N N N
0 N 1 N 0 1 1 N N N
1 N 0 N N N N 1 0 0
N 0 N 1 0 1 1 N N N
N 1 N 0 N N N 1 0 0
0 0 1 1 0 1 1 0 1 1
0 1 1 0 0 1 1 1 0 0
1 0 0 1 0 1 1 1 0 0
1 1 0 0 1 0 0 1 0 0
^ ^ ^ ^
+------+ +----+
Comparing "not (A and B)" to "(not A or not B)" shows, they are
equal. We find the same for "not (A or B)" and "(not A and not B)".

This results in ternary logic being compliand at least to the
commutative law by definition 1. to 10. and we proved that it is also
compliand to the De Morgan Rules.

> What you found is that MySQL does not fully conform to it.
> In MySQL:
> 4. null AND false -> null
This has enormous implications:
A) MySQLs ternary logic (MTL) does not comply to the commutative law.
B) MTL does not comply to the De Morgan Rules. C) MTL does not comply
to what is known as Shannons Inversiontheorem (sorry, I translated
that word by word ;-) ). D) MTL does not comply to the associative
law. ...

Therefore, MTL turns unusable, as its results become almost
unpredictable and turn out not to be optimizable by classical means
of
mathematics.

> > a) NOT ((e IS NOT NULL) AND (e>"2002-07-30"))
> > b) NOT ((e>"2002-07-30") AND (e IS NOT NULL))
> > c) ((e IS NULL) OR (e<="2002-07-30"))
> > d) ((e<="2002-07-30") OR (e IS NULL))
> (e>"2002-07-30") -> NULL for row where e IS NULL
Okay.

> So for such row you have:
> a) NOT (false AND NULL)) -> 1
a) NOT (false AND NULL) -> NOT false -> true (according to SQL99)

> b) NOT (NULL AND false) -> 0
b) NOT (NULL AND false) -> NOT (false / NULL) -> true / NULL
(according to SQL99 / MLT)

> But you must note that:
> b) NOT (NULL AND false) -> should be NULL according to SQL99
> because NOT NULL -> NULL;
The result is not in accordance to SQL99 but to MLT. SQL99 prevents
this result by rule 4 (NULL AND false -> FALSE).

You missed the step between: NULL AND false results in false
according to SQL99 but in NULL according to MLT.

If you the continue using MLT or SQL99 while using NOT, you'll result
in an appropiate result among both definitions. NOT NULL = NULL
according to both SQL99 / MLT. NOT false = true according to both
SQL99 / MLT.

So saying NOT NULL should be NULL is *not* the prove for being
allowed to break rule 4.

> So final result will be the same because
No, they are not. Because there is a difference between false and
NULL
;-)

> will never match any row. So at least for your query MySQL conforms
> to ANSI specifications.
As shown above, it does not comply to ANSI. Not even almost. ;-)

Bye

Tobias

------- End of forwarded message -------
-------------------------------
Tobias Eggendorfer
E-Mail: nospam@acme.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12887@lists.mysql.com
To unsubscribe, e-mail

Re: Bug De-Morgan-Rules

Hello,
Tobias Eggendorfer wrote:
> Hello,
> On 1 Nov 2002 at 13:27, Alexander Keremidarski wrote:
>
>>Tobias, your further analysys is perfect but unfortunately you falled
>>into very common trap. SQL does not comply to Boolean logic. SQL uses
>>Ternary or Three-Way Logic -> true, false, NULL

I can see I was wrong. Your theorertical background is much better than common
in SQL world. Sorry for this.

>
> But this logic is also compliand to
> a) the commutative law
> b) the De Morgan Rules.

What do you mean?
I don't argue - I am asking you. Where did you find that rules - in what theory?
Does Relational Algebra requires this?

>>1. true AND null -> null
>>2. false AND null -> false
>>3. null AND true -> null
>>4. null AND false -> false
>>5. null AND null -> null
>>6. true OR null -> true
>>7. false OR null -> null
>>8. null OR true -> true
>>9. null OR false -> null
>>10.null OR null -> null
>
>
> a) is proven by:
> 1. is the same as 3., 2. as 4., 5. is obvious => commutative for
> "AND".
> 6. is the same as 8., 7. as 9., 10. is obvious => commutative for
> "OR"
>
> b) might easily be proven by a chart
> Writing 0 = FALSE, 1 = TRUE, N = NULL, nA = not A, nB = not B,
> AB = A AND B, AoB = A or B, n(AB) = not (A and B) and assuming that
> NOT NULL = NULL
>
> A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
> N N N N N N N N N N
> 0 N 1 N 0 1 1 N N N
> 1 N 0 N N N N 1 0 0
> N 0 N 1 0 1 1 N N N
> N 1 N 0 N N N 1 0 0
> 0 0 1 1 0 1 1 0 1 1
> 0 1 1 0 0 1 1 1 0 0
> 1 0 0 1 0 1 1 1 0 0
> 1 1 0 0 1 0 0 1 0 0
> ^ ^ ^ ^
> +------+ +----+
> Comparing "not (A and B)" to "(not A or not B)" shows, they are
> equal. We find the same for "not (A or B)" and "(not A and not B)".


Being addicted to SQL :) I prepared test case based on your table:

create table bool(a int, b int);
insert into bool values(null, null), (0, null), (1, null), (null, 0), (null, 1),
(0, 0), (0, 1), (1, 0), (1, 1);


mysql> select ifnull(A, 'N') as A, ifnull(B, 'N') as B, ifnull(not A, 'N') as
nA, ifnull(not B, 'N') as nB, ifnull(not (A and B), 'N') as `n(AB)`, ifnull((not
A or not B), 'N') as nAonB, ifnull(A or B, 'N') as AoB, ifnull(not(A or B), 'N')
as `n(AoB)`, ifnull(not A and not B, 'N') as nAnB from bool;
+---+---+----+----+-------+-------+-----+--------+------+
| A | B | nA | nB | n(AB) | nAonB | AoB | n(AoB) | nAnB |
+---+---+----+----+-------+-------+-----+--------+------+
| N | N | N | N | N | N | N | N | N |
| 0 | N | 1 | N | 1 | 1 | N | N | N |
| 1 | N | 0 | N | N | N | 1 | 0 | 0 |
| N | 0 | N | 1 | N | 1 | N | N | N |
| N | 1 | N | 0 | N | N | 1 | 0 | N |
| 0 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 1 |
| 0 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 0 |
| 1 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 |
| 1 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
+---+---+----+----+-------+-------+-----+--------+------+

Difference from your table comes into columns:

mysql> select ifnull(not (A and B), 'N') as `n(AB)`, ifnull((not A or not B),
'N') as nAonB from bool;
+-------+-------+
| n(AB) | nAonB |
+-------+-------+
| N | N |
| 1 | 1 |
| N | N |
| N | 1 | >>> Here.
| N | N |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 0 | 0 |
+-------+-------+

mysql> select ifnull(not(A or B), 'N') as `n(AoB)`, ifnull(not A and not B, 'N')
as nAnB from bool;
+--------+------+
| n(AoB) | nAnB |
+--------+------+
| N | N |
| N | N |
| 0 | 0 |
| N | N |
| 0 | N | >>> Here.
| 1 | 1 |
| 0 | 0 |
| 0 | 0 |
| 0 | 0 |
+--------+------+

> This results in ternary logic being compliand at least to the
> commutative law by definition 1. to 10. and we proved that it is also
> compliand to the De Morgan Rules.

At this point I have no other choice than to resign and agree with you.

>>What you found is that MySQL does not fully conform to it.
>>In MySQL:
>>4. null AND false -> null
>
> This has enormous implications:
> A) MySQLs ternary logic (MTL) does not comply to the commutative law.
> B) MTL does not comply to the De Morgan Rules.
> C) MTL does not comply to what is known as Shannons Inversiontheorem
> (sorry, I translated that word by word ;-) ). I'd say: Îáðàòíà òåîðåìà íà Øàíúí :)
> D) MTL does not comply to the associative law.
> ...

I will leave this without comment.

>
> Therefore, MTL turns unusable, as its results become almost
> unpredictable and turn out not to be optimizable by classical means
> of mathematics.

I will slightly disagree with you as MySQL is used as is by millions of people.

Why do you say results are unpredictable? Until MySQL evaluates expression
always in same way result is fully predictable.


>>b) NOT (NULL AND false) -> 0
>
> b) NOT (NULL AND false) -> NOT (false / NULL) -> true / NULL
> (according to SQL99 / MLT)
>
>
>>But you must note that:
>>b) NOT (NULL AND false) -> should be NULL according to SQL99 because
>>NOT NULL -> NULL;

Sorry my fault - typo.

mysql> select (NULL AND (0 = 1)); -> NULL
^false^

> The result is not in accordance to SQL99 but to MLT. SQL99 prevents
> this result by rule 4 (NULL AND false -> FALSE).
>
> You missed the step between: NULL AND false results in false
> according to SQL99 but in NULL according to MLT.

Yes. See above.

> If you the continue using MLT or SQL99 while using NOT, you'll result
> in an appropiate result among both definitions.
> NOT NULL = NULL according to both SQL99 / MLT.
> NOT false = true according to both SQL99 / MLT.
>
> So saying NOT NULL should be NULL is *not* the prove for being
> allowed to break rule 4.

I didn't meant that it is 'allowed' to break rule 4.

I tend to agree that Manual doesn't explain that case as it says (in chapter
6.3.1.3 Logical Operators)
....

AND
&&
Logical AND. For non-NULL operands, evaluates to 1 if both operands are non-zero
and to 0 otherwise. Produces NULL if either operand is NULL

Which is ovbiously not the case with (true AND NULL) :(


>>So final result will be the same because
>
> No, they are not. Because there is a difference between false and
> NULL ;-)

Logically yes you are right - result set is still correct as it contains same
rows :)

Ok. I will discuss this case within our development team and will vote for
'fixing' it according to SQL99. You convinced me.

>
> Bye
>
> Tobias

Best regards

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12888@lists.mysql.com
To unsubscribe, e-mail

Re: Bug De-Morgan-Rules

Hi,

On 1 Nov 2002 at 19:23, Alexander Keremidarski wrote:
> I can see I was wrong. Your theorertical background is much better
> than common in SQL world. Sorry for this.
No problem ;-)

> > But this logic is also compliand to
> > a) the commutative law
> > b) the De Morgan Rules.
> What do you mean?
Well, I don't know where to start ;-) I guess, I'll now start telling
you things, you already know.

> I don't argue - I am asking you. Where did you find that rules - in
> what theory? Does Relational Algebra requires this?
Those theories are "classic" mathematics. Most algebras rely on them.

Commutative Law:
For "numeric" operations:
i) a + b = b + a
ii) a * b = b * a
i.e. it does not matter, in which order you process an addition /
multiplication as long as there is no operation with higher priority.

Associative Law:
For "numeric" operations:
i) a + b + c = a + (b+c) = (a+b)+c
ii) a * b * c = a * (b*c) = (a*b)*c
i.e. it does not matter where you place your brackets or where you
start in a longer statement, as long as all operations have the same
priority

Distributive Law:
For "numeric" operations:
i) a * (b + c) = a*b + a*c

Those rules are essential to create an algebra.

Ternary algebra (as any other algebra) must support some kind of a
commutative law, associative law and distributive law.

They are defined:
Commutative law:
a and b = b and a
a or b = b or a
(somewhat logic: you would be surprised, if true or false would
result in something different then false or true does.)

Associative law:
a and (b and c) = (a and b) and c
a or (b or c) = (a or b) or c
(It would be strange, if
true and (false and true) = true and false = false
was different to
(true and false) and true = false and true = false
)

Distributive law:
a and (b or c) = (a and b) or (a and c)
a or (b and c) = (a or c) and (b or c)
There is no direct clue, why this should be. But if you go back to
the numeric rules above you'll see the similarity.

By defining the basic operations as SQL99 does:
> >>1. true AND null -> null
> >>2. false AND null -> false
> >>3. null AND true -> null
> >>4. null AND false -> false
> >>5. null AND null -> null
> >>6. true OR null -> true
> >>7. false OR null -> null
> >>8. null OR true -> true
> >>9. null OR false -> null
> >>10.null OR null -> null
you create a stable base for the laws shown above.

If you would, as MySQL does, turn rule 4 to:
null and false -> null
but keep rule 1:
false and null -> false
you "kill" the commutative law. Because according to it,
null and false = false and null
is always right.
But in MySQL's defintion it is not.

For boolean logic, further rules exist. One of them was derived from
the algebra of sets and found there by De Morgan and says:
not (A and B) = (not A) or (not B)
not (A or B) = (not A) and (not B)

The boolean algebra is kind of an algebra of sets. That's why De
Morgan's Rules apply to it. But ternary algebra is also kind of an
algebra of sets.
In boolean algebra, you might say, the set is {false,true}, in
ternary algebra {false,true,NULL}.

It is no prove in the sense of a mathematical prove, but it is kind
of a sign: If ternary algebra is derived from algebra of sets and
ternary algebra is commutative, associative, knows about negation
etc., De Morgan's Rules should apply to it.

That's what I tried to prove using the chart below. (Using SQL99
logic)
> > b) might easily be proven by a chart
> > A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
> > N N N N N N N N N N
> > 0 N 1 N 0 1 1 N N N
> > 1 N 0 N N N N 1 0 0
> > N 0 N 1 0 1 1 N N N
> > N 1 N 0 N N N 1 0 0
> > 0 0 1 1 0 1 1 0 1 1
> > 0 1 1 0 0 1 1 1 0 0
> > 1 0 0 1 0 1 1 1 0 0
> > 1 1 0 0 1 0 0 1 0 0
> > ^ ^ ^ ^
> > +------+ +----+
> > Comparing "not (A and B)" to "(not A or not B)" shows, they are
> > equal. We find the same for "not (A or B)" and "(not A and not B)".

> Being addicted to SQL :) I prepared test case based on your table:
I bet, you did it on MySQL?

[...]

For easier comparison I included line-numbers into what I quoted from
your mail.

> +---+---+----+----+-------+-------+-----+--------+------+
> | A | B | nA | nB | n(AB) | nAonB | AoB | n(AoB) | nAnB |
> +---+---+----+----+-------+-------+-----+--------+------+
> 1 | N | N | N | N | N | N | N | N | N |
> 2 | 0 | N | 1 | N | 1 | 1 | N | N | N |
> 3 | 1 | N | 0 | N | N | N | 1 | 0 | 0 |
> 4 | N | 0 | N | 1 | N | 1 | N | N | N |
> 5 | N | 1 | N | 0 | N | N | 1 | 0 | N |
> 6 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 1 |
> 7 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 0 |
> 8 | 1 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 |
> 9 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
> +---+---+----+----+-------+-------+-----+--------+------+

> Difference from your table comes into columns:
> +-------+-------+
> | n(AB) | nAonB |
> +-------+-------+
> 1 | N | N |
> 2 | 1 | 1 |
> 3 | N | N |
> 4 | N | 1 | >>> Here.
> 5 | N | N |
> 6 | 1 | 1 |
> 7 | 1 | 1 |
> 8 | 1 | 1 |
> 9 | 0 | 0 |
> +-------+-------+

Line 4 implies A = N, B = 0.
Line 2 implies A = 0, B = N.
So, if commutative law applies to MySQL ;-), line 2 and 4 have to be
equivalent. But they are not, as you found.

> +--------+------+
> | n(AoB) | nAnB |
> +--------+------+
> 1 | N | N |
> 2 | N | N |
> 3 | 0 | 0 |
> 4 | N | N |
> 5 | 0 | N | >>> Here.
> 6 | 1 | 1 |
> 7 | 0 | 0 |
> 8 | 0 | 0 |
> 9 | 0 | 0 |
> +--------+------+

Line 5 implies A = N, B = 1.
Line 3 implies A = 1, B = N.
Same logic as above: If MySQL was compliand to SQL99, which is built
with the commutative law in mind, Line 3 and 5 are equivalent,
because A or B = B or A.

[...]
> > Therefore, MTL turns unusable, as its results become almost
> > unpredictable and turn out not to be optimizable by classical means
> > of mathematics.
> I will slightly disagree with you as MySQL is used as is by millions
> of people.
You probably know lemmings? ;-)
Millions of them would not make a mistake. (There are some more
recent political examples to that too, but this would be far off
topic ;-) )

> Why do you say results are unpredictable? Until MySQL evaluates
> expression always in same way result is fully predictable.
But MySQL solves expressions in a different way if the "sub-
"expressions are sorted in a different way.
So
SELECT ... WHERE a>5 AND b>3
is not the same as
SELECT ... WHERE b>3 AND a>5
..
(Okay, I did use some "easier" WHERE-clauses than those really needed
to make "my" bug work, but I think, those show the impact better and
more clearly ;-) )

To me, this turns MySQL's logic unpredictable. I should have the
option to optimize the query in any way and using any "logic" I want
to. AND, OR, NAND and NOR-Logics must be equivalent. At least,
mathematics says, they are and shows ways to transform them.
One of those ways is the De Morgan-Rule or better Shannon's inversion
proposition, derived from De Morgan.

[...]
> mysql> select (NULL AND (0 = 1)); -> NULL
> ^false^
> > The result is not in accordance to SQL99 but to MLT. SQL99 prevents
> > this result by rule 4 (NULL AND false -> FALSE).
> >
> > You missed the step between: NULL AND false results in false
> > according to SQL99 but in NULL according to MLT.
> Yes. See above.
And that's the problem ;-)

> I tend to agree that Manual doesn't explain that case as it says (in
> chapter 6.3.1.3 Logical Operators) ...

Chapter 6.3.1.3 always says "if either operand is". According to
that, one should guess, that MLT supports commutative law. :-)

> Logically yes you are right - result set is still correct as it
> contains same rows :)
No, it did not, as shown with my example ;-)

> Ok. I will discuss this case within our development team and will vote
> for 'fixing' it according to SQL99. You convinced me.
That'll be great!

Bye

Tobias

P.S.: for the "test"
How to repeat:

-------------------------------
Tobias Eggendorfer
E-Mail: nospam@acme.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12892@lists.mysql.com
To unsubscribe, e-mail

Bug De-Morgan-Rules

Hi!

>>>>> "Tobias" == Tobias Eggendorfer writes:

Tobias> Hello,
Tobias> I guess I found a bug in MySQL 3.23.36. I have not had the
Tobias> opportunity to test it on a more recent version, but I did not find a
Tobias> clue in the mysql ChangeLog that it might have been fixed. I do hope,
Tobias> it is still existent.

Tobias> Bug-Description:
Tobias> Having a colum "e" type "date" that is allowed to become NULL and
Tobias> where an index is calculated over, the following statements should be
Tobias> equivalent.



Tobias> How-to-repeat:
Tobias> 1) CREATE TABLE f (primkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, e
Tobias> DATE);

mysql> select e from f where
Tobias> -> NOT ((e IS NOT NULL) AND (e>"2002-07-30"));
Tobias> +------------+
Tobias> | e |
Tobias> +------------+
Tobias> | NULL |
Tobias> | 2002-06-10 |
Tobias> | 2002-07-10 |
Tobias> +------------+
Tobias> 3 rows in set (0.00 sec)

As you concluded with Salle, MySQL had did calculation as follows:

NULL AND FALSE -> NULL
FALSE AND NULL -> FALSE

In WHERE clause it's normally equal if the result is NULL or FALSE and
to get more speed MySQL stopped to parse a WHERE expression when it
found a NULL or FALSE in an AND expression.

This optimisation works ok in all cases, except when you are using
NOT.

This bug was fixed in 4.0.5 that was released a while ago.

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13252@lists.mysql.com
To unsubscribe, e-mail