Changeset: 3d91504e964f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3d91504e964f
Modified Files:
sql/test/analytics/Tests/analytics13.sql
sql/test/analytics/Tests/analytics13.stable.out
Branch: grouping-analytics
Log Message:
Updated test
diffs (165 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
@@ -52,13 +52,6 @@ FROM another_T t1
GROUP BY CUBE(col1, col2);
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
NOT GROUPING(t1.col6) IN (SELECT SUM(t1.col6) FROM tbl_ProductSales tp
HAVING MAX(t1.col1) > MIN(tp.colID)),
GROUPING(t1.col6) IN (SELECT SUM(t1.col7) HAVING GROUPING(t1.col7) <
SUM(t1.col4)),
GROUPING(t1.col6) = ALL (SELECT 1),
@@ -70,16 +63,6 @@ FROM another_T t1
GROUP BY CUBE(t1.col6, t1.col7);
SELECT
- 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
-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;
-
-SELECT
DISTINCT
NOT GROUPING(t1.col6) IN (SELECT SUM(t1.col6) FROM tbl_ProductSales tp
HAVING MAX(t1.col1) > MIN(tp.colID)),
GROUPING(t1.col6) IN (SELECT SUM(t1.col7) HAVING GROUPING(t1.col7) <
SUM(t1.col4)),
@@ -92,7 +75,15 @@ FROM another_T t1
GROUP BY CUBE(t1.col6, t1.col7);
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)
+FROM another_T
+GROUP BY CUBE(col1, col2, col5);
+
+SELECT
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,
@@ -103,6 +94,22 @@ GROUP BY CUBE(col1, col2, col5)
ORDER BY a1 NULLS FIRST, a2 NULLS FIRST, a3 NULLS FIRST, a4 NULLS FIRST, a5
NULLS FIRST;
SELECT
+ GROUPING(col1, col2, col3, col4, col5, col6, col7, col8), AVG(col1),
CAST(SUM(col2) * 3 AS BIGINT), col3 + col4,
+ CAST(MAX(col5) * MIN(col6) AS BIGINT), col7, col1 IN (SELECT ColID FROM
tbl_ProductSales), col2 IN (SELECT ColID + col3 FROM tbl_ProductSales)
+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),
@@ -113,6 +120,7 @@ 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),
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)),
@@ -123,13 +131,14 @@ GROUP BY CUBE(col1, col2, col5);
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)
+ 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
FROM another_T
-GROUP BY CUBE(col1, col2, col5);
+GROUP BY CUBE(col1, col2, col5)
+ORDER BY a1 NULLS FIRST, a2 NULLS FIRST, a3 NULLS FIRST, a4 NULLS FIRST, a5
NULLS FIRST;
SELECT
GROUPING(col1, col2, col5, col8),
@@ -148,12 +157,5 @@ SELECT
FROM another_T
GROUP BY CUBE(col1, col2, col5, col8), GROUPING SETS (());
-SELECT
- GROUPING(col1, col2, col3, col4, col5, col6, col7, col8), AVG(col1),
CAST(SUM(col2) * 3 AS BIGINT), col3 + col4,
- CAST(MAX(col5) * MIN(col6) AS BIGINT), col7, col1 IN (SELECT ColID FROM
tbl_ProductSales), col2 IN (SELECT ColID + col3 FROM tbl_ProductSales)
-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);
-
DROP TABLE tbl_ProductSales;
DROP TABLE another_T;
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
@@ -299,6 +299,45 @@ stdout of test 'analytics13` in director
[ false, true, false, NULL, 0 ]
[ false, true, false, NULL, 0 ]
#SELECT
+# 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, 0 ]
+[ NULL, true, false, NULL, 0 ]
+[ NULL, true, false, NULL, 0 ]
+[ NULL, true, false, NULL, 0 ]
+[ NULL, true, false, NULL, 0 ]
+[ NULL, true, false, NULL, 0 ]
+[ NULL, true, false, NULL, 1 ]
+[ NULL, true, false, NULL, 1 ]
+[ NULL, true, false, NULL, 1 ]
+[ NULL, true, false, NULL, 1 ]
+[ NULL, true, false, NULL, 1 ]
+[ NULL, true, false, NULL, 1 ]
+[ NULL, true, false, 1, 0 ]
+[ NULL, true, false, 1, 0 ]
+[ NULL, true, false, 1, 0 ]
+[ false, true, false, NULL, 0 ]
+[ false, true, false, NULL, 0 ]
+[ false, true, false, NULL, 0 ]
+[ false, true, false, NULL, 1 ]
+[ false, true, false, NULL, 1 ]
+[ false, true, false, 1, 0 ]
+[ false, true, false, 1, 0 ]
+[ true, true, false, NULL, 0 ]
+[ true, true, false, NULL, 0 ]
+[ true, true, false, NULL, 0 ]
+[ true, true, false, 1, 0 ]
+[ true, true, false, 1, 0 ]
+[ true, true, false, 1, 0 ]
+#SELECT
# GROUPING(col1, col2, col3, col4, col5, col6, col7, col8), AVG(col1),
CAST(SUM(col2) * 3 AS BIGINT), col3 + col4,
# CAST(MAX(col5) * MIN(col6) AS BIGINT), col7, col1 IN (SELECT ColID FROM
tbl_ProductSales), col2 IN (SELECT ColID + col3 FROM tbl_ProductSales)
#FROM another_T
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
