Changeset: 8ea86d691bf8 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=8ea86d691bf8
Modified Files:
sql/test/analytics/Tests/analytics13.sql
sql/test/analytics/Tests/analytics13.stable.out
Branch: grouping-analytics
Log Message:
Approved output of queries with right results
diffs (103 lines):
diff --git a/sql/test/analytics/Tests/analytics13.sql
b/sql/test/analytics/Tests/analytics13.sql
--- a/sql/test/analytics/Tests/analytics13.sql
+++ b/sql/test/analytics/Tests/analytics13.sql
@@ -100,25 +100,6 @@ FROM another_T
GROUP BY ROLLUP(col1, col2, col3, col4, col5, col6, col7, col8) --with 8
columns, a smallint is necessary for grouping's output
ORDER BY GROUPING(col1, col2, col3, col4, col5, col6, col7, col8);
----Queries bellow give wrong results and errors
-
-SELECT
- CASE WHEN NOT t1.col2 NOT IN (SELECT (SELECT MAX(t1.col7)) UNION (SELECT
MIN(ColID) FROM tbl_ProductSales LEFT JOIN another_T t2 ON t2.col5 = t1.col1))
THEN 1 ELSE 2 END,
- CASE WHEN NOT t1.col2 NOT IN (SELECT (SELECT MAX(t1.col7)) UNION (SELECT
MIN(ColID) FROM tbl_ProductSales LEFT JOIN another_T t2 ON MIN(t1.col5) =
t1.col1)) THEN 1 ELSE 2 END,
- CASE WHEN NOT t1.col2 NOT IN (SELECT (SELECT MAX(t1.col7)) UNION (SELECT
MIN(ColID) FROM tbl_ProductSales tp LEFT JOIN another_T t2 ON tp.ColID =
t1.col1 AND tp.ColID = t2.col2)) THEN 1 ELSE 2 END
-FROM another_T t1
-GROUP BY CUBE(t1.col1, t1.col2);
-
-SELECT
- GROUPING(t1.col6, t1.col7) IN (SELECT SUM(t2.col2) FROM another_T t2 GROUP
BY t2.col5),
- NOT 32 * GROUPING(t1.col7, t1.col6) IN (SELECT MAX(t2.col2) FROM another_T
t2),
- GROUPING(t1.col6, t1.col7) NOT IN (SELECT MIN(t2.col2) FROM another_T t2
GROUP BY t1.col6),
- NOT SUM(t1.col2) * GROUPING(t1.col6, t1.col6, t1.col6, t1.col6) NOT IN
(SELECT MAX(t2.col6) FROM another_T t2 GROUP BY t1.col6 HAVING t1.col7 +
MIN(t2.col8) < MAX(t2.col7 - t1.col6)),
- GROUPING(t1.col6) <> ANY (SELECT t1.col7 INTERSECT SELECT t1.col6),
- GROUPING(t1.col7) = ALL (SELECT GROUPING(t1.col6) UNION ALL SELECT 10 *
MIN(t1.col8))
-FROM another_T t1
-GROUP BY CUBE(t1.col7, t1.col6);
-
SELECT
DISTINCT
NOT col1 * col5 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MAX(col2) >
2),
@@ -140,6 +121,25 @@ FROM another_T
GROUP BY CUBE(col1, col2, col5)
ORDER BY a1 NULLS FIRST, a2 NULLS FIRST, a3 NULLS FIRST, a4 NULLS FIRST, a5
NULLS FIRST;
+---Queries bellow give wrong results and errors
+
+SELECT
+ CASE WHEN NOT t1.col2 NOT IN (SELECT (SELECT MAX(t1.col7)) UNION (SELECT
MIN(ColID) FROM tbl_ProductSales LEFT JOIN another_T t2 ON t2.col5 = t1.col1))
THEN 1 ELSE 2 END,
+ CASE WHEN NOT t1.col2 NOT IN (SELECT (SELECT MAX(t1.col7)) UNION (SELECT
MIN(ColID) FROM tbl_ProductSales LEFT JOIN another_T t2 ON MIN(t1.col5) =
t1.col1)) THEN 1 ELSE 2 END,
+ CASE WHEN NOT t1.col2 NOT IN (SELECT (SELECT MAX(t1.col7)) UNION (SELECT
MIN(ColID) FROM tbl_ProductSales tp LEFT JOIN another_T t2 ON tp.ColID =
t1.col1 AND tp.ColID = t2.col2)) THEN 1 ELSE 2 END
+FROM another_T t1
+GROUP BY CUBE(t1.col1, t1.col2);
+
+SELECT
+ GROUPING(t1.col6, t1.col7) IN (SELECT SUM(t2.col2) FROM another_T t2 GROUP
BY t2.col5),
+ NOT 32 * GROUPING(t1.col7, t1.col6) IN (SELECT MAX(t2.col2) FROM another_T
t2),
+ GROUPING(t1.col6, t1.col7) NOT IN (SELECT MIN(t2.col2) FROM another_T t2
GROUP BY t1.col6),
+ NOT SUM(t1.col2) * GROUPING(t1.col6, t1.col6, t1.col6, t1.col6) NOT IN
(SELECT MAX(t2.col6) FROM another_T t2 GROUP BY t1.col6 HAVING t1.col7 +
MIN(t2.col8) < MAX(t2.col7 - t1.col6)),
+ GROUPING(t1.col6) <> ANY (SELECT t1.col7 INTERSECT SELECT t1.col6),
+ GROUPING(t1.col7) = ALL (SELECT GROUPING(t1.col6) UNION ALL SELECT 10 *
MIN(t1.col8))
+FROM another_T t1
+GROUP BY CUBE(t1.col7, t1.col6);
+
SELECT
GROUPING(col1, col2, col5, col8),
col1 IN (SELECT ColID + col2 FROM tbl_ProductSales),
diff --git a/sql/test/analytics/Tests/analytics13.stable.out
b/sql/test/analytics/Tests/analytics13.stable.out
--- a/sql/test/analytics/Tests/analytics13.stable.out
+++ b/sql/test/analytics/Tests/analytics13.stable.out
@@ -380,6 +380,44 @@ stdout of test 'analytics13` in director
[ 127, 111, 666, NULL, 369630, NULL, false, NULL ]
[ 127, 1111, 6666, NULL, 37029630, NULL, false, NULL ]
[ 255, 308.5, 7404, NULL, 33330, NULL, NULL, NULL ]
+#SELECT
+# DISTINCT
+# NOT col1 * col5 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MAX(col2) >
2),
+# NOT AVG(col2) * col1 <> ANY (SELECT 20 FROM tbl_ProductSales HAVING
MAX(col1) IS NULL),
+# NOT EXISTS (SELECT ColID - 12 FROM tbl_ProductSales GROUP BY ColID HAVING
MAX(col1) IS NOT NULL OR MIN(col1) < MIN(col2)),
+# CAST (NOT col1 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER)
| CAST (col2 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER),
+# CAST (EXISTS (SELECT MAX(col5) * MAX(col4) FROM another_T GROUP BY col5,
col4) AS INTEGER) & CAST (GROUPING(col1, col5) IN (SELECT DISTINCT col2 FROM
another_T GROUP BY col2) AS INTEGER)
+% .%211, .%212, .%213, .%214, .%215 # table_name
+% %211, %212, %213, %214, %215 # name
+% boolean, boolean, boolean, int, int # type
+% 5, 5, 5, 1, 1 # length
+[ true, true, false, 1, 0 ]
+[ NULL, true, false, NULL, 1 ]
+[ true, true, false, NULL, 0 ]
+[ NULL, true, false, 1, 0 ]
+[ NULL, true, false, NULL, 0 ]
+[ false, true, false, 1, 0 ]
+[ false, true, false, NULL, 1 ]
+[ false, true, false, NULL, 0 ]
+#SELECT
+# DISTINCT
+# NOT col1 * col5 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MAX(col2) >
2) AS a1,
+# NOT AVG(col2) * col1 <> ANY (SELECT 20 FROM tbl_ProductSales HAVING
MAX(col1) IS NULL) AS a2,
+# NOT EXISTS (SELECT ColID - 12 FROM tbl_ProductSales GROUP BY ColID HAVING
MAX(col1) IS NOT NULL OR MIN(col1) < MIN(col2)) AS a3,
+# CAST (NOT col1 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER)
| CAST (col2 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER) AS a4,
+# CAST (EXISTS (SELECT MAX(col5) * MAX(col4) FROM another_T GROUP BY col5,
col4) AS INTEGER) & CAST (GROUPING(col1, col5) IN (SELECT DISTINCT col2 FROM
another_T GROUP BY col2) AS INTEGER) AS a5
+% ., ., ., ., . # table_name
+% a1, a2, a3, a4, a5 # name
+% boolean, boolean, boolean, int, int # type
+% 5, 5, 5, 1, 1 # length
+[ NULL, true, false, NULL, 0 ]
+[ NULL, true, false, NULL, 1 ]
+[ NULL, true, false, 1, 0 ]
+[ false, true, false, NULL, 0 ]
+[ false, true, false, NULL, 1 ]
+[ false, true, false, 1, 0 ]
+[ true, true, false, NULL, 0 ]
+[ true, true, false, 1, 0 ]
#DROP TABLE tbl_ProductSales;
#DROP TABLE another_T;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
