Update of /cvsroot/monetdb/sql/src/test/BugTracker-2009/Tests
In directory
sfp-cvsdas-1.v30.ch3.sourceforge.com:/tmp/cvs-serv18032/src/test/BugTracker-2009/Tests
Modified Files:
TribooleanFailureWithSubquery.SF-2679434.sql
Log Message:
propagated changes of Friday Mar 05 2010 - Wednesday Mar 10 2010
from the Feb2010 branch to the development trunk
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2010/03/05 - sjoerd:
src/test/BugTracker-2009/Tests/TribooleanFailureWithSubquery.SF-2679434.sql,1.2.8.1
CRLF -> LF.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Index: TribooleanFailureWithSubquery.SF-2679434.sql
===================================================================
RCS file:
/cvsroot/monetdb/sql/src/test/BugTracker-2009/Tests/TribooleanFailureWithSubquery.SF-2679434.sql,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -d -r1.2 -r1.3
--- TribooleanFailureWithSubquery.SF-2679434.sql 17 Mar 2009 14:57:38
-0000 1.2
+++ TribooleanFailureWithSubquery.SF-2679434.sql 10 Mar 2010 14:30:11
-0000 1.3
@@ -1,150 +1,150 @@
--- 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.
-
--------------------------------
--- on 1), arithmetic comparison
--------------------------------
-
--- correct
-SELECT SUM(a) AS suma
-FROM (
- SELECT *
- FROM (
- SELECT 1 AS a, 'x' AS b
- ) sub
- WHERE b IN ('y')
-) sub2
-;
-
--- correct
-SELECT SUM(a) AS suma
-FROM (
- SELECT *
- FROM (
- SELECT 1 AS a, 'x' AS b
- ) sub
- WHERE b IN ('y')
-) sub2
-HAVING SUM(a) IS NULL
-;
-
--- correct
-SELECT SUM(a) AS suma
-FROM (
- SELECT *
- FROM (
- SELECT 1 AS a, 'x' AS b
- ) sub
- WHERE b IN ('y')
-) sub2
-HAVING SUM(a) IS NOT NULL
-;
-
--- WRONG
-SELECT SUM(a) AS suma
-FROM (
- SELECT *
- FROM (
- SELECT 1 AS a, 'x' AS b
- ) sub
- WHERE b IN ('y')
-) sub2
-HAVING SUM(a) > 0
-;
-
--- STILL WRONG
-SELECT *
-FROM (
- SELECT SUM(a) AS suma, NULL AS mynull
- FROM (
- SELECT *
- FROM (
- SELECT 1 AS a, 'x' AS b
- ) sub
- WHERE b IN ('y')
- ) sub2
-) sub3
-WHERE suma > 0
-;
-
--- although this works
-SELECT *
-FROM (
- SELECT SUM(a) AS suma, NULL AS mynull
- FROM (
- SELECT *
- FROM (
- SELECT 1 AS a, 'x' AS b
- ) sub
- WHERE b IN ('y')
- ) sub2
-) sub3
-WHERE mynull > 0
-;
-
--- WRONG
-SELECT *
-FROM (
- SELECT 1 AS a, NULL AS b
-) sub
-WHERE b>0
-;
-
--- aparently right
-SELECT *
-FROM (
- SELECT 1 AS a, NULL AS b
-) sub
-WHERE NOT b<=0
-;
-
-
--- LOOKS better when using table instead of subquery, BUT
-CREATE TABLE dummy (a INTEGER, b INTEGER);
-INSERT INTO dummy VALUES (1, NULL);
-SELECT * FROM dummy;
-SELECT * FROM dummy WHERE b>0;
-SELECT * FROM dummy WHERE NOT b<=0;
-SELECT * FROM dummy WHERE NOT b>0;
-SELECT * FROM dummy WHERE b<=0;
-
-
---------------------------
--- on 2), logic comparison
---------------------------
-
--- let's test this systematically
-DROP table dummy;
-CREATE TABLE dummy (a BOOLEAN, b BOOLEAN);
-INSERT INTO dummy VALUES (TRUE, TRUE);
-INSERT INTO dummy VALUES (TRUE, FALSE);
-INSERT INTO dummy VALUES (TRUE, NULL);
-INSERT INTO dummy VALUES (FALSE, TRUE);
-INSERT INTO dummy VALUES (FALSE, FALSE);
-INSERT INTO dummy VALUES (FALSE, NULL);
-INSERT INTO dummy VALUES (NULL, TRUE);
-INSERT INTO dummy VALUES (NULL, FALSE);
-INSERT INTO dummy VALUES (NULL, NULL);
-
-
--- GOTCHA: NULL OR TRUE gives NULL which is wrong (TRUE known to give TRUE
whatever the other expression is)
--- GOTCHA: NULL AND FALSE gives NULL which is wrong (FALSE known to give FALSE
whatever the other expression is)
-SELECT
- a
-, b
-, NOT a AS "NOT_a"
-, a=b AS "a_EQ_b"
-, a<>b AS "a_NE_b"
-, a<b AS "a_LT_b"
-, a<=b AS "a_LE_b"
-, a>b AS "a_GT_b"
-, a>=b AS "a_GE_b"
-, a OR b AS "a_OR_b"
-, a AND b AS "a_AND_b"
-FROM dummy
-;
-
-DROP table dummy;
+-- 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.
+
+-------------------------------
+-- on 1), arithmetic comparison
+-------------------------------
+
+-- correct
+SELECT SUM(a) AS suma
+FROM (
+ SELECT *
+ FROM (
+ SELECT 1 AS a, 'x' AS b
+ ) sub
+ WHERE b IN ('y')
+) sub2
+;
+
+-- correct
+SELECT SUM(a) AS suma
+FROM (
+ SELECT *
+ FROM (
+ SELECT 1 AS a, 'x' AS b
+ ) sub
+ WHERE b IN ('y')
+) sub2
+HAVING SUM(a) IS NULL
+;
+
+-- correct
+SELECT SUM(a) AS suma
+FROM (
+ SELECT *
+ FROM (
+ SELECT 1 AS a, 'x' AS b
+ ) sub
+ WHERE b IN ('y')
+) sub2
+HAVING SUM(a) IS NOT NULL
+;
+
+-- WRONG
+SELECT SUM(a) AS suma
+FROM (
+ SELECT *
+ FROM (
+ SELECT 1 AS a, 'x' AS b
+ ) sub
+ WHERE b IN ('y')
+) sub2
+HAVING SUM(a) > 0
+;
+
+-- STILL WRONG
+SELECT *
+FROM (
+ SELECT SUM(a) AS suma, NULL AS mynull
+ FROM (
+ SELECT *
+ FROM (
+ SELECT 1 AS a, 'x' AS b
+ ) sub
+ WHERE b IN ('y')
+ ) sub2
+) sub3
+WHERE suma > 0
+;
+
+-- although this works
+SELECT *
+FROM (
+ SELECT SUM(a) AS suma, NULL AS mynull
+ FROM (
+ SELECT *
+ FROM (
+ SELECT 1 AS a, 'x' AS b
+ ) sub
+ WHERE b IN ('y')
+ ) sub2
+) sub3
+WHERE mynull > 0
+;
+
+-- WRONG
+SELECT *
+FROM (
+ SELECT 1 AS a, NULL AS b
+) sub
+WHERE b>0
+;
+
+-- aparently right
+SELECT *
+FROM (
+ SELECT 1 AS a, NULL AS b
+) sub
+WHERE NOT b<=0
+;
+
+
+-- LOOKS better when using table instead of subquery, BUT
+CREATE TABLE dummy (a INTEGER, b INTEGER);
+INSERT INTO dummy VALUES (1, NULL);
+SELECT * FROM dummy;
+SELECT * FROM dummy WHERE b>0;
+SELECT * FROM dummy WHERE NOT b<=0;
+SELECT * FROM dummy WHERE NOT b>0;
+SELECT * FROM dummy WHERE b<=0;
+
+
+--------------------------
+-- on 2), logic comparison
+--------------------------
+
+-- let's test this systematically
+DROP table dummy;
+CREATE TABLE dummy (a BOOLEAN, b BOOLEAN);
+INSERT INTO dummy VALUES (TRUE, TRUE);
+INSERT INTO dummy VALUES (TRUE, FALSE);
+INSERT INTO dummy VALUES (TRUE, NULL);
+INSERT INTO dummy VALUES (FALSE, TRUE);
+INSERT INTO dummy VALUES (FALSE, FALSE);
+INSERT INTO dummy VALUES (FALSE, NULL);
+INSERT INTO dummy VALUES (NULL, TRUE);
+INSERT INTO dummy VALUES (NULL, FALSE);
+INSERT INTO dummy VALUES (NULL, NULL);
+
+
+-- GOTCHA: NULL OR TRUE gives NULL which is wrong (TRUE known to give TRUE
whatever the other expression is)
+-- GOTCHA: NULL AND FALSE gives NULL which is wrong (FALSE known to give FALSE
whatever the other expression is)
+SELECT
+ a
+, b
+, NOT a AS "NOT_a"
+, a=b AS "a_EQ_b"
+, a<>b AS "a_NE_b"
+, a<b AS "a_LT_b"
+, a<=b AS "a_LE_b"
+, a>b AS "a_GT_b"
+, a>=b AS "a_GE_b"
+, a OR b AS "a_OR_b"
+, a AND b AS "a_AND_b"
+FROM dummy
+;
+
+DROP table dummy;
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
