Changeset: 25ae5d5134a7 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=25ae5d5134a7
Modified Files:
sql/server/rel_select.c
sql/test/BugDay_2005-10-06_2.9.3/Tests/having.SF-922614.stable.err
sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.out
sql/test/analytics/Tests/analytics10.sql
sql/test/analytics/Tests/analytics10.stable.err
sql/test/analytics/Tests/analytics10.stable.out
sql/test/miscellaneous/Tests/groupby_expressions.sql
sql/test/miscellaneous/Tests/groupby_expressions.stable.err
sql/test/miscellaneous/Tests/groupby_expressions.stable.out
Branch: default
Log Message:
The * operator doesn't match with a group by relation if that is the topmost
projection, so search for a projection bellow it
diffs (truncated from 723 to 300 lines):
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -5002,14 +5002,39 @@ rel_table_exp(sql_query *query, sql_rel
} else if (column_e->token == SQL_TABLE) {
char *tname = column_e->data.lval->h->data.sval;
list *exps;
-
- if ((exps = rel_table_projections(sql, *rel, tname, 0)) != NULL
&& !list_empty(exps))
+ sql_rel *project = *rel, *groupby = NULL;
+
+ /* if there's a group by relation in the tree, skip it for the
'*' case and use the underlying projection */
+ if (project) {
+ while (is_groupby(project->op) ||
is_select(project->op)) {
+ if (is_groupby(project->op))
+ groupby = project;
+ if (project->l)
+ project = project->l;
+ }
+ assert(project);
+ }
+
+ if ((exps = rel_table_projections(sql, project, tname, 0)) !=
NULL && !list_empty(exps)) {
+ if (groupby) {
+ groupby->exps =
list_distinct(list_merge(groupby->exps, exps, (fdup) NULL), (fcmp) exp_equal,
(fdup) NULL);
+ for (node *n = groupby->exps->h ; n ; n =
n->next) {
+ sql_exp *e = n->data;
+
+ if (e->card > groupby->card) {
+ if (exp_name(e))
+ return sql_error(sql,
ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in
query results without an aggregate function", exp_name(e));
+ else
+ return sql_error(sql,
ERR_GROUPBY, SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query
results without an aggregate function");
+ }
+ }
+ }
+
return exps;
+ }
if (!tname)
- return sql_error(sql, 02,
- SQLSTATE(42000) "Table expression without table
name");
- return sql_error(sql, 02,
- SQLSTATE(42000) "Column expression Table '%s'
unknown", tname);
+ return sql_error(sql, 02, SQLSTATE(42000) "Table
expression without table name");
+ return sql_error(sql, 02, SQLSTATE(42000) "Column expression
Table '%s' unknown", tname);
}
return NULL;
}
diff --git a/sql/test/BugDay_2005-10-06_2.9.3/Tests/having.SF-922614.stable.err
b/sql/test/BugDay_2005-10-06_2.9.3/Tests/having.SF-922614.stable.err
--- a/sql/test/BugDay_2005-10-06_2.9.3/Tests/having.SF-922614.stable.err
+++ b/sql/test/BugDay_2005-10-06_2.9.3/Tests/having.SF-922614.stable.err
@@ -12,7 +12,7 @@ stderr of test 'having.SF-922614` in dir
MAPI = (monetdb) /var/tmp/mtest-27483/.s.monetdb.35395
QUERY = select * from t10 having i= max(i);
-ERROR = !Table expression without table name
+ERROR = !SELECT: cannot use non GROUP BY column 'i' in query results without
an aggregate function
CODE = 42000
# 16:54:28 >
diff --git
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.out
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.out
---
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.out
+++
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-having-not-null-not-in.Bug-6557.stable.out
@@ -33,12 +33,12 @@ stdout of test 'sqlitelogictest-having-n
% 1 # length
#SELECT DISTINCT * FROM tab0 AS cor0 GROUP BY cor0.col1, cor0.col2, cor0.col0;
% sys.cor0, sys.cor0, sys.cor0 # table_name
-% col1, col2, col0 # name
+% col0, col1, col2 # name
% int, int, int # type
% 2, 2, 2 # length
-[ 0, 38, 83 ]
-[ 0, 79, 26 ]
-[ 81, 24, 43 ]
+[ 83, 0, 38 ]
+[ 26, 0, 79 ]
+[ 43, 81, 24 ]
#SELECT CAST(SUM(col0) AS BIGINT) FROM tab0 WHERE + + col0 BETWEEN NULL AND +
col2;
% sys.%1 # table_name
% %1 # name
diff --git a/sql/test/analytics/Tests/analytics10.sql
b/sql/test/analytics/Tests/analytics10.sql
--- a/sql/test/analytics/Tests/analytics10.sql
+++ b/sql/test/analytics/Tests/analytics10.sql
@@ -13,6 +13,11 @@ SELECT
FROM tbl_ProductSales
GROUP BY (), (); --does the same global aggregate
+SELECT
+ *
+FROM tbl_ProductSales
+GROUP BY (); --error, cardinality mismatch on the projection
+
SELECT
CAST(SUM(TotalSales) as BIGINT) AS TotalSales
FROM tbl_ProductSales
diff --git a/sql/test/analytics/Tests/analytics10.stable.err
b/sql/test/analytics/Tests/analytics10.stable.err
--- a/sql/test/analytics/Tests/analytics10.stable.err
+++ b/sql/test/analytics/Tests/analytics10.stable.err
@@ -23,7 +23,14 @@ stderr of test 'analytics10` in director
# 11:30:14 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-10427" "--port=34219"
# 11:30:14 >
-MAPI = (monetdb) /var/tmp/mtest-25423/.s.monetdb.37936
+MAPI = (monetdb) /var/tmp/mtest-136728/.s.monetdb.39881
+QUERY = SELECT
+ *
+ FROM tbl_ProductSales
+ GROUP BY (); --error, cardinality mismatch on the projection
+ERROR = !SELECT: cannot use non GROUP BY column 'colid' in query results
without an aggregate function
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-136728/.s.monetdb.39881
QUERY = SELECT
CAST(SUM(TotalSales) as BIGINT) AS TotalSales
FROM tbl_ProductSales
diff --git a/sql/test/analytics/Tests/analytics10.stable.out
b/sql/test/analytics/Tests/analytics10.stable.out
--- a/sql/test/analytics/Tests/analytics10.stable.out
+++ b/sql/test/analytics/Tests/analytics10.stable.out
@@ -27,7 +27,7 @@ stdout of test 'analytics10` in director
#INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO
Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);
[ 4 ]
#SELECT CAST(SUM(TotalSales) as BIGINT) AS TotalSales FROM tbl_ProductSales;
-% sys.%3 # table_name
+% sys. # table_name
% totalsales # name
% bigint # type
% 4 # length
@@ -36,7 +36,7 @@ stdout of test 'analytics10` in director
# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY (); --global aggregate
-% sys.%3 # table_name
+% sys. # table_name
% totalsales # name
% bigint # type
% 4 # length
@@ -45,7 +45,7 @@ stdout of test 'analytics10` in director
# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY (), (); --does the same global aggregate
-% sys.%3 # table_name
+% sys. # table_name
% totalsales # name
% bigint # type
% 4 # length
@@ -54,7 +54,7 @@ stdout of test 'analytics10` in director
# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY Product_Category;
-% sys.%3 # table_name
+% sys. # table_name
% totalsales # name
% bigint # type
% 3 # length
@@ -64,7 +64,7 @@ stdout of test 'analytics10` in director
# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY Product_Category, (); --same as GROUP BY Product_Category
-% sys.%3 # table_name
+% sys. # table_name
% totalsales # name
% bigint # type
% 3 # length
@@ -74,7 +74,7 @@ stdout of test 'analytics10` in director
# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY Product_Name;
-% sys.%3 # table_name
+% sys. # table_name
% totalsales # name
% bigint # type
% 3 # length
@@ -86,7 +86,7 @@ stdout of test 'analytics10` in director
# CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY Product_Category, Product_Name;
-% sys.%3 # table_name
+% sys. # table_name
% totalsales # name
% bigint # type
% 3 # length
@@ -98,7 +98,7 @@ stdout of test 'analytics10` in director
# Product_Category, CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY ROLLUP(Product_Category);
-% .tbl_productsales, .%5 # table_name
+% .tbl_productsales, . # table_name
% product_category, totalsales # name
% varchar, bigint # type
% 7, 4 # length
@@ -109,7 +109,7 @@ stdout of test 'analytics10` in director
# Product_Name, CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY ROLLUP(Product_Name);
-% .tbl_productsales, .%5 # table_name
+% .tbl_productsales, . # table_name
% product_name, totalsales # name
% varchar, bigint # type
% 9, 4 # length
@@ -122,7 +122,7 @@ stdout of test 'analytics10` in director
# Product_Category, Product_Name, CAST(SUM(TotalSales) as BIGINT) AS
TotalSales
#FROM tbl_ProductSales
#GROUP BY ROLLUP(Product_Category, Product_Name);
-% .tbl_productsales, .tbl_productsales, .%7 # table_name
+% .tbl_productsales, .tbl_productsales, . # table_name
% product_category, product_name, totalsales # name
% varchar, varchar, bigint # type
% 7, 9, 4 # length
@@ -137,7 +137,7 @@ stdout of test 'analytics10` in director
# Product_Category, Product_Name, CAST(SUM(TotalSales) as BIGINT) AS
TotalSales
#FROM tbl_ProductSales
#GROUP BY ROLLUP(Product_Category, Product_Name) HAVING SUM(TotalSales) > 400;
-% .tbl_productsales, .tbl_productsales, .%10 # table_name
+% .tbl_productsales, .tbl_productsales, . # table_name
% product_category, product_name, totalsales # name
% varchar, varchar, bigint # type
% 7, 5, 4 # length
@@ -148,7 +148,7 @@ stdout of test 'analytics10` in director
#SELECT
# Product_Category, CAST(SUM(TotalSales) as BIGINT) AS TotalSales FROM
tbl_ProductSales
#GROUP BY CUBE(Product_Category);
-% .tbl_productsales, .%5 # table_name
+% .tbl_productsales, . # table_name
% product_category, totalsales # name
% varchar, bigint # type
% 7, 4 # length
@@ -159,7 +159,7 @@ stdout of test 'analytics10` in director
# Product_Name, CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY CUBE(Product_Name);
-% .tbl_productsales, .%5 # table_name
+% .tbl_productsales, . # table_name
% product_name, totalsales # name
% varchar, bigint # type
% 9, 4 # length
@@ -172,7 +172,7 @@ stdout of test 'analytics10` in director
# Product_Category, Product_Name, CAST(SUM(TotalSales) as BIGINT) AS
TotalSales
#FROM tbl_ProductSales
#GROUP BY CUBE(Product_Category, Product_Name);
-% .tbl_productsales, .tbl_productsales, .%10 # table_name
+% .tbl_productsales, .tbl_productsales, . # table_name
% product_category, product_name, totalsales # name
% varchar, varchar, bigint # type
% 7, 9, 4 # length
@@ -210,7 +210,7 @@ stdout of test 'analytics10` in director
# Product_Category, Product_Name, CAST(SUM(TotalSales) as BIGINT) AS
TotalSales
#FROM tbl_ProductSales
#GROUP BY ColID, ROLLUP(Product_Category, Product_Name);
-% .tbl_productsales, .tbl_productsales, .%7 # table_name
+% .tbl_productsales, .tbl_productsales, . # table_name
% product_category, product_name, totalsales # name
% varchar, varchar, bigint # type
% 7, 9, 3 # length
@@ -230,7 +230,7 @@ stdout of test 'analytics10` in director
# Product_Category, Product_Name, CAST(SUM(TotalSales) as BIGINT) AS
TotalSales
#FROM tbl_ProductSales
#GROUP BY (ColID), ROLLUP(Product_Category, Product_Name);
-% .tbl_productsales, .tbl_productsales, .%7 # table_name
+% .tbl_productsales, .tbl_productsales, . # table_name
% product_category, product_name, totalsales # name
% varchar, varchar, bigint # type
% 7, 9, 3 # length
@@ -250,7 +250,7 @@ stdout of test 'analytics10` in director
# Product_Category, Product_Name, CAST(SUM(TotalSales) as BIGINT) AS
TotalSales
#FROM tbl_ProductSales
#GROUP BY (ColID), CUBE(Product_Category, Product_Name);
-% .tbl_productsales, .tbl_productsales, .%10 # table_name
+% .tbl_productsales, .tbl_productsales, . # table_name
% product_category, product_name, totalsales # name
% varchar, varchar, bigint # type
% 7, 9, 3 # length
@@ -274,7 +274,7 @@ stdout of test 'analytics10` in director
# Product_Category, CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY ROLLUP(Product_Category), ROLLUP(Product_Category);
-% .tbl_productsales, .%7 # table_name
+% .tbl_productsales, . # table_name
% product_category, totalsales # name
% varchar, bigint # type
% 7, 4 # length
@@ -289,7 +289,7 @@ stdout of test 'analytics10` in director
# Product_Category, CAST(SUM(TotalSales) as BIGINT) AS TotalSales
#FROM tbl_ProductSales
#GROUP BY CUBE(Product_Category), CUBE(Product_Category);
-% .tbl_productsales, .%7 # table_name
+% .tbl_productsales, . # table_name
% product_category, totalsales # name
% varchar, bigint # type
% 7, 4 # length
@@ -304,7 +304,7 @@ stdout of test 'analytics10` in director
# Product_Category, Product_Name, CAST(SUM(TotalSales) as BIGINT) AS
TotalSales
#FROM tbl_ProductSales
#GROUP BY ROLLUP(Product_Category, Product_Name), ROLLUP(Product_Category,
Product_Name);
-% .tbl_productsales, .tbl_productsales, .%15 # table_name
+% .tbl_productsales, .tbl_productsales, . # table_name
% product_category, product_name, totalsales # name
% varchar, varchar, bigint # type
% 7, 9, 4 # length
@@ -339,7 +339,7 @@ stdout of test 'analytics10` in director
# Product_Category, Product_Name, CAST(SUM(TotalSales) as BIGINT) AS
TotalSales
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
