Changeset: ed6eec0afa8f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ed6eec0afa8f
Modified Files:
sql/test/analytics/Tests/analytics13.sql
sql/test/analytics/Tests/analytics13.stable.err
sql/test/analytics/Tests/analytics13.stable.out
Branch: grouping-analytics
Log Message:
Approved output and updated failing queries
diffs (193 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
@@ -155,6 +155,40 @@ ORDER BY
a11 ASC NULLS FIRST, a12 DESC NULLS LAST, a13 ASC NULLS FIRST, a14 DESC
NULLS LAST;
SELECT
+ NOT t1.col1 BETWEEN (SELECT MAX(t1.col7) EXCEPT SELECT tp.ColID FROM
tbl_ProductSales tp) AND (SELECT MIN(t1.col5) EXCEPT SELECT t1.col2) a1,
+ NOT GROUPING(t1.col1, t1.col2, t1.col4) * RANK() OVER (PARTITION BY
AVG(DISTINCT t1.col5)) NOT
+ BETWEEN (SELECT tp2.proj * t1.col1 + MAX(t1.col5) FROM LATERAL (SELECT
tp.ColID + MIN(t1.col6) - t1.col1 as proj FROM tbl_ProductSales tp) AS tp2)
+ AND
+ (SELECT SUM(t1.col7) FROM tbl_ProductSales tp HAVING t1.col2 <
ALL(SELECT MAX(tp.ColID))) a2
+FROM another_T t1
+GROUP BY CUBE(t1.col1, t1.col2), GROUPING SETS ((t1.col4))
+ORDER BY a1 ASC NULLS FIRST, a2 ASC NULLS LAST; --error, cardinality
violation, scalar expression expected
+
+SELECT
+ CAST((SELECT tp2.proj * t1.col1 + MAX(t1.col5) FROM LATERAL (SELECT
MAX(tp.ColID) + MIN(t1.col6) - t1.col1 as proj FROM tbl_ProductSales tp HAVING
NULL IS NOT NULL) AS tp2) AS BIGINT) AS a1
+FROM another_T t1
+GROUP BY ROLLUP(t1.col1, t1.col2), GROUPING SETS ((t1.col4))
+ORDER BY a1 ASC NULLS FIRST;
+
+SELECT
+ DISTINCT
+ CAST((SELECT tp2.proj * t1.col1 + MAX(t1.col5) FROM LATERAL (SELECT
MAX(tp.ColID) + MIN(t1.col6) - t1.col1 as proj FROM tbl_ProductSales tp HAVING
NULL IS NOT NULL) AS tp2) AS BIGINT) AS a1
+FROM another_T t1
+GROUP BY ROLLUP(t1.col1, t1.col2), GROUPING SETS ((t1.col4))
+ORDER BY a1 ASC NULLS FIRST;
+
+---Queries bellow give wrong results and errors
+
+SELECT
+ NOT GROUPING(t1.col2, t1.col4) <> ALL (SELECT t1.col2 FROM
tbl_ProductSales tp WHERE tp.colID = 1) a1
+FROM another_T t1
+GROUP BY ROLLUP(t1.col1, t1.col2), GROUPING SETS ((t1.col4))
+HAVING (t1.col1 = ANY (SELECT MAX(ColID + col2) FROM tbl_ProductSales)) NOT IN
+ ((SELECT NOT EXISTS (SELECT t1.col2 FROM tbl_ProductSales WHERE
tbl_ProductSales.ColID = t1.col1)) UNION ALL
+ (SELECT NOT GROUPING(t1.col1) BETWEEN (SELECT MAX(t1.col7) EXCEPT SELECT
tp.ColID FROM tbl_ProductSales tp) AND (SELECT MIN(t1.col5) EXCEPT SELECT
t1.col2)))
+ORDER BY a1 DESC NULLS FIRST;
+
+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),
@@ -164,7 +198,20 @@ SELECT
FROM another_T t1
GROUP BY CUBE(t1.col7, t1.col6);
----Queries bellow give wrong results and errors
+SELECT
+ CASE WHEN t1.col1 IN (SELECT 1 FROM tbl_ProductSales tp LEFT JOIN
another_T t2 ON tp.ColID = t1.col1) THEN 1 ELSE 2 END,
+ CASE WHEN SUM(t1.col3) IN (SELECT MAX(t1.col3) FROM tbl_ProductSales tp
LEFT JOIN another_T t2 ON tp.ColID = t1.col1) THEN 1 ELSE 2 END,
+ CASE WHEN t1.col2 IN (SELECT MAX(MAX(t1.col3)) OVER (PARTITION BY t1.col2
ORDER BY tp.ColID) FROM tbl_ProductSales tp LEFT JOIN another_T t2 ON tp.ColID
= t1.col1) THEN 1 ELSE 2 END
+FROM another_T t1
+GROUP BY ROLLUP(t1.col1, t1.col2);
+
+SELECT
+ CASE WHEN t1.col2 IN (SELECT MIN(ColID) FROM tbl_ProductSales tp INNER
JOIN another_T t2 ON tp.ColID = t1.col1 AND tp.ColID = t2.col2) THEN 1 ELSE 2
END,
+ CASE WHEN t1.col2 IN (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,
+ CASE WHEN t1.col2 IN (SELECT MIN(ColID) FROM tbl_ProductSales tp RIGHT
JOIN another_T t2 ON tp.ColID = t1.col1 AND tp.ColID = t2.col2) THEN 1 ELSE 2
END,
+ CASE WHEN t1.col2 IN (SELECT MIN(ColID) FROM tbl_ProductSales tp FULL
OUTER 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 ROLLUP(t1.col1, t1.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,
diff --git a/sql/test/analytics/Tests/analytics13.stable.err
b/sql/test/analytics/Tests/analytics13.stable.err
--- a/sql/test/analytics/Tests/analytics13.stable.err
+++ b/sql/test/analytics/Tests/analytics13.stable.err
@@ -23,6 +23,15 @@ stderr of test 'analytics13` in director
# 14:18:27 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-237518" "--port=38050"
# 14:18:27 >
+MAPI = (monetdb) /var/tmp/mtest-58182/.s.monetdb.34785
+QUERY = SELECT
+ NOT t1.col1 BETWEEN (SELECT MAX(t1.col7) EXCEPT SELECT tp.ColID
FROM tbl_ProductSales tp) AND (SELECT MIN(t1.col5) EXCEPT SELECT t1.col2) a1,
+ NOT GROUPING(t1.col1, t1.col2, t1.col4) * RANK() OVER (PARTITION
BY AVG(DISTINCT t1.col5)) NOT
+ BETWEEN (SELECT tp2.proj * t1.col1 + MAX(t1.col5) FROM LATERAL
(SELECT tp.ColID + MIN(t1.col6) - t1.col1 as proj FROM tbl_ProductSales tp) AS
tp2)
+ AND
+ (SELECT SUM(t1.col7) FROM tbl_ProductSales tp HAVING t1.col2 <
ALL(SELECT MAX(tp.ColID))) a2
+ERROR = !zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
# 14:18:27 >
# 14:18:27 > "Done."
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
@@ -123,8 +123,8 @@ stdout of test 'analytics13` in director
# col3 > ALL (SELECT 1 FROM tbl_ProductSales HAVING MIN(col4) > 30)
#FROM another_T
#GROUP BY ROLLUP(col3, col4);
-% .%27 # table_name
-% %27 # name
+% .%30 # table_name
+% %30 # name
% boolean # type
% 5 # length
[ true ]
@@ -140,8 +140,8 @@ stdout of test 'analytics13` in director
# col1 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MIN(col2) IS NULL)
#FROM another_T
#GROUP BY CUBE(col1, col2);
-% .%32 # table_name
-% %32 # name
+% .%27 # table_name
+% %27 # name
% boolean # type
% 5 # length
[ true ]
@@ -224,8 +224,8 @@ stdout of test 'analytics13` in director
# GROUPING(t1.col6) = ALL (SELECT GROUPING(t1.col7) FROM tbl_ProductSales)
#FROM another_T t1
#GROUP BY CUBE(t1.col6, t1.col7);
-% .%315, .%45, .%317, .%320, .%321, .%322, .%323 # table_name
-% %315, %45, %317, %320, %321, %322, %323 # name
+% .%317, .%45, .%321, .%322, .%323, .%324, .%325 # table_name
+% %317, %45, %321, %322, %323, %324, %325 # name
% boolean, boolean, boolean, boolean, boolean,
boolean, boolean # type
% 5, 5, 5, 5, 5, 5, 5 # length
[ true, false, false, false, false, true, true ]
@@ -251,8 +251,8 @@ stdout of test 'analytics13` in director
# GROUPING(t1.col6) = ALL (SELECT GROUPING(t1.col7)),
# GROUPING(t1.col6) = ALL (SELECT GROUPING(t1.col7) FROM tbl_ProductSales)
#FROM another_T t1
-% .%315, .%45, .%317, .%320, .%321, .%322, .%323 # table_name
-% %315, %45, %317, %320, %321, %322, %323 # name
+% .%317, .%45, .%321, .%322, .%323, .%324, .%325 # table_name
+% %317, %45, %321, %322, %323, %324, %325 # name
% boolean, boolean, boolean, boolean, boolean,
boolean, boolean # type
% 5, 5, 5, 5, 5, 5, 5 # length
[ true, false, false, false, false, true, true ]
@@ -265,8 +265,8 @@ stdout of test 'analytics13` in director
# 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
+% .%210, .%211, .%212, .%213, .%214 # table_name
+% %210, %211, %212, %213, %214 # name
% boolean, boolean, boolean, int, int # type
% 5, 5, 5, 1, 1 # length
[ true, true, false, 1, 0 ]
@@ -467,8 +467,8 @@ stdout of test 'analytics13` in director
# 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
+% .%210, .%211, .%212, .%213, .%214 # table_name
+% %210, %211, %212, %213, %214 # name
% boolean, boolean, boolean, int, int # type
% 5, 5, 5, 1, 1 # length
[ true, true, false, 1, 0 ]
@@ -571,6 +571,38 @@ stdout of test 'analytics13` in director
[ 14, NULL, NULL, NULL, true, false, NULL, 11, NULL, 48,
73, "44 plus 1", 111, NULL ]
[ 14, NULL, NULL, NULL, true, false, NULL, 111, NULL, 48,
103, "444 plus 1", 111, NULL ]
[ 15, NULL, NULL, NULL, true, false, NULL, 1234, NULL, 56,
403, "4-sep-44-sep-444-sep-4444 plus 1", 111, NULL ]
+#SELECT
+# CAST((SELECT tp2.proj * t1.col1 + MAX(t1.col5) FROM LATERAL (SELECT
MAX(tp.ColID) + MIN(t1.col6) - t1.col1 as proj FROM tbl_ProductSales tp HAVING
NULL IS NOT NULL) AS tp2) AS BIGINT) AS a1
+#FROM another_T t1
+#GROUP BY ROLLUP(t1.col1, t1.col2), GROUPING SETS ((t1.col4))
+#ORDER BY a1 ASC NULLS FIRST;
+% . # table_name
+% a1 # name
+% bigint # type
+% 1 # length
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+#SELECT
+# DISTINCT
+# CAST((SELECT tp2.proj * t1.col1 + MAX(t1.col5) FROM LATERAL (SELECT
MAX(tp.ColID) + MIN(t1.col6) - t1.col1 as proj FROM tbl_ProductSales tp HAVING
NULL IS NOT NULL) AS tp2) AS BIGINT) AS a1
+#FROM another_T t1
+#GROUP BY ROLLUP(t1.col1, t1.col2), GROUPING SETS ((t1.col4))
+#ORDER BY a1 ASC NULLS FIRST;
+% . # table_name
+% a1 # name
+% bigint # type
+% 1 # length
+[ NULL ]
#DROP TABLE tbl_ProductSales;
#DROP TABLE another_T;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
