Changeset: 916c851736c6 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=916c851736c6 Modified Files: sql/test/subquery/Tests/subquery4.sql sql/test/subquery/Tests/subquery4.stable.out Branch: default Log Message:
Approved output diffs (112 lines): diff --git a/sql/test/subquery/Tests/subquery4.sql b/sql/test/subquery/Tests/subquery4.sql --- a/sql/test/subquery/Tests/subquery4.sql +++ b/sql/test/subquery/Tests/subquery4.sql @@ -105,7 +105,14 @@ FROM integers i1; UPDATE another_T SET col1 = MIN(col1); --error, aggregates not allowed in update set clause UPDATE another_T SET col2 = 1 WHERE col1 = SUM(col2); --error, aggregates not allowed in update set clause UPDATE another_T SET col3 = (SELECT MAX(col5)); --error, aggregates not allowed in update set clause -UPDATE another_T SET col4 = (SELECT SUM(col4 + ColID) FROM tbl_ProductSales); +UPDATE another_T SET col4 = (SELECT SUM(col4 + ColID) FROM tbl_ProductSales); --4 rows affected + +SELECT col4 FROM another_T; + -- 26 + -- 186 + -- 1786 + -- 17786 + UPDATE another_T SET col5 = 1 WHERE col5 = (SELECT AVG(col2)); --error, aggregates not allowed in where clause UPDATE another_T SET col6 = 1 WHERE col6 = (SELECT COUNT(col3 + ColID) FROM tbl_ProductSales); UPDATE another_T SET col8 = (SELECT 1 FROM integers i2 WHERE AVG(i2.i)); --error, aggregates not allowed in update set clause @@ -113,18 +120,25 @@ UPDATE another_T SET col7 = 1 WHERE col5 DELETE FROM another_T WHERE col1 = COUNT(col2); --error, aggregates not allowed in where clause DELETE FROM another_T WHERE col7 = (SELECT MIN(col3)); --error, aggregates not allowed in where clause -DELETE FROM another_T WHERE col8 = (SELECT AVG(col6 + ColID) FROM tbl_ProductSales); +DELETE FROM another_T WHERE col8 = (SELECT AVG(col6 + ColID) FROM tbl_ProductSales); --0 rows affected DELETE FROM another_T WHERE col2 = (SELECT 1 FROM integers i2 WHERE AVG(i2.i)); --error, aggregates not allowed in where clause UPDATE another_T SET col1 = AVG(col1) OVER (); --error, window functions not allowed in update set clause UPDATE another_T SET col2 = 1 WHERE col1 = COUNT(col2) OVER (); --error, window functions not allowed in where clause -UPDATE another_T SET col3 = (SELECT SUM(col5) OVER ()); +UPDATE another_T SET col3 = (SELECT SUM(col5) OVER ()); --4 rows affected + +SELECT col3 FROM another_T; + -- 5 + -- 55 + -- 555 + -- 5555 + UPDATE another_T SET col4 = (SELECT MIN(col4 + ColID) OVER () FROM tbl_ProductSales); --error, more than one row returned by a subquery used as an expression -UPDATE another_T SET col5 = 1 WHERE col5 = (SELECT MAX(col2) OVER ()); +UPDATE another_T SET col5 = 1 WHERE col5 = (SELECT MAX(col2) OVER ()); --0 rows affected UPDATE another_T SET col6 = 1 WHERE col6 = (SELECT MIN(col3 + ColID) OVER () FROM tbl_ProductSales); --error, more than one row returned by a subquery used as an expression DELETE FROM another_T WHERE col1 = AVG(col2) OVER (); --error, window functions not allowed in where clause -DELETE FROM another_T WHERE col7 = (SELECT SUM(col3) OVER ()); +DELETE FROM another_T WHERE col7 = (SELECT SUM(col3) OVER ()); --0 rows affected DELETE FROM another_T WHERE col8 = (SELECT MAX(col6 + ColID) OVER () FROM tbl_ProductSales); --error, more than one row returned by a subquery used as an expression DECLARE x int; @@ -134,6 +148,9 @@ SET y = MIN(1); --error, not allowed INSERT INTO another_T VALUES (SUM(1),2,3,4,5,6,7,8); --error, not allowed INSERT INTO another_T VALUES (AVG(1) OVER (),2,3,4,5,6,7,8); --error, not allowed +INSERT INTO another_T VALUES ((SELECT SUM(1)),(SELECT SUM(2) OVER ()),3,4,5,6,7,8); --allowed + +SELECT * FROM another_T; CREATE PROCEDURE crashme(a int) BEGIN DECLARE x INT; SET x = a; END; diff --git a/sql/test/subquery/Tests/subquery4.stable.out b/sql/test/subquery/Tests/subquery4.stable.out --- a/sql/test/subquery/Tests/subquery4.stable.out +++ b/sql/test/subquery/Tests/subquery4.stable.out @@ -137,18 +137,48 @@ stdout of test 'subquery4` in directory [ 3 ] #UPDATE another_T SET col4 = (SELECT SUM(col4 + ColID) FROM tbl_ProductSales); [ 4 ] +#SELECT col4 FROM another_T; +% sys.another_t # table_name +% col4 # name +% int # type +% 5 # length +[ 26 ] +[ 186 ] +[ 1786 ] +[ 17786 ] #UPDATE another_T SET col6 = 1 WHERE col6 = (SELECT COUNT(col3 + ColID) FROM tbl_ProductSales); [ 0 ] #DELETE FROM another_T WHERE col8 = (SELECT AVG(col6 + ColID) FROM tbl_ProductSales); [ 0 ] #UPDATE another_T SET col3 = (SELECT SUM(col5) OVER ()); [ 4 ] -#UPDATE another_T SET col5 = 1 WHERE col5 = (SELECT MAX(col2) OVER ()); +#SELECT col3 FROM another_T; +% sys.another_t # table_name +% col3 # name +% int # type +% 4 # length +[ 5 ] +[ 55 ] +[ 555 ] +[ 5555 ] +#UPDATE another_T SET col5 = 1 WHERE col5 = (SELECT MAX(col2) OVER ()); --0 rows affected [ 0 ] #DELETE FROM another_T WHERE col7 = (SELECT SUM(col3) OVER ()); [ 0 ] #DECLARE x int; #DECLARE y int; +#INSERT INTO another_T VALUES ((SELECT SUM(1)),(SELECT SUM(2) OVER ()),3,4,5,6,7,8); --allowed +[ 1 ] +#SELECT * FROM another_T; +% sys.another_t, sys.another_t, sys.another_t, sys.another_t, sys.another_t, sys.another_t, sys.another_t, sys.another_t # table_name +% col1, col2, col3, col4, col5, col6, col7, col8 # name +% int, int, int, int, int, int, int, int # type +% 4, 4, 4, 5, 4, 4, 4, 4 # length +[ 1, 2, 5, 26, 5, 6, 7, 8 ] +[ 11, 22, 55, 186, 55, 66, 77, 88 ] +[ 111, 222, 555, 1786, 555, 666, 777, 888 ] +[ 1111, 2222, 5555, 17786, 5555, 6666, 7777, 8888 ] +[ 1, 2, 3, 4, 5, 6, 7, 8 ] #CREATE PROCEDURE crashme(a int) BEGIN DECLARE x INT; SET x = a; END; #create sequence "debugme" as integer start with 1; #alter sequence "debugme" restart with (select MAX(1)); _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
