Changeset: 5661a18ab53c for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5661a18ab53c Added Files: sql/test/merge-statements/Tests/mergestmt04.sql sql/test/merge-statements/Tests/mergestmt04.stable.err sql/test/merge-statements/Tests/mergestmt04.stable.out sql/test/miscellaneous/Tests/groupings.sql sql/test/miscellaneous/Tests/groupings.stable.err sql/test/miscellaneous/Tests/groupings.stable.out Modified Files: sql/test/merge-statements/Tests/All sql/test/miscellaneous/Tests/All Branch: linear-hashing Log Message:
Added tests for the recent blogpost demonstrations diffs (truncated from 350 to 300 lines): diff --git a/sql/test/merge-statements/Tests/All b/sql/test/merge-statements/Tests/All --- a/sql/test/merge-statements/Tests/All +++ b/sql/test/merge-statements/Tests/All @@ -2,3 +2,4 @@ mergestmt00 mergestmt01 mergestmt02 mergestmt03 +mergestmt04 diff --git a/sql/test/merge-statements/Tests/mergestmt04.sql b/sql/test/merge-statements/Tests/mergestmt04.sql new file mode 100644 --- /dev/null +++ b/sql/test/merge-statements/Tests/mergestmt04.sql @@ -0,0 +1,41 @@ +CREATE TABLE food_store (id INT, "name" TEXT, new_item BOOL); +INSERT INTO food_store VALUES (1, 'bread', false), (2, 'coffee', false), (3, 'tea', false), (4, 'butter', false), (5, 'chocolate', false); + +CREATE TABLE releases (id INT, reason CLOB); +INSERT INTO releases VALUES (5, 'too warm to eat chocolate'), (6, 'end of chestnuts season'); + +CREATE TABLE incoming (id INT, "name" CLOB); +INSERT INTO incoming VALUES (6, 'ice cream'); + +MERGE INTO food_store AS to_update USING releases AS food_updates + ON to_update.id = food_updates.id + WHEN MATCHED THEN DELETE; + +MERGE INTO food_store USING (SELECT id, "name" FROM incoming) AS new_food + ON food_store.id = new_food.id + WHEN NOT MATCHED THEN INSERT VALUES (new_food.id, new_food."name", true); + +SELECT id, "name", new_item FROM food_store; + +TRUNCATE incoming; +INSERT INTO incoming VALUES (1, 'cookies'), (2, 'cake'), (7, 'peanuts'); + +MERGE INTO food_store USING (SELECT id, "name" FROM incoming) AS food_updates + ON food_store.id = food_updates.id + WHEN NOT MATCHED THEN INSERT VALUES (food_updates.id, food_updates."name", true) + WHEN MATCHED THEN UPDATE SET "name" = food_updates."name", new_item = true; + +SELECT id, "name", new_item FROM food_store; + +TRUNCATE releases; +INSERT INTO releases VALUES (1, 'chocolate cookies'), (1, 'gluten cookies'); + +MERGE INTO food_store USING (SELECT id, reason FROM releases) AS food_updates + ON food_store.id = food_updates.id + WHEN MATCHED THEN UPDATE SET new_item = true; --error, more than one row match + +SELECT id, "name", new_item FROM food_store; + +DROP TABLE food_store; +DROP TABLE releases; +DROP TABLE incoming; diff --git a/sql/test/merge-statements/Tests/mergestmt04.stable.err b/sql/test/merge-statements/Tests/mergestmt04.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/merge-statements/Tests/mergestmt04.stable.err @@ -0,0 +1,18 @@ +stderr of test 'mergestmt04` in directory 'sql/test/merge-statements` itself: + + +# 14:43:05 > +# 14:43:05 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-100215" "--port=37878" +# 14:43:05 > + +MAPI = (monetdb) /var/tmp/mtest-100215/.s.monetdb.37878 +QUERY = MERGE INTO food_store USING (SELECT id, reason FROM releases) AS food_updates + ON food_store.id = food_updates.id + WHEN MATCHED THEN UPDATE SET new_item = true; --error, more than one row match +ERROR = !MERGE UPDATE: Multiple rows in the input relation 'food_updates' match the same row in the target table 'sys.food_store' +CODE = M0M29 + +# 14:43:05 > +# 14:43:05 > "Done." +# 14:43:05 > + diff --git a/sql/test/merge-statements/Tests/mergestmt04.stable.out b/sql/test/merge-statements/Tests/mergestmt04.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/merge-statements/Tests/mergestmt04.stable.out @@ -0,0 +1,77 @@ +stdout of test 'mergestmt04` in directory 'sql/test/merge-statements` itself: + + +# 14:43:05 > +# 14:43:05 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-100215" "--port=37878" +# 14:43:05 > + +#CREATE TABLE food_store (id INT, name TEXT, new_item BOOL); +#INSERT INTO food_store VALUES (1, 'bread', false), (2, 'coffee', false), (3, 'tea', false), (4, 'butter', false), (5, 'chocolate', false); +[ 5 ] +#CREATE TABLE releases (id INT, reason CLOB); +#INSERT INTO releases VALUES (5, 'too warm to eat chocolate'), (6, 'end of chestnuts season'); +[ 2 ] +#CREATE TABLE incoming (id INT, name CLOB); +#INSERT INTO incoming VALUES (6, 'ice cream'); +[ 1 ] +#MERGE INTO food_store AS to_update USING releases AS food_updates +# ON to_update.id = food_updates.id +# WHEN MATCHED THEN DELETE; +[ 1 ] +#MERGE INTO food_store USING (SELECT id, name FROM incoming) AS new_food +# ON food_store.id = new_food.id +# WHEN NOT MATCHED THEN INSERT VALUES (new_food.id, new_food.name, true); +[ 1 ] +#SELECT id, name, new_item FROM food_store; +% sys.food_store, sys.food_store, sys.food_store # table_name +% id, name, new_item # name +% int, clob, boolean # type +% 1, 9, 5 # length +[ 1, "bread", false ] +[ 2, "coffee", false ] +[ 3, "tea", false ] +[ 4, "butter", false ] +[ 6, "ice cream", true ] +#TRUNCATE incoming; +[ 1 ] +#INSERT INTO incoming VALUES (1, 'cookies'), (2, 'cake'), (7, 'peanuts'); +[ 3 ] +#MERGE INTO food_store USING (SELECT id, name FROM incoming) AS food_updates +# ON food_store.id = food_updates.id +# WHEN NOT MATCHED THEN INSERT VALUES (food_updates.id, food_updates.name, true) +# WHEN MATCHED THEN UPDATE SET name = food_updates.name, new_item = true; +[ 3 ] +#SELECT id, name, new_item FROM food_store; +% sys.food_store, sys.food_store, sys.food_store # table_name +% id, name, new_item # name +% int, clob, boolean # type +% 1, 9, 5 # length +[ 1, "cookies", true ] +[ 2, "cake", true ] +[ 3, "tea", false ] +[ 4, "butter", false ] +[ 6, "ice cream", true ] +[ 7, "peanuts", true ] +#TRUNCATE releases; +[ 2 ] +#INSERT INTO releases VALUES (1, 'chocolate cookies'), (1, 'gluten cookies'); +[ 2 ] +#SELECT id, name, new_item FROM food_store; +% sys.food_store, sys.food_store, sys.food_store # table_name +% id, name, new_item # name +% int, clob, boolean # type +% 1, 9, 5 # length +[ 1, "cookies", true ] +[ 2, "cake", true ] +[ 3, "tea", false ] +[ 4, "butter", false ] +[ 6, "ice cream", true ] +[ 7, "peanuts", true ] +#DROP TABLE food_store; +#DROP TABLE releases; +#DROP TABLE incoming; + +# 14:43:05 > +# 14:43:05 > "Done." +# 14:43:05 > + diff --git a/sql/test/miscellaneous/Tests/All b/sql/test/miscellaneous/Tests/All --- a/sql/test/miscellaneous/Tests/All +++ b/sql/test/miscellaneous/Tests/All @@ -10,5 +10,6 @@ groupby_error alter_table_add_column view-mul-digits select_groupby +groupings HAVE_PYMONETDB?pk_fk_stress deallocate diff --git a/sql/test/miscellaneous/Tests/groupings.sql b/sql/test/miscellaneous/Tests/groupings.sql new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/groupings.sql @@ -0,0 +1,51 @@ +START TRANSACTION; +CREATE TABLE categories (id INTEGER, "description" TEXT); +CREATE TABLE sections (id INTEGER, "description" TEXT); +CREATE TABLE products (id INTEGER, categoryid int, sectionid INTEGER, "description" TEXT, price DECIMAL(6,2)); +CREATE TABLE sales (productid INTEGER, sale_day DATE, units INTEGER); + +INSERT INTO categories VALUES (1, 'fresh food'), (2, 'dry food'), (3, 'drinks'); +INSERT INTO sections VALUES (1, 'front'), (2, 'side'), (3, 'back'); +INSERT INTO products VALUES (1, 1, 1, 'apples', 1.5), (2, 1, 2, 'melons', 4.0), (3, 2, 2, 'peanuts', 2.0), (4, 3, 1, 'water', 1.0), (5, 3, 3, 'wine', 5.0), (6, 2, 3, 'walnuts', 1.5); +INSERT INTO sales VALUES (1, date '2020-03-01', 10), (2, date '2020-03-01', 3), (4, date '2020-03-01', 4), (1, date '2020-03-02', 6), (4, date '2020-03-02', 5), (5, date '2020-03-02', 2), (1, date '2020-03-03', 7), (3, date '2020-03-03', 4), (2, date '2020-03-03', 3), (5, date '2020-03-03', 1), (6, date '2020-03-03', 1); + +SELECT COALESCE(products."description", 'all_products') AS "description", + COALESCE(sale_day, 'all_days') AS sale_day, + CAST(totals.total AS DECIMAL (12,4)) +FROM ( + SELECT productid, sale_day, SUM(units * price) AS total + FROM products + LEFT JOIN sales ON sales.productid = products.id + GROUP BY ROLLUP(productid, sale_day) +) AS totals +LEFT JOIN products ON products.id = totals.productid +ORDER BY sale_day NULLS LAST, productid NULLS LAST; + +SELECT COALESCE(sections."description", 'all_sections') AS section, + COALESCE(categories."description", 'all_categories') AS category, + CAST(totals.total AS DECIMAL (12,4)) +FROM ( + SELECT categoryid, sectionid, SUM(units * price) AS total + FROM products + LEFT JOIN sales ON sales.productid = products.id + GROUP BY GROUPING SETS ((categoryid), (sectionid), ()) +) AS totals +LEFT JOIN categories ON totals.categoryid = categories.id +LEFT JOIN sections ON totals.sectionid = sections.id; + +CREATE VIEW sales_totals AS + SELECT GROUPING(categoryid) AS category_aggregates, + GROUPING(sectionid) AS section_aggregates, + categoryid, sectionid, SUM(units * price) AS total + FROM products + LEFT JOIN sales ON sales.productid = products.id + GROUP BY GROUPING SETS ((categoryid), (sectionid), ()); + +SELECT "description", CAST(total AS DECIMAL (12,4)) +FROM sales_totals +LEFT JOIN categories ON sales_totals.categoryid = categories.id +WHERE category_aggregates = 0; + +SELECT * FROM categories GROUP BY (); --error + +ROLLBACK; diff --git a/sql/test/miscellaneous/Tests/groupings.stable.err b/sql/test/miscellaneous/Tests/groupings.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/groupings.stable.err @@ -0,0 +1,16 @@ +stderr of test 'groupings` in directory 'sql/test/miscellaneous` itself: + + +# 14:32:50 > +# 14:32:50 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-98783" "--port=31431" +# 14:32:50 > + +MAPI = (monetdb) /var/tmp/mtest-99724/.s.monetdb.34876 +QUERY = SELECT * FROM categories GROUP BY (); --error +ERROR = !SELECT: cannot use non GROUP BY column 'id' in query results without an aggregate function +CODE = 42000 + +# 14:32:50 > +# 14:32:50 > "Done." +# 14:32:50 > + diff --git a/sql/test/miscellaneous/Tests/groupings.stable.out b/sql/test/miscellaneous/Tests/groupings.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/groupings.stable.out @@ -0,0 +1,99 @@ +stdout of test 'groupings` in directory 'sql/test/miscellaneous` itself: + + +# 14:32:50 > +# 14:32:50 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-98783" "--port=31431" +# 14:32:50 > + +#START TRANSACTION; +#CREATE TABLE categories (id INTEGER, "description" TEXT); +#CREATE TABLE sections (id INTEGER, "description" TEXT); +#CREATE TABLE products (id INTEGER, categoryid int, sectionid INTEGER, "description" TEXT, price DECIMAL(6,2)); +#CREATE TABLE sales (productid INTEGER, sale_day DATE, units INTEGER); +#INSERT INTO categories VALUES (1, 'fresh food'), (2, 'dry food'), (3, 'drinks'); +[ 3 ] +#INSERT INTO sections VALUES (1, 'front'), (2, 'side'), (3, 'back'); +[ 3 ] +#INSERT INTO products VALUES (1, 1, 1, 'apples', 1.5), (2, 1, 2, 'melons', 4.0), (3, 2, 2, 'peanuts', 2.0), (4, 3, 1, 'water', 1.0), (5, 3, 3, 'wine', 5.0), (6, 2, 3, 'walnuts', 1.5); +[ 6 ] +#INSERT INTO sales VALUES (1, date '2020-03-01', 10), (2, date '2020-03-01', 3), (4, date '2020-03-01', 4), (1, date '2020-03-02', 6), (4, date '2020-03-02', 5), (5, date '2020-03-02', 2), (1, date '2020-03-03', 7), (3, date '2020-03-03', 4), (2, date '2020-03-03', 3), (5, date '2020-03-03', 1), (6, date '2020-03-03', 1); +[ 11 ] +#SELECT COALESCE(products."description", 'all_products') AS "description", +# COALESCE(sale_day, 'all_days') AS sale_day, +# totals.total +#FROM ( +# SELECT productid, sale_day, SUM(units * price) AS total +# FROM products +# LEFT JOIN sales ON sales.productid = products.id +# GROUP BY ROLLUP(productid, sale_day) +#) AS totals +#LEFT JOIN products ON products.id = totals.productid +#ORDER BY sale_day NULLS LAST, productid NULLS LAST; +% ., ., .totals # table_name +% description, sale_day, total # name +% clob, char, decimal # type +% 12, 10, 14 # length +[ "apples", "2020-03-01", 15.0000 ] +[ "melons", "2020-03-01", 12.0000 ] +[ "water", "2020-03-01", 4.0000 ] +[ "apples", "2020-03-02", 9.0000 ] +[ "water", "2020-03-02", 5.0000 ] +[ "wine", "2020-03-02", 10.0000 ] +[ "apples", "2020-03-03", 10.5000 ] +[ "melons", "2020-03-03", 12.0000 ] +[ "peanuts", "2020-03-03", 8.0000 ] +[ "wine", "2020-03-03", 5.0000 ] +[ "walnuts", "2020-03-03", 1.5000 ] +[ "apples", "all_days", 34.5000 ] +[ "melons", "all_days", 24.0000 ] +[ "peanuts", "all_days", 8.0000 ] _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
