Changeset: 338d50e74f15 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=338d50e74f15 Modified Files: sql/test/subquery/Tests/subquery4.sql sql/test/subquery/Tests/subquery4.stable.out Branch: default Log Message:
Update statement crashing :( diffs (67 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 @@ -146,12 +146,26 @@ UPDATE another_T SET col5 = 1 WHERE col5 DELETE FROM another_T WHERE col1 = (SELECT 1 UNION ALL SELECT 2); --error, more than one row returned by a subquery used as an expression INSERT INTO another_T VALUES ((SELECT 1 UNION ALL SELECT 2),2,3,4,5,6,7,8); --error, more than one row returned by a subquery used as an expression -UPDATE another_T SET (col5, col6) = (SELECT MIN(10), MAX(21) OVER ()); --4 rows affected +UPDATE another_T SET (col5, col6) = (SELECT MIN(10), MAX(col5) OVER (PARTITION BY col1)); --4 rows affected + +SELECT col5, col6 FROM another_T; --Postgresql uses the updated value of col5 to update col6, but MonetDB and SQLite use the old value of col5, which makes more sense + -- 10 5 + -- 10 55 + -- 10 555 + -- 10 5555 + UPDATE another_T SET (col7, col8) = (SELECT 1,2 UNION ALL SELECT 1,2); --error, more than one row returned by a subquery used as an expression UPDATE another_T SET (col7, col8) = (SELECT 1 UNION ALL SELECT 2); --error, number of columns does not match number of values UPDATE another_T SET (col7, col8) = (SELECT 1,2,3); --error, number of columns does not match number of values UPDATE another_T SET col5 = 1, col5 = 6; --error, multiple assignments to same column "col5" UPDATE another_T SET (col5, col6) = ((select 1,2)), col5 = 6; --error, multiple assignments to same column "col5" +UPDATE another_T SET (col5, col6) = (SELECT MIN(col1), MAX(col2)); --error, aggregate functions are not allowed in UPDATE + +UPDATE another_T SET (col7, col8) = (SELECT NTILE(col1) OVER (), MAX(col3) OVER (PARTITION BY col4)); --4 rows affected +UPDATE another_T t1 SET (col1, col2) = (SELECT MIN(t1.col3 + tb.ColID), MAX(tb.ColID) FROM tbl_ProductSales tb); --4 rows affected +UPDATE another_T t1 SET (col3, col4) = (SELECT COUNT(tb.ColID), SUM(tb.ColID) FROM tbl_ProductSales tb); --4 rows affected + +SELECT col1, col2, col3, col4, col7, col8 FROM another_T; DECLARE x int; SET x = MAX(1) over (); --error, not allowed 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 @@ -175,6 +175,15 @@ stdout of test 'subquery4` in directory [ 0 ] #UPDATE another_T SET (col5, col6) = (SELECT MIN(10), MAX(21) OVER ()); --4 rows affected [ 4 ] +#SELECT col5, col6 FROM another_T; --Postgresql uses the updated value of col5 to update col6, but MonetDB and SQLite use the old value of col5, which makes more sense +% sys.another_t, sys.another_t # table_name +% col5, col6 # name +% int, int # type +% 2, 4 # length +[ 10, 5 ] +[ 10, 55 ] +[ 10, 555 ] +[ 10, 5555 ] #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 @@ -183,11 +192,11 @@ stdout of test 'subquery4` in directory % 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, 2, 2, 4, 4 # length -[ 1, 2, 5, 26, 10, 21, 7, 8 ] -[ 11, 22, 55, 186, 10, 21, 77, 88 ] -[ 111, 222, 555, 1786, 10, 21, 777, 888 ] -[ 1111, 2222, 5555, 17786, 10, 21, 7777, 8888 ] +% 4, 4, 4, 5, 2, 4, 4, 4 # length +[ 1, 2, 5, 26, 10, 5, 7, 8 ] +[ 11, 22, 55, 186, 10, 55, 77, 88 ] +[ 111, 222, 555, 1786, 10, 555, 777, 888 ] +[ 1111, 2222, 5555, 17786, 10, 5555, 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; _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
