Re: SQL:2011 application time

On Tue, Jan 2, 2024 at 9:59 AM Paul Jungwirth
<pj@illuminatedcomputing.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.