Work around but its annoying.

sql>\d rank_test

create view rank_test as (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);

sql>create table rank1 as select * from rank_test;

syntax error, unexpected SCOLON, expecting WITH in: "create table rank1 as select * from rank_test;"

sql>create table rank1 as select * from rank_test with data;

operation successful (40.399ms)

sql>select * from rank1;

+------------+-------+------+-------+------+------+------+------+------+

| 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 (8.079ms)

sql>select * from rank1 where rank = 1;

+------------+-------+------+-------+------+------+------+------+------+

| aday | key1 | msid | sid | aid | eid | src | r | rank |

+============+=======+======+=======+======+======+======+======+======+

| 2016-05-02 | inst1 | 675 | 10002 | null | 500 | TABA | 111 | 1 |

| 2016-05-02 | inst1 | 675 | 10001 | 77 | 200 | TABB | 88 | 1 |

| 2016-05-02 | inst1 | 675 | 10002 | 100 | 500 | TABC | 111 | 1 |

| 2016-05-02 | inst1 | 789 | 10001 | 112 | 800 | TABC | 130 | 1 |

+------------+-------+------+-------+------+------+------+------+------+

4 tuples (3.546ms)

sql>

On Wed, May 4, 2016 at 1:01 PM, Brian Hood <brianh6854@googlemail.com> wrote:

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 <nikkne@gmx.ch> 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