joinable generate_series with arguments from a table

Stefan Manegold Stefan.Manegold at cwi.nl
Wed Jun 13 12:45:49 CEST 2018


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

-- 
| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |


More information about the users-list mailing list