joinable generate_series with arguments from a table

Mark Clements mark.clements at ki.se
Wed Jun 13 14:28:10 CEST 2018


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



More information about the users-list mailing list