Update of /cvsroot/monetdb/sql/src/test/BugTracker-2009/Tests
In directory
sfp-cvsdas-1.v30.ch3.sourceforge.com:/tmp/cvs-serv29430/BugTracker-2009/Tests
Modified Files:
Tag: Feb2010
AVG_ReturnsNoLines.SF-2596084.sql
TribooleanFailureWithSubquery.SF-2679434.sql
Log Message:
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.2.8.1
diff -u -d -r1.2 -r1.2.8.1
--- TribooleanFailureWithSubquery.SF-2679434.sql 17 Mar 2009 14:57:38
-0000 1.2
+++ TribooleanFailureWithSubquery.SF-2679434.sql 5 Mar 2010 17:03:48
-0000 1.2.8.1
@@ -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;
Index: AVG_ReturnsNoLines.SF-2596084.sql
===================================================================
RCS file:
/cvsroot/monetdb/sql/src/test/BugTracker-2009/Tests/AVG_ReturnsNoLines.SF-2596084.sql,v
retrieving revision 1.2
retrieving revision 1.2.8.1
diff -u -d -r1.2 -r1.2.8.1
--- AVG_ReturnsNoLines.SF-2596084.sql 15 Feb 2009 12:43:20 -0000 1.2
+++ AVG_ReturnsNoLines.SF-2596084.sql 5 Mar 2010 17:03:48 -0000 1.2.8.1
@@ -1,59 +1,59 @@
-CREATE TABLE x (
-x DOUBLE
-)
-;
-
-INSERT INTO x VALUES (1);
-INSERT INTO x VALUES (2);
-INSERT INTO x VALUES (3);
-
--- does not return records
-SELECT AVG(x) AS avgx, AVG(x) AS avgx2
-FROM x;
-
-SELECT AVG(x) AS avgx, SUM(x) AS sumx
-FROM x;
-
-SELECT AVG(x) AS avgx, COUNT(x) AS countx
-FROM x;
-
-
--- fine
-SELECT AVG(x) AS avgx, COUNT(*) AS countstar
-FROM x;
-
-SELECT AVG(x) AS avgx, MIN(x) AS minx
-FROM x;
-
-SELECT AVG(x) AS avgx, MAX(x) AS maxx
-FROM x;
-
-SELECT SUM(x) AS sumx, MIN(x) AS minx, MAX(x) AS maxx, COUNT(x) AS countx,
COUNT(*) AS countstar
-FROM x;
-
-
--- one pass standard deviation: numerically less stable than the same
caluclation in R
-SELECT COUNT(x) AS n1, sum(x)/count(x) as m1, sqrt( sum(x*x)/count(x) -
(sum(x)/count(x))*(sum(x)/count(x)) ) as sd1
-FROM x;
-
--- two pass standard deviation with inner count (FAILS due to the AGGREGATION
BUG)
-SELECT MIN(n) AS n2, MIN(m) AS m2, SQRT(SUM((x-m)*(x-m))/MIN(n)) AS sd2
-FROM
-(
- SELECT AVG(x) AS m, COUNT(x) AS n
- FROM x
-) pass1
-, x
-;
-
--- two pass standard deviation with outer count (works in MonetDB)
-SELECT COUNT(x) AS n2, MIN(m) AS m2, SQRT(SUM((x-m)*(x-m))/COUNT(x)) AS sd2
-FROM
-(
- SELECT AVG(x) AS m
- FROM x
-) agg
-, x
-;
-
-drop table x;
+CREATE TABLE x (
+x DOUBLE
+)
+;
+
+INSERT INTO x VALUES (1);
+INSERT INTO x VALUES (2);
+INSERT INTO x VALUES (3);
+
+-- does not return records
+SELECT AVG(x) AS avgx, AVG(x) AS avgx2
+FROM x;
+
+SELECT AVG(x) AS avgx, SUM(x) AS sumx
+FROM x;
+
+SELECT AVG(x) AS avgx, COUNT(x) AS countx
+FROM x;
+
+
+-- fine
+SELECT AVG(x) AS avgx, COUNT(*) AS countstar
+FROM x;
+
+SELECT AVG(x) AS avgx, MIN(x) AS minx
+FROM x;
+
+SELECT AVG(x) AS avgx, MAX(x) AS maxx
+FROM x;
+
+SELECT SUM(x) AS sumx, MIN(x) AS minx, MAX(x) AS maxx, COUNT(x) AS countx,
COUNT(*) AS countstar
+FROM x;
+
+
+-- one pass standard deviation: numerically less stable than the same
caluclation in R
+SELECT COUNT(x) AS n1, sum(x)/count(x) as m1, sqrt( sum(x*x)/count(x) -
(sum(x)/count(x))*(sum(x)/count(x)) ) as sd1
+FROM x;
+
+-- two pass standard deviation with inner count (FAILS due to the AGGREGATION
BUG)
+SELECT MIN(n) AS n2, MIN(m) AS m2, SQRT(SUM((x-m)*(x-m))/MIN(n)) AS sd2
+FROM
+(
+ SELECT AVG(x) AS m, COUNT(x) AS n
+ FROM x
+) pass1
+, x
+;
+
+-- two pass standard deviation with outer count (works in MonetDB)
+SELECT COUNT(x) AS n2, MIN(m) AS m2, SQRT(SUM((x-m)*(x-m))/COUNT(x)) AS sd2
+FROM
+(
+ SELECT AVG(x) AS m
+ FROM x
+) agg
+, x
+;
+
+drop table x;
------------------------------------------------------------------------------
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
