AND / OR evaluation inconsistency
Hi, I've come across something that is either a bug or is just my misunderstanding of how MonetDB deals with NULL results from boolean expressions. The issue I'm having is the OR and AND expressions behave differently in when evaluating a bollean expression where one of the variable is NULL (This is using version 11.15.19) If I run a query: SELECT ([some expression that evaluates to NULL] OR [some expression that evaluates to True] ) as "Result" - the returned value is <null> If I try SELECT ([some expression that evaluates to NULL] AND [some expression that evaluates to True] ) as "Result" - the returned value is True This would seem to suggest the AND evaluation is ignoring NULL or Treating NULL as meaning True whereas OR is propagating it. Which is correct and is this a bug? Thanks, Scott Mathieson ________________________________
Very interesting subject. If NULL is to be considered as unknown, then SQL
should use the 3-valued logic. In that case
NULL OR TRUE = TRUE
and
NULL AND TRUE = NULL
see also here http://en.wikipedia.org/wiki/Null_(SQL) the section
"Comparisons with NULL and the three-valued logic"
It looks like MonetDB is doing the opposite, or could it be that you
copy-paste the results in different order?
On Tue, Sep 2, 2014 at 6:57 PM, Scott Mathieson
Hi,
I’ve come across something that is either a bug or is just my misunderstanding of how MonetDB deals with NULL results from boolean expressions.
The issue I’m having is the OR and AND expressions behave differently in when evaluating a bollean expression where one of the variable is NULL (This is using version 11.15.19)
If I run a query:
SELECT ([some expression that evaluates to NULL] OR [some expression that evaluates to True] ) as “Result” – the returned value is <null>
If I try
SELECT ([some expression that evaluates to NULL] AND [some expression that evaluates to True] ) as “Result” – the returned value is True
This would seem to suggest the AND evaluation is ignoring NULL or Treating NULL as meaning True whereas OR is propagating it. Which is correct and is this a bug?
Thanks,
Scott Mathieson
------------------------------
_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
On Tue, Sep 02, 2014 at 04:57:25PM +0000, Scott Mathieson wrote:
Hi,
I’ve come across something that is either a bug or is just my misunderstanding of how MonetDB deals with NULL results from boolean expressions.
The issue I’m having is the OR and AND expressions behave differently in when evaluating a bollean expression where one of the variable is NULL (This is using version 11.15.19)
If I run a query:
SELECT ([some expression that evaluates to NULL] OR [some expression that evaluates to True] ) as “Result” – the returned value is <null>
If I try
SELECT ([some expression that evaluates to NULL] AND [some expression that evaluates to True] ) as “Result” – the returned value is True
This would seem to suggest the AND evaluation is ignoring NULL or Treating NULL as meaning True whereas OR is propagating it. Which is correct and is this a bug?
Thanks,
Scott Mathieson
Scott Do you have an example sql>select cast( NULL as bool) and TRUE; +--------------------+ | and_=_single_value | +====================+ | null | +--------------------+ 1 tuple (1.200ms) sql>select cast( NULL as bool) OR TRUE; +-------------------+ | or_=_single_value | +===================+ | true | +-------------------+ 1 tuple (1.215ms) Seems correct Niels
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
participants (3)
-
Lefteris -
Niels Nes -
Scott Mathieson