Changeset: 477ff5c1d012 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=477ff5c1d012 Added Files: sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.sql sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.stable.err sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.stable.out sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.sql sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.stable.err sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.stable.out Modified Files: sql/test/BugTracker-2019/Tests/All sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.sql sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.stable.out Branch: Apr2019 Log Message:
merged diffs (truncated from 404 to 300 lines): diff --git a/sql/test/BugTracker-2019/Tests/All b/sql/test/BugTracker-2019/Tests/All --- a/sql/test/BugTracker-2019/Tests/All +++ b/sql/test/BugTracker-2019/Tests/All @@ -17,3 +17,5 @@ correlated-subquery-aggregation.Bug-6714 merge-statement-aliases.Bug-6715 insert-into-select.Bug-6718 copy-into-unicode-quote.Bug-6716 +select_window_function_and_asterisk.Bug-6722 +disallow_duplicate_column_aliases.Bug-6723 diff --git a/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.sql b/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.sql @@ -0,0 +1,8 @@ +select 1 as T, 2 as T; +select a.* from (select 1 as T, 2 as T) a; +with wa as (select 1 as T, 2 as T) select wa.* from wa; + +select 1,2,3 as "L2"; +select a.* from (select 1,2,3 as "L2") a; +with wa as (select 1,2,3 as "L2") select wa.* from wa; + diff --git a/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.stable.err b/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.stable.err @@ -0,0 +1,56 @@ +stderr of test 'disallow_duplicate_column_aliases.Bug-6723` in directory 'sql/test/BugTracker-2019` itself: + + +# 13:22:42 > +# 13:22:42 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=33477" "--set" "mapi_usock=/var/tmp/mtest-15745/.s.monetdb.33477" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2019" "--set" "embedded_c=true" +# 13:22:42 > + +# builtin opt gdk_dbpath = /export/scratch1/dinther/INSTALL/var/monetdb5/dbfarm/demo +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = false +# builtin opt mapi_autosense = false +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 33477 +# cmdline opt mapi_usock = /var/tmp/mtest-15745/.s.monetdb.33477 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2019 +# cmdline opt embedded_c = true + +# 13:22:43 > +# 13:22:43 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-15745" "--port=33477" +# 13:22:43 > + +MAPI = (monetdb) /var/tmp/mtest-15745/.s.monetdb.33477 +QUERY = select 1 as T, 2 as T; +ERROR = !SELECT: Duplicate column name or alias 'T' not allowed +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-15745/.s.monetdb.33477 +QUERY = select a.* from (select 1 as T, 2 as T) a; +ERROR = !SELECT: Duplicate column name or alias 'T' not allowed +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-15745/.s.monetdb.33477 +QUERY = with wa as (select 1 as T, 2 as T) select wa.* from wa; +ERROR = !SELECT: Duplicate column name or alias 'T' not allowed +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-15745/.s.monetdb.33477 +QUERY = select 1,2,3 as "L2"; +ERROR = !SELECT: Duplicate column name or alias 'L2' not allowed +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-15745/.s.monetdb.33477 +QUERY = select a.* from (select 1,2,3 as "L2") a; +ERROR = !SELECT: Duplicate column name or alias 'L2' not allowed +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-15745/.s.monetdb.33477 +QUERY = with wa as (select 1,2,3 as "L2") select wa.* from wa; +ERROR = !SELECT: Duplicate column name or alias 'L2' not allowed +CODE = 42000 + +# 13:22:43 > +# 13:22:43 > "Done." +# 13:22:43 > + diff --git a/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.stable.out b/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2019/Tests/disallow_duplicate_column_aliases.Bug-6723.stable.out @@ -0,0 +1,31 @@ +stdout of test 'disallow_duplicate_column_aliases.Bug-6723` in directory 'sql/test/BugTracker-2019` itself: + + +# 13:22:42 > +# 13:22:42 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=33477" "--set" "mapi_usock=/var/tmp/mtest-15745/.s.monetdb.33477" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2019" "--set" "embedded_c=true" +# 13:22:42 > + +# MonetDB 5 server v11.33.4 (hg id: 0e77729ef717+) +# This is an unreleased version +# Serving database 'mTests_sql_test_BugTracker-2019', using 4 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers +# Found 31.305 GiB available main-memory. +# Copyright (c) 1993 - July 2008 CWI. +# Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved +# Visit https://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://catskill.da.cwi.nl:33477/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-15745/.s.monetdb.33477 +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded + +Ready. + +# 13:22:43 > +# 13:22:43 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-15745" "--port=33477" +# 13:22:43 > + + +# 13:22:43 > +# 13:22:43 > "Done." +# 13:22:43 > + diff --git a/sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.sql b/sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.sql --- a/sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.sql +++ b/sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.sql @@ -1,5 +1,24 @@ -start transaction; CREATE TABLE ProductTarget (ProductID SERIAL, Name VARCHAR(100) NOT NULL, ProductNumber BIGINT, Color VARCHAR(30) NOT NULL); CREATE TABLE ProductSource (ProductID SERIAL, Name VARCHAR(100) NOT NULL, ProductNumber BIGINT, Color VARCHAR(30) NOT NULL); + MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID WHEN MATCHED THEN UPDATE SET Name = S.Name; -rollback; + +MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID WHEN MATCHED THEN UPDATE SET T.Name = S.Name, ProductTarget.ProductNumber = S.ProductNumber, ProductTarget.Color = S.Color; + +MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID WHEN NOT MATCHED THEN INSERT (T.ProductID, ProductTarget.ProductNumber, ProductTarget.Color, T.Name) VALUES (S.ProductID, S.ProductNumber, S.Color, S.Name); + +MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID + WHEN MATCHED THEN UPDATE SET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.Color + WHEN NOT MATCHED THEN INSERT (ProductID, ProductNumber, Color, Name) VALUES (S.ProductID, S.ProductNumber, Color, Name); + +MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID + WHEN MATCHED THEN UPDATE SET ProductTarget.Name = S.Name, ProductTarget.ProductNumber = S.ProductNumber, T.Color = S.Color + WHEN NOT MATCHED THEN INSERT (T.ProductID, ProductTarget.ProductNumber, ProductTarget.Color, T.Name) VALUES (ProductID, S.ProductNumber, S.Color, Name); + +MERGE INTO ProductTarget T USING ProductSource S ON T.ProductID = S.ProductID + WHEN MATCHED THEN UPDATE SET T.Name = S.Name, T.ProductNumber = ProductNumber, T.Color = Color + WHEN NOT MATCHED THEN INSERT (T.ProductID, T.ProductNumber, T.Color, T.Name) VALUES (ProductID, S.ProductNumber, S.Color, Name); + +DROP TABLE ProductTarget; +DROP TABLE ProductSource; + diff --git a/sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.stable.out b/sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.stable.out --- a/sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.stable.out +++ b/sql/test/BugTracker-2019/Tests/merge-statement-aliases.Bug-6715.stable.out @@ -16,46 +16,6 @@ stdout of test 'merge-statement-aliases. # Listening for connection requests on mapi:monetdb://localhost.localdomain:31635/ # Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-18243/.s.monetdb.31635 # MonetDB/GIS module loaded -# SQL catalog created, loading sql scripts once -# loading sql script: 09_like.sql -# loading sql script: 10_math.sql -# loading sql script: 11_times.sql -# loading sql script: 12_url.sql -# loading sql script: 13_date.sql -# loading sql script: 14_inet.sql -# loading sql script: 15_querylog.sql -# loading sql script: 16_tracelog.sql -# loading sql script: 17_temporal.sql -# loading sql script: 18_index.sql -# loading sql script: 20_vacuum.sql -# loading sql script: 21_dependency_views.sql -# loading sql script: 22_clients.sql -# loading sql script: 23_skyserver.sql -# loading sql script: 25_debug.sql -# loading sql script: 26_sysmon.sql -# loading sql script: 27_rejects.sql -# loading sql script: 39_analytics.sql -# loading sql script: 39_analytics_hge.sql -# loading sql script: 40_geom.sql -# loading sql script: 40_json.sql -# loading sql script: 40_json_hge.sql -# loading sql script: 41_md5sum.sql -# loading sql script: 45_uuid.sql -# loading sql script: 46_profiler.sql -# loading sql script: 51_sys_schema_extension.sql -# loading sql script: 60_wlcr.sql -# loading sql script: 72_fits.sql -# loading sql script: 74_netcdf.sql -# loading sql script: 75_lidar.sql -# loading sql script: 75_shp.sql -# loading sql script: 75_storagemodel.sql -# loading sql script: 80_statistics.sql -# loading sql script: 80_udf.sql -# loading sql script: 80_udf_hge.sql -# loading sql script: 85_bam.sql -# loading sql script: 90_generator.sql -# loading sql script: 90_generator_hge.sql -# loading sql script: 99_system.sql # MonetDB/SQL module loaded Ready. @@ -64,12 +24,29 @@ Ready. # 10:27:55 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-18243" "--port=31635" # 10:27:55 > -#start transaction; #CREATE TABLE ProductTarget (ProductID SERIAL, Name VARCHAR(100) NOT NULL, ProductNumber BIGINT, Color VARCHAR(30) NOT NULL); #CREATE TABLE ProductSource (ProductID SERIAL, Name VARCHAR(100) NOT NULL, ProductNumber BIGINT, Color VARCHAR(30) NOT NULL); #MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID WHEN MATCHED THEN UPDATE SET Name = S.Name; [ 0 ] -#rollback; +#MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID WHEN MATCHED THEN UPDATE SET T.Name = S.Name, ProductTarget.ProductNumber = S.ProductNumber, ProductTarget.Color = S.Color; +[ 0 ] +#MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID WHEN NOT MATCHED THEN INSERT (T.ProductID, ProductTarget.ProductNumber, ProductTarget.Color, T.Name) VALUES (S.ProductID, S.ProductNumber, S.Color, S.Name); +[ 0 ] +#MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID +# WHEN MATCHED THEN UPDATE SET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.Color +# WHEN NOT MATCHED THEN INSERT (ProductID, ProductNumber, Color, Name) VALUES (S.ProductID, S.ProductNumber, Color, Name); +[ 0 ] +#MERGE INTO ProductTarget T USING ProductSource S ON S.ProductID = T.ProductID +# WHEN MATCHED THEN UPDATE SET ProductTarget.Name = S.Name, ProductTarget.ProductNumber = S.ProductNumber, T.Color = S.Color +# WHEN NOT MATCHED THEN INSERT (T.ProductID, ProductTarget.ProductNumber, ProductTarget.Color, T.Name) VALUES (ProductID, S.ProductNumber, S.Color, Name); +[ 0 ] +#MERGE INTO ProductTarget T USING ProductSource S ON T.ProductID = S.ProductID +# WHEN MATCHED THEN UPDATE SET T.Name = S.Name, T.ProductNumber = ProductNumber, T.Color = Color +# WHEN NOT MATCHED THEN INSERT (T.ProductID, T.ProductNumber, T.Color, T.Name) VALUES (ProductID, S.ProductNumber, S.Color, Name); +[ 0 ] +#DROP TABLE ProductTarget; +#DROP TABLE ProductSource; + # 10:27:55 > # 10:27:55 > "Done." diff --git a/sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.sql b/sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.sql @@ -0,0 +1,26 @@ +create table test_6722 (part string, qty double); +insert into test_6722 values ('a', 18), ('a', 13),('a', 16),('b', 15),('b', 16),('c', 17),('c', 18),('c', 12),('d', 12),('d', 12); + +select + dense_rank() over (order by part, qty) as rank_id, + row_number() over (order by part, qty) as row_id, + * +from test_6722 +order by part, qty; + +select + (dense_rank() over (order by part, qty)) as rank_id, + *, + (row_number() over (order by part, qty)) as row_id +from test_6722 +order by part, qty; + +select + *, + (dense_rank() over (order by part, qty)) as rank_id, + (row_number() over (order by part, qty)) as row_id +from test_6722 +order by part, qty; + +drop table test_6722; + diff --git a/sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.stable.err b/sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2019/Tests/select_window_function_and_asterisk.Bug-6722.stable.err @@ -0,0 +1,35 @@ +stderr of test 'select_window_function_and_asterisk.Bug-6722` in directory 'sql/test/BugTracker-2019` itself: + + +# 13:01:42 > +# 13:01:42 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=31714" "--set" "mapi_usock=/var/tmp/mtest-13851/.s.monetdb.31714" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2019" "--set" "embedded_r=yes" "--set" "embedded_py=2" "--set" "embedded_c=true" +# 13:01:42 > + +# builtin opt gdk_dbpath = /export/scratch1/dinther/INSTALL/var/monetdb5/dbfarm/demo +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = false +# builtin opt mapi_autosense = false +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 31714 +# cmdline opt mapi_usock = /var/tmp/mtest-13851/.s.monetdb.31714 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2019 +# cmdline opt embedded_r = yes +# cmdline opt embedded_py = 2 +# cmdline opt embedded_c = true +#main thread:!ERROR:MALException:client.quit:Server stopped + +# 13:01:42 > +# 13:01:42 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-13851" "--port=31714" +# 13:01:42 > + + +# 13:01:42 > +# 13:01:42 > "Done." _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
