The following solution may be slow if there is a wide range for a and b:

-- example data drop table test; create table test as select 1 as a, 2 as b union select 10, 12; -- generate series between min(a) and max(b)+1 and join select * from generate_series((select min(a), max(b)+1 from test)) inner join test on value between a and b;

As I noted previously, the LATERAL join will not BAT functions - but it does give nice code.

-- Mark

On 06/13/2018 12:46 PM, Stefan Manegold wrote:

Hi Torsten & Stefan,

with Mar2018 & Mar2018-SP1 and this script:

drop table if exists segments; create table segments (meter int, distance int, speed int); insert into segments values (1,1,1),(9,9,9); select * from segments;

I get: SELECT t.meter, s.distance, s.speed FROM segments AS s, LATERAL generate_series(s.meter, s.meter+s.distance+1) AS t(meter); -> SELECT: no such column 't.meter'

SELECT * FROM segments AS s, LATERAL generate_series(s.meter, s.meter+s.distance+1) ; -> mserver5: /ufs/manegold/_/Monet/preview/source/MonetDB/sql/backends/monet5/rel_bin.c:1473: rel2bin_table: Assertion `0' failed. Aborted (core dumped)

SELECT t.* , s.distance, s.speed FROM segments AS s, LATERAL generate_series(s.meter, s.meter+s.distance+1) AS t(meter); -> mserver5: /ufs/manegold/_/Monet/preview/source/MonetDB/sql/backends/monet5/rel_bin.c:1473: rel2bin_table: Assertion `0' failed. Aborted (core dumped)

Hence, indeed this asks for a bug report: bugs.monetdb.org

Best, Stefan

----- On Jun 13, 2018, at 9:42 AM, Torsten Grust torsten.grust@gmail.com wrote:

...

Hi Stefan,

note that the use of generate_series() in the SELECT clause is deprecated by the PostgreSQL folks. That functionality may go away in future releases.

An alternative formulation of your segment expansion query would be the following (I assume that your input table is segments(meter, distance, speed)):

SELECT t.meter, s.distance, s.speed FROM segments AS s, LATERAL generate_series(s.meter, s.meter+s.distance+1) AS t(meter);

Note the use of LATERAL such that in the invocation of generate_series() we may refer to row variable s.

Now, as I understand it, this query should work in MonetDB. LATERAL support is in MonetDB and has received recent bug fixes. The query work does not work, though: MonetDB 5 server v11.27.13 "Jul2017-SP4" (on macOS Sierra 10.12.6) goes into an infinite loop already when I try to plan the query. Which turns this reply into a bug report, I guess. ¯_(ツ)_/¯

Best wishes, —Torsten

On Tue, Jun 12, 2018 at 9:33 AM Stefan de Konink < stefan@konink.de > wrote:

Goodmorning,

At this moment there is no functional compatibility between the PostgreSQL and MonetDB with respect to the generate_series function. The range in PostgreSQL is inclusive, in MonetDB exclusive. More importantly generate_series can't be used with argument that is joined from a different table, MonetDB just seems to support a subquery, hence joining that back without a primary is impossible.

PostgreSQL; select generate_series(a, b), a from (select 1 as a, 2 as b) as a; generate_series | a -----------------+--- 1 | 1 2 | 1 (2 rows)

MonetDB; select * from generate_series((select 1 as a, 2 as b)); +-------+ | value | +=======+ | 1 | +-------+

If I would like to functionally achieve the following;

I have list that states the speeds over partial segments, identified by linear referencing. From 10 meter to 12 meter, the distance was 2 meter and speed was 2s.

10 2 2

I would like to expand these values to segments of one meter, hence end up with a table that would state:

10 2 2 11 2 2 12 2 2

Now I am aware of the Python bindings and I could achieve what I wanted by the function below, but I would find generate_series still very practical.

DROP FUNCTION expand_per_meter; CREATE FUNCTION expand_per_meter(trip_hash BIGINT, userstopcode STRING, distancesincelastuserstop INTEGER, s INTEGER, t INTEGER) RETURNS TABLE(trip_hash BIGINT, userstopcode STRING, distancesincelastuserstop INTEGER, s INTEGER, t INTEGER) LANGUAGE PYTHON { result = dict() result['trip_hash'] = [] result['userstopcode'] = [] result['distancesincelastuserstop'] = [] result['s'] = [] result['t'] = [] for i in range(0, len(trip_hash)): expanded = range(distancesincelastuserstop[i], distancesincelastuserstop[i] + s[i] + 1) expand = len(expanded) result['trip_hash'] += [trip_hash[i]] * expand result['userstopcode'] += [userstopcode[i]] * expand result['distancesincelastuserstop'] += expanded result['s'] += [s[i]] * expand result['t'] += [t[i]] * expand return result };

-- Stefan _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list -- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany) | ✉︎ torsten.grust@uni-tuebingen.de | db.inf.uni-tuebingen.de

---

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list