Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit821f467

Browse files
committed
Make OVERLAPS operators conform to SQL92 spec regarding NULL handling.
As I read it, the spec requires a non-null result in some cases whereone of the inputs is NULL: specifically, if the other endpoint of thatinterval is between the endpoints of the other interval, then the resultis known TRUE despite the missing endpoint. The spec could've been alot simpler if they did not intend this behavior.I did not force an initdb for this change, but if you don't do one you'llstill see the old strict-function behavior.
1 parent8bb4dab commit821f467

File tree

3 files changed

+294
-70
lines changed

3 files changed

+294
-70
lines changed

‎src/backend/utils/adt/date.c

Lines changed: 193 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/date.c,v 1.53 2000/12/03 14:51:01 thomas Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/date.c,v 1.54 2000/12/07 18:38:59 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -529,37 +529,122 @@ time_smaller(PG_FUNCTION_ARGS)
529529
PG_RETURN_TIMEADT((time1<time2) ?time1 :time2);
530530
}
531531

532-
/* overlaps_time()
533-
* Implements the SQL92 OVERLAPS operator.
534-
* Algorithm from Date and Darwen, 1997
532+
/* overlaps_time() --- implements the SQL92 OVERLAPS operator.
533+
*
534+
* Algorithm is per SQL92 spec. This is much harder than you'd think
535+
* because the spec requires us to deliver a non-null answer in some cases
536+
* where some of the inputs are null.
535537
*/
536538
Datum
537539
overlaps_time(PG_FUNCTION_ARGS)
538540
{
539-
TimeADTts1=PG_GETARG_TIMEADT(0);
540-
TimeADTte1=PG_GETARG_TIMEADT(1);
541-
TimeADTts2=PG_GETARG_TIMEADT(2);
542-
TimeADTte2=PG_GETARG_TIMEADT(3);
541+
/* The arguments are TimeADT, but we leave them as generic Datums
542+
* to avoid dereferencing nulls (TimeADT is pass-by-reference!)
543+
*/
544+
Datumts1=PG_GETARG_DATUM(0);
545+
Datumte1=PG_GETARG_DATUM(1);
546+
Datumts2=PG_GETARG_DATUM(2);
547+
Datumte2=PG_GETARG_DATUM(3);
548+
boolts1IsNull=PG_ARGISNULL(0);
549+
boolte1IsNull=PG_ARGISNULL(1);
550+
boolts2IsNull=PG_ARGISNULL(2);
551+
boolte2IsNull=PG_ARGISNULL(3);
543552

544-
/* Make sure we have ordered pairs... */
545-
if (ts1>te1)
546-
{
547-
TimeADTtt=ts1;
553+
#defineTIMEADT_GT(t1,t2) \
554+
(DatumGetTimeADT(t1) >DatumGetTimeADT(t2))
555+
#defineTIMEADT_LT(t1,t2) \
556+
(DatumGetTimeADT(t1) < DatumGetTimeADT(t2))
548557

558+
/*
559+
* If both endpoints of interval 1 are null, the result is null (unknown).
560+
* If just one endpoint is null, take ts1 as the non-null one.
561+
* Otherwise, take ts1 as the lesser endpoint.
562+
*/
563+
if (ts1IsNull)
564+
{
565+
if (te1IsNull)
566+
PG_RETURN_NULL();
567+
/* swap null for non-null */
549568
ts1=te1;
550-
te1=tt;
569+
te1IsNull=true;
551570
}
552-
if (ts2>te2)
571+
elseif (!te1IsNull)
553572
{
554-
TimeADTtt=ts2;
573+
if (TIMEADT_GT(ts1,te1))
574+
{
575+
Datumtt=ts1;
576+
577+
ts1=te1;
578+
te1=tt;
579+
}
580+
}
555581

582+
/* Likewise for interval 2. */
583+
if (ts2IsNull)
584+
{
585+
if (te2IsNull)
586+
PG_RETURN_NULL();
587+
/* swap null for non-null */
556588
ts2=te2;
557-
te2=tt;
589+
te2IsNull= true;
590+
}
591+
elseif (!te2IsNull)
592+
{
593+
if (TIMEADT_GT(ts2,te2))
594+
{
595+
Datumtt=ts2;
596+
597+
ts2=te2;
598+
te2=tt;
599+
}
558600
}
559601

560-
PG_RETURN_BOOL((ts1>ts2&& (ts1<te2||te1<te2))||
561-
(ts1<ts2&& (ts2<te1||te2<te1))||
562-
(ts1==ts2));
602+
/*
603+
* At this point neither ts1 nor ts2 is null, so we can consider three
604+
* cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
605+
*/
606+
if (TIMEADT_GT(ts1,ts2))
607+
{
608+
/* This case is ts1 < te2 OR te1 < te2, which may look redundant
609+
* but in the presence of nulls it's not quite completely so.
610+
*/
611+
if (te2IsNull)
612+
PG_RETURN_NULL();
613+
if (TIMEADT_LT(ts1,te2))
614+
PG_RETURN_BOOL(true);
615+
if (te1IsNull)
616+
PG_RETURN_NULL();
617+
/* If te1 is not null then we had ts1 <= te1 above, and we just
618+
* found ts1 >= te2, hence te1 >= te2.
619+
*/
620+
PG_RETURN_BOOL(false);
621+
}
622+
elseif (TIMEADT_LT(ts1,ts2))
623+
{
624+
/* This case is ts2 < te1 OR te2 < te1 */
625+
if (te1IsNull)
626+
PG_RETURN_NULL();
627+
if (TIMEADT_LT(ts2,te1))
628+
PG_RETURN_BOOL(true);
629+
if (te2IsNull)
630+
PG_RETURN_NULL();
631+
/* If te2 is not null then we had ts2 <= te2 above, and we just
632+
* found ts2 >= te1, hence te2 >= te1.
633+
*/
634+
PG_RETURN_BOOL(false);
635+
}
636+
else
637+
{
638+
/* For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
639+
* rather silly way of saying "true if both are nonnull, else null".
640+
*/
641+
if (te1IsNull||te2IsNull)
642+
PG_RETURN_NULL();
643+
PG_RETURN_BOOL(true);
644+
}
645+
646+
#undef TIMEADT_GT
647+
#undef TIMEADT_LT
563648
}
564649

565650
/* timestamp_time()
@@ -964,53 +1049,122 @@ timetz_mi_interval(PG_FUNCTION_ARGS)
9641049
PG_RETURN_TIMETZADT_P(result);
9651050
}
9661051

967-
/* overlaps_timetz()
968-
* Implements the SQL92 OVERLAPS operator.
969-
* Algorithm from Date and Darwen, 1997
1052+
/* overlaps_timetz() --- implements the SQL92 OVERLAPS operator.
1053+
*
1054+
* Algorithm is per SQL92 spec. This is much harder than you'd think
1055+
* because the spec requires us to deliver a non-null answer in some cases
1056+
* where some of the inputs are null.
9701057
*/
9711058
Datum
9721059
overlaps_timetz(PG_FUNCTION_ARGS)
9731060
{
9741061
/* The arguments are TimeTzADT *, but we leave them as generic Datums
975-
* for convenience of notation.
1062+
* for convenience of notation --- and to avoid dereferencing nulls.
9761063
*/
9771064
Datumts1=PG_GETARG_DATUM(0);
9781065
Datumte1=PG_GETARG_DATUM(1);
9791066
Datumts2=PG_GETARG_DATUM(2);
9801067
Datumte2=PG_GETARG_DATUM(3);
1068+
boolts1IsNull=PG_ARGISNULL(0);
1069+
boolte1IsNull=PG_ARGISNULL(1);
1070+
boolts2IsNull=PG_ARGISNULL(2);
1071+
boolte2IsNull=PG_ARGISNULL(3);
9811072

9821073
#defineTIMETZ_GT(t1,t2) \
9831074
DatumGetBool(DirectFunctionCall2(timetz_gt,t1,t2))
9841075
#defineTIMETZ_LT(t1,t2) \
9851076
DatumGetBool(DirectFunctionCall2(timetz_lt,t1,t2))
986-
#defineTIMETZ_EQ(t1,t2) \
987-
DatumGetBool(DirectFunctionCall2(timetz_eq,t1,t2))
9881077

989-
/* Make sure we have ordered pairs... */
990-
if (TIMETZ_GT(ts1,te1))
1078+
/*
1079+
* If both endpoints of interval 1 are null, the result is null (unknown).
1080+
* If just one endpoint is null, take ts1 as the non-null one.
1081+
* Otherwise, take ts1 as the lesser endpoint.
1082+
*/
1083+
if (ts1IsNull)
9911084
{
992-
Datumtt=ts1;
993-
1085+
if (te1IsNull)
1086+
PG_RETURN_NULL();
1087+
/* swap null for non-null */
9941088
ts1=te1;
995-
te1=tt;
1089+
te1IsNull=true;
9961090
}
997-
if (TIMETZ_GT(ts2,te2))
1091+
elseif (!te1IsNull)
9981092
{
999-
Datumtt=ts2;
1093+
if (TIMETZ_GT(ts1,te1))
1094+
{
1095+
Datumtt=ts1;
1096+
1097+
ts1=te1;
1098+
te1=tt;
1099+
}
1100+
}
10001101

1102+
/* Likewise for interval 2. */
1103+
if (ts2IsNull)
1104+
{
1105+
if (te2IsNull)
1106+
PG_RETURN_NULL();
1107+
/* swap null for non-null */
10011108
ts2=te2;
1002-
te2=tt;
1109+
te2IsNull= true;
1110+
}
1111+
elseif (!te2IsNull)
1112+
{
1113+
if (TIMETZ_GT(ts2,te2))
1114+
{
1115+
Datumtt=ts2;
1116+
1117+
ts2=te2;
1118+
te2=tt;
1119+
}
10031120
}
10041121

1005-
PG_RETURN_BOOL((TIMETZ_GT(ts1,ts2)&&
1006-
(TIMETZ_LT(ts1,te2)||TIMETZ_LT(te1,te2)))||
1007-
(TIMETZ_GT(ts2,ts1)&&
1008-
(TIMETZ_LT(ts2,te1)||TIMETZ_LT(te2,te1)))||
1009-
TIMETZ_EQ(ts1,ts2));
1122+
/*
1123+
* At this point neither ts1 nor ts2 is null, so we can consider three
1124+
* cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
1125+
*/
1126+
if (TIMETZ_GT(ts1,ts2))
1127+
{
1128+
/* This case is ts1 < te2 OR te1 < te2, which may look redundant
1129+
* but in the presence of nulls it's not quite completely so.
1130+
*/
1131+
if (te2IsNull)
1132+
PG_RETURN_NULL();
1133+
if (TIMETZ_LT(ts1,te2))
1134+
PG_RETURN_BOOL(true);
1135+
if (te1IsNull)
1136+
PG_RETURN_NULL();
1137+
/* If te1 is not null then we had ts1 <= te1 above, and we just
1138+
* found ts1 >= te2, hence te1 >= te2.
1139+
*/
1140+
PG_RETURN_BOOL(false);
1141+
}
1142+
elseif (TIMETZ_LT(ts1,ts2))
1143+
{
1144+
/* This case is ts2 < te1 OR te2 < te1 */
1145+
if (te1IsNull)
1146+
PG_RETURN_NULL();
1147+
if (TIMETZ_LT(ts2,te1))
1148+
PG_RETURN_BOOL(true);
1149+
if (te2IsNull)
1150+
PG_RETURN_NULL();
1151+
/* If te2 is not null then we had ts2 <= te2 above, and we just
1152+
* found ts2 >= te1, hence te2 >= te1.
1153+
*/
1154+
PG_RETURN_BOOL(false);
1155+
}
1156+
else
1157+
{
1158+
/* For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
1159+
* rather silly way of saying "true if both are nonnull, else null".
1160+
*/
1161+
if (te1IsNull||te2IsNull)
1162+
PG_RETURN_NULL();
1163+
PG_RETURN_BOOL(true);
1164+
}
10101165

10111166
#undef TIMETZ_GT
10121167
#undef TIMETZ_LT
1013-
#undef TIMETZ_EQ
10141168
}
10151169

10161170
/* timestamp_timetz()

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp