Message-ID: <d5b6527c-7386-4756-a633-4fc90622aa4e@illuminatedcomputing.com>
Date: Mon, 8 Jan 2024 10:54:13 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
Content-Language: en-US
To: jian he <jian.universality@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <CACJufxGt4oYfa7UN2eAXymgOKH1irbhC5eY_oNkkdj30w-fefw@mail.gmail.com>
 <CACJufxExyO_mN8-OkgaTdgqmE5OfE=RBoQXizf=8uRj4c-pAYw@mail.gmail.com>
 <CACJufxGeUsmxOhnd2760kBv-hUWRwCy7A0s45pGSPTe8DJVTGw@mail.gmail.com>
 <2d9740ad-3bb7-473c-8441-344351caa8ee@illuminatedcomputing.com>
 <fd41e772-296e-43e9-a70e-2e27bb8b1cd5@eisentraut.org>
 <af62a16b-9f88-4746-8e87-fdf0c242b266@illuminatedcomputing.com>
 <a37f180d-b54b-46d6-9e52-cc15f32be200@eisentraut.org>
 <596fefdd-6202-4228-b7c6-f240b1cbe06e@eisentraut.org>
 <df018b96-f0dc-49ba-98af-3a3983f683fe@illuminatedcomputing.com>
 <35400587-6865-4a2c-9ea4-4761669478ea@eisentraut.org>
 <71b45adb-b496-4e58-b134-0a091014e63f@illuminatedcomputing.com>
 <159ba553-43c7-4557-bf05-74447cc6013b@illuminatedcomputing.com>
 <CACJufxG4DvrXD1=Ex2gt2_7U+nF0PZ35qDmqprtFkFd+3E9dTw@mail.gmail.com>
 <CACJufxEJAKEVc-Uv6X57BS7=c_wAuPF7RXo7qLdEZWet88r08Q@mail.gmail.com>
From: Paul Jungwirth <pj@illuminatedcomputing.com>
In-Reply-To: <CACJufxEJAKEVc-Uv6X57BS7=c_wAuPF7RXo7qLdEZWet88r08Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d5b6527c-7386-4756-a633-4fc90622aa4e%40illuminatedcomputing.com>
Precedence: bulk

On 1/8/24 06:54, jian he wrote:
 > On Fri, Jan 5, 2024 at 1:06 PM jian he <jian.universality@gmail.com> wrote:
 >
 > range_intersect returns the intersection of two ranges.
 > I think here we are doing the opposite.
 > names the main SQL function "range_not_intersect" and the internal
 > function as "range_not_intersect_internal" should be fine.
 > so people don't need to understand the meaning of "portion".

Thank you for helping me figure out a name here! I realize that can be a bike-sheddy kind of 
discussion, so let me share some of my principles.

Range and multirange are highly mathematically "pure", and that's something I value in them. It 
makes them more general-purpose, less encumbered by edge cases, easier to combine, and easier to 
reason about. Preserving that close connection to math is a big goal.

What I've called `without_portion` is (like) a closed form of minus (hence `@-` for the operator). 
Minus isn't closed under everything (e.g. ranges), so `without_portion` adds arrays---much as to 
close subtraction we add negative numbers and to close division we add rationals). We get the same 
effect from multiranges, but that only buys us range support. It would be awesome to support 
arbitrary types: ranges, multiranges, mdranges, boxes, polygons, inets, etc., so I think an array is 
the way to go here. And then each array element is a "leftover". What do we call a closed form of 
minus that returns arrays?

Using "not" suggests a function that returns true/false, but `@-` returns an array of things. So 
instead of "not" let's consider "complement". I think that's what you're expressing re intersection.

But `@-` is not the same as the complement of intersection. For one thing, `@-` is not commutative. 
`old_range @- target_portion` is not the same as `target_portion @- old_range`. But 
`complement(old_range * target_portion)` *is* the same as `complement(target_portion * old_range)`. 
Or from another angle: it's true that `old_range @- target_portion = old_range @- (old_range * 
target_portion)`, but the intersection isn't "doing" anything here. It's true that intersection and 
minus both "reduce" what you put in, but minus is more accurate.

So I think we want a name that captures that idea of "minus". Both "not" and "intersection" are 
misleading IMO.

Of course "minus" is already taken (and you wouldn't expect it to return arrays anyway), which is 
why I'm thinking about names like "without" or "except". Or maybe "multi-minus". I still think 
"without portion" is the closest to capturing everything above (and avoids ambiguity with other SQL 
operations). And the "portion" ties the operator to `FOR PORTION OF`, which is its purpose. But I 
wouldn't be surprised if there were something better.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com