Re: SQL:2011 application time

On Tue, Jan 2, 2024 at 9:59 AM Paul Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
>
> On 12/31/23 00:51, Paul Jungwirth wrote:
> > That's it for now.
>
> Here is another update. I fixed FOR PORTION OF on partitioned tables, in particular when the attnums
> are different from the root partition.
>
> Rebased to cea89c93a1.
>

Hi.

+/*
+ * range_without_portion_internal - Sets outputs and outputn to the ranges
+ * remaining and their count (respectively) after subtracting r2 from r1.
+ * The array should never contain empty ranges.
+ * The outputs will be ordered. We expect that outputs is an array of
+ * RangeType pointers, already allocated with two slots.
+ */
+void
+range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1,
+ RangeType *r2, RangeType **outputs, int *outputn)
+{
+ int cmp_l1l2,
+ cmp_l1u2,
+ cmp_u1l2,
+ cmp_u1u2;
+ RangeBound lower1,
+ lower2;
+ RangeBound upper1,
+ upper2;
+ bool empty1,
+ empty2;
+
+ range_deserialize(typcache, r1, &lower1, &upper1, &empty1);
+ range_deserialize(typcache, r2, &lower2, &upper2, &empty2);
+
+ if (empty1)
+ {
+ /* if r1 is empty then r1 - r2 is empty, so return zero results */
+ *outputn = 0;
+ return;
+ }
+ else if (empty2)
+ {
+ /* r2 is empty so the result is just r1 (which we know is not empty) */
+ outputs[0] = r1;
+ *outputn = 1;
+ return;
+ }
+
+ /*
+ * Use the same logic as range_minus_internal,
+ * but support the split case
+ */
+ cmp_l1l2 = range_cmp_bounds(typcache, &lower1, &lower2);
+ cmp_l1u2 = range_cmp_bounds(typcache, &lower1, &upper2);
+ cmp_u1l2 = range_cmp_bounds(typcache, &upper1, &lower2);
+ cmp_u1u2 = range_cmp_bounds(typcache, &upper1, &upper2);
+
+ if (cmp_l1l2 < 0 && cmp_u1u2 > 0)
+ {
+ lower2.inclusive = !lower2.inclusive;
+ lower2.lower = false; /* it will become the upper bound */
+ outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL);
+
+ upper2.inclusive = !upper2.inclusive;
+ upper2.lower = true; /* it will become the lower bound */
+ outputs[1] = make_range(typcache, &upper2, &upper1, false, NULL);
+
+ *outputn = 2;
+ }
+ else if (cmp_l1u2 > 0 || cmp_u1l2 < 0)
+ {
+ outputs[0] = r1;
+ *outputn = 1;
+ }
+ else if (cmp_l1l2 >= 0 && cmp_u1u2 <= 0)
+ {
+ *outputn = 0;
+ }
+ else if (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0)
+ {
+ lower2.inclusive = !lower2.inclusive;
+ lower2.lower = false; /* it will become the upper bound */
+ outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL);
+ *outputn = 1;
+ }
+ else if (cmp_l1l2 >= 0 && cmp_u1u2 >= 0 && cmp_l1u2 <= 0)
+ {
+ upper2.inclusive = !upper2.inclusive;
+ upper2.lower = true; /* it will become the lower bound */
+ outputs[0] = make_range(typcache, &upper2, &upper1, false, NULL);
+ *outputn = 1;
+ }
+ else
+ {
+ elog(ERROR, "unexpected case in range_without_portion");
+ }
+}

I am confused.
say condition: " (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0)"
the following code will only run PartA, never run PartB?

`
else if (cmp_l1l2 >= 0 && cmp_u1u2 <= 0)
PartA
else if (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0)
PartB
`

minimum example:
#include<stdio.h>
#include<string.h>
#include<stdlib.h>
#include<assert.h>
int
main(void)
{
int cmp_l1l2;
int cmp_u1u2;
int cmp_u1l2;
int cmp_l1u2;
cmp_l1u2 = -1;
cmp_l1l2 = 0;
cmp_u1u2 = 0;
cmp_u1l2 = 0;
assert(cmp_u1l2 == 0);
if (cmp_l1u2 > 0 || cmp_u1l2 < 0)
printf("calling partA\n");
else if (cmp_l1l2 >= 0 && cmp_u1u2 <= 0)
printf("calling partB\n");
else if (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0)
printf("calling partC\n");
}

I am confused with the name "range_without_portion", I think
"range_not_overlap" would be better.

select numrange(1.1, 2.2) @- numrange(2.0, 3.0);
the result is not the same as
select numrange(2.0, 3.0) @- numrange(1.1, 2.2);

So your categorize oprkind as 'b' for operator "@-" is wrong?
select oprname,oprkind,oprcanhash,oprcanmerge,oprleft,oprright,oprresult,oprcode
from pg_operator
where oprname = '@-';

aslo
select count(*), oprkind from pg_operator group by oprkind;
there are only 5% are prefix operators.
maybe we should design it as:
1. if both inputs are empty range, the result array is empty.
2. if both inputs are non-empty and never overlaps, put both of them
to the result array.
3. if one input is empty another one is not, then put the non-empty
one into the result array.

after applying the patch: now the catalog data seems not correct to me.
SELECT a1.amopfamily
,a1.amoplefttype::regtype
,a1.amoprighttype
,a1.amopstrategy
,amoppurpose
,amopsortfamily
,amopopr
,op.oprname
,am.amname
FROM pg_amop as a1 join pg_operator op on op.oid = a1.amopopr
join pg_am am on am.oid = a1.amopmethod
where amoppurpose = 'p';
output:
amopfamily | amoplefttype | amoprighttype | amopstrategy |
amoppurpose | amopsortfamily | amopopr | oprname | amname
------------+---------------+---------------+--------------+-------------+----------------+---------+---------+--------
2593 | box | 603 | 31 | p
| 0 | 803 | # | gist
3919 | anyrange | 3831 | 31 | p
| 0 | 3900 | * | gist
6158 | anymultirange | 4537 | 31 | p
| 0 | 4394 | * | gist
3919 | anyrange | 3831 | 32 | p
| 0 | 8747 | @- | gist
6158 | anymultirange | 4537 | 32 | p
| 0 | 8407 | @- | gist
(5 rows)

select oprcode, oprname, oprleft::regtype
from pg_operator opr
where opr.oprname in ('#','*','@-')
and oprleft = oprright
and oprleft in (603,3831,4537);
output:

oprcode | oprname | oprleft
----------------------------+---------+---------------
box_intersect | # | box
range_intersect | * | anyrange
multirange_intersect | * | anymultirange
range_without_portion | @- | anyrange
multirange_without_portion | @- | anymultirange
(5 rows)

should amoppurpose = 'p' is true apply to ' @-' operator?

catalog-pg-amop.html:
`
amopsortfamily oid (references pg_opfamily.oid):
The B-tree operator family this entry sorts according to, if an
ordering operator; zero if a search operator
`
you should also update the above entry, the amopsortfamily is also
zero for "portion operator" for the newly implemented "portion
operator".

v21-0006-Add-UPDATE-DELETE-FOR-PORTION-OF.patch
create mode 100644 src/backend/utils/adt/period.c
create mode 100644 src/include/utils/period.h
you should put these two files to v21-0008-Add-PERIODs.patch.
it's not related to that patch, it also makes people easy to review.