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/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/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/39Group by and distinct period: Temporal bounds must be FQN, not only column names2016-09-08T10:54:33+02:00Moser PeterGroup by and distinct period: Temporal bounds must be FQN, not only column names*Created by: Piiit*
We need this to point to the correct column when multiple table references are used.
For instance:
```
select a, count(*) from e1, e2 group by period (e2.t) a;
```
Is this really the case? Needs to be checked!*Created by: Piiit*
We need this to point to the correct column when multiple table references are used.
For instance:
```
select a, count(*) from e1, e2 group by period (e2.t) a;
```
Is this really the case? Needs to be checked!Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/36GROUP BY PERIOD: Hardcoded alias if self-normalization is used2016-09-08T10:53:40+02:00Moser PeterGROUP BY PERIOD: Hardcoded alias if self-normalization is used*Created by: Piiit*
`N(r;r)` for example needs an explicit alias for the right argument.
Code:
```
/* PEMOSER FIXME Hardcoded alias! */
((RangeVar *) normalizer->rarg)->alias = makeAlias("r_periodgroupby", NIL);
```
`r_periodg...*Created by: Piiit*
`N(r;r)` for example needs an explicit alias for the right argument.
Code:
```
/* PEMOSER FIXME Hardcoded alias! */
((RangeVar *) normalizer->rarg)->alias = makeAlias("r_periodgroupby", NIL);
```
`r_periodgroupby` must be chosen at runtime, it must be an unique alias name.Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/31Function to make checks for ts and te2015-11-11T11:56:57+01:00Moser PeterFunction to make checks for ts and te*Created by: Piiit*
```
SELECT *, checkT(ts,te), ...
where
checkT checks various constraints (see wiki), and fails on any error.
```*Created by: Piiit*
```
SELECT *, checkT(ts,te), ...
where
checkT checks various constraints (see wiki), and fails on any error.
```Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/27Test: Missing / wrong input -> Meaningful error messages (i.e., useful for en...2016-06-21T16:20:44+02:00Moser PeterTest: Missing / wrong input -> Meaningful error messages (i.e., useful for end-user)*Created by: Piiit*
*Created by: Piiit*
Future workhttps://gitlab.inf.unibz.it/peter-moser/tpg/-/issues/11R normalize R not possible2016-06-21T16:20:15+02:00Moser PeterR normalize R not possible*Created by: Piiit*
```
select * from (r03 normalize r03 on a=a with (ts,te,ts,te))x;
ERROR: table name "r03" specified more than once
```*Created by: Piiit*
```
select * from (r03 normalize r03 on a=a with (ts,te,ts,te))x;
ERROR: table name "r03" specified more than once
```Future work