From 432a671517e78061edc87d18aec291f5629fcbe6 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Thu, 27 Aug 2020 14:49:21 +0900 Subject: [PATCH v1] Fix NaN handling of some geometric operators and functions Some geometric operators shows somewhat odd behavior comes from NaN handling and that leads to inconsistency between index scan and seq scan on geometric conditions. For example: point '(NaN,NaN)' <-> polygon '((0,0),(0,1),(1,1))' => 0, not NaN point '(0.3,0.5)' <-> polygon '((0,0),(0,NaN),(1,1))' => 1.14, not NaN point '(NaN,NaN)' <@ polygon '((0,0),(0,1),(1,1))' => true, not false Some other functions returned totally wrong result like this: point '(1e+300,Infinity)' ## box '(2,2),(0,0)' => '(0,2)' Fix NaN handling of geo_ops.c so that these generates saner results. --- src/backend/utils/adt/geo_ops.c | 183 ++++++++++++++++-- src/test/regress/expected/create_index.out | 2 +- src/test/regress/expected/geometry.out | 209 +++++++++------------ 3 files changed, 248 insertions(+), 146 deletions(-) diff --git a/src/backend/utils/adt/geo_ops.c b/src/backend/utils/adt/geo_ops.c index a7db783958..916ae87d05 100644 --- a/src/backend/utils/adt/geo_ops.c +++ b/src/backend/utils/adt/geo_ops.c @@ -904,9 +904,19 @@ box_intersect(PG_FUNCTION_ARGS) result = (BOX *) palloc(sizeof(BOX)); - result->high.x = float8_min(box1->high.x, box2->high.x); + /* float8_min conceals NaN, check separately for NaNs */ + if (unlikely(isnan(box1->high.x) || isnan(box2->high.x))) + result->high.x = get_float8_nan(); + else + result->high.x = float8_min(box1->high.x, box2->high.x); + result->low.x = float8_max(box1->low.x, box2->low.x); - result->high.y = float8_min(box1->high.y, box2->high.y); + + if (unlikely(isnan(box1->high.y) || isnan(box2->high.y))) + result->high.x = get_float8_nan(); + else + result->high.y = float8_min(box1->high.y, box2->high.y); + result->low.y = float8_max(box1->low.y, box2->low.y); PG_RETURN_BOX_P(result); @@ -1061,6 +1071,10 @@ line_construct(LINE *result, Point *pt, float8 m) result->A = -1.0; result->B = 0.0; result->C = pt->x; + + /* Avoid creating a valid line from an invalid point */ + if (unlikely(isnan(pt->y))) + result->C = get_float8_nan(); } else { @@ -1084,6 +1098,7 @@ line_construct_pp(PG_FUNCTION_ARGS) Point *pt2 = PG_GETARG_POINT_P(1); LINE *result = (LINE *) palloc(sizeof(LINE)); + /* NaNs are considered to be equal by point_eq_point */ if (point_eq_point(pt1, pt2)) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), @@ -1104,8 +1119,12 @@ line_intersect(PG_FUNCTION_ARGS) { LINE *l1 = PG_GETARG_LINE_P(0); LINE *l2 = PG_GETARG_LINE_P(1); + Point xp; - PG_RETURN_BOOL(line_interpt_line(NULL, l1, l2)); + if (line_interpt_line(&xp, l1, l2) && !isnan(xp.x) && !isnan(xp.y)) + PG_RETURN_BOOL(true); + else + PG_RETURN_BOOL(false); } Datum @@ -1123,14 +1142,17 @@ line_perp(PG_FUNCTION_ARGS) LINE *l1 = PG_GETARG_LINE_P(0); LINE *l2 = PG_GETARG_LINE_P(1); + if (unlikely(isnan(l1->C) || isnan(l2->C))) + return false; + if (FPzero(l1->A)) - PG_RETURN_BOOL(FPzero(l2->B)); + PG_RETURN_BOOL(FPzero(l2->B) && !isnan(l1->B) && !isnan(l2->A)); if (FPzero(l2->A)) - PG_RETURN_BOOL(FPzero(l1->B)); + PG_RETURN_BOOL(FPzero(l1->B) && !isnan(l2->B) && !isnan(l1->A)); if (FPzero(l1->B)) - PG_RETURN_BOOL(FPzero(l2->A)); + PG_RETURN_BOOL(FPzero(l2->A) && !isnan(l1->A) && !isnan(l2->B)); if (FPzero(l2->B)) - PG_RETURN_BOOL(FPzero(l1->A)); + PG_RETURN_BOOL(FPzero(l1->A) && !isnan(l2->A) && !isnan(l1->B)); PG_RETURN_BOOL(FPeq(float8_div(float8_mul(l1->A, l2->A), float8_mul(l1->B, l2->B)), -1.0)); @@ -1141,7 +1163,7 @@ line_vertical(PG_FUNCTION_ARGS) { LINE *line = PG_GETARG_LINE_P(0); - PG_RETURN_BOOL(FPzero(line->B)); + PG_RETURN_BOOL(FPzero(line->B) && !isnan(line->A) && !isnan(line->C)); } Datum @@ -1149,7 +1171,7 @@ line_horizontal(PG_FUNCTION_ARGS) { LINE *line = PG_GETARG_LINE_P(0); - PG_RETURN_BOOL(FPzero(line->A)); + PG_RETURN_BOOL(FPzero(line->A) && !isnan(line->B) && !isnan(line->C)); } @@ -1195,9 +1217,19 @@ static inline float8 line_sl(LINE *line) { if (FPzero(line->A)) + { + /* C is likely to be NaN than B */ + if (unlikely(isnan(line->C) || isnan(line->B))) + return get_float8_nan(); return 0.0; + } if (FPzero(line->B)) + { + /* C is likely to be NaN than A */ + if (unlikely(isnan(line->C) || isnan(line->A))) + return get_float8_nan(); return DBL_MAX; + } return float8_div(line->A, -line->B); } @@ -1209,9 +1241,19 @@ static inline float8 line_invsl(LINE *line) { if (FPzero(line->A)) + { + /* C is likely to be NaN than B */ + if (unlikely(isnan(line->C) || isnan(line->B))) + return get_float8_nan(); return DBL_MAX; + } if (FPzero(line->B)) + { + /* C is likely to be NaN than A */ + if (unlikely(isnan(line->C) || isnan(line->A))) + return get_float8_nan(); return 0.0; + } return float8_div(line->B, line->A); } @@ -1224,14 +1266,23 @@ line_distance(PG_FUNCTION_ARGS) { LINE *l1 = PG_GETARG_LINE_P(0); LINE *l2 = PG_GETARG_LINE_P(1); + Point xp; float8 ratio; - if (line_interpt_line(NULL, l1, l2)) /* intersecting? */ + if (line_interpt_line(&xp, l1, l2)) /* intersecting? */ + { + /* return NaN if NaN was involved */ + if (isnan(xp.x) || isnan(xp.y)) + PG_RETURN_FLOAT8(get_float8_nan()); + PG_RETURN_FLOAT8(0.0); + } - if (!FPzero(l1->A) && !isnan(l1->A) && !FPzero(l2->A) && !isnan(l2->A)) + if (unlikely(isnan(l1->A) || isnan(l1->B) || isnan(l2->A) || isnan(l2->B))) + ratio = get_float8_nan(); + else if (!FPzero(l1->A) && !FPzero(l2->A)) ratio = float8_div(l1->A, l2->A); - else if (!FPzero(l1->B) && !isnan(l1->B) && !FPzero(l2->B) && !isnan(l2->B)) + else if (!FPzero(l1->B) && !FPzero(l2->B)) ratio = float8_div(l1->B, l2->B); else ratio = 1.0; @@ -1626,18 +1677,32 @@ path_inter(PG_FUNCTION_ARGS) b1.high.y = b1.low.y = p1->p[0].y; for (i = 1; i < p1->npts; i++) { + /* float8_min conceals NaN, check separately for NaNs */ b1.high.x = float8_max(p1->p[i].x, b1.high.x); b1.high.y = float8_max(p1->p[i].y, b1.high.y); - b1.low.x = float8_min(p1->p[i].x, b1.low.x); + if (unlikely(isnan(p1->p[i].x))) + b1.low.x = p1->p[i].x; + else + b1.low.x = float8_min(p1->p[i].x, b1.low.x); + if (unlikely(isnan(p1->p[i].y))) + b1.low.x = p1->p[i].y; + else b1.low.y = float8_min(p1->p[i].y, b1.low.y); } b2.high.x = b2.low.x = p2->p[0].x; b2.high.y = b2.low.y = p2->p[0].y; for (i = 1; i < p2->npts; i++) { + /* float8_min conceals NaN, check separately for NaNs */ b2.high.x = float8_max(p2->p[i].x, b2.high.x); b2.high.y = float8_max(p2->p[i].y, b2.high.y); - b2.low.x = float8_min(p2->p[i].x, b2.low.x); + if (unlikely(isnan(p2->p[i].x))) + b2.low.x = p2->p[i].x; + else + b2.low.x = float8_min(p2->p[i].x, b2.low.x); + if (unlikely(isnan(p2->p[i].y))) + b2.low.y = p1->p[i].y; + else b2.low.y = float8_min(p2->p[i].y, b2.low.y); } if (!box_ov(&b1, &b2)) @@ -1728,6 +1793,11 @@ path_distance(PG_FUNCTION_ARGS) statlseg_construct(&seg2, &p2->p[jprev], &p2->p[j]); tmp = lseg_closept_lseg(NULL, &seg1, &seg2); + + /* return NULL immediately if NaN is involved */ + if (isnan(tmp)) + PG_RETURN_NULL(); + if (!have_min || float8_lt(tmp, min)) { min = tmp; @@ -1980,9 +2050,17 @@ static inline float8 point_sl(Point *pt1, Point *pt2) { if (FPeq(pt1->x, pt2->x)) + { + if (unlikely(isnan(pt1->y) || isnan(pt2->y))) + return get_float8_nan(); return DBL_MAX; + } if (FPeq(pt1->y, pt2->y)) + { + if (unlikely(isnan(pt1->x) || isnan(pt2->x))) + return get_float8_nan(); return 0.0; + } return float8_div(float8_mi(pt1->y, pt2->y), float8_mi(pt1->x, pt2->x)); } @@ -1996,9 +2074,17 @@ static inline float8 point_invsl(Point *pt1, Point *pt2) { if (FPeq(pt1->x, pt2->x)) + { + if (unlikely(isnan(pt1->y) || isnan(pt2->y))) + return get_float8_nan(); return 0.0; + } if (FPeq(pt1->y, pt2->y)) + { + if (unlikely(isnan(pt1->x) || isnan(pt2->x))) + return get_float8_nan(); return DBL_MAX; + } return float8_div(float8_mi(pt1->x, pt2->x), float8_mi(pt2->y, pt1->y)); } @@ -2414,6 +2500,11 @@ dist_ppath_internal(Point *pt, PATH *path) statlseg_construct(&lseg, &path->p[iprev], &path->p[i]); tmp = lseg_closept_point(NULL, &lseg, pt); + + /* return NaN if NaN is involved */ + if (unlikely(isnan(tmp))) + return tmp; + if (!have_min || float8_lt(tmp, result)) { result = tmp; @@ -2645,6 +2736,8 @@ dist_ppoly_internal(Point *pt, POLYGON *poly) d = lseg_closept_point(NULL, &seg, pt); if (float8_lt(d, result)) result = d; + else if (unlikely(isnan(d))) + return get_float8_nan(); } return result; @@ -2674,7 +2767,8 @@ lseg_interpt_line(Point *result, LSEG *lseg, LINE *line) * intersection point, we are done. */ line_construct(&tmp, &lseg->p[0], lseg_sl(lseg)); - if (!line_interpt_line(&interpt, &tmp, line)) + if (!line_interpt_line(&interpt, &tmp, line) || + unlikely(isnan(interpt.x) || isnan(interpt.y))) return false; /* @@ -2803,6 +2897,7 @@ lseg_closept_lseg(Point *result, LSEG *on_lseg, LSEG *to_lseg) Point point; float8 dist, d; + bool isnan = false; /* First, we handle the case when the line segments are intersecting. */ if (lseg_interpt_lseg(result, on_lseg, to_lseg)) @@ -2814,6 +2909,7 @@ lseg_closept_lseg(Point *result, LSEG *on_lseg, LSEG *to_lseg) */ dist = lseg_closept_point(result, on_lseg, &to_lseg->p[0]); d = lseg_closept_point(&point, on_lseg, &to_lseg->p[1]); + isnan |= (isnan(dist) || isnan(d)); if (float8_lt(d, dist)) { dist = d; @@ -2823,6 +2919,7 @@ lseg_closept_lseg(Point *result, LSEG *on_lseg, LSEG *to_lseg) /* The closest point can still be one of the endpoints, so we test them. */ d = lseg_closept_point(NULL, to_lseg, &on_lseg->p[0]); + isnan |= isnan(d); if (float8_lt(d, dist)) { dist = d; @@ -2830,6 +2927,7 @@ lseg_closept_lseg(Point *result, LSEG *on_lseg, LSEG *to_lseg) *result = on_lseg->p[0]; } d = lseg_closept_point(NULL, to_lseg, &on_lseg->p[1]); + isnan |= isnan(d); if (float8_lt(d, dist)) { dist = d; @@ -2837,6 +2935,12 @@ lseg_closept_lseg(Point *result, LSEG *on_lseg, LSEG *to_lseg) *result = on_lseg->p[1]; } + if (unlikely(isnan)) + { + if (result != NULL) + result->x = result->y = get_float8_nan(); + return get_float8_nan(); + } return dist; } @@ -2873,6 +2977,7 @@ box_closept_point(Point *result, BOX *box, Point *pt) Point point, closept; LSEG lseg; + bool isnan = false; if (box_contain_point(box, pt)) { @@ -2887,9 +2992,10 @@ box_closept_point(Point *result, BOX *box, Point *pt) point.y = box->high.y; statlseg_construct(&lseg, &box->low, &point); dist = lseg_closept_point(result, &lseg, pt); - + isnan |= isnan(dist); statlseg_construct(&lseg, &box->high, &point); d = lseg_closept_point(&closept, &lseg, pt); + isnan |= isnan(d); if (float8_lt(d, dist)) { dist = d; @@ -2901,6 +3007,7 @@ box_closept_point(Point *result, BOX *box, Point *pt) point.y = box->low.y; statlseg_construct(&lseg, &box->low, &point); d = lseg_closept_point(&closept, &lseg, pt); + isnan |= isnan(d); if (float8_lt(d, dist)) { dist = d; @@ -2910,6 +3017,7 @@ box_closept_point(Point *result, BOX *box, Point *pt) statlseg_construct(&lseg, &box->high, &point); d = lseg_closept_point(&closept, &lseg, pt); + isnan |= isnan(d); if (float8_lt(d, dist)) { dist = d; @@ -2917,6 +3025,13 @@ box_closept_point(Point *result, BOX *box, Point *pt) *result = closept; } + if (unlikely(isnan)) + { + if (result != NULL) + result->x = result->y = get_float8_nan(); + return get_float8_nan(); + } + return dist; } @@ -2988,6 +3103,7 @@ close_sl(PG_FUNCTION_ARGS) * even because of simple roundoff issues, there may not be a single closest * point. We are likely to set the result to the second endpoint in these * cases. + * Returns Nan and stores {NaN,NaN} to result if NaN is involved, */ static float8 lseg_closept_line(Point *result, LSEG *lseg, LINE *line) @@ -3010,6 +3126,14 @@ lseg_closept_line(Point *result, LSEG *lseg, LINE *line) } else { + /* return NaN if any of the two is NaN */ + if (unlikely(isnan(dist1) || isnan(dist2))) + { + if (result != NULL) + result->x = result->y = get_float8_nan(); + return get_float8_nan(); + } + if (result != NULL) *result = lseg->p[1]; @@ -3436,6 +3560,12 @@ make_bound_box(POLYGON *poly) y2 = y1 = poly->p[0].y; for (i = 1; i < poly->npts; i++) { + /* if NaN found, make an invalid boundbox */ + if (unlikely(isnan(poly->p[i].x) || isnan(poly->p[i].y))) + { + x1 = x2 = y1 = y2 = get_float8_nan(); + break; + } if (float8_lt(poly->p[i].x, x1)) x1 = poly->p[i].x; if (float8_gt(poly->p[i].x, x2)) @@ -3911,6 +4041,11 @@ lseg_inside_poly(Point *a, Point *b, POLYGON *poly, int start) t.p[1] = *b; s.p[0] = poly->p[(start == 0) ? (poly->npts - 1) : (start - 1)]; + /* Fast path. Check against boundbox. Also checks NaNs. */ + if (!box_contain_point(&poly->boundbox, a) || + !box_contain_point(&poly->boundbox, b)) + return false; + for (i = start; i < poly->npts && res; i++) { Point interpt; @@ -5350,6 +5485,10 @@ point_inside(Point *p, int npts, Point *plist) x0 = float8_mi(plist[0].x, p->x); y0 = float8_mi(plist[0].y, p->y); + /* NaN makes the point cannot be inside the polygon */ + if (unlikely(isnan(x0) || isnan(y0) || isnan(p->x) || isnan(p->y))) + return 0; + prev_x = x0; prev_y = y0; /* loop over polygon points and aggregate total_cross */ @@ -5359,6 +5498,10 @@ point_inside(Point *p, int npts, Point *plist) x = float8_mi(plist[i].x, p->x); y = float8_mi(plist[i].y, p->y); + /* NaN makes the point cannot be inside the polygon */ + if (unlikely(isnan(x) || isnan(y))) + return 0; + /* compute previous to current point crossing */ if ((cross = lseg_crossing(x, y, prev_x, prev_y)) == POINT_ON_POLYGON) return 2; @@ -5517,12 +5660,12 @@ pg_hypot(float8 x, float8 y) result; /* Handle INF and NaN properly */ - if (isinf(x) || isinf(y)) + if (unlikely(isnan(x) || isnan(y))) + return get_float8_nan(); + + if (unlikely(isinf(x) || isinf(y))) return get_float8_infinity(); - if (isnan(x) || isnan(y)) - return get_float8_nan(); - /* Else, drop any minus signs */ x = fabs(x); y = fabs(y); diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index e3e6634d7e..5382259159 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -139,7 +139,7 @@ SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; count ------- - 5 + 4 (1 row) SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out index 5b9d37030f..cad0716915 100644 --- a/src/test/regress/expected/geometry.out +++ b/src/test/regress/expected/geometry.out @@ -494,9 +494,9 @@ SELECT p.f1, l.s, p.f1 <-> l.s AS dist_pl, l.s <-> p.f1 AS dist_lp FROM POINT_TB (1e+300,Infinity) | {0,-1,5} | Infinity | Infinity (1e+300,Infinity) | {1,0,5} | NaN | NaN (1e+300,Infinity) | {0,3,0} | Infinity | Infinity - (1e+300,Infinity) | {1,-1,0} | Infinity | Infinity - (1e+300,Infinity) | {-0.4,-1,-6} | Infinity | Infinity - (1e+300,Infinity) | {-0.000184615384615,-1,15.3846153846} | Infinity | Infinity + (1e+300,Infinity) | {1,-1,0} | NaN | NaN + (1e+300,Infinity) | {-0.4,-1,-6} | NaN | NaN + (1e+300,Infinity) | {-0.000184615384615,-1,15.3846153846} | NaN | NaN (1e+300,Infinity) | {3,NaN,5} | NaN | NaN (1e+300,Infinity) | {NaN,NaN,NaN} | NaN | NaN (1e+300,Infinity) | {0,-1,3} | Infinity | Infinity @@ -580,9 +580,9 @@ SELECT p.f1, l.s, p.f1 <-> l.s AS dist_ps, l.s <-> p.f1 AS dist_sp FROM POINT_TB (1e+300,Infinity) | [(10,-10),(-3,-4)] | Infinity | Infinity (1e+300,Infinity) | [(-1000000,200),(300000,-40)] | Infinity | Infinity (1e+300,Infinity) | [(11,22),(33,44)] | Infinity | Infinity - (1e+300,Infinity) | [(-10,2),(-10,3)] | Infinity | Infinity + (1e+300,Infinity) | [(-10,2),(-10,3)] | NaN | NaN (1e+300,Infinity) | [(0,-20),(30,-20)] | Infinity | Infinity - (1e+300,Infinity) | [(NaN,1),(NaN,90)] | Infinity | Infinity + (1e+300,Infinity) | [(NaN,1),(NaN,90)] | NaN | NaN (NaN,NaN) | [(1,2),(3,4)] | NaN | NaN (NaN,NaN) | [(0,0),(6,6)] | NaN | NaN (NaN,NaN) | [(10,-10),(-3,-4)] | NaN | NaN @@ -635,11 +635,11 @@ SELECT p.f1, b.f1, p.f1 <-> b.f1 AS dist_pb, b.f1 <-> p.f1 AS dist_bp FROM POINT (1e-300,-1e-300) | (-2,2),(-8,-10) | 2 | 2 (1e-300,-1e-300) | (2.5,3.5),(2.5,2.5) | 3.53553390593 | 3.53553390593 (1e-300,-1e-300) | (3,3),(3,3) | 4.24264068712 | 4.24264068712 - (1e+300,Infinity) | (2,2),(0,0) | Infinity | Infinity - (1e+300,Infinity) | (3,3),(1,1) | Infinity | Infinity - (1e+300,Infinity) | (-2,2),(-8,-10) | Infinity | Infinity - (1e+300,Infinity) | (2.5,3.5),(2.5,2.5) | Infinity | Infinity - (1e+300,Infinity) | (3,3),(3,3) | Infinity | Infinity + (1e+300,Infinity) | (2,2),(0,0) | NaN | NaN + (1e+300,Infinity) | (3,3),(1,1) | NaN | NaN + (1e+300,Infinity) | (-2,2),(-8,-10) | NaN | NaN + (1e+300,Infinity) | (2.5,3.5),(2.5,2.5) | NaN | NaN + (1e+300,Infinity) | (3,3),(3,3) | NaN | NaN (NaN,NaN) | (2,2),(0,0) | NaN | NaN (NaN,NaN) | (3,3),(1,1) | NaN | NaN (NaN,NaN) | (-2,2),(-8,-10) | NaN | NaN @@ -716,7 +716,7 @@ SELECT p.f1, p1.f1, p.f1 <-> p1.f1 AS dist_ppath, p1.f1 <-> p.f1 AS dist_pathp F (1e+300,Infinity) | ((1,2),(3,4)) | Infinity | Infinity (1e+300,Infinity) | ((1,2),(3,4)) | Infinity | Infinity (1e+300,Infinity) | [(1,2),(3,4)] | Infinity | Infinity - (1e+300,Infinity) | ((10,20)) | Infinity | Infinity + (1e+300,Infinity) | ((10,20)) | NaN | NaN (1e+300,Infinity) | [(11,12),(13,14)] | Infinity | Infinity (1e+300,Infinity) | ((11,12),(13,14)) | Infinity | Infinity (NaN,NaN) | [(1,2),(3,4)] | NaN | NaN @@ -785,20 +785,20 @@ SELECT p.f1, p1.f1, p.f1 <-> p1.f1 AS dist_ppoly, p1.f1 <-> p.f1 AS dist_polyp F (1e-300,-1e-300) | ((1,2),(7,8),(5,6),(3,-4)) | 1.58113883008 | 1.58113883008 (1e-300,-1e-300) | ((0,0)) | 0 | 0 (1e-300,-1e-300) | ((0,1),(0,1)) | 1 | 1 - (1e+300,Infinity) | ((2,0),(2,4),(0,0)) | Infinity | Infinity - (1e+300,Infinity) | ((3,1),(3,3),(1,0)) | Infinity | Infinity + (1e+300,Infinity) | ((2,0),(2,4),(0,0)) | NaN | NaN + (1e+300,Infinity) | ((3,1),(3,3),(1,0)) | NaN | NaN (1e+300,Infinity) | ((1,2),(3,4),(5,6),(7,8)) | Infinity | Infinity (1e+300,Infinity) | ((7,8),(5,6),(3,4),(1,2)) | Infinity | Infinity (1e+300,Infinity) | ((1,2),(7,8),(5,6),(3,-4)) | Infinity | Infinity - (1e+300,Infinity) | ((0,0)) | Infinity | Infinity - (1e+300,Infinity) | ((0,1),(0,1)) | Infinity | Infinity - (NaN,NaN) | ((2,0),(2,4),(0,0)) | 0 | 0 - (NaN,NaN) | ((3,1),(3,3),(1,0)) | 0 | 0 - (NaN,NaN) | ((1,2),(3,4),(5,6),(7,8)) | 0 | 0 - (NaN,NaN) | ((7,8),(5,6),(3,4),(1,2)) | 0 | 0 - (NaN,NaN) | ((1,2),(7,8),(5,6),(3,-4)) | 0 | 0 - (NaN,NaN) | ((0,0)) | 0 | 0 - (NaN,NaN) | ((0,1),(0,1)) | 0 | 0 + (1e+300,Infinity) | ((0,0)) | NaN | NaN + (1e+300,Infinity) | ((0,1),(0,1)) | NaN | NaN + (NaN,NaN) | ((2,0),(2,4),(0,0)) | NaN | NaN + (NaN,NaN) | ((3,1),(3,3),(1,0)) | NaN | NaN + (NaN,NaN) | ((1,2),(3,4),(5,6),(7,8)) | NaN | NaN + (NaN,NaN) | ((7,8),(5,6),(3,4),(1,2)) | NaN | NaN + (NaN,NaN) | ((1,2),(7,8),(5,6),(3,-4)) | NaN | NaN + (NaN,NaN) | ((0,0)) | NaN | NaN + (NaN,NaN) | ((0,1),(0,1)) | NaN | NaN (10,10) | ((2,0),(2,4),(0,0)) | 10 | 10 (10,10) | ((3,1),(3,3),(1,0)) | 9.89949493661 | 9.89949493661 (10,10) | ((1,2),(3,4),(5,6),(7,8)) | 3.60555127546 | 3.60555127546 @@ -875,9 +875,9 @@ SELECT p.f1, l.s, p.f1 ## l.s FROM POINT_TBL p, LINE_TBL l; (1e+300,Infinity) | {0,-1,5} | (1e+300,5) (1e+300,Infinity) | {1,0,5} | (1e+300,Infinity) | {0,3,0} | (1e+300,0) - (1e+300,Infinity) | {1,-1,0} | (Infinity,NaN) - (1e+300,Infinity) | {-0.4,-1,-6} | (-Infinity,NaN) - (1e+300,Infinity) | {-0.000184615384615,-1,15.3846153846} | (-Infinity,NaN) + (1e+300,Infinity) | {1,-1,0} | + (1e+300,Infinity) | {-0.4,-1,-6} | + (1e+300,Infinity) | {-0.000184615384615,-1,15.3846153846} | (1e+300,Infinity) | {3,NaN,5} | (1e+300,Infinity) | {NaN,NaN,NaN} | (1e+300,Infinity) | {0,-1,3} | (1e+300,3) @@ -961,9 +961,9 @@ SELECT p.f1, l.s, p.f1 ## l.s FROM POINT_TBL p, LSEG_TBL l; (1e+300,Infinity) | [(10,-10),(-3,-4)] | (-3,-4) (1e+300,Infinity) | [(-1000000,200),(300000,-40)] | (300000,-40) (1e+300,Infinity) | [(11,22),(33,44)] | (33,44) - (1e+300,Infinity) | [(-10,2),(-10,3)] | (-10,3) + (1e+300,Infinity) | [(-10,2),(-10,3)] | (1e+300,Infinity) | [(0,-20),(30,-20)] | (30,-20) - (1e+300,Infinity) | [(NaN,1),(NaN,90)] | (NaN,90) + (1e+300,Infinity) | [(NaN,1),(NaN,90)] | (NaN,NaN) | [(1,2),(3,4)] | (NaN,NaN) | [(0,0),(6,6)] | (NaN,NaN) | [(10,-10),(-3,-4)] | @@ -1016,11 +1016,11 @@ SELECT p.f1, b.f1, p.f1 ## b.f1 FROM POINT_TBL p, BOX_TBL b; (1e-300,-1e-300) | (-2,2),(-8,-10) | (-2,-1e-300) (1e-300,-1e-300) | (2.5,3.5),(2.5,2.5) | (2.5,2.5) (1e-300,-1e-300) | (3,3),(3,3) | (3,3) - (1e+300,Infinity) | (2,2),(0,0) | (0,2) - (1e+300,Infinity) | (3,3),(1,1) | (1,3) - (1e+300,Infinity) | (-2,2),(-8,-10) | (-8,2) - (1e+300,Infinity) | (2.5,3.5),(2.5,2.5) | (2.5,3.5) - (1e+300,Infinity) | (3,3),(3,3) | (3,3) + (1e+300,Infinity) | (2,2),(0,0) | + (1e+300,Infinity) | (3,3),(1,1) | + (1e+300,Infinity) | (-2,2),(-8,-10) | + (1e+300,Infinity) | (2.5,3.5),(2.5,2.5) | + (1e+300,Infinity) | (3,3),(3,3) | (NaN,NaN) | (2,2),(0,0) | (NaN,NaN) | (3,3),(1,1) | (NaN,NaN) | (-2,2),(-8,-10) | @@ -1060,12 +1060,7 @@ SELECT p.f1, p1.f1 FROM POINT_TBL p, PATH_TBL p1 WHERE p.f1 <@ p1.f1; ------------------+--------------------------- (0,0) | [(0,0),(3,0),(4,5),(1,6)] (1e-300,-1e-300) | [(0,0),(3,0),(4,5),(1,6)] - (NaN,NaN) | ((1,2),(3,4)) - (NaN,NaN) | ((1,2),(3,4)) - (NaN,NaN) | ((1,2),(3,4)) - (NaN,NaN) | ((10,20)) - (NaN,NaN) | ((11,12),(13,14)) -(7 rows) +(2 rows) -- -- Lines @@ -1153,8 +1148,8 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2; {0,-1,5} | {1,-1,0} | 0 {0,-1,5} | {-0.4,-1,-6} | 0 {0,-1,5} | {-0.000184615384615,-1,15.3846153846} | 0 - {0,-1,5} | {3,NaN,5} | 0 - {0,-1,5} | {NaN,NaN,NaN} | 0 + {0,-1,5} | {3,NaN,5} | NaN + {0,-1,5} | {NaN,NaN,NaN} | NaN {0,-1,5} | {0,-1,3} | 2 {0,-1,5} | {-1,0,3} | 0 {1,0,5} | {0,-1,5} | 0 @@ -1163,8 +1158,8 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2; {1,0,5} | {1,-1,0} | 0 {1,0,5} | {-0.4,-1,-6} | 0 {1,0,5} | {-0.000184615384615,-1,15.3846153846} | 0 - {1,0,5} | {3,NaN,5} | 0 - {1,0,5} | {NaN,NaN,NaN} | 0 + {1,0,5} | {3,NaN,5} | NaN + {1,0,5} | {NaN,NaN,NaN} | NaN {1,0,5} | {0,-1,3} | 0 {1,0,5} | {-1,0,3} | 8 {0,3,0} | {0,-1,5} | 5 @@ -1173,8 +1168,8 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2; {0,3,0} | {1,-1,0} | 0 {0,3,0} | {-0.4,-1,-6} | 0 {0,3,0} | {-0.000184615384615,-1,15.3846153846} | 0 - {0,3,0} | {3,NaN,5} | 0 - {0,3,0} | {NaN,NaN,NaN} | 0 + {0,3,0} | {3,NaN,5} | NaN + {0,3,0} | {NaN,NaN,NaN} | NaN {0,3,0} | {0,-1,3} | 3 {0,3,0} | {-1,0,3} | 0 {1,-1,0} | {0,-1,5} | 0 @@ -1183,8 +1178,8 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2; {1,-1,0} | {1,-1,0} | 0 {1,-1,0} | {-0.4,-1,-6} | 0 {1,-1,0} | {-0.000184615384615,-1,15.3846153846} | 0 - {1,-1,0} | {3,NaN,5} | 0 - {1,-1,0} | {NaN,NaN,NaN} | 0 + {1,-1,0} | {3,NaN,5} | NaN + {1,-1,0} | {NaN,NaN,NaN} | NaN {1,-1,0} | {0,-1,3} | 0 {1,-1,0} | {-1,0,3} | 0 {-0.4,-1,-6} | {0,-1,5} | 0 @@ -1193,8 +1188,8 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2; {-0.4,-1,-6} | {1,-1,0} | 0 {-0.4,-1,-6} | {-0.4,-1,-6} | 0 {-0.4,-1,-6} | {-0.000184615384615,-1,15.3846153846} | 0 - {-0.4,-1,-6} | {3,NaN,5} | 0 - {-0.4,-1,-6} | {NaN,NaN,NaN} | 0 + {-0.4,-1,-6} | {3,NaN,5} | NaN + {-0.4,-1,-6} | {NaN,NaN,NaN} | NaN {-0.4,-1,-6} | {0,-1,3} | 0 {-0.4,-1,-6} | {-1,0,3} | 0 {-0.000184615384615,-1,15.3846153846} | {0,-1,5} | 0 @@ -1203,38 +1198,38 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2; {-0.000184615384615,-1,15.3846153846} | {1,-1,0} | 0 {-0.000184615384615,-1,15.3846153846} | {-0.4,-1,-6} | 0 {-0.000184615384615,-1,15.3846153846} | {-0.000184615384615,-1,15.3846153846} | 0 - {-0.000184615384615,-1,15.3846153846} | {3,NaN,5} | 0 - {-0.000184615384615,-1,15.3846153846} | {NaN,NaN,NaN} | 0 + {-0.000184615384615,-1,15.3846153846} | {3,NaN,5} | NaN + {-0.000184615384615,-1,15.3846153846} | {NaN,NaN,NaN} | NaN {-0.000184615384615,-1,15.3846153846} | {0,-1,3} | 0 {-0.000184615384615,-1,15.3846153846} | {-1,0,3} | 0 - {3,NaN,5} | {0,-1,5} | 0 - {3,NaN,5} | {1,0,5} | 0 - {3,NaN,5} | {0,3,0} | 0 - {3,NaN,5} | {1,-1,0} | 0 - {3,NaN,5} | {-0.4,-1,-6} | 0 - {3,NaN,5} | {-0.000184615384615,-1,15.3846153846} | 0 - {3,NaN,5} | {3,NaN,5} | 0 - {3,NaN,5} | {NaN,NaN,NaN} | 0 - {3,NaN,5} | {0,-1,3} | 0 - {3,NaN,5} | {-1,0,3} | 0 - {NaN,NaN,NaN} | {0,-1,5} | 0 - {NaN,NaN,NaN} | {1,0,5} | 0 - {NaN,NaN,NaN} | {0,3,0} | 0 - {NaN,NaN,NaN} | {1,-1,0} | 0 - {NaN,NaN,NaN} | {-0.4,-1,-6} | 0 - {NaN,NaN,NaN} | {-0.000184615384615,-1,15.3846153846} | 0 - {NaN,NaN,NaN} | {3,NaN,5} | 0 - {NaN,NaN,NaN} | {NaN,NaN,NaN} | 0 - {NaN,NaN,NaN} | {0,-1,3} | 0 - {NaN,NaN,NaN} | {-1,0,3} | 0 + {3,NaN,5} | {0,-1,5} | NaN + {3,NaN,5} | {1,0,5} | NaN + {3,NaN,5} | {0,3,0} | NaN + {3,NaN,5} | {1,-1,0} | NaN + {3,NaN,5} | {-0.4,-1,-6} | NaN + {3,NaN,5} | {-0.000184615384615,-1,15.3846153846} | NaN + {3,NaN,5} | {3,NaN,5} | NaN + {3,NaN,5} | {NaN,NaN,NaN} | NaN + {3,NaN,5} | {0,-1,3} | NaN + {3,NaN,5} | {-1,0,3} | NaN + {NaN,NaN,NaN} | {0,-1,5} | NaN + {NaN,NaN,NaN} | {1,0,5} | NaN + {NaN,NaN,NaN} | {0,3,0} | NaN + {NaN,NaN,NaN} | {1,-1,0} | NaN + {NaN,NaN,NaN} | {-0.4,-1,-6} | NaN + {NaN,NaN,NaN} | {-0.000184615384615,-1,15.3846153846} | NaN + {NaN,NaN,NaN} | {3,NaN,5} | NaN + {NaN,NaN,NaN} | {NaN,NaN,NaN} | NaN + {NaN,NaN,NaN} | {0,-1,3} | NaN + {NaN,NaN,NaN} | {-1,0,3} | NaN {0,-1,3} | {0,-1,5} | 2 {0,-1,3} | {1,0,5} | 0 {0,-1,3} | {0,3,0} | 3 {0,-1,3} | {1,-1,0} | 0 {0,-1,3} | {-0.4,-1,-6} | 0 {0,-1,3} | {-0.000184615384615,-1,15.3846153846} | 0 - {0,-1,3} | {3,NaN,5} | 0 - {0,-1,3} | {NaN,NaN,NaN} | 0 + {0,-1,3} | {3,NaN,5} | NaN + {0,-1,3} | {NaN,NaN,NaN} | NaN {0,-1,3} | {0,-1,3} | 0 {0,-1,3} | {-1,0,3} | 0 {-1,0,3} | {0,-1,5} | 0 @@ -1243,8 +1238,8 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2; {-1,0,3} | {1,-1,0} | 0 {-1,0,3} | {-0.4,-1,-6} | 0 {-1,0,3} | {-0.000184615384615,-1,15.3846153846} | 0 - {-1,0,3} | {3,NaN,5} | 0 - {-1,0,3} | {NaN,NaN,NaN} | 0 + {-1,0,3} | {3,NaN,5} | NaN + {-1,0,3} | {NaN,NaN,NaN} | NaN {-1,0,3} | {0,-1,3} | 0 {-1,0,3} | {-1,0,3} | 0 (100 rows) @@ -1262,31 +1257,23 @@ SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s ?# l2.s; {0,-1,5} | {1,-1,0} {0,-1,5} | {-0.4,-1,-6} {0,-1,5} | {-0.000184615384615,-1,15.3846153846} - {0,-1,5} | {3,NaN,5} - {0,-1,5} | {NaN,NaN,NaN} {0,-1,5} | {-1,0,3} {1,0,5} | {0,-1,5} {1,0,5} | {0,3,0} {1,0,5} | {1,-1,0} {1,0,5} | {-0.4,-1,-6} {1,0,5} | {-0.000184615384615,-1,15.3846153846} - {1,0,5} | {3,NaN,5} - {1,0,5} | {NaN,NaN,NaN} {1,0,5} | {0,-1,3} {0,3,0} | {1,0,5} {0,3,0} | {1,-1,0} {0,3,0} | {-0.4,-1,-6} {0,3,0} | {-0.000184615384615,-1,15.3846153846} - {0,3,0} | {3,NaN,5} - {0,3,0} | {NaN,NaN,NaN} {0,3,0} | {-1,0,3} {1,-1,0} | {0,-1,5} {1,-1,0} | {1,0,5} {1,-1,0} | {0,3,0} {1,-1,0} | {-0.4,-1,-6} {1,-1,0} | {-0.000184615384615,-1,15.3846153846} - {1,-1,0} | {3,NaN,5} - {1,-1,0} | {NaN,NaN,NaN} {1,-1,0} | {0,-1,3} {1,-1,0} | {-1,0,3} {-0.4,-1,-6} | {0,-1,5} @@ -1294,8 +1281,6 @@ SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s ?# l2.s; {-0.4,-1,-6} | {0,3,0} {-0.4,-1,-6} | {1,-1,0} {-0.4,-1,-6} | {-0.000184615384615,-1,15.3846153846} - {-0.4,-1,-6} | {3,NaN,5} - {-0.4,-1,-6} | {NaN,NaN,NaN} {-0.4,-1,-6} | {0,-1,3} {-0.4,-1,-6} | {-1,0,3} {-0.000184615384615,-1,15.3846153846} | {0,-1,5} @@ -1303,46 +1288,20 @@ SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s ?# l2.s; {-0.000184615384615,-1,15.3846153846} | {0,3,0} {-0.000184615384615,-1,15.3846153846} | {1,-1,0} {-0.000184615384615,-1,15.3846153846} | {-0.4,-1,-6} - {-0.000184615384615,-1,15.3846153846} | {3,NaN,5} - {-0.000184615384615,-1,15.3846153846} | {NaN,NaN,NaN} {-0.000184615384615,-1,15.3846153846} | {0,-1,3} {-0.000184615384615,-1,15.3846153846} | {-1,0,3} - {3,NaN,5} | {0,-1,5} - {3,NaN,5} | {1,0,5} - {3,NaN,5} | {0,3,0} - {3,NaN,5} | {1,-1,0} - {3,NaN,5} | {-0.4,-1,-6} - {3,NaN,5} | {-0.000184615384615,-1,15.3846153846} - {3,NaN,5} | {3,NaN,5} - {3,NaN,5} | {NaN,NaN,NaN} - {3,NaN,5} | {0,-1,3} - {3,NaN,5} | {-1,0,3} - {NaN,NaN,NaN} | {0,-1,5} - {NaN,NaN,NaN} | {1,0,5} - {NaN,NaN,NaN} | {0,3,0} - {NaN,NaN,NaN} | {1,-1,0} - {NaN,NaN,NaN} | {-0.4,-1,-6} - {NaN,NaN,NaN} | {-0.000184615384615,-1,15.3846153846} - {NaN,NaN,NaN} | {3,NaN,5} - {NaN,NaN,NaN} | {NaN,NaN,NaN} - {NaN,NaN,NaN} | {0,-1,3} - {NaN,NaN,NaN} | {-1,0,3} {0,-1,3} | {1,0,5} {0,-1,3} | {1,-1,0} {0,-1,3} | {-0.4,-1,-6} {0,-1,3} | {-0.000184615384615,-1,15.3846153846} - {0,-1,3} | {3,NaN,5} - {0,-1,3} | {NaN,NaN,NaN} {0,-1,3} | {-1,0,3} {-1,0,3} | {0,-1,5} {-1,0,3} | {0,3,0} {-1,0,3} | {1,-1,0} {-1,0,3} | {-0.4,-1,-6} {-1,0,3} | {-0.000184615384615,-1,15.3846153846} - {-1,0,3} | {3,NaN,5} - {-1,0,3} | {NaN,NaN,NaN} {-1,0,3} | {0,-1,3} -(84 rows) +(48 rows) -- Intersect with box SELECT l.s, b.f1 FROM LINE_TBL l, BOX_TBL b WHERE l.s ?# b.f1; @@ -3467,13 +3426,13 @@ SELECT '' AS twentyfour, p.f1, poly.f1, poly.f1 @> p.f1 AS contains | (1e+300,Infinity) | ((1,2),(7,8),(5,6),(3,-4)) | f | (1e+300,Infinity) | ((0,0)) | f | (1e+300,Infinity) | ((0,1),(0,1)) | f - | (NaN,NaN) | ((2,0),(2,4),(0,0)) | t - | (NaN,NaN) | ((3,1),(3,3),(1,0)) | t - | (NaN,NaN) | ((1,2),(3,4),(5,6),(7,8)) | t - | (NaN,NaN) | ((7,8),(5,6),(3,4),(1,2)) | t - | (NaN,NaN) | ((1,2),(7,8),(5,6),(3,-4)) | t - | (NaN,NaN) | ((0,0)) | t - | (NaN,NaN) | ((0,1),(0,1)) | t + | (NaN,NaN) | ((2,0),(2,4),(0,0)) | f + | (NaN,NaN) | ((3,1),(3,3),(1,0)) | f + | (NaN,NaN) | ((1,2),(3,4),(5,6),(7,8)) | f + | (NaN,NaN) | ((7,8),(5,6),(3,4),(1,2)) | f + | (NaN,NaN) | ((1,2),(7,8),(5,6),(3,-4)) | f + | (NaN,NaN) | ((0,0)) | f + | (NaN,NaN) | ((0,1),(0,1)) | f | (10,10) | ((2,0),(2,4),(0,0)) | f | (10,10) | ((3,1),(3,3),(1,0)) | f | (10,10) | ((1,2),(3,4),(5,6),(7,8)) | f @@ -3536,13 +3495,13 @@ SELECT '' AS twentyfour, p.f1, poly.f1, p.f1 <@ poly.f1 AS contained | (1e+300,Infinity) | ((1,2),(7,8),(5,6),(3,-4)) | f | (1e+300,Infinity) | ((0,0)) | f | (1e+300,Infinity) | ((0,1),(0,1)) | f - | (NaN,NaN) | ((2,0),(2,4),(0,0)) | t - | (NaN,NaN) | ((3,1),(3,3),(1,0)) | t - | (NaN,NaN) | ((1,2),(3,4),(5,6),(7,8)) | t - | (NaN,NaN) | ((7,8),(5,6),(3,4),(1,2)) | t - | (NaN,NaN) | ((1,2),(7,8),(5,6),(3,-4)) | t - | (NaN,NaN) | ((0,0)) | t - | (NaN,NaN) | ((0,1),(0,1)) | t + | (NaN,NaN) | ((2,0),(2,4),(0,0)) | f + | (NaN,NaN) | ((3,1),(3,3),(1,0)) | f + | (NaN,NaN) | ((1,2),(3,4),(5,6),(7,8)) | f + | (NaN,NaN) | ((7,8),(5,6),(3,4),(1,2)) | f + | (NaN,NaN) | ((1,2),(7,8),(5,6),(3,-4)) | f + | (NaN,NaN) | ((0,0)) | f + | (NaN,NaN) | ((0,1),(0,1)) | f | (10,10) | ((2,0),(2,4),(0,0)) | f | (10,10) | ((3,1),(3,3),(1,0)) | f | (10,10) | ((1,2),(3,4),(5,6),(7,8)) | f -- 2.18.4