Обсуждение: Why is NULL = unbounded for rangetypes?
<div>Hi.</div><div> </div><div>Both of these queries return TRUE because NULL means "unmounded":</div><div><style type="text/css"></style><preclass="western" style="text-align: left"> <font face="DejaVu Sans Mono"><font color="#000080"><b>select daterange</b></font><font color="#000000">(</font><font color="#008000"><b>'2013-07-01'</b></font><font color="#000000">:: </font><font color="#000080"><b>DATE</b></font><font color="#000000">,</font><font color="#000080"><b>null</b></font><font color="#000000">, </font><font color="#008000"><b>'[]'</b></font><fontcolor="#000000">) && </font><font color="#000080"><b>daterange</b></font><fontcolor="#000000">(</font><font color="#008000"><b>'2013-07-04' </b></font><fontcolor="#000000">:: </font><font color="#000080"><b>DATE</b></font><font color="#000000">, </font><font color="#008000"><b>'2013-07-30'</b></font><font color="#000000">:: </font><font color="#000080"><b>DATE</b></font><font color="#000000">,</font><font color="#008000"><b>'[]'</b></font><font color="#000000">);</font></font> <font face="DejaVu Sans Mono"><font color="#000080"><b>select daterange</b></font><font color="#000000">(</font><font color="#000080"><b>null</b></font><fontcolor="#000000">, </font><font color="#008000"><b>'2013-08-11' </b></font><font color="#000000">::</font><font color="#000080"><b>DATE</b></font><font color="#000000">, </font><font color="#008000"><b>'[]'</b></font><fontcolor="#000000">) && </font><font color="#000080"><b>daterange</b></font><fontcolor="#000000">(</font><font color="#008000"><b>'2013-07-04' </b></font><fontcolor="#000000">:: </font><font color="#000080"><b>DATE</b></font><font color="#000000">, </font><font color="#008000"><b>'2013-07-30'</b></font><font color="#000000">:: </font><font color="#000080"><b>DATE</b></font><font color="#000000">,</font><font color="#008000"><b>'[]'</b></font><font color="#000000">);</font></font></pre><div>What isthe rational behind this behavior of NULL?</div></div><div> <div class="origo-email-signature">--<br /> Andreas JosephKrogh <andreak@officenet.no> mob: +47 909 56 963<br /> Senior Software Developer / CTO - OfficeNet AS -http://www.officenet.no<br /> Public key: http://home.officenet.no/~andreak/public_key.asc</div></div>
On Mon, 2013-07-08 at 10:19 +0200, Andreas Joseph Krogh wrote:
> Hi.
>
> Both of these queries return TRUE because NULL means "unmounded":
> select daterange('2013-07-01' :: DATE, null, '[]') && daterange('2013-07-04' :: DATE, '2013-07-30' :: DATE, '[]');
> select daterange(null, '2013-08-11' :: DATE, '[]') && daterange('2013-07-04' :: DATE, '2013-07-30' :: DATE, '[]');
> What is the rational behind this behavior of NULL?
It's just a convenience that passing NULL to a constructor creates an
unbounded range. The alternatives of having extra constructors for
unbounded ranges were discussed, but seemed more awkward.
Note that ranges do not allow either bound to be NULL. That would create
a lot of semantic problems.
Does that answer your question?
Regards,
Jeff Davis
P=C3=A5 mandag 08. juli 2013 kl. 19:16:15, skrev Jeff Davis <<a hre=
f=3D"mailto:pgsql@j-davis.com" target=3D"_blank">pgsql@j-davis.com>:=
<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
0pt 0pt 0.8ex; padding-left: 1ex;">
On =
Mon, 2013-07-08 at 10:19 +0200, Andreas Joseph Krogh wrote:
> Hi.
>=C2=A0
> Both of these queries return TRUE because NULL means "unmounded&q=
uot;:
> select daterange('2013-07-01' :: DATE, null, '[]') && daterang=
e('2013-07-04' :: DATE, '2013-07-30' :: DATE, '[]');
> select daterange(null, '2013-08-11' :: DATE, '[]') && daterang=
e('2013-07-04' :: DATE, '2013-07-30' :: DATE, '[]');
> What is the rational behind this behavior of NULL?
It's just a convenience that passing NULL to a constructor creates an
unbounded range. The alternatives of having extra constructors for
unbounded ranges were discussed, but seemed more awkward.
Note that ranges do not allow either bound to be NULL. That would create
a lot of semantic problems.
Does that answer your question?
=C2=A0
I would expect the queries above to return FALSE and have to use INFIN=
ITY to have them return TRUE. I don't understand what you mean by ranges no=
t allowing either bound to be NULL as it seems to be the case (as in "=
it works").
=C2=A0
--
Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 mob: =
+47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc
=C2=A0=
On Tue, 2013-07-09 at 10:45 +0200, Andreas Joseph Krogh wrote:
> I would expect the queries above to return FALSE and have to use
> INFINITY to have them return TRUE. I don't understand what you mean by
> ranges not allowing either bound to be NULL as it seems to be the case
> (as in "it works").
Although passing NULL to the constructor works, it does *not* create a
range where one bound is NULL. It actually creates an unbounded range;
that is, a range where one bound is infinite.
NULL semantics are far too confusing to be useful with ranges. For
instance, if ranges did support NULLs; the queries you mention would
have to return NULL, not FALSE.
Regards,
Jeff Davis
P=C3=A5 fredag 30. august 2013 kl. 03:23:09, skrev Jeff Davis <<a h= ref=3D"mailto:pgsql@j-davis.com" target=3D"_blank">pgsql@j-davis.com>= ;: <blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt= 0pt 0pt 0.8ex; padding-left: 1ex;"> On = Tue, 2013-07-09 at 10:45 +0200, Andreas Joseph Krogh wrote: > I would expect the queries above to return FALSE and have to use > INFINITY to have them return TRUE. I don't understand what you mean by= > ranges not allowing either bound to be NULL as it seems to be the case= > (as in "it works"). Although passing NULL to the constructor works, it does *not* create a range where one bound is NULL. It actually creates an unbounded range; that is, a range where one bound is infinite. NULL semantics are far too confusing to be useful with ranges. For instance, if ranges did support NULLs; the queries you mention would have to return NULL, not FALSE. =C2=A0 But I agree that returning NULL would be OK, then it would be easy to = catch in queries when starting playing with range-types in queries. Having = it implicitly mean infinity comes as a surprise, to me at least. =C2=A0 But now that I know this it's exactly not a blocker... =C2=A0 -- Andreas Joseph Krogh <andreak@officenet.no>=C2=A0 =C2=A0 =C2=A0 mob: = +47 909 56 963 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no Public key: http://home.officenet.no/~andreak/public_key.asc =C2=A0=
On Fri, 2013-08-30 at 11:22 +0200, Andreas Joseph Krogh wrote:
> But I agree that returning NULL would be OK, then it would be easy to
> catch in queries when starting playing with range-types in queries.
> Having it implicitly mean infinity comes as a surprise, to me at
> least.
Agreed. This was discussed at the time, and the original version of
Range Types experimented with other means of specifying unbounded ranges
in order to avoid this possible confusion.
Unfortunately, everything we tried was awkward one way or another; and
we eventually made the decision to go with greater convenience, even if
it could cause some confusion.
Regards,
Jeff Davis