Bugs item #2679434, was opened at 2009-03-10 16:58
Message generated for change (Comment added) made by stmane
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2679434&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/Core
Group: SQL CVS Head
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Jens Oehlschlägel (jens_oehl)
Assigned to: Niels Nes (nielsnes)
Summary: SQL:tri-boolean problems with NULL
Initial Comment:
Dear all,
I found a couple of inconsistencies (guess bugs) around tri-boolean evaluations
(involving NULL, or more precisely UNKNOWN).
1) NULL arithmetically compared should always give NULL, and in bi-boolean
decisions like WHERE or HAVING conditions NULL should always be mapped to
FALSE, i.e. dropping the rows. At least in combination with subquerying this is
not the case.
2) NULL logically compared should be propagated using tri-boolean logic. It is
not.
-- A greek seer says: if an operator is given a null operand, the result is
always null. The only operator that does not follow this rule is concatenation
(||).
-- A big blue Gaul says: if any operand determines the result (like TRUE OR
something is TRUE) then the result is NOT NULL
-- SQL92 defines tri-booleans
<truth value> ::=
TRUE
| FALSE
| UNKNOWN
-- and says
"A null value is an implementation-dependent special value that is distinct
from all non-null values of the associated data type. There is effectively only
one null value and that value is a member of every SQL data type. There is no
<literal> for a null value, although the keyword NULL is used in some places to
indicate that a null value is desired."
-- The truth tables in the SQL92 standard are hardly readable, but the
intention is clearly aiming on tri-boolean: (TRUE OR UNKNOWN) = TRUE
Cheers
Jens
MonetDB server v5.10.1 (32-bit), based on kernel v1.28.1 (32-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Configured for prefix: C:\Documents and Settings\sjoerd\My
Documents\src\stable\vs32\NT32
Libraries:
libpcre: 7.7 2008-05-07
libxml2: 2.6.32
Compiled by: sjo...@gans
Compilation: cl -GF -W3 -WX -wd4273 -wd4102 -MD -nologo -Ox
Linking : cl -GF -W3 -WX -wd4273 -wd4102 -MD -nologo -Ox
----------------------------------------------------------------------
>Comment By: Stefan Manegold (stmane)
Date: 2009-03-17 13:04
Message:
added test in
sql/src/test/BugTracker-2009/Tests/TribooleanFailureWithSubquery.SF-2679434.*
Everything appears to work fine, now,
except that
TRUE or NULL => NULL (instead of TRUE)
NULL or TRUE => NULL (instead of TRUE)
FALSE and NULL => NULL (instead of FALSE)
NULL and FALSE => NULL (instead of FALSE)
However, while not totally complying with tri-boolean logic,
the behavior as such is consistent.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2679434&group_id=56967
------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
