Changeset: 7aa85e613da6 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7aa85e613da6 Added Files: sql/test/analytics/Tests/analytics18.sql sql/test/analytics/Tests/analytics18.stable.err sql/test/analytics/Tests/analytics18.stable.out Modified Files: sql/test/analytics/Tests/All Branch: Jun2020 Log Message:
Added test for recent bugfix diffs (159 lines): diff --git a/sql/test/analytics/Tests/All b/sql/test/analytics/Tests/All --- a/sql/test/analytics/Tests/All +++ b/sql/test/analytics/Tests/All @@ -21,3 +21,6 @@ analytics14 analytics15 analytics16 analytics17 + +# Other tests +analytics18 diff --git a/sql/test/analytics/Tests/analytics18.sql b/sql/test/analytics/Tests/analytics18.sql new file mode 100644 --- /dev/null +++ b/sql/test/analytics/Tests/analytics18.sql @@ -0,0 +1,59 @@ +START TRANSACTION; +CREATE TABLE tab1 ( + col1 varchar(100) NOT NULL, + col2 varchar(100) NOT NULL, + col3 int NOT NULL, + col4 int, + col5 int, + col6 varchar(20), + col11 decimal(13,4) +); + +CREATE SEQUENCE "myseq"; +CREATE TABLE tab2 ( + col4 int DEFAULT next value for "myseq" NOT NULL, + col1 varchar(100) NOT NULL, + col7 varchar(100), + col8 int, + col9 int, + col10 int, + CONSTRAINT myconstraint PRIMARY KEY (col4) +); + +SELECT +tab2.col10, +tab2.col7, +tab2.col1, +tab2.col8, +col2, +col3, +tab2.col4 as col4, +case when col11 like '%-%' and col3 < 20190101 then -1 +when col5=6 and tab2.col8 in (2,4,6,8,10,12,14,16,18,20) and col3 >= 20190101 then 1 +when col5=6 and tab2.col8 in (1,3,5,7,9,11,13,15,17,21) and col3 >= 20190101 then -1 +else 1 END as something, +RANK() OVER mywindow as rnk1 +FROM tab2 +JOIN tab1 +ON tab2.col4 = tab1.col4 +WINDOW mywindow as ( +PARTITION BY +tab2.col10, +tab2.col7, +tab2.col1, +col2, +col3 +ORDER BY +tab2.col9 DESC, +case when col11 like '%-%' and col3 < 20190101 then -1 +when col5=6 and tab2.col8 in (2,4,6,8,10,12,14,16,18,20) and col3 >= 20190101 then 1 +when col5=6 and tab2.col8 in (1,3,5,7,9,11,13,15,17,21) and col3 >= 20190101 then -1 +else 1 END DESC, +tab2.col4 DESC +) +where +((col5<>0 and col3 >= 20190101) or col3 < 20190101) +and tab2.col10 = 5 +and tab1.col6 is null +and col2 <> 'HEHEHEHE'; +ROLLBACK; diff --git a/sql/test/analytics/Tests/analytics18.stable.err b/sql/test/analytics/Tests/analytics18.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/analytics/Tests/analytics18.stable.err @@ -0,0 +1,12 @@ +stderr of test 'analytics18` in directory 'sql/test/analytics` itself: + + +# 11:48:39 > +# 11:48:39 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-272796" "--port=35121" +# 11:48:39 > + + +# 11:48:40 > +# 11:48:40 > "Done." +# 11:48:40 > + diff --git a/sql/test/analytics/Tests/analytics18.stable.out b/sql/test/analytics/Tests/analytics18.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/analytics/Tests/analytics18.stable.out @@ -0,0 +1,63 @@ +stdout of test 'analytics18` in directory 'sql/test/analytics` itself: + + +# 11:48:39 > +# 11:48:39 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-272796" "--port=35121" +# 11:48:39 > + +#START TRANSACTION; +#CREATE TABLE tab1 ( +# col1 varchar(100) NOT NULL, +# col2 varchar(100) NOT NULL, +# col3 int NOT NULL, +# col4 int, +# col5 int, +# col6 varchar(20), +# col11 decimal(13,4) +#); +#CREATE SEQUENCE "myseq"; +#CREATE TABLE tab2 ( +# col4 int DEFAULT next value for "myseq" NOT NULL, +# col1 varchar(100) NOT NULL, +# col7 varchar(100), +# col8 int, +# col9 int, +# col10 int, +# CONSTRAINT myconstraint PRIMARY KEY (col4) +#); +#SELECT +#tab2.col10, +#tab2.col7, +#tab2.col1, +#tab2.col8, +#col2, +#col3, +#tab2.col4 as col4, +#case when col11 like '%-%' and col3 < 20190101 then -1 +#when col5=6 and tab2.col8 in (2,4,6,8,10,12,14,16,18,20) and col3 >= 20190101 then 1 +#when col5=6 and tab2.col8 in (1,3,5,7,9,11,13,15,17,21) and col3 >= 20190101 then -1 +#else 1 END as something, +#RANK() OVER mywindow as rnk1 +#FROM tab2 +#JOIN tab1 +#ON tab2.col4 = tab1.col4 +#WINDOW mywindow as ( +#PARTITION BY +#tab2.col10, +#tab2.col7, +#tab2.col1, +#col2, +#col3 +#ORDER BY +#tab2.col9 DESC, +#case when col11 like '%-%' and col3 < 20190101 then -1 +% sys.tab2, sys.tab2, sys.tab2, sys.tab2, sys.tab1, sys.tab1, sys., sys., sys. # table_name +% col10, col7, col1, col8, col2, col3, col4, something, rnk1 # name +% int, varchar, varchar, int, varchar, int, int, tinyint, int # type +% 1, 0, 0, 1, 0, 1, 1, 1, 1 # length +#ROLLBACK; + +# 11:48:40 > +# 11:48:40 > "Done." +# 11:48:40 > + _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
