Changeset: f2aad6057a3f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f2aad6057a3f
Modified Files:
sql/server/rel_select.c
sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
sql/test/miscellaneous/Tests/simple_plans.stable.out
sql/test/miscellaneous/Tests/values.stable.err
sql/test/miscellaneous/Tests/values.stable.out
sql/test/subquery/Tests/subquery6.stable.err
sql/test/subquery/Tests/subquery6.stable.out
Branch: Jun2020
Log Message:
small fix in handling types in values lists
diffs (230 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
@@ -709,7 +709,7 @@ exp_values_set_supertype(mvc *sql, sql_e
}
ttpe = exp_subtype(e);
if (tpe && ttpe) {
- supertype(&super, tpe, ttpe);
+ supertype(&super, ttpe, tpe);
values->tpe = super;
tpe = &values->tpe;
} else {
diff --git
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
---
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
+++
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
@@ -88,7 +88,7 @@ stdout of test 'memory-consumption-query
% .plan # table_name
% rel # name
% clob # type
-% 2622 # length
+% 2588 # length
top N (
| project (
| | project (
@@ -169,7 +169,7 @@ top N (
| | | | | ) [ "a3"."t3pkcol" HASHCOL = "table1"."t1cold113" ],
| | | | | table(sys.table12) [ "table12"."t12cola1" ] COUNT
| | | | ) [ "table12"."t12cola1" = "table1"."t1cola1" ]
-| | | ) [ (((((((((((((((clob[char["table1"."t1cold1"]] as "table1"."t1cold1")
FILTER ilike (clob "%a%", clob "")) or ((clob[char["table1"."t1cola1"]] as
"table1"."t1cola1") FILTER ilike (clob "%a%", clob ""))) or
((clob[char["table1"."t1colb1"]] as "table1"."t1colb1") FILTER ilike (clob
"%a%", clob ""))) or ((clob[char["table1"."t1cola11"]] as "table1"."t1cola11")
FILTER ilike (clob "%business%", clob ""))) or ("table1"."t1colc91" >=
timestamp(7)[char(19) "2016-03-21 05:00:00"])) or ("table1"."t1cola101" =
tinyint "1")) or ((clob[char["table1"."t1cola12"]] as "table1"."t1cola12")
FILTER ilike (clob "%Vijay%", clob ""))) or ((clob[char["table2"."t2cola1"]] as
"table2"."t2cola1") ! FILTER ilike (clob "%gmail%", clob ""),
(clob[char["table2"."t2cola1"]] as "table2"."t2cola1") ! FILTER ilike (clob
"%yahoo%", clob ""))) or ((clob[char["table2"."t2cola1"]] as
"table2"."t2cola1") FILTER ilike (clob "%efequitygroup.com%", clob ""))) or
("table4"."t4cola1" = clob "Customer")) or ("table4"."
t4cola2" ! * = clob "NULL")) or ("table2"."t2cola81" >= date "2009-08-31")) or
((("table5"."t5cola1" = clob "BAT") or ((clob[char["table5"."t5cola2"]] as
"table5"."t5cola2") FILTER ilike (clob "%AUSTRALIA%", clob ""))) or
((clob[char["table5"."t5cola2"]] as "table5"."t5cola2") FILTER ilike (clob
"%Monet%", clob ""), "table5"."t5cola3" = clob "Facebook", "table5"."t5cola5" =
clob "new", "table5"."t5cola81" > date "2015-07-30"))) or
((("table10"."t10cola1" != clob "Completed", "table9"."t9cola1" = clob "Tasks",
"table9"."t9cola91" >= timestamp(7)[char(19) "2012-01-01 04:32:27"],
"table10"."t10cola91" <= timestamp(7)[char(19) "2013-01-01 04:32:27"]) or
("table9"."t9cola1" = clob "Events", timestamp(7)[char(19) "2012-01-01
04:32:27"] <= "table11"."t11cola91" <= timestamp(7)[char(19) "2013-01-01
04:32:27"] BETWEEN )) or ("table9"."t9cola1" = clob "Calls",
timestamp(7)[char(19) "2012-01-01 04:32:27"] <= "table10"."t10cola91" <=
timestamp(7)[char(19) "2013-01-01 04:32:27"] BETWEEN )), "tab
le1"."t1cold111" in (bigint "15842000014793046" as "%64"."%64", bigint
"15842000017701488" as "%65"."%65", bigint "15842000000024019" as "%66"."%66",
bigint "15842000000074007" as "%67"."%67", bigint "15842000009358096" as
"%70"."%70", bigint "15842000010487625" as "%71"."%71", bigint
"15842000006731919" as "%72"."%72", bigint "15842000002590112" as "%73"."%73",
bigint "15842000000019001" as "%74"."%74", bigint "15842000014923682" as
"%75"."%75", bigint "15842000027547249" as "%76"."%76")) or
("table12"."t12cola1" in (clob[bigint "15842000280111951"] as "%100"."%100",
clob[bigint "15842000280163015"] as "%101"."%101")) ]
+| | | ) [ (((((((((((((((clob[char["table1"."t1cold1"]] as "table1"."t1cold1")
FILTER ilike (clob "%a%", clob "")) or ((clob[char["table1"."t1cola1"]] as
"table1"."t1cola1") FILTER ilike (clob "%a%", clob ""))) or
((clob[char["table1"."t1colb1"]] as "table1"."t1colb1") FILTER ilike (clob
"%a%", clob ""))) or ((clob[char["table1"."t1cola11"]] as "table1"."t1cola11")
FILTER ilike (clob "%business%", clob ""))) or ("table1"."t1colc91" >=
timestamp(7)[char(19) "2016-03-21 05:00:00"])) or ("table1"."t1cola101" =
tinyint "1")) or ((clob[char["table1"."t1cola12"]] as "table1"."t1cola12")
FILTER ilike (clob "%Vijay%", clob ""))) or ((clob[char["table2"."t2cola1"]] as
"table2"."t2cola1") ! FILTER ilike (clob "%gmail%", clob ""),
(clob[char["table2"."t2cola1"]] as "table2"."t2cola1") ! FILTER ilike (clob
"%yahoo%", clob ""))) or ((clob[char["table2"."t2cola1"]] as
"table2"."t2cola1") FILTER ilike (clob "%efequitygroup.com%", clob ""))) or
("table4"."t4cola1" = clob "Customer")) or ("table4"."
t4cola2" ! * = clob "NULL")) or ("table2"."t2cola81" >= date "2009-08-31")) or
((("table5"."t5cola1" = clob "BAT") or ((clob[char["table5"."t5cola2"]] as
"table5"."t5cola2") FILTER ilike (clob "%AUSTRALIA%", clob ""))) or
((clob[char["table5"."t5cola2"]] as "table5"."t5cola2") FILTER ilike (clob
"%Monet%", clob ""), "table5"."t5cola3" = clob "Facebook", "table5"."t5cola5" =
clob "new", "table5"."t5cola81" > date "2015-07-30"))) or
((("table10"."t10cola1" != clob "Completed", "table9"."t9cola1" = clob "Tasks",
"table9"."t9cola91" >= timestamp(7)[char(19) "2012-01-01 04:32:27"],
"table10"."t10cola91" <= timestamp(7)[char(19) "2013-01-01 04:32:27"]) or
("table9"."t9cola1" = clob "Events", timestamp(7)[char(19) "2012-01-01
04:32:27"] <= "table11"."t11cola91" <= timestamp(7)[char(19) "2013-01-01
04:32:27"] BETWEEN )) or ("table9"."t9cola1" = clob "Calls",
timestamp(7)[char(19) "2012-01-01 04:32:27"] <= "table10"."t10cola91" <=
timestamp(7)[char(19) "2013-01-01 04:32:27"] BETWEEN )), "tab
le1"."t1cold111" in (bigint "15842000014793046" as "%64"."%64", bigint
"15842000017701488" as "%65"."%65", bigint "15842000000024019" as "%66"."%66",
bigint "15842000000074007" as "%67"."%67", bigint "15842000009358096" as
"%70"."%70", bigint "15842000010487625" as "%71"."%71", bigint
"15842000006731919" as "%72"."%72", bigint "15842000002590112" as "%73"."%73",
bigint "15842000000019001" as "%74"."%74", bigint "15842000014923682" as
"%75"."%75", bigint "15842000027547249" as "%76"."%76")) or
("table12"."t12cola1" in (clob[bigint "15842000280111951"], clob[bigint
"15842000280163015"])) ]
| | ) [ "table1"."t1pkcol" NOT NULL HASHCOL , "table1"."t1cola82",
"table2"."t2cola10", "table1"."t1cola1", "table1"."t1cola91", "a1"."t3cola1",
"table2"."t2cola82" ]
| ) [ "table1"."t1pkcol" NOT NULL HASHCOL , "table1"."t1cola82",
"table2"."t2cola10", "table1"."t1cola1", "table1"."t1cola91", "a1"."t3cola1" ]
[ "table2"."t2cola82" NULLS LAST ]
) [ bigint "10", bigint "0" ]
diff --git a/sql/test/miscellaneous/Tests/simple_plans.stable.out
b/sql/test/miscellaneous/Tests/simple_plans.stable.out
--- a/sql/test/miscellaneous/Tests/simple_plans.stable.out
+++ b/sql/test/miscellaneous/Tests/simple_plans.stable.out
@@ -11,31 +11,31 @@ stdout of test 'simple_plans` in directo
% .plan # table_name
% rel # name
% clob # type
-% 134 # length
+% 138 # length
project (
| select (
| | table(sys.myx) [ "myx"."x", "myx"."y" ] COUNT
-| ) [ ("myx"."x" = uuid[char "1aea00e5db6e0810b554fde31d961965"]) or
("myx"."y" = uuid[char(32) "1aea00e5db6e0810b554fde31d961965"]) ]
+| ) [ ("myx"."x" = uuid[char(32) "1aea00e5db6e0810b554fde31d961965"]) or
("myx"."y" = uuid[char(32) "1aea00e5db6e0810b554fde31d961965"]) ]
) [ "myx"."x", "myx"."y" ]
#plan select * from myx where x in ('1aea00e5db6e0810b554fde31d961965') or y
is null;
% .plan # table_name
% rel # name
% clob # type
-% 98 # length
+% 102 # length
project (
| select (
| | table(sys.myx) [ "myx"."x", "myx"."y" ] COUNT
-| ) [ ("myx"."x" = uuid[char "1aea00e5db6e0810b554fde31d961965"]) or
("myx"."y" * = uuid "NULL") ]
+| ) [ ("myx"."x" = uuid[char(32) "1aea00e5db6e0810b554fde31d961965"]) or
("myx"."y" * = uuid "NULL") ]
) [ "myx"."x", "myx"."y" ]
#plan select * from myx where x in ('1aea00e5db6e0810b554fde31d961965',
'1aea00e5db6e0810b554fde31d961966') or y = '1aea00e5db6e0810b554fde31d961967';
% .plan # table_name
% rel # name
% clob # type
-% 210 # length
+% 192 # length
project (
| select (
| | table(sys.myx) [ "myx"."x", "myx"."y" ] COUNT
-| ) [ ("myx"."x" in (uuid[char "1aea00e5db6e0810b554fde31d961965"] as
"%2"."%2", uuid[char "1aea00e5db6e0810b554fde31d961966"] as "%3"."%3")) or
("myx"."y" = uuid[char(32) "1aea00e5db6e0810b554fde31d961967"]) ]
+| ) [ ("myx"."x" in (uuid[char(32) "1aea00e5db6e0810b554fde31d961965"],
uuid[char(32) "1aea00e5db6e0810b554fde31d961966"])) or ("myx"."y" =
uuid[char(32) "1aea00e5db6e0810b554fde31d961967"]) ]
) [ "myx"."x", "myx"."y" ]
#insert into myx values ('1aea00e5db6e0810b554fde31d961965',
'1aea00e5db6e0810b554fde31d961967');
[ 1 ]
diff --git a/sql/test/miscellaneous/Tests/values.stable.err
b/sql/test/miscellaneous/Tests/values.stable.err
--- a/sql/test/miscellaneous/Tests/values.stable.err
+++ b/sql/test/miscellaneous/Tests/values.stable.err
@@ -41,7 +41,11 @@ MAPI = (monetdb) /var/tmp/mtest-2671/.s
QUERY = values (1,2), (1), (3,3); --error
ERROR = !VALUES: number of columns doesn't match between rows
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-20225/.s.monetdb.30800
+MAPI = (monetdb) /var/tmp/mtest-25203/.s.monetdb.33709
+QUERY = values (1), ('ok');
+ERROR = !conversion of string 'ok' to type bte failed.
+CODE = 22018
+MAPI = (monetdb) /var/tmp/mtest-25203/.s.monetdb.33709
QUERY = with t1(a,b) as (values (1,1), (2,2)),
t2(a,b) as (values (2,4), (3,3,5))
select * from t1 inner join t2 on t1.a = t2.a; --error
@@ -51,7 +55,11 @@ MAPI = (monetdb) /var/tmp/mtest-20225/.
QUERY = with t1(a,b) as (select 1) select * from t1; --error
ERROR = !WITH CLAUSE: number of columns does not match
CODE = 21S02
-MAPI = (monetdb) /var/tmp/mtest-20225/.s.monetdb.30800
+MAPI = (monetdb) /var/tmp/mtest-25203/.s.monetdb.33709
+QUERY = with t1 as (select 1) values (3,4,5,6,7,'ok'), (6,8,1,2,'still','ok');
+ERROR = !conversion of string 'still' to type bte failed.
+CODE = 22018
+MAPI = (monetdb) /var/tmp/mtest-25203/.s.monetdb.33709
QUERY = create function foo() returns table (aa int, bb int) begin return
table(values (1,2), (3)); end; --error
ERROR = !VALUES: number of columns doesn't match between rows
CODE = 42000
diff --git a/sql/test/miscellaneous/Tests/values.stable.out
b/sql/test/miscellaneous/Tests/values.stable.out
--- a/sql/test/miscellaneous/Tests/values.stable.out
+++ b/sql/test/miscellaneous/Tests/values.stable.out
@@ -48,59 +48,52 @@ stdout of test 'values` in directory 'sq
[ 1, 2, 3 ]
[ 4, NULL, 6 ]
[ 7, 8, NULL ]
-#values (1), ('ok');
-% .%1 # table_name
-% %1 # name
-% char # type
-% 2 # length
-[ "1" ]
-[ "ok" ]
#values (1) union values (3);
-% .%7 # table_name
+% .%11 # table_name
% %1 # name
% tinyint # type
% 1 # length
[ 1 ]
[ 3 ]
#values (1,1) union values (1,1);
-% .%11, .%11 # table_name
+% .%15, .%15 # table_name
% %1, %2 # name
% tinyint, tinyint # type
% 1, 1 # length
[ 1, 1 ]
#values (1,2,3) union all values (1,2,3);
-% .%13, .%13, .%13 # table_name
+% .%21, .%21, .%21 # table_name
% %1, %2, %3 # name
% tinyint, tinyint, tinyint # type
% 1, 1, 1 # length
[ 1, 2, 3 ]
[ 1, 2, 3 ]
#values (3), (2) intersect values (3);
-% .%7 # table_name
+% .%12 # table_name
% %1 # name
% tinyint # type
% 1 # length
[ 3 ]
#values (1,2,3), (4,5,6) except select 1,2,4;
-% .%13, .%13, .%13 # table_name
+% .%21, .%21, .%21 # table_name
% %1, %2, %3 # name
% tinyint, tinyint, tinyint # type
% 1, 1, 1 # length
[ 1, 2, 3 ]
[ 4, 5, 6 ]
#values (1,2,3), (4,5,6) except select 1,2,3;
-% .%13, .%13, .%13 # table_name
+% .%21, .%21, .%21 # table_name
% %1, %2, %3 # name
% tinyint, tinyint, tinyint # type
% 1, 1, 1 # length
[ 4, 5, 6 ]
#select 'a', 'c' union select 'b', 'c' except values ('a', 'c'), ('b', 'c');
-% .%21, .%21 # table_name
+% .%24, .%24 # table_name
% %2, %3 # name
% char, char # type
% 1, 1 # length
#select 'a', 'c' union select 'b', 'c' except values ('a', 'c'), ('b', 'd');
-% .%21, .%21 # table_name
+% .%24, .%24 # table_name
% %2, %3 # name
% char, char # type
% 1, 1 # length
@@ -125,16 +118,9 @@ stdout of test 'values` in directory 'sq
% tinyint # type
% 1 # length
[ 2 ]
-#with t1 as (select 1) values (3,4,5,6,7,'ok'), (6,8,1,2,'still','ok');
-% .%1, .%2, .%3, .%4, .%5, .%6 # table_name
-% %1, %2, %3, %4, %5, %6 # name
-% tinyint, tinyint, tinyint, tinyint, char, char #
type
-% 1, 1, 1, 1, 5, 2 # length
-[ 3, 4, 5, 6, "7", "ok" ]
-[ 6, 8, 1, 2, "still", "ok" ]
#create function foo() returns table (aa int, bb int) begin return
table(values (1,2)); end;
#select aa, bb + 1 from foo();
-% .%1, .%3 # table_name
+% .%1, . # table_name
% aa, %3 # name
% int, bigint # type
% 1, 1 # length
diff --git a/sql/test/subquery/Tests/subquery6.stable.err
b/sql/test/subquery/Tests/subquery6.stable.err
--- a/sql/test/subquery/Tests/subquery6.stable.err
+++ b/sql/test/subquery/Tests/subquery6.stable.err
@@ -27,6 +27,18 @@ MAPI = (monetdb) /var/tmp/mtest-18680/.
QUERY = SELECT 1 IN (col4, MIN(col2)) FROM another_t;
ERROR = !SELECT: cannot use non GROUP BY column 'col4' in query results
without an aggregate function
CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-25203/.s.monetdb.33709
+QUERY = SELECT (SELECT col1) IN ('not a number') FROM another_t;
+ERROR = !conversion of string 'not a number' to type int failed.
+CODE = 22018
+MAPI = (monetdb) /var/tmp/mtest-25203/.s.monetdb.33709
+QUERY = SELECT CASE WHEN ColID IS NULL THEN CAST(Product_Category AS INT) ELSE
TotalSales END FROM tbl_ProductSales;
+ERROR = !conversion of string 'Game' to type int failed.
+CODE = 22018
+MAPI = (monetdb) /var/tmp/mtest-25203/.s.monetdb.33709
+QUERY = SELECT ColID FROM tbl_ProductSales WHERE CASE WHEN ColID IS NULL THEN
CAST(Product_Category AS INT) ELSE TotalSales END;
+ERROR = !conversion of string 'Fashion' to type int failed.
+CODE = 22018
# 11:45:43 >
# 11:45:43 > "Done."
diff --git a/sql/test/subquery/Tests/subquery6.stable.out
b/sql/test/subquery/Tests/subquery6.stable.out
--- a/sql/test/subquery/Tests/subquery6.stable.out
+++ b/sql/test/subquery/Tests/subquery6.stable.out
@@ -38,6 +38,12 @@ stdout of test 'subquery6` in directory
% boolean # type
% 5 # length
[ false ]
+#SELECT (SELECT (SELECT SUM(col1)) IN (MAX(col2), '12')) FROM another_t;
+% .%6 # table_name
+% %6 # name
+% boolean # type
+% 5 # length
+[ false ]
#DROP TABLE tbl_ProductSales;
#DROP TABLE another_T;
#DROP TABLE integers;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
