Re: Problem with RANK/DENSE_RANK in a subquery (dense_rank_grp undefined)

4 May 2016

      Hi Nikola,

I've just managed the reproduce exactly the same which produces and error
in the MAL plan.

sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank
more>FROM (
more>SELECT  *
more>        ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid,
eid ORDER BY r) as rank
more>  FROM (
more>        (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as
src, r FROM taba)
more>        UNION
more>        (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM
tabb)
more>        UNION
more>        (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r
FROM tabc)
more>      ) AS dummy1
more>) AS dummy2
more>WHERE rank = 1
more>;
TypeException:user.s8_1[110]:'calc.dense_rank_grp' undefined in:
calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng);
program contains errors

Seems like a bug.

Regards,

Brian Hood

On Tue, May 3, 2016 at 12:21 PM, Knezevic Nikola  wrote:
...
Hi all,
I have several tables which represent different sources (sensors) of data.
I want to find out which sensors picked up the signal first, and thus I
decided to join these tables together, rank on common criteria and then
select the first ones. However, that fails with:
TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in:
 calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng);
program contains errors
Any ideas how to fix this?
Cheers,
Nikola
Data:
CREATE TABLE taba (
aday DATE,
key1 varchar(15),
msid int,
sid int,
eid bigint,
r bigint
);
CREATE TABLE tabb (
aday DATE,
key1 varchar(15),
msid int,
sid int,
aid bigint,
eid bigint,
r bigint
);
CREATE TABLE tabc (
aday DATE,
key1 varchar(15),
msid int,
sid int,
aid bigint,
m bigint,
r bigint
);
COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n';
2016-05-02|inst1|675|10001|200|100
2016-05-02|inst1|675|10002|500|111
2016-05-02|inst1|789|10001|800|135
COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n';
2016-05-02|inst1|675|10001|77|200|88
2016-05-02|inst1|675|10002|100|500|112
2016-05-02|inst1|789|10001|112|800|134
COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n';
2016-05-02|inst1|675|10001|77|200|100
2016-05-02|inst1|675|10002|100|500|111
2016-05-02|inst1|789|10001|112|800|130
Query:
SELECT aday, key1, msid, sid, aid, eid, src, r, rank
FROM (
SELECT  *
        ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid, eid
ORDER BY r) as rank
  FROM (
        (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as src, r
FROM taba)
        UNION
        (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r FROM
tabb)
        UNION
        (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src, r
FROM tabc)
      ) AS dummy1
) AS dummy2
WHERE rank = 1
;
Results:
sql>CREATE TABLE taba (
more>aday DATE,
more>key1 varchar(15),
more>msid int,
more>sid int,
more>eid bigint,
more>r bigint
more>);
operation successful (4.102ms)
sql>CREATE TABLE tabb (
more>aday DATE,
more>key1 varchar(15),
more>msid int,
more>sid int,
more>aid bigint,
more>eid bigint,
more>r bigint
more>);
operation successful (3.172ms)
sql>CREATE TABLE tabc (
more>aday DATE,
more>key1 varchar(15),
more>msid int,
more>sid int,
more>aid bigint,
more>m bigint,
more>r bigint
more>);
operation successful (3.326ms)
sql>
sql>COPY 3 RECORDS INTO taba FROM stdin USING DELIMITERS '|','\n';
more>2016-05-02|inst1|675|10001|200|100
more>2016-05-02|inst1|675|10002|500|111
more>2016-05-02|inst1|789|10001|800|135
3 affected rows (88.249ms)
sql>
sql>COPY 3 RECORDS INTO tabb FROM stdin USING DELIMITERS '|','\n';
more>2016-05-02|inst1|675|10001|77|200|88
more>2016-05-02|inst1|675|10002|100|500|112
more>2016-05-02|inst1|789|10001|112|800|134
3 affected rows (59.917ms)
sql>
sql>COPY 3 RECORDS INTO tabc FROM stdin USING DELIMITERS '|','\n';
more>2016-05-02|inst1|675|10001|77|200|100
more>2016-05-02|inst1|675|10002|100|500|111
more>2016-05-02|inst1|789|10001|112|800|130
3 affected rows (89.804ms)
sql>
sql>select * from taba;
+------------+-------+------+-------+------+------+
| aday       | key1  | msid | sid   | eid  | r    |
+============+=======+======+=======+======+======+
| 2016-05-02 | inst1 |  675 | 10001 |  200 |  100 |
| 2016-05-02 | inst1 |  675 | 10002 |  500 |  111 |
| 2016-05-02 | inst1 |  789 | 10001 |  800 |  135 |
+------------+-------+------+-------+------+------+
3 tuples (4.340ms)
sql>select * from tabb;
+------------+-------+------+-------+------+------+------+
| aday       | key1  | msid | sid   | aid  | eid  | r    |
+============+=======+======+=======+======+======+======+
| 2016-05-02 | inst1 |  675 | 10001 |   77 |  200 |   88 |
| 2016-05-02 | inst1 |  675 | 10002 |  100 |  500 |  112 |
| 2016-05-02 | inst1 |  789 | 10001 |  112 |  800 |  134 |
+------------+-------+------+-------+------+------+------+
3 tuples (3.484ms)
sql>select * from tabc;
+------------+-------+------+-------+------+------+------+
| aday       | key1  | msid | sid   | aid  | eid  | m    |
+============+=======+======+=======+======+======+======+
| 2016-05-02 | inst1 |  675 | 10001 |   77 |  200 |  100 |
| 2016-05-02 | inst1 |  675 | 10002 |  100 |  500 |  111 |
| 2016-05-02 | inst1 |  789 | 10001 |  112 |  800 |  130 |
+------------+-------+------+-------+------+------+------+
3 tuples (3.215ms)
sql>SELECT *
more>FROM (
more>SELECT  *
more>        ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid,
eid ORDER BY r) as rank
more>  FROM (
more>        (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as
src, r FROM taba)
more>        UNION
more>        (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r
FROM tabb)
more>        UNION
more>        (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src,
r FROM tabc)
more>      ) AS dummy1
more>) AS dummy2;
+------------+-------+------+-------+------+------+------+------+------+
| aday       | key1  | msid | sid   | aid  | eid  | src  | r    | rank |
+============+=======+======+=======+======+======+======+======+======+
| 2016-05-02 | inst1 |  675 | 10001 | null |  200 | TABA |  100 |    2 |
| 2016-05-02 | inst1 |  675 | 10002 | null |  500 | TABA |  111 |    1 |
| 2016-05-02 | inst1 |  789 | 10001 | null |  800 | TABA |  135 |    3 |
| 2016-05-02 | inst1 |  675 | 10001 |   77 |  200 | TABB |   88 |    1 |
| 2016-05-02 | inst1 |  675 | 10002 |  100 |  500 | TABB |  112 |    2 |
| 2016-05-02 | inst1 |  789 | 10001 |  112 |  800 | TABB |  134 |    2 |
| 2016-05-02 | inst1 |  675 | 10001 |   77 |  200 | TABC |  100 |    2 |
| 2016-05-02 | inst1 |  675 | 10002 |  100 |  500 | TABC |  111 |    1 |
| 2016-05-02 | inst1 |  789 | 10001 |  112 |  800 | TABC |  130 |    1 |
+------------+-------+------+-------+------+------+------+------+------+
9 tuples (13.527ms)
sql>SELECT aday, key1, msid, sid, aid, eid, src, r, rank
more>FROM (
more>SELECT  *
more>        ,sys.DENSE_RANK() OVER (PARTITION BY aday, key1, msid, sid,
eid ORDER BY r) as rank
more>  FROM (
more>        (SELECT aday, key1, msid, sid, NULL as aid, eid, 'TABA' as
src, r FROM taba)
more>        UNION
more>        (SELECT aday, key1, msid, sid, aid, eid, 'TABB' as src, r
FROM tabb)
more>        UNION
more>        (SELECT aday, key1, msid, sid, aid, m as eid, 'TABC' AS src,
r FROM tabc)
more>      ) AS dummy1
more>) AS dummy2
more>WHERE rank = 1
more>;
TypeException:user.s22_3[110]:'calc.dense_rank_grp' undefined in:
 calc.dense_rank_grp(X_505:date,X_507:str,X_509:int,X_511:int,X_513:lng);
program contains errors
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list