Changeset: 181e76a702af for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=181e76a702af Added Files: sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.csv sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.sql.in sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.stable.err sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.stable.out Modified Files: sql/test/BugTracker-2013/Tests/All Branch: Feb2013 Log Message:
added test for bug 3277: The problem is threefold: 1) As of the Feb2013 release of MonetDB, stddev() has been replaced by stddev_pop() & stddev_samp(), i.e., there is no stddev() any more. We might have failed to communicate this change clearly. Sorry! 2) stddev_pop() & stddev_samp() fail on the results of count(), as the latter returns MonetDB internal type wrd, which is not a standard/valid SQL type, and (thus?) there is not implementation of stddev_pop() & stddev_samp() for type wrd. Explicitly casting the result of count() to a valid standard SQL integer type (tinying, smallint, integer, bigint) makes these functions work fine. Open question is, whether we should simple "treat the symptom" by adding implementations for stddev_pop() & stddev_samp() on type wrd, or rather cure the cause by making count() return a valid standard SQL type, e.g., integer on 32-bit systems and bigint on 64-bit systems. 3) sign() seems to fail as the created physical plan tries to insert a value of type bte into a BAT with tail type int, and while this should be no problem, there seems to be no signature that allows this. We'll need to investigate whether simply adding that signature is a valid solution, or whether there is a need to fix the translation of sign(). diffs (truncated from 501 to 300 lines): diff --git a/sql/test/BugTracker-2013/Tests/All b/sql/test/BugTracker-2013/Tests/All --- a/sql/test/BugTracker-2013/Tests/All +++ b/sql/test/BugTracker-2013/Tests/All @@ -9,3 +9,4 @@ stddev-group.Bug-3257 between.Bug-3259 correlated_select.Bug-3250 groupby_on_value_view.Bug-3264 +aggregates-typing-issues.Bug-3277 diff --git a/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.csv b/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.csv new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.csv @@ -0,0 +1,33 @@ +"","mpg","cyl","disp","hp","drat","wt","qsec","vs","am","gear","carb" +"Mazda RX4",21,6,160,110,3.9,2.62,16.46,0,1,4,4 +"Mazda RX4 Wag",21,6,160,110,3.9,2.875,17.02,0,1,4,4 +"Datsun 710",22.8,4,108,93,3.85,2.32,18.61,1,1,4,1 +"Hornet 4 Drive",21.4,6,258,110,3.08,3.215,19.44,1,0,3,1 +"Hornet Sportabout",18.7,8,360,175,3.15,3.44,17.02,0,0,3,2 +"Valiant",18.1,6,225,105,2.76,3.46,20.22,1,0,3,1 +"Duster 360",14.3,8,360,245,3.21,3.57,15.84,0,0,3,4 +"Merc 240D",24.4,4,146.7,62,3.69,3.19,20,1,0,4,2 +"Merc 230",22.8,4,140.8,95,3.92,3.15,22.9,1,0,4,2 +"Merc 280",19.2,6,167.6,123,3.92,3.44,18.3,1,0,4,4 +"Merc 280C",17.8,6,167.6,123,3.92,3.44,18.9,1,0,4,4 +"Merc 450SE",16.4,8,275.8,180,3.07,4.07,17.4,0,0,3,3 +"Merc 450SL",17.3,8,275.8,180,3.07,3.73,17.6,0,0,3,3 +"Merc 450SLC",15.2,8,275.8,180,3.07,3.78,18,0,0,3,3 +"Cadillac Fleetwood",10.4,8,472,205,2.93,5.25,17.98,0,0,3,4 +"Lincoln Continental",10.4,8,460,215,3,5.424,17.82,0,0,3,4 +"Chrysler Imperial",14.7,8,440,230,3.23,5.345,17.42,0,0,3,4 +"Fiat 128",32.4,4,78.7,66,4.08,2.2,19.47,1,1,4,1 +"Honda Civic",30.4,4,75.7,52,4.93,1.615,18.52,1,1,4,2 +"Toyota Corolla",33.9,4,71.1,65,4.22,1.835,19.9,1,1,4,1 +"Toyota Corona",21.5,4,120.1,97,3.7,2.465,20.01,1,0,3,1 +"Dodge Challenger",15.5,8,318,150,2.76,3.52,16.87,0,0,3,2 +"AMC Javelin",15.2,8,304,150,3.15,3.435,17.3,0,0,3,2 +"Camaro Z28",13.3,8,350,245,3.73,3.84,15.41,0,0,3,4 +"Pontiac Firebird",19.2,8,400,175,3.08,3.845,17.05,0,0,3,2 +"Fiat X1-9",27.3,4,79,66,4.08,1.935,18.9,1,1,4,1 +"Porsche 914-2",26,4,120.3,91,4.43,2.14,16.7,0,1,5,2 +"Lotus Europa",30.4,4,95.1,113,3.77,1.513,16.9,1,1,5,2 +"Ford Pantera L",15.8,8,351,264,4.22,3.17,14.5,0,1,5,4 +"Ferrari Dino",19.7,6,145,175,3.62,2.77,15.5,0,1,5,6 +"Maserati Bora",15,8,301,335,3.54,3.57,14.6,0,1,5,8 +"Volvo 142E",21.4,4,121,109,4.11,2.78,18.6,1,1,4,2 diff --git a/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.sql.in b/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.sql.in new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.sql.in @@ -0,0 +1,102 @@ +CREATE TABLE mtcars ( rownames VARCHAR(255) , mpg DOUBLE PRECISION , cyl DOUBLE PRECISION , disp DOUBLE PRECISION , hp DOUBLE PRECISION , drat DOUBLE PRECISION , wt DOUBLE PRECISION , qsec DOUBLE PRECISION , vs DOUBLE PRECISION , am DOUBLE PRECISION , gear DOUBLE PRECISION , carb DOUBLE PRECISION ) ; + +COPY 33 offset 2 records into mtcars from '$TSTSRCDIR/aggregates-typing-issues.Bug-3277.csv' USING DELIMITERS ',','\n','\"' NULL as '' ; +select * from mtcars; + + +-- plain count() +create table mtcars2 as select carb , count( distinct case when gear in ( 3 , 4 ) then mpg end ) as prob1 from mtcars group by carb with data ; +select * from mtcars2; + +--THESE WORK; +select median( prob1 ) from mtcars2; +select prod( prob1 ) from mtcars2; + +--THESE BREAK; +select stddev_pop( prob1 ) from mtcars2; +select stddev_samp( prob1 ) from mtcars2; +select sign( prob1 ) from mtcars2; + +-- stddev() has been replaced by stddev_pop() & stddev_samp() +select stddev( prob1 ) from mtcars2; + +drop table mtcars2; + + +-- cast(count() as tinyint) +create table mtcars2 as select carb , cast(count( distinct case when gear in ( 3 , 4 ) then mpg end ) as tinyint) as prob1 from mtcars group by carb with data ; +select * from mtcars2; + +--THESE WORK; +select median( prob1 ) from mtcars2; +select prod( prob1 ) from mtcars2; +select stddev_pop( prob1 ) from mtcars2; + +--THESE BREAK; +select stddev_samp( prob1 ) from mtcars2; +select sign( prob1 ) from mtcars2; + +-- stddev() has been replaced by stddev_pop() & stddev_samp() +select stddev( prob1 ) from mtcars2; + +drop table mtcars2; + + +-- cast(count() as smallint) +create table mtcars2 as select carb , cast(count( distinct case when gear in ( 3 , 4 ) then mpg end ) as smallint) as prob1 from mtcars group by carb with data ; +select * from mtcars2; + +--THESE WORK; +select median( prob1 ) from mtcars2; +select prod( prob1 ) from mtcars2; +select stddev_pop( prob1 ) from mtcars2; + +--THESE BREAK; +select stddev_samp( prob1 ) from mtcars2; +select sign( prob1 ) from mtcars2; + +-- stddev() has been replaced by stddev_pop() & stddev_samp() +select stddev( prob1 ) from mtcars2; + +drop table mtcars2; + + +-- cast(count() as integer) +create table mtcars2 as select carb , cast(count( distinct case when gear in ( 3 , 4 ) then mpg end ) as integer) as prob1 from mtcars group by carb with data ; +select * from mtcars2; + +--THESE WORK; +select median( prob1 ) from mtcars2; +select prod( prob1 ) from mtcars2; +select stddev_pop( prob1 ) from mtcars2; + +--THESE BREAK; +select stddev_samp( prob1 ) from mtcars2; +select sign( prob1 ) from mtcars2; + +-- stddev() has been replaced by stddev_pop() & stddev_samp() +select stddev( prob1 ) from mtcars2; + +drop table mtcars2; + + +-- cast(count() as bigint) +create table mtcars2 as select carb , cast(count( distinct case when gear in ( 3 , 4 ) then mpg end ) as bigint) as prob1 from mtcars group by carb with data ; +select * from mtcars2; + +--THESE WORK; +select median( prob1 ) from mtcars2; +select prod( prob1 ) from mtcars2; +select stddev_pop( prob1 ) from mtcars2; + +--THESE BREAK; +select stddev_samp( prob1 ) from mtcars2; +select sign( prob1 ) from mtcars2; + +-- stddev() has been replaced by stddev_pop() & stddev_samp() +select stddev( prob1 ) from mtcars2; + +drop table mtcars2; + + +drop table mtcars; diff --git a/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.stable.err b/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.stable.err @@ -0,0 +1,50 @@ +stderr of test 'aggregates-typing-issues.Bug-3277` in directory 'sql/test/BugTracker-2013` itself: + + +# 10:46:44 > +# 10:46:44 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=38855" "--set" "mapi_usock=/var/tmp/mtest-24209/.s.monetdb.38855" "--set" "monet_prompt=" "--forcemito" "--set" "mal_listing=2" "--dbpath=/ufs/manegold/_/Monet/HG/Feb2013/prefix/--disable-debug_--enable-assert_--enable-optimize/var/MonetDB/mTests_sql_test_BugTracker-2013" "--set" "mal_listing=0" +# 10:46:44 > + +# builtin opt gdk_dbpath = /ufs/manegold/_/Monet/HG/Feb2013/prefix/--disable-debug_--enable-assert_--enable-optimize/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = yes +# 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 = 38855 +# cmdline opt mapi_usock = /var/tmp/mtest-24209/.s.monetdb.38855 +# cmdline opt monet_prompt = +# cmdline opt mal_listing = 2 +# cmdline opt gdk_dbpath = /ufs/manegold/_/Monet/HG/Feb2013/prefix/--disable-debug_--enable-assert_--enable-optimize/var/MonetDB/mTests_sql_test_BugTracker-2013 +# cmdline opt mal_listing = 0 + +# 10:46:44 > +# 10:46:44 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-24209" "--port=38855" +# 10:46:44 > + +MAPI = (monetdb) /var/tmp/mtest-29812/.s.monetdb.32950 +QUERY = select stddev( prob1 ) from mtcars2; +ERROR = !SELECT: no such unary operator 'stddev(wrd)' +MAPI = (monetdb) /var/tmp/mtest-29812/.s.monetdb.32950 +QUERY = select stddev( prob1 ) from mtcars2; +ERROR = !SELECT: no such unary operator 'stddev(tinyint)' +MAPI = (monetdb) /var/tmp/mtest-29812/.s.monetdb.32950 +QUERY = select stddev( prob1 ) from mtcars2; +ERROR = !SELECT: no such unary operator 'stddev(smallint)' +MAPI = (monetdb) /var/tmp/mtest-29812/.s.monetdb.32950 +QUERY = select stddev( prob1 ) from mtcars2; +ERROR = !SELECT: no such unary operator 'stddev(int)' +MAPI = (monetdb) /var/tmp/mtest-29812/.s.monetdb.32950 +QUERY = select stddev( prob1 ) from mtcars2; +ERROR = !SELECT: no such unary operator 'stddev(bigint)' + +# 10:46:44 > +# 10:46:44 > "Done." +# 10:46:44 > + diff --git a/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.stable.out b/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.stable.out @@ -0,0 +1,288 @@ +stdout of test 'aggregates-typing-issues.Bug-3277` in directory 'sql/test/BugTracker-2013` itself: + + +# 10:46:44 > +# 10:46:44 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=38855" "--set" "mapi_usock=/var/tmp/mtest-24209/.s.monetdb.38855" "--set" "monet_prompt=" "--forcemito" "--set" "mal_listing=2" "--dbpath=/ufs/manegold/_/Monet/HG/Feb2013/prefix/--disable-debug_--enable-assert_--enable-optimize/var/MonetDB/mTests_sql_test_BugTracker-2013" "--set" "mal_listing=0" +# 10:46:44 > + +# MonetDB 5 server v11.15.6 +# This is an unreleased version +# Serving database 'mTests_sql_test_BugTracker-2013', using 8 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked +# Found 15.629 GiB available main-memory. +# Copyright (c) 1993-July 2008 CWI. +# Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved +# Visit http://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://rome.ins.cwi.nl:38855/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-24209/.s.monetdb.38855 +# MonetDB/GIS module loaded +# MonetDB/JAQL module loaded +# MonetDB/SQL module loaded + +Ready. + +# 10:46:44 > +# 10:46:44 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-24209" "--port=38855" +# 10:46:44 > + +#CREATE TABLE mtcars ( rownames VARCHAR(255) , mpg DOUBLE PRECISION , cyl DOUBLE PRECISION , disp DOUBLE PRECISION , hp DOUBLE PRECISION , drat DOUBLE PRECISION , wt DOUBLE PRECISION , qsec DOUBLE PRECISION , vs DOUBLE PRECISION , am DOUBLE PRECISION , gear DOUBLE PRECISION , carb DOUBLE PRECISION ) ; +#COPY 33 offset 2 records into mtcars from '/net/rome.ins.cwi.nl/export/scratch2/manegold/.rig./scratch0/Monet/HG/Feb2013/source/MonetDB/sql/test/BugTracker-2013/Tests/aggregates-typing-issues.Bug-3277.csv' USING DELIMITERS ',','\n','\"' NULL as '' ; +[ 32 ] +#select * from mtcars; +% sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars, sys.mtcars # table_name +% rownames, mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb # name +% varchar, double, double, double, double, double, double, double, double, double, double, double # type +% 19, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24 # length +[ "Mazda RX4", 21, 6, 160, 110, 3.9, 2.62, 16.46, 0, 1, 4, 4 ] +[ "Mazda RX4 Wag", 21, 6, 160, 110, 3.9, 2.875, 17.02, 0, 1, 4, 4 ] +[ "Datsun 710", 22.8, 4, 108, 93, 3.85, 2.32, 18.61, 1, 1, 4, 1 ] +[ "Hornet 4 Drive", 21.4, 6, 258, 110, 3.08, 3.215, 19.44, 1, 0, 3, 1 ] +[ "Hornet Sportabout", 18.7, 8, 360, 175, 3.15, 3.44, 17.02, 0, 0, 3, 2 ] +[ "Valiant", 18.1, 6, 225, 105, 2.76, 3.46, 20.22, 1, 0, 3, 1 ] +[ "Duster 360", 14.3, 8, 360, 245, 3.21, 3.57, 15.84, 0, 0, 3, 4 ] +[ "Merc 240D", 24.4, 4, 146.7, 62, 3.69, 3.19, 20, 1, 0, 4, 2 ] +[ "Merc 230", 22.8, 4, 140.8, 95, 3.92, 3.15, 22.9, 1, 0, 4, 2 ] +[ "Merc 280", 19.2, 6, 167.6, 123, 3.92, 3.44, 18.3, 1, 0, 4, 4 ] +[ "Merc 280C", 17.8, 6, 167.6, 123, 3.92, 3.44, 18.9, 1, 0, 4, 4 ] +[ "Merc 450SE", 16.4, 8, 275.8, 180, 3.07, 4.07, 17.4, 0, 0, 3, 3 ] +[ "Merc 450SL", 17.3, 8, 275.8, 180, 3.07, 3.73, 17.6, 0, 0, 3, 3 ] +[ "Merc 450SLC", 15.2, 8, 275.8, 180, 3.07, 3.78, 18, 0, 0, 3, 3 ] +[ "Cadillac Fleetwood", 10.4, 8, 472, 205, 2.93, 5.25, 17.98, 0, 0, 3, 4 ] +[ "Lincoln Continental", 10.4, 8, 460, 215, 3, 5.424, 17.82, 0, 0, 3, 4 ] +[ "Chrysler Imperial", 14.7, 8, 440, 230, 3.23, 5.345, 17.42, 0, 0, 3, 4 ] +[ "Fiat 128", 32.4, 4, 78.7, 66, 4.08, 2.2, 19.47, 1, 1, 4, 1 ] +[ "Honda Civic", 30.4, 4, 75.7, 52, 4.93, 1.615, 18.52, 1, 1, 4, 2 ] +[ "Toyota Corolla", 33.9, 4, 71.1, 65, 4.22, 1.835, 19.9, 1, 1, 4, 1 ] +[ "Toyota Corona", 21.5, 4, 120.1, 97, 3.7, 2.465, 20.01, 1, 0, 3, 1 ] +[ "Dodge Challenger", 15.5, 8, 318, 150, 2.76, 3.52, 16.87, 0, 0, 3, 2 ] +[ "AMC Javelin", 15.2, 8, 304, 150, 3.15, 3.435, 17.3, 0, 0, 3, 2 ] +[ "Camaro Z28", 13.3, 8, 350, 245, 3.73, 3.84, 15.41, 0, 0, 3, 4 ] +[ "Pontiac Firebird", 19.2, 8, 400, 175, 3.08, 3.845, 17.05, 0, 0, 3, 2 ] +[ "Fiat X1-9", 27.3, 4, 79, 66, 4.08, 1.935, 18.9, 1, 1, 4, 1 ] +[ "Porsche 914-2", 26, 4, 120.3, 91, 4.43, 2.14, 16.7, 0, 1, 5, 2 ] +[ "Lotus Europa", 30.4, 4, 95.1, 113, 3.77, 1.513, 16.9, 1, 1, 5, 2 ] +[ "Ford Pantera L", 15.8, 8, 351, 264, 4.22, 3.17, 14.5, 0, 1, 5, 4 ] +[ "Ferrari Dino", 19.7, 6, 145, 175, 3.62, 2.77, 15.5, 0, 1, 5, 6 ] +[ "Maserati Bora", 15, 8, 301, 335, 3.54, 3.57, 14.6, 0, 1, 5, 8 ] +[ "Volvo 142E", 21.4, 4, 121, 109, 4.11, 2.78, 18.6, 1, 1, 4, 2 ] +#create table mtcars2 as select carb , count( distinct case when gear in ( 3 , 4 ) then mpg end ) as prob1 from mtcars group by carb with data ; +#select * from mtcars2; +% sys.mtcars2, sys.mtcars2 # table_name +% carb, prob1 # name +% double, wrd # type +% 24, 1 # length +[ 4, 7 ] +[ 1, 7 ] +[ 2, 8 ] +[ 3, 3 ] +[ 6, 0 ] +[ 8, 0 ] +#select median( prob1 ) from mtcars2; +% sys. # table_name +% L1 # name +% tinyint # type +% 1 # length +[ 3 ] +#select prod( prob1 ) from mtcars2; +% sys. # table_name _______________________________________________ checkin-list mailing list [email protected] http://mail.monetdb.org/mailman/listinfo/checkin-list
