Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQL returns TypeException 'aggr.subcorr' undefined for sys.corr function #3978

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

SQL returns TypeException 'aggr.subcorr' undefined for sys.corr function #3978

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL
Milestone
Ancient Release

Comments

@monetdb-team
Copy link

Date: 2016-04-07 14:18:28 +0200
From: Rob Erie <<rob.erie>>
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: @njnes

Last updated: 2016-06-23 10:24:31 +0200

Comment 22023

Date: 2016-04-07 14:18:28 +0200
From: Rob Erie <<rob.erie>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2490.86 Safari/537.36
Build Identifier:

Created required tables in MonetDB for testing correlation and performance. Ran SQL query in steps to reproduce received error as indicated in Actual Results.

Reproducible: Always

Steps to Reproduce:

1.run SQL: select milageRange, sys.corr(numberOfTheseParts,avgHomeValue) FROM aPartsByZipRange INNER JOIN avgHomeValueZipPeriod ON zipCode = zip where numberOfTheseParts <> 0 and avgHomeValue <>0 group by milageRange;

Actual Results:

TypeException:user.s78_2[44]:'aggr.subcorr' undefined in: X_75:bat[:any,:int] := aggr.subcorr(X_72:bat[:oid,:int],X_74:bat[:oid,:int],X_67:bat[:oid,:oid],r1_67:bat[:oid,:oid],true:bit);
program contains errors

Expected Results:

Correlation between variables by milageRange

Environment Info:
Amazon Web Service Instance
Red Hat Enterprise Linux Server release 7.2 (Maipo) (Linux 3.10.0-327.el7.x86_64 1 SMP Thu Oct 29 17:29:29 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux)
MySQL Server version: 5.7.11-log MySQL Community Server
Java version "1.8.0_74"; Java(TM) SE Runtime Environment (build 1.8.0_74-b02); Java HotSpot(TM) 64-Bit Server VM (build 25.74-b02, mixed mode).
Apache Tomcat 7.0.54
Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP3)
Database: MonetDB v11.21.17 (Jul2015-SP3), 'mapi:monetdb://ip-10-153-0-5.ec2.internal:50000/set_mktng'

mserver5 --version:
MonetDB 5 server v11.21.17 "Jul2015-SP3" (64-bit, 64-bit oids, 128-bit integers)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 31.0GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.32 2012-11-30 (compiled with 8.32)
openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with )
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: mockbuild@ (x86_64-redhat-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64 -Wl,-z,relro

SQL:
select milageRange, sys.corr(numberOfTheseParts,avgHomeValue) FROM aPartsByZipRange INNER JOIN avgHomeValueZipPeriod ON zipCode = zip group by milageRange;
Result:
TypeException:user.s6_1[35]:'aggr.subcorr' undefined in: X_64:bat[:any,:int] := aggr.subcorr(X_54:bat[:oid,:int],X_63:bat[:oid,:int],X_38:bat[:oid,:oid],r1_38:bat[:oid,:oid],true:bit);
program contains errors

Explain:
explain select milageRange, sys.corr(numberOfTheseParts,avgHomeValue) FROM aPartsByZipRange INNER JOIN avgHomeValueZipPeriod ON zipCode = zip group by milageRange;
TypeException:user.s7_1[35]:'aggr.subcorr' undefined in: X_64:bat[:any,:int] := aggr.subcorr(X_54:bat[:oid,:int],X_63:bat[:oid,:int],X_38:bat[:oid,:oid],r1_38:bat[:oid,:oid],true:bit);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mal |
+=========================================================================================================================================================================================================+
| function user.s7_1{autoCommit=true}():void; |
| sql.mvc(); |
| X_3:bat[:oid,:oid] := sql.tid(X_2,"set_mktng","apartsbyziprange"); |
| X_6:bat[:oid,:int] := sql.bind(X_2,"set_mktng","apartsbyziprange","zipcode",0); |
| (X_9,r1_9) := sql.bind(X_2,"set_mktng","apartsbyziprange","zipcode",2); |
| X_12:bat[:oid,:int] := sql.bind(X_2,"set_mktng","apartsbyziprange","zipcode",1); |
| sql.delta(X_6,X_9,r1_9,X_12); |
| algebra.leftfetchjoin(X_3,X_14); |
| X_16:bat[:oid,:oid] := sql.tid(X_2,"set_mktng","avghomevaluezipperiod"); |
| X_18:bat[:oid,:int] := sql.bind(X_2,"set_mktng","avghomevaluezipperiod","zip",0); |
| (X_20,r1_20) := sql.bind(X_2,"set_mktng","avghomevaluezipperiod","zip",2); |
| X_22:bat[:oid,:int] := sql.bind(X_2,"set_mktng","avghomevaluezipperiod","zip",1); |
| sql.delta(X_18,X_20,r1_20,X_22); |
| algebra.leftfetchjoin(X_16,X_23); |
| (X_25,r1_25) := algebra.subjoin(X_15,X_24,nil:BAT,nil:BAT,false,nil:lng); |
| X_30:bat[:oid,:str] := sql.bind(X_2,"set_mktng","apartsbyziprange","milagerange",0); |
| (X_32,r1_32) := sql.bind(X_2,"set_mktng","apartsbyziprange","milagerange",2); |
| X_34:bat[:oid,:str] := sql.bind(X_2,"set_mktng","apartsbyziprange","milagerange",1); |
| sql.delta(X_30,X_32,r1_32,X_34); |
| algebra.leftfetchjoin(X_3,X_35); |
| algebra.leftfetchjoin(X_25,X_36); |
| (X_38,r1_38,r2_38) := group.subgroupdone(X_37); |
| algebra.leftfetchjoin(r1_38,X_37); |
| X_42:bat[:oid,:int] := sql.bind(X_2,"set_mktng","apartsbyziprange","numberoftheseparts",0); |
| (X_47,r1_47) := sql.bind(X_2,"set_mktng","apartsbyziprange","numberoftheseparts",2); |
| X_50:bat[:oid,:int] := sql.bind(X_2,"set_mktng","apartsbyziprange","numberoftheseparts",1); |
| sql.delta(X_42,X_47,r1_47,X_50); |
| algebra.leftfetchjoin(X_3,X_52); |
| algebra.leftfetchjoin(X_25,X_53); |
| X_55:bat[:oid,:int] := sql.bind(X_2,"set_mktng","avghomevaluezipperiod","avghomevalue",0); |
| (X_58,r1_58) := sql.bind(X_2,"set_mktng","avghomevaluezipperiod","avghomevalue",2); |
| X_60:bat[:oid,:int] := sql.bind(X_2,"set_mktng","avghomevaluezipperiod","avghomevalue",1); |
| sql.delta(X_55,X_58,r1_58,X_60); |
| algebra.leftfetchjoin(X_16,X_61); |
| algebra.leftfetchjoin(r1_25,X_62); |
| X_64:bat[:any,:int] := aggr.subcorr(X_54,X_63,X_38,r1_38,true); |
| bat.new(nil:oid,nil:str); |
| bat.new(nil:oid,nil:str); |
| bat.new(nil:oid,nil:str); |
| bat.new(nil:oid,nil:int); |
| bat.new(nil:oid,nil:int); |
| bat.append(X_67,"set_mktng.apartsbyziprange"); |
| bat.append(X_70,"milagerange"); |
| bat.append(X_71,"varchar"); |
| bat.append(X_72,18); |
| bat.append(X_74,0); |
| bat.append(X_75,"set_mktng.L1"); |
| bat.append(X_77,"L1"); |
| bat.append(X_79,"int"); |
| bat.append(X_81,32); |
| bat.append(X_83,0); |
| sql.resultSet(X_85,X_87,X_89,X_91,X_93,X_41,X_64); |
| end user.s7_1; |
| querylog.define("explain select milagerange, sys.corr(numberoftheseparts,avghomevalue) from apartsbyziprange inner join avghomevaluezipperiod on zipcode = zip group by milagerange;","default_pipe") |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
54 tuples (0.921ms)

Plan:

plan select milageRange, sys.corr(numberOfTheseParts,avgHomeValue) FROM aPartsByZipRange INNER JOIN avgHomeValueZipPeriod ON zipCode = zip group by milageRange;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rel |
+========================================================================================================================================================================================================================+
| project ( |
| | group by ( |
| | | join ( |
| | | | table(set_mktng.apartsbyziprange) [ apartsbyziprange.zipcode, apartsbyziprange.milagerange NOT NULL, apartsbyziprange.numberoftheseparts NOT NULL ] COUNT , |
| | | | table(set_mktng.avghomevaluezipperiod) [ avghomevaluezipperiod.zip NOT NULL, avghomevaluezipperiod.avghomevalue NOT NULL ] COUNT |
| | | ) [ apartsbyziprange.zipcode = avghomevaluezipperiod.zip NOT NULL ] |
| | ) [ apartsbyziprange.milagerange NOT NULL ] [ apartsbyziprange.milagerange NOT NULL, sys.corr no nil (apartsbyziprange.numberoftheseparts NOT NULL, avghomevaluezipperiod.avghomevalue NOT NULL) NOT NULL as L1.L1 ] |
| ) [ apartsbyziprange.milagerange NOT NULL, L1 NOT NULL as L1.L1 ] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 tuples (0.493ms)

merovingian.log:
2016-04-07 07:53:55 MSG merovingian[3057]: Merovingian 1.7 (Jul2015-SP3) starting
2016-04-07 07:53:55 MSG merovingian[3057]: monitoring dbfarm /tmpdata/monetdb/
2016-04-07 07:53:55 MSG merovingian[3057]: accepting connections on TCP socket 0.0.0.0:50000
2016-04-07 07:53:55 MSG merovingian[3057]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000
2016-04-07 07:53:55 MSG discovery[3057]: listening for UDP messages on 0.0.0.0:50000
2016-04-07 07:53:55 MSG control[3057]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000
2016-04-07 07:53:55 MSG discovery[3057]: new neighbour ip-10-153-0-5.ec2.internal (ip-10-153-0-5.ec2.internal)
2016-04-07 07:53:57 MSG discovery[3057]: new database mapi:monetdb://ip-10-153-0-5.ec2.internal:50000/set_mktng (ttl=660s)
2016-04-07 07:58:35 MSG merovingian[3057]: starting database 'set_mktng', up min/avg/max: 1d/1d/1d, crash average: 0.00 0.00 0.00 (1-1=0)
2016-04-07 07:58:36 MSG set_mktng[3102]: arguments: /usr/bin/mserver5 --dbpath=/tmpdata/monetdb/set_mktng --set merovingian_uri=mapi:monetdb://ip-10-153-0-5.ec2.internal:50000/set_mktng --set mapi_ open=false --set mapi_port=0 --set mapi_usock=/tmpdata/monetdb/set_mktng/.mapi.sock --set monet_vault_key=/tmpdata/monetdb/set_mktng/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_ optimizer=default_pipe --set monet_daemon=yes
2016-04-07 07:58:37 MSG set_mktng[3102]: MonetDB 5 server v11.21.17 "Jul2015-SP3"
2016-04-07 07:58:37 MSG set_mktng[3102]: Serving database 'set_mktng', using 8 threads
2016-04-07 07:58:37 MSG set_mktng[3102]: Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked
2016-04-07 07:58:37 MSG set_mktng[3102]: Found 31.013 GiB available main-memory.
2016-04-07 07:58:37 MSG set_mktng[3102]: Copyright (c) 1993-July 2008 CWI.
2016-04-07 07:58:37 MSG set_mktng[3102]: Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
2016-04-07 07:58:37 MSG set_mktng[3102]: Visit http://www.monetdb.org/ for further information
2016-04-07 07:58:37 MSG set_mktng[3102]: Listening for UNIX domain connection requests on mapi:monetdb:///tmpdata/monetdb/set_mktng/.mapi.sock
2016-04-07 07:58:37 MSG set_mktng[3102]: MonetDB/GIS module loaded
2016-04-07 07:58:37 MSG set_mktng[3102]: Start processing logs sql/sql_logs version 52200
2016-04-07 07:58:37 MSG set_mktng[3102]: Start reading the write-ahead log 'sql_logs/sql/log.20'
2016-04-07 07:58:37 MSG set_mktng[3102]: Finished reading the write-ahead log 'sql_logs/sql/log.20'
2016-04-07 07:58:37 MSG set_mktng[3102]: Finished processing logs sql/sql_logs
2016-04-07 07:58:37 MSG set_mktng[3102]: MonetDB/SQL module loaded
2016-04-07 07:58:38 MSG merovingian[3057]: proxying client (local) for database 'set_mktng' to mapi:monetdb:///tmpdata/monetdb/set_mktng/.mapi.sock?database=set_mktng
2016-04-07 07:58:38 MSG merovingian[3057]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying
[root@ip-10-153-0-5 monetdb] cat merovingian.log
2016-04-07 07:53:55 MSG merovingian[3057]: Merovingian 1.7 (Jul2015-SP3) starting
2016-04-07 07:53:55 MSG merovingian[3057]: monitoring dbfarm /tmpdata/monetdb/
2016-04-07 07:53:55 MSG merovingian[3057]: accepting connections on TCP socket 0.0.0.0:50000
2016-04-07 07:53:55 MSG merovingian[3057]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000
2016-04-07 07:53:55 MSG discovery[3057]: listening for UDP messages on 0.0.0.0:50000
2016-04-07 07:53:55 MSG control[3057]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000
2016-04-07 07:53:55 MSG discovery[3057]: new neighbour ip-10-153-0-5.ec2.internal (ip-10-153-0-5.ec2.internal)
2016-04-07 07:53:57 MSG discovery[3057]: new database mapi:monetdb://ip-10-153-0-5.ec2.internal:50000/set_mktng (ttl=660s)
2016-04-07 07:58:35 MSG merovingian[3057]: starting database 'set_mktng', up min/avg/max: 1d/1d/1d, crash average: 0.00 0.00 0.00 (1-1=0)
2016-04-07 07:58:36 MSG set_mktng[3102]: arguments: /usr/bin/mserver5 --dbpath=/tmpdata/monetdb/set_mktng --set merovingian_uri=mapi:monetdb://ip-10-153-0-5.ec2.internal:50000/set_mktng --set mapi_open=false --set mapi_port=0 --set mapi_usock=/tmpdata/monetdb/set_mktng/.mapi.sock --set monet_vault_key=/tmpdata/monetdb/set_mktng/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes
2016-04-07 07:58:37 MSG set_mktng[3102]: MonetDB 5 server v11.21.17 "Jul2015-SP3"
2016-04-07 07:58:37 MSG set_mktng[3102]: Serving database 'set_mktng', using 8 threads
2016-04-07 07:58:37 MSG set_mktng[3102]: Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked
2016-04-07 07:58:37 MSG set_mktng[3102]: Found 31.013 GiB available main-memory.
2016-04-07 07:58:37 MSG set_mktng[3102]: Copyright (c) 1993-July 2008 CWI.
2016-04-07 07:58:37 MSG set_mktng[3102]: Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
2016-04-07 07:58:37 MSG set_mktng[3102]: Visit http://www.monetdb.org/ for further information
2016-04-07 07:58:37 MSG set_mktng[3102]: Listening for UNIX domain connection requests on mapi:monetdb:///tmpdata/monetdb/set_mktng/.mapi.sock
2016-04-07 07:58:37 MSG set_mktng[3102]: MonetDB/GIS module loaded
2016-04-07 07:58:37 MSG set_mktng[3102]: Start processing logs sql/sql_logs version 52200
2016-04-07 07:58:37 MSG set_mktng[3102]: Start reading the write-ahead log 'sql_logs/sql/log.20'
2016-04-07 07:58:37 MSG set_mktng[3102]: Finished reading the write-ahead log 'sql_logs/sql/log.20'
2016-04-07 07:58:37 MSG set_mktng[3102]: Finished processing logs sql/sql_logs
2016-04-07 07:58:37 MSG set_mktng[3102]: MonetDB/SQL module loaded
2016-04-07 07:58:38 MSG merovingian[3057]: proxying client (local) for database 'set_mktng' to mapi:monetdb:///tmpdata/monetdb/set_mktng/.mapi.sock?database=set_mktng
2016-04-07 07:58:38 MSG merovingian[3057]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying

Comment 22047

Date: 2016-04-16 13:05:34 +0200
From: @njnes

the corr and friend functions were overlooked when switching to the sub* interface. This is now fixed.

Comment 22048

Date: 2016-04-16 13:08:13 +0200
From: MonetDB Mercurial Repository <>

Changeset b2d229a59b68 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=b2d229a59b68

Changeset description:

added test for bug #3978

Comment 22049

Date: 2016-04-16 13:09:16 +0200
From: MonetDB Mercurial Repository <>

Changeset 25dba272d62f made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=25dba272d62f

Changeset description:

fixes for bug #3978, ie use the (not so) new sub* interface
for aggregates.
@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working normal SQL
Projects
None yet
Milestone
Ancient Release
Development

No branches or pull requests
2 participants
@sjoerdmullender @monetdb-team