tpg issueshttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues2017-09-17T21:00:13+02:00https://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/105GROUP BY PERIOD: Does not support GROUPING SETS2017-09-17T21:00:13+02:00Moser PeterGROUP BY PERIOD: Does not support GROUPING SETS```
A=# select a, count(*) from test group by period with (ts,te) a;
a | count
---+------- ...```
A=# select a, count(*) from test group by period with (ts,te) a;
a | count
---+-------
a | 1
j | 1
a | 1
(3 rows)
A=# select a, count(*) from test group by period with (ts,te) cube(a);
ERROR: column unnamed_normalize_2.cube does not exist
```Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/104src/backend/parser/parse_temporal.c contains outdated comments (master branch)2017-09-17T21:00:13+02:00Moser Petersrc/backend/parser/parse_temporal.c contains outdated comments (master branch)Update comments about query rewrites and add query rewrites for range types.
For instance,
`transformTemporalJoin` has a deprecated documentation comment.Update comments about query rewrites and add query rewrites for range types.
For instance,
`transformTemporalJoin` has a deprecated documentation comment.Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/96PG testing routine for temporal primitives is not complete2017-09-17T21:00:13+02:00Moser PeterPG testing routine for temporal primitives is not complete*Created by: Piiit*
All major test cases have been added, we consider the rest as future work.
The following test cases are still missing for the PRIMITIVE TEST:
- `test-ambiguous-columns.sql`
- `test-indexes.sql`
- `test-subselec...*Created by: Piiit*
All major test cases have been added, we consider the rest as future work.
The following test cases are still missing for the PRIMITIVE TEST:
- `test-ambiguous-columns.sql`
- `test-indexes.sql`
- `test-subselects-and-aliases.sql`
NaN for floats is not checked yet. It should produce an ERROR, but currently it is treated like a regular number.
We remove it from the test for now.
```
INSERT INTO tpg_table12 VALUES
(0, 1.0, 'NaN'),
(1, 'NaN', 2.0);
-- Data types: Double precision with "NaN" (ERROR expected, not implemented yet)
SELECT a, ts, te FROM (
tpg_table12 t1 ALIGN tpg_table12 t2
ON TRUE
WITH (ts, te, ts, te)
) x;
-- Data types: Double precision with "NaN" (ERROR expected, not implemented yet)
SELECT a, ts, te FROM (
tpg_table12 t1 NORMALIZE tpg_table12 t2
ON TRUE
WITH (ts, te, ts, te)
) x;
```Primitiveshttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/95Collect re-used strings (for ex., error messages)2017-09-17T21:00:13+02:00Moser PeterCollect re-used strings (for ex., error messages)*Created by: Piiit*
Also `parse_cte.c` does this in `static const char *const recursion_errormsgs[]`.
With this we could collect error messages, hints, and internal-use-only column names etc.*Created by: Piiit*
Also `parse_cte.c` does this in `static const char *const recursion_errormsgs[]`.
With this we could collect error messages, hints, and internal-use-only column names etc.Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/94We should use execdebug.h stuff for executor debugging2017-09-17T21:00:13+02:00Moser PeterWe should use execdebug.h stuff for executor debugging*Created by: Piiit*
*Created by: Piiit*
Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/93Find correct way to set error codes in errcodes.txt2017-09-17T21:00:13+02:00Moser PeterFind correct way to set error codes in errcodes.txt*Created by: Piiit*
*Created by: Piiit*
Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/91Use right terminology2017-09-17T21:00:13+02:00Moser PeterUse right terminology*Created by: Piiit*
Instant timestamp instead of scalar boundaries for example. Check words...*Created by: Piiit*
Instant timestamp instead of scalar boundaries for example. Check words...Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/89TEMPORAL SETOPS: Change syntax from "UNION PERIOD" to "PERIOD UNION"2017-09-17T21:00:13+02:00Moser PeterTEMPORAL SETOPS: Change syntax from "UNION PERIOD" to "PERIOD UNION"*Created by: Piiit*
Belongs to #88.*Created by: Piiit*
Belongs to #88.Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/88TEMPORAL SET OPERATIONS2017-09-17T21:00:13+02:00Moser PeterTEMPORAL SET OPERATIONS*Created by: Piiit*
*Created by: Piiit*
First full versionhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/87TEMPORAL SETOPS: With additional rewrite rules we could support the "all" flag2017-09-17T21:00:13+02:00Moser PeterTEMPORAL SETOPS: With additional rewrite rules we could support the "all" flag*Created by: Piiit*
*Created by: Piiit*
Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/84TEMPORAL JOINS: "schema.relation.column" does not work for boundaries2017-09-17T21:00:13+02:00Moser PeterTEMPORAL JOINS: "schema.relation.column" does not work for boundaries*Created by: Piiit*
```
A=# select * from (e as e(x,y,t)
full join period with (e.t,public.f.tf) f on x=a and y=b) r ;
ERROR: i...*Created by: Piiit*
```
A=# select * from (e as e(x,y,t)
full join period with (e.t,public.f.tf) f on x=a and y=b) r ;
ERROR: invalid reference to FROM-clause entry for table "f"
LINE 2: full join period with (e.t,public.f.tf) f on x=a and y=b) r ...
^
HINT: There is an entry for table "f", but it cannot be referenced from this part of the query.
```
Belongs to #55.Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/82TEMPORAL JOINS: Relation does not exists error, talks about a WITH item -> hi...2017-09-17T21:00:13+02:00Moser PeterTEMPORAL JOINS: Relation does not exists error, talks about a WITH item -> hide internals*Created by: Piiit*
We need to hide internal rewrites, to not confuse the user. From the input there is no WITH clause at all!
```
A=# select * from (r ...*Created by: Piiit*
We need to hide internal rewrites, to not confuse the user. From the input there is no WITH clause at all!
```
A=# select * from (r
join period with (ts,te,ts,te) s on r.b=d) r ;
ERROR: relation "r" does not exist
LINE 1: select * from (r
^
DETAIL: There is a WITH item named "r", but it cannot be referenced from this part of the query.
HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
```
Belongs to #55Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/74Create first-steps / how-to-start / first-compilation README file2017-09-17T21:00:13+02:00Moser PeterCreate first-steps / how-to-start / first-compilation README file*Created by: Piiit*
We need documentation for new users that want to try out the patch.
- Prerequisites for the compilation of PG
- First steps to set up a cluster, database, ...
- Connect (warn that pgAdmin may show errors)
- Exa...*Created by: Piiit*
We need documentation for new users that want to try out the patch.
- Prerequisites for the compilation of PG
- First steps to set up a cluster, database, ...
- Connect (warn that pgAdmin may show errors)
- Examples and howtos for primitives
- Examples and howtos for temporal operators
Primitiveshttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/73Wrong error hints for internal-use-only columns2017-09-17T21:00:13+02:00Moser PeterWrong error hints for internal-use-only columns*Created by: Piiit*
```
A=# select * from ((select * from rt) rt(rn, t) align rt s on true with (rn_1,a,p1,h)) x;
ERROR: column rt.rn_1 does not exist
LINE 1: ...ect * fr...*Created by: Piiit*
```
A=# select * from ((select * from rt) rt(rn, t) align rt s on true with (rn_1,a,p1,h)) x;
ERROR: column rt.rn_1 does not exist
LINE 1: ...ect * from rt) rt(rn, t) align rt s on true with (rn_1,a,p1,...
^
HINT: Perhaps you meant to reference the column "rt"."rn_0".
```
rn_0 should not be visible here!First full versionhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/67TEMPORAL JOINS: We loose the original temporal boundaries2017-09-17T21:00:13+02:00Moser PeterTEMPORAL JOINS: We loose the original temporal boundaries*Created by: Piiit*
Normally a join operation does not remove any columns. The temporal join discards the original boundary values (i.e., the columns that get internally renamed to Us/Ue and Vs/Ve).
The question is: Should we keep t...*Created by: Piiit*
Normally a join operation does not remove any columns. The temporal join discards the original boundary values (i.e., the columns that get internally renamed to Us/Ue and Vs/Ve).
The question is: Should we keep them and rename them back to the original bound column names (for example, Ts/Te)?
Belongs to #55, #65, and #85 Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/59TEMPORAL JOINS: Multiple joins2017-09-17T21:00:13+02:00Moser PeterTEMPORAL JOINS: Multiple joins*Created by: Piiit*
```
A=# select * from s natural join s x right join period with (ts,te,ts,te) r on d=b;
ERROR: Argument has no alias or is not supported.
A=# select * ...*Created by: Piiit*
```
A=# select * from s natural join s x right join period with (ts,te,ts,te) r on d=b;
ERROR: Argument has no alias or is not supported.
A=# select * from (s natural join s x) x right join period with (ts,te,ts,te) r on d=b;
ERROR: Argument has no alias or is not supported.
```Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/55TEMPORAL JOINS2017-09-17T21:00:13+02:00Moser PeterTEMPORAL JOINS*Created by: Piiit*
This is a meta issue to collect all issues regarding temporal joins.*Created by: Piiit*
This is a meta issue to collect all issues regarding temporal joins.First full versionhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/48Push-down does not work, if we use CTEs to materialize temporal primitive sub...2017-09-17T21:00:13+02:00Moser PeterPush-down does not work, if we use CTEs to materialize temporal primitive sub-queries*Created by: Piiit*
The filter `Filter: (r_1.a = 'A'::bpchar)` gets pushed down into sub-selects if we do not use any CTEs.
That's good, since we calculate temporal primitives with less rows, sorting out any unwanted rows as fast as po...*Created by: Piiit*
The filter `Filter: (r_1.a = 'A'::bpchar)` gets pushed down into sub-selects if we do not use any CTEs.
That's good, since we calculate temporal primitives with less rows, sorting out any unwanted rows as fast as possible.
```
A=# explain analyze select * from (select * from (r align r r2 on r.a=r2.a with (ts,te,ts,te)) where ts=4 and a='A') x;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on r (cost=96.00..96.74 rows=1 width=24) (actual time=0.028..0.028 rows=0 loops=1)
Filter: (r.ts = 4)
-> Adjustment(for ALIGN) (cost=96.00..96.44 rows=24 width=40) (actual time=0.028..0.028 rows=0 loops=1)
-> Sort (cost=96.00..96.02 rows=8 width=40) (actual time=0.026..0.026 rows=0 loops=1)
Sort Key: r_1.rn, (GREATEST(r_1.ts, r2.ts)), (LEAST(r_1.te, r2.te))
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.00..95.88 rows=8 width=40) (actual time=0.014..0.014 rows=0 loops=1)
Join Filter: ((r_1.ts < r2.te) AND (r_1.te > r2.ts) AND (r_1.a = r2.a))
-> Subquery Scan on r_1 (cost=0.00..64.95 rows=8 width=32) (actual time=0.014..0.014 rows=0 loops=1)
Filter: (r_1.a = 'A'::bpchar)
Rows Removed by Filter: 3
-> WindowAgg (cost=0.00..45.33 rows=1570 width=24) (actual time=0.009..0.010 rows=3 loops=1)
-> Seq Scan on r r_2 (cost=0.00..25.70 rows=1570 width=24) (actual time=0.004..0.005 rows=3 loops=1)
-> Materialize (cost=0.00..29.66 rows=8 width=16) (never executed)
-> Seq Scan on r r2 (cost=0.00..29.62 rows=8 width=16) (never executed)
Filter: (a = 'A'::bpchar)
Planning time: 0.113 ms
Execution time: 0.058 ms
(18 rows)
```
If we use CTEs instead, the filter `Filter: (r_1.a = 'A'::bpchar)` is on top, resulting in a late filtering. Therefore, we must handle all unwanted rows inside the temporal primitives before we can sort them out. That is slower!
```
A=# explain analyze with r as (with r as ( select * from r ) select * from (r align r r2 on r.a=r2.a with (ts,te,ts,te)) ) select * from r w
here ts=4 and a='A';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
----
CTE Scan on r (cost=761.62..879.37 rows=1 width=24) (actual time=0.108..0.108 rows=0 loops=1)
Filter: ((ts = 4) AND (a = 'A'::bpchar))
Rows Removed by Filter: 3
CTE r
-> Subquery Scan on r_2 (cost=628.17..761.62 rows=4710 width=24) (actual time=0.097..0.102 rows=3 loops=1)
CTE r
-> Seq Scan on r r_1 (cost=0.00..25.70 rows=1570 width=24) (actual time=0.007..0.008 rows=3 loops=1)
-> Adjustment(for ALIGN) (cost=602.47..688.82 rows=4710 width=40) (actual time=0.096..0.100 rows=3 loops=1)
-> Sort (cost=602.47..606.40 rows=1570 width=40) (actual time=0.091..0.092 rows=3 loops=1)
Sort Key: (row_number() OVER (?)), (GREATEST(r_3.ts, r2.ts)), (LEAST(r_3.te, r2.te))
Sort Method: quicksort Memory: 25kB
-> Merge Left Join (cost=264.80..519.13 rows=1570 width=40) (actual time=0.069..0.076 rows=3 loops=1)
Merge Cond: (r_3.a = r2.a)
Join Filter: ((r_3.ts < r2.te) AND (r_3.te > r2.ts))
-> Sort (cost=150.06..153.99 rows=1570 width=32) (actual time=0.042..0.042 rows=3 loops=1)
Sort Key: r_3.a
Sort Method: quicksort Memory: 25kB
-> WindowAgg (cost=0.00..51.03 rows=1570 width=24) (actual time=0.016..0.025 rows=3 loops=1)
-> CTE Scan on r r_3 (cost=0.00..31.40 rows=1570 width=24) (actual time=0.008..0.010 rows=3 loops
=1)
-> Sort (cost=114.74..118.66 rows=1570 width=16) (actual time=0.018..0.019 rows=3 loops=1)
Sort Key: r2.a
Sort Method: quicksort Memory: 25kB
-> CTE Scan on r r2 (cost=0.00..31.40 rows=1570 width=16) (actual time=0.001..0.003 rows=3 loops=1)
Planning time: 0.271 ms
Execution time: 0.217 ms
(25 rows)
```Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/44SELECT DISTINCT ON (expression [, expression ...]) select_list ... not possib...2017-09-17T21:00:13+02:00Moser PeterSELECT DISTINCT ON (expression [, expression ...]) select_list ... not possible with PERIOD*Created by: Piiit*
Is this a bug?*Created by: Piiit*
Is this a bug?Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/41Normalization of an already normalized relation2017-09-17T21:00:13+02:00Moser PeterNormalization of an already normalized relation*Created by: Piiit*
Is the following assumption correct?
```
/*
* Create a subquery to handle temporal normalization, if not done
* already by the GROUP BY PERIOD transformation above. Normalization
* of a already normalized rela...*Created by: Piiit*
Is the following assumption correct?
```
/*
* Create a subquery to handle temporal normalization, if not done
* already by the GROUP BY PERIOD transformation above. Normalization
* of a already normalized relation has no effect, if the same bounds
* and non-temporal attributes are used.
* PEMOSER Check if this is always the case if period group by and
* distinct period are both set.
*/
```First full version